使用聚合函式

將 SQL 應用於真實世界問題

Dmitriy (Dima) Gorenshteyn

Lead Data Scientist, Memorial Sloan Kettering Cancer Center

GROUP BY

SELECT rating,
  AVG(replacement_cost)
FROM film
GROUP BY rating

GROUP BY

SELECT rating,
  AVG(replacement_cost)
FROM film
GROUP BY rating

GROUP BY

SELECT rating,
  AVG(replacement_cost)
FROM film
GROUP BY rating

GROUP BY

SELECT rating,
  AVG(replacement_cost)
FROM film
GROUP BY rating

GROUP BY

SELECT rating,
  AVG(replacement_cost)
FROM film
GROUP BY rating

數值型聚合函式

SELECT rating,
       AVG(replacement_cost) AS avg_cost,
       COUNT(rating) AS number_elements,
       SUM(replacement_cost) AS total_cost
FROM film
GROUP BY rating;

rating    avg_cost    number_elements    total_cost
PG-13     20.40256    223                4549.77    
R         20.23103    195                3945.05    
G         20.12483    178                3582.22    
PG        18.95907    194                3678.06

字串型聚合函式

STRING_AGG(<column>, '<separator>')

SELECT rating,
       STRING_AGG(title, ',') as films
FROM film
GROUP BY rating;

rating       films
PG-13        AIRPLANE SIERRA,ALABAMA DEVIL,...
R            AIRPORT POLLOCK,DATE SPEED,...
G            ACE GOLDFINGER,AFFAIR PREJUDICE,...

開始做聚合！

將 SQL 應用於真實世界問題