有一张销售记录表 t_sales,它记录了公司在某个年份的销售记录。由于一些原因,目前只能看到两个字段:month 和 quantity,它们分别对应的中文描述是月份和售额。
t_sales 的数据如下:
month quantity
------ ----------
1 100
1 300
2 200
3 300
4 400
5 300
5 500
6 600
7 600
8 600
9 600
10 700
11 800
12 900
注意,在一个月里面是可以产生多条销售记录。
现在,我们希望从这份数据中统计出「总销售额」、「月销售额」、「当前累积销售额」,统计的结果如下:
月份 销售额 总销售额 月销售额 累积销售额
------ -------- -------- --------- --------
1 100 6900 400 400
1 300 6900 400 400
2 200 6900 200 600
3 300 6900 300 900
4 400 6900 400 1300
5 300 6900 800 2100
5 500 6900 800 2100
6 600 6900 600 2700
7 600 6900 600 3300
8 600 6900 600 3900
9 600 6900 600 4500
10 700 6900 700 5200
11 800 6900 800 6000
12 900 6900 900 6900
统计总销售额可以使用 SELECT SUM(quantity) FROM t_sales
;
统计月销售额可以使用 SELECT month,SUM(quantity) FROM t_sales GROUP BY month
;
统计当前累积销售额使用:
SELECT
(SELECT
SUM(quantity)
FROM
t_sales
WHERE MONTH <= a.month) AS '累积销售额'
FROM
t_sales a
再使用原表分别去关联这些统计结果,完整的 SQL 如下:
SELECT
a.month AS '月份',
a.quantity AS '销售额',
(SELECT
SUM(quantity)
FROM
t_sales) AS '总销售额',
c.amount AS '月销售额',
(SELECT
SUM(quantity)
FROM
t_sales
WHERE MONTH <= a.month) AS '累积销售额'
FROM
t_sales a
LEFT JOIN
(SELECT
MONTH,
SUM(quantity) AS amount
FROM
t_sales
GROUP BY MONTH) c
ON c.month = a.month
如果使用窗口函数,整个 SQL 的实现会简洁很多。
用 sum(quantity)over()
统计出总销售额;
用 sum(quantity) over(PARTITION by month)
统计月销售额;
用 sum(quantity) over(order by month)
统计当前累积销售额。
具体的 SQL 如下:
SELECT
MONTH AS '月份',
quantity AS '销售额',
SUM(quantity) over () AS '总销售额',
SUM(quantity) over (PARTITION BY MONTH) AS '月销售额',
SUM(quantity) over (
ORDER BY MONTH) AS '累积销售额'
FROM
t_sales