共计 883 个字符,预计需要花费 3 分钟才能阅读完成。
本来的 SQL 和查出的后果如下:
SELECT
biz_day,
record.amount
FROM
ods_sale_pay_record record
WHERE
project.delete_flag = 'N'
AND ( record.biz_day >= '2021-10-01' AND record.biz_day <= '2021-10-31'
OR record.biz_day >= '2022-01-01' AND record.biz_day <= '2022-01-31' )
后果:
现要求依照月份为维度汇总数据,能够应用 DATE_FORMAT 函数
批改后的 SQL 如下:
SELECT
DATE_FORMAT(biz_day,'%Y%m') months,
SUM(record.amount) 'amount'
FROM
ods_sale_pay_record record
WHERE
record.status_code = 'PAY_RECORD_PAID'
AND ( record.biz_day >= '2021-10-01' AND record.biz_day <= '2021-10-31'
OR record.biz_day >= '2022-01-01' AND record.biz_day <= '2022-01-31' )
GROUP BY
record.biz_day
后果如下:
拓展:
如果想要依照天、周、月等不同的粒度对数据进行分组统计也能够参考如下的语法:
1)按天统计:select DATE_FORMAT(biz_day,'%Y%m%d') days,SUM(record.amount) 'amount' from test group by biz_day;
2)按周统计:select DATE_FORMAT(biz_day,'%Y%u') weeks,SUM(record.amount) 'amount' from test group by biz_day;
3)按月统计:
select DATE_FORMAT(biz_day,'%Y%m') months,SUM(record.amount) 'amount' from test group by biz_day;
正文完