下午网上找了3个小时,就是没有找到一个合适的.
需求:以周为维度,自定义开始结束日期,比如从周五开始,周四结束,计算数据.
SELECT FROM_DAYS(TO_DAYS(gmt_create) -MOD(TO_DAYS(gmt_create) -6, 7)) AS startWeek,FROM_DAYS(TO_DAYS(gmt_create) -MOD(TO_DAYS(gmt_create)-6 , 7)+6) AS endWork,
count(1) count
FROM table_name
WHERE XXX
GROUP BY startWeek
ORDER BY startWeek desc;
关键是这个:FROM_DAYS(TO_DAYS(gmt_create) -MOD(TO_DAYS(gmt_create) -6, 7))
结果如下:
2020-06-19 2020-06-25
2020-06-12 2020-06-18
2020-06-05 2020-06-11
2020-05-29 2020-06-04