MySQL按天/周/月/季度/半年/年统计数据
1、用到的mysql函数
-
FROM_UNIXTIME(时间戳, 格式)
- 将时间戳格式成指定字符串
- 格式说明
- %Y 年, 数字, 4 位
- %m 月, 数字(01……12)
- %c 月, 数字(1……12)
- %u 星期(0……52), 这里星期一是星期的第一天
- %U 星期(0……52), 这里星期天是星期的第一天
- %d 月份中的天数, 数字(00……31)
- %e 月份中的天数, 数字(0……31)
select FROM_UNIXTIME(‘1598812205‘,‘%Y-%m-%d‘); # 2021-09-03
-
YEAR(时间字符)
-
返回时间中的年份
select YEAR(‘2021-09-03‘); # 2021
-
-
QUARTER(时间字符)
-
返回时间中的季度,1-4
select QUARTER(‘2021-09-03‘); # 3
-
-
IF(条件, 正确值, 错误值)
-
判断条件,条件满足返回正确值,条件不满足返回错误值
select IF(1=1,‘正确‘,‘错误‘); # 正确
-
-
浮点数取整
-
ceil(): 向上取整
-
floor():向下取整
-
round():四舍五入
select ceil(4.5); # 5 select floor(4.5); # 4 select round(4.4); # 4 select round(4.5); # 5
-
-
CONCAT(字符1,字符2,...)
-
拼接字符
select CONCAT(‘aa‘,‘bb‘,‘cc‘); # aabbcc
-
2、数据表
- order表
id | order_amount | add_time |
---|---|---|
1 | 16.99 | 1598806195 |
2 | 17.99 | 1598919265 |
... | ... | ... |
3、编码
3.1、按天统计
SELECT
FROM_UNIXTIME(add_time,‘%c月%e日‘) 日期,
count(*) 订单数,
sum(order_amount) 订单金额
FROM
`order`
GROUP BY
日期;
查询结果:
日期 | 订单数 | 订单金额 |
---|---|---|
8月31日 | 24 | 1377.19 |
9月1日 | 10 | 407.76 |
3.2、按周统计
SELECT
FROM_UNIXTIME(add_time,‘%Y年%u周‘) 日期,
count(*) 订单数,
sum(order_amount) 订单金额
FROM
`order`
GROUP BY
日期;
查询结果:
日期 | 订单数 | 订单金额 |
---|---|---|
2020年36周 | 34 | 1784.95 |
3.3、按月统计
SELECT
FROM_UNIXTIME(add_time,‘%Y年%c月‘) 日期,
count(*) 订单数,
sum(order_amount) 订单金额
FROM
`order`
GROUP BY
日期;
查询结果:
日期 | 订单数 | 订单金额 |
---|---|---|
2020年8月 | 24 | 1377.19 |
2020年9月 | 10 | 407.76 |
3.4、按季度统计
SELECT
CONCAT(YEAR(FROM_UNIXTIME(add_time)),‘年第‘,QUARTER(FROM_UNIXTIME(add_time)),‘季度‘) 日期,
count(*) 订单数,
sum(order_amount) 订单金额
FROM
`order`
GROUP BY
日期;
查询结果:
日期 | 订单数 | 订单金额 |
---|---|---|
2020年第3季度 | 34 | 1784.95 |
3.5、按半年统计
SELECT
CONCAT(YEAR(FROM_UNIXTIME(add_time)),‘年‘,IF(CEIL(QUARTER(FROM_UNIXTIME(add_time))/2)=1,‘上‘,‘下‘),‘半年‘) 日期,
count(*) 订单数,
sum(order_amount) 订单金额
FROM
`order`
GROUP BY
日期;
查询结果:
日期 | 订单数 | 订单金额 |
---|---|---|
2020年下半年 | 34 | 1784.95 |
3.6、按年统计
SELECT
FROM_UNIXTIME(add_time,‘%Y年‘) 日期,
count(*) 订单数,
sum(order_amount) 订单金额
FROM
`order`
GROUP BY
日期;
查询结果:
日期 | 订单数 | 订单金额 |
---|---|---|
2020年 | 34 | 1784.95 |