mysql为每日报表创建自定义日期范围

myzjeezk  于 2021-07-24  发布在  Java
关注(0)|答案(1)|浏览(239)

mysql 8.0版
我有这样的数据集:

user_id        transaction_time      item_price
   1          2020-01-01 13:00:00       100 
   1          2020-01-01 18:00:00       100 
   1          2020-01-01 19:00:00       100 
   1          2020-01-01 23:00:00       100 
   1          2020-01-02 04:00:00       100 
   1          2020-01-02 09:00:00       100

通常如果我想找到每日价格的总和(价格),我会这样做

select date(transcation_time) as dt
     , sum(item_price) as sum_price
from table
group by date(transaction_time)

哪个会输出

dt       sum_price
2020-01-01     400
2020-01-02     200

但不是 2020-01-01 合计价格 2020-01-01 00:00:002020-01-01 23:59:59 我想计算一下 2020-01-01 18:00:002020-01-02 06:00:00 会被标记为 2020-01-01 所以

2020-01-01   sums prices from 2020-01-01 18:00:00 ~ 2020-01-02 06:00:00 
2020-01-02   sums prices from 2020-01-02 18:00:00 ~ 2020-01-03 06:00:00 
2020-01-03   sums prices from 2020-01-03 18:00:00 ~ 2020-01-04 06:00:00

等等。。。
那么我得到的聚合将如下所示:

dt       sum_price
2020-01-01     500
2020-01-02     100

注意,第一行没有添加,因为13:00:00不在18:00:00~06:00:00之间
我该怎么做?

bmvo0sr5

bmvo0sr51#

我想你只需要减去6个小时——或者加上18个小时:

select date(transaction_time + interval -6 hour) as dt,
       sum(item_price) as sum_price
from table
group by dt;

如果你真的只想要12个小时,那就加一个 where 条款:

select date(transaction_time + interval -6 hour) as dt,
       sum(item_price) as sum_price
from table
where time(transaction_time) >= '18:00:00' or
      time(transaction_time) < '06:00:00' 
group by dt

相关问题