Làm việc với hàm tổng hợp

Ứng dụng SQL vào các bài toán thực tế

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

Hàm tổng hợp số

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

Hàm tổng hợp chuỗi

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,...

Đến lúc tổng hợp!

Ứng dụng SQL vào các bài toán thực tế