MySQL按天/周/月/季度/半年/年统计数据

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