用SQL构建数据透视表

主要内容：CASE WHEN和开窗函数

1.简单使用group by

-- 计数
SELECT vend_id, COUNT(*) AS num_prods
FROM Products
GROUP BY vend_id;
-- 求和
SELECT country, SUM(population) AS sum_pop
FROM Table_A
GROUP BY country;

2.使用CASE WHEN和GROUP BY

-- 定性分组
SELECT SUM(population),                 -- 原本简单的SELECT FROM就求得整列的总和
CASE WHEN 'china'  THEN 'asia'          -- 但CASE WHEN设定了多个分组，故分组求和
     WHEN 'india'  THEN 'asia'
     WHEN 'japan'  THEN 'asia'
     WHEN 'usa'    THEN 'america'
     WHEN 'canada' THEN 'america'
     WHEN 'mexico' THEN 'america'
                   ELSE 'others'  END
FROM Table_A
GROUP BY
CASE WHEN 'china'  THEN 'asia'
     WHEN 'india'  THEN 'asia'
     WHEN 'japan'  THEN 'asia'
     WHEN 'usa'    THEN 'america'
     WHEN 'canada' THEN 'america'
     WHEN 'mexico' THEN 'america'
                   ELSE 'others'  END;   -- 整个CASE WHEN语句是分组依据

before
after

-- 定量分组（无图）
SELECT                                               -- 整个CASE WHEN将salary转化成了salary_class，和着COUNT(*)一起被SELECT出来
CASE WHEN salary <= 500 THEN '1'                     -- 设定区间也能用CASE WHEN
     WHEN salary > 500 AND salary <= 600  THEN '2'
     WHEN salary > 600 AND salary <= 800  THEN '3'
     WHEN salary > 800 AND salary <= 1000 THEN '4'
                                          ELSE NULL END salary_class, COUNT(*) FROM Table_A
GROUP BY
CASE WHEN salary <= 500 THEN '1'
     WHEN salary > 500 AND salary <= 600  THEN '2'
     WHEN salary > 600 AND salary <= 800  THEN '3'
     WHEN salary > 800 AND salary <= 1000 THEN '4'
                                          ELSE NULL END;


-- 二次分组（分完国家分男女）
SELECT country,
SUM(CASE WHEN sex = '1' THEN population ELSE 0 END),  --男性人口
SUM(CASE WHEN sex = '2' THEN population ELSE 0 END)   --女性人口
FROM  Table_A
GROUP BY country;

before
after

3.开窗函数

-- 现有一群人的信息，包括姓名、所在城市、年龄和工资，需查阅5000元以下的人的信息及数量。
-- 聚合函数（不能得到汇总数量，因为聚合函数分组汇总了）
SELECT city, age, COUNT(*) AS num
FROM Table_A
WHERE salary < 5000
GROUP BY city, age;


-- 开窗函数
SELECT city, age, COUNT(*) OVER() AS num -- OVER()中常添加选项以改变聚合运算的窗口范围
FROM Table_A
WHERE salary < 5000;

image.png
image.png