mysql 把date类型数据查出来后按周,按月分组

原本的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'  )

结果:
mysql 把date类型数据查出来后按周,按月分组_第1张图片
现要求按照月份为维度汇总数据,可以使用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 

结果如下:
mysql 把date类型数据查出来后按周,按月分组_第2张图片

拓展:
如果想要按照天、周、月等不同的粒度对数据进行分组统计也可以参考如下的语法:

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; 

你可能感兴趣的