使用聚合函数

将 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 应用于真实问题