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