在Oracle数据库中,通过to_char()函数来操作日期变量,而在MySQL中,则通过date_format()函数实现日期相关周期的统计。date_format()函数一共有两个参数:date参数表示日期变量,format参数表示日期格式。
如果想查看date_format()函数的具体参数及其取值情况,可以搜索引擎中自行查询。比如输入“MYsql date_format”可以看到W3school中对该函数的详细介绍。
http://www.w3school.com.cn/sql/func_date_format.asp
- 1. 按天统计
format参数的取值为’%y%m%d’,可以按天输出统计结果。
SELECT DATE_FORMAT(postDateTime,'%y年%m月%d日') as d,count(*)
FROM table
GROUP BY DATE_FORMAT(postDateTime,'%y%m%d')
ORDER BY d asc;
输出结果如下所示:
- 2. 按自然周统计
format()函数的format参数取值为’%y%u’时,可实现按年、年中的周来统计结果。如果在where条件中限制是某一年的周期,可以直接将format参数的值配置为’%u’,否则一定要用’%y%u’,不然会把不同年的第n周合并到一起而出现错乱。
SELECT DATE_FORMAT(postDateTime,'%y年%u周') as w,min(postDateTime) as st,count(*)
FROM table
GROUP BY DATE_FORMAT(postDateTime,'%y%u')
ORDER BY w asc;
输出结果如下所示:
- 3. 按月统计
format()函数的format参数值为’%y%m’时,可实现按月份输出聚合结果。
SELECT DATE_FORMAT(postDateTime,'%y年%m月') as m,count(*)
FROM table
GROUP BY DATE_FORMAT(postDateTime,'%y%m')
ORDER BY m asc
其统计结果如下所示:
- 4. 按季度统计
date_format()函数没有直接按照季节输出结果的功能,但这对于数据分析师并不是什么难事,自己利用月度聚合结果去加工以下即可:
SELECT FLOOR((DATE_FORMAT(postDateTime,'%m')-1)/3)+1 as q,min(postDateTime) as st,count(*)
FROM table
WHERE DATE_FORMAT(postDateTime,'%Y') = 2018
GROUP BY FLOOR((DATE_FORMAT(postDateTime,'%m')-1)/3)+1
ORDER BY q asc;
使用到了floor函数,根据月份去判断所属季节,并输出季节对应的起始月份。
结果如下所示:
- 5. 按年份统计
date_format()函数的format参数值为’%Y’或’%y’时可实现按年份输出统计结果。
SELECT DATE_FORMAT(postDateTime,'%Y') as y,count(*)
FROM table
GROUP BY DATE_FORMAT(postDateTime,'%Y')
ORDER BY y asc;