{DAY10} SQL查询语法2

前言

我上的课程一样是郭耀仁老师在台大资讯系统训练班所开设的『SQL资料分析』

这篇文章会使用老师上课教到的观念

练习的SQL查询语法分成两个部分

1.) 条件逻辑

2.) 分组与聚合结果筛选

当我们用SELECT挑出我们想要的栏位之後

可以利用条件逻辑的CASE衍生计算栏位

搭配布林值更进阶的过滤栏位内的条件

也可以用在不同的保留字像是ORDER BY之後

当我们想要找出独一值并且排序

或是加上聚合函数用来汇总资讯

可以使用GROUP BY

或是以HAVING 筛选分组聚合结果

下面就来开始今天的练习吧

使用的资料库来源分别是：

1. IMDb.com 最高评等的电影 https://www.imdb.com/chart/top
2. NBA 官方 API https://data.nba.net/prod/v1/today.json

条件

以 CASE筛选

条件除了能够运用在 WHERE 筛选资料，还能使用 CASE
CASE的用法：

SELECT CASE WHEN condition_1 THEN result_1
            WHEN condition_2 THEN result_2
            ...
            ELSE result_else
        END AS alias;

在电影的资料集里，若是想看电影是否在2000年之後上映

SELECT title,
       release_year,
       CASE WHEN release_year >= 2000 THEN 1
            ELSE 0 END AS after_millennium
  FROM movies
 LIMIT 5;

可以看到在2000年後上映的电影在after_millennium那栏里显示1，之前的显示为0
若是想改成用true/false表示
只要改变then後的参数

SELECT title,
       release_year,
       CASE WHEN release_year >= 2000 THEN 'TRUE' 
            ELSE 'FALSE' END AS after_millennium
  FROM movies
 LIMIT 5;

可以看到从上面的1/0变为下面的true/false

如果在某个观测值发生 condition_1 与 condition_2 都判断为真的情况下，会以 result_1 表示

SELECT CASE WHEN condition_1 THEN result_1
            WHEN condition_2 THEN result_2
            ...
            ELSE result_else
        END AS alias;

CASE ＋ SELECT ＋ORDER BY

SELECT CASE WHEN condition_1 THEN result_1
            WHEN condition_2 THEN result_2
            ...
            ELSE result_else END AS alias
  FROM table_name
 ORDER BY alias;

SELECT firstName,
       lastName,
       CASE WHEN pos IN ('C', 'C-F') THEN 'Center'
            WHEN pos IN ('G', 'G-F') THEN 'Guard'
            ELSE 'Forward' END AS pos_new
  FROM players
 ORDER BY pos_new
 LIMIT 8;

分组与聚合

* GROUP BY 分组 ＝ DISTINCT ＋ ORDER BY *

用法如下

SELECT column_names
  FROM table_name
 GROUP BY column_names;

当我想筛选出独一值的球员位置并且排序独一值

SELECT DISTINCT pos AS distinct_pos
  FROM players
 ORDER BY distinct_pos;

上面的程序码使用DISTICT之後加上ORDER BY

会显示出想要的结果

SELECT pos AS distinct_pos
  FROM players
 GROUP BY pos;

在使用GROUP BY之後

可以看到得到相同结果

所以当我们想要找出独一值并且排序

就可以使用到GROUP BY

假如希望计算不同位置的球员平均身高

原先要拆成两步骤

1. 先知道有哪些锋卫位置。
2. 筛选不同锋卫位置的球员，计算平均身高。

但是到了第二部要计算不同冲锋位置的平均身高时

就会要一一计算平均身高（因为当作筛选条件栏位有很多独一值）

会非常耗时，看起来版面也会很乱

结合聚合函数与GROUP BY 可以便捷地完成分组聚合
使用方式如下

SELECT AGGREGATE_FUNCTION(column_names) AS alias
  FROM table_name
 GROUP BY column_names;

使用GROUP BY之後就可以不用分成两段的计算含有聚合函数的需求

现在一样是筛选不同位置的球员计算平均身高

SELECT pos,
       ROUND(AVG(heightMeters), 2) AS avg_height_meters
  FROM players
 GROUP BY pos;  

来计算看看不同国家球员的平均身高

SELECT country,
       ROUND(AVG(heightMeters), 2) AS avg_height_meters
  FROM players
 GROUP BY country; 

* HAVING 筛选分组聚合後的结果*

用来筛选资料的两种方式：

1. WHERE:用在观测值。
2. HAVING：用在分组聚合後的结果

SELECT AGGREGATE_FUNCTION(column_names) AS alias
  FROM table_name
 GROUP BY column_names
HAVING conditions;

当我们想筛选平均身高大於 2 公尺的 pos（锋卫位置）

就可以使用HAVING 筛选分组聚合结果

SELECT pos,
       ROUND(AVG(heightMeters), 2) AS avg_height_meters
  FROM players
 GROUP BY pos
HAVING AVG(heightMeters) >= 2;  

小结

因为写作顺序必须遵从标准 SQL 的规定

要注意保留字的使用顺序

目前已经大致处理好同个资料表内的资料查询

明天就要进阶到

处理多张有关连的资料表

这也是关联式资料表的重要之处