MYSQL中按照季度、月份等分组

最新推荐文章于 2024-07-19 03:23:23 发布

一叶菩提_

最新推荐文章于 2024-07-19 03:23:23 发布

阅读量1.4k

点赞数

文章标签： mysql 数据库开发

本文链接：https://blog.csdn.net/qq_39577371/article/details/123658047

版权

本文介绍了如何使用MySQL进行时间分组查询，包括按季度、周和月份对数据进行统计。示例查询展示了如何结合YEAR()、QUARTER()、DATE_FORMAT()等函数来构造时间字段，并进行聚合操作，如统计总金额、新用户数和活跃用户数。

摘要由CSDN通过智能技术生成

-- 季度
select CONCAT(YEAR(stat_date),'_',quarter(stat_date)) qu,sum(total_amount) as count_amount, sum(total_new_user) as count_new_user, sum(da_active_user) as count_active_user from xxx
WHERE `stat_date` BETWEEN '2016-01-02' AND '2017-05-30' group by qu;

-- 周
select CONCAT(YEAR(stat_date),'_',DATE_FORMAT(stat_date,'%U')) weeks,sum(total_amount) as count_amount, sum(total_new_user) as count_new_user, sum(da_active_user) as count_active_user from xxx
WHERE `stat_date` BETWEEN '2016-01-02' AND '2017-05-30' group by weeks;
-- 天
select CONCAT(YEAR(stat_date),'_',DATE_FORMAT(stat_date,'%m'),'_',DATE_FORMAT(stat_date,'%d')) days, sum(total_amount) as count_amount, sum(

关注