sql 按照月 周 天 每两小时统计 记录一下自己的学习

 

#按照月
SELECT a.day, date_format(a.day, '%Y-%m') month, SUM(a.openPrice) FROM date_group AS a GROUP BY  month;

#按照周
SELECT a.day, date_format(a.day, '%x-%v') week, SUM(a.lowPrice) FROM date_group AS a GROUP BY  week;

#按照天

 

SELECT DATE(day) AS date, COUNT(*) AS num
FROM date_group
#WHERE Flag = 0 AND Duration >= 300
GROUP BY date
ORDER BY date;

#每两小时
SELECT time, COUNT( * ) AS num
FROM
    (
    SELECT
        DATE_FORMAT(
            concat( date( apply_date ), ' ', HOUR ( apply_date ), ':', floor( MINUTE ( apply_date ) / 120 ) * 120),
            '%Y-%m-%d %H:%i'
        ) AS time
    FROM loan_apply
    WHERE del_flag = 0  
AND  DATE_FORMAT(apply_date,'%Y-%m-%d') = '2018-08-24'
    ) a
GROUP BY DATE_FORMAT( time, '%Y-%m-%d %H:%i' )
ORDER BY time;