mysql按组、时间间隔运行总计

bq3bfh9z 于 2021-07-24 发布在 Java

关注(0)|答案(2)|浏览(391)

我想要一张这样的客户订单表：

customer_id | order_date | amount
0           | 2020-03-01 | 10.00
0           | 2020-03-02 |  2.00
1           | 2020-03-02 |  5.00
1           | 2020-03-02 |  1.00
2           | 2020-03-08 |  2.00
1           | 2020-03-09 |  1.00
0           | 2020-03-10 |  1.00
0           | 2020-03-16 |  1.00

并创建一个表，按周计算累计运行总量，从最早的日期（2020-03-01、2020-03-08等）开始按7天对周进行分段。比如：

customer_id | week_0 | week_1 |  week_2
0           | 12.00  | 13.00  |  14.00 
1           |  6.00  | 7.00   |   7.00
2           |  0.00  | 2.00   |   2.00

谢谢你的帮助！

sql mysql window-functions Date pivot

来源：https://stackoverflow.com/questions/62590516/mysql-running-total-by-group-time-interval

2条答案

按热度按时间

rjjhvcjd1#

您可以使用聚合和窗口函数（这需要mysql 8.0）。将周放在行中比放在列中更容易和更具可伸缩性：

select
    customer_id,
    year_week(order_date) order_week,
    sum(sum(amount)) over(partition by customer_id order by year_week(order_date)) running_amount
from mytable
group by customer_id, year_week(order_date)
order by customer_id, year_week(order_date)

您也可以将此数据透视到列-但您需要枚举周数：

select
    customer_id,
    max(case when order_week = 202001 then running_amount end) week_01,
    max(case when order_week = 202002 then running_amount end) week_02,
    max(case when order_week = 202003 then running_amount end) week_03,
    ...
from (
    select
        customer_id,
        year_week(order_date) order_week,
        sum(sum(amount)) over(partition by customer_id order by year_week(order_date)) running_amount
    from mytable
    group by customer_id, year_week(order_date)
) t
order by customer_id

赞(0）回复(0）举报 2021-07-24

50few1ms2#

我认为您需要条件聚合——在计算第一个订单日期之后：

select customer_id,
       sum(case when order_date >= min_order_date + interval 0 day and order_date < min_order_date + interval 7 day
                then amount else 0
           end) as week_0,
       sum(case when order_date >= min_order_date + interval 7 day and order_date < min_order_date + interval 14 day
                then amount else 0
           end) as week_1,
       sum(case when order_date >= min_order_date + interval 14 day and order_date < min_order_date + interval 21 day
                then amount else 0
           end) as week_2
from (select t.*, min(order_date) over () as min_order_date
      from t
     ) t
group by customer_id;

赞(0）回复(0）举报 2021-07-24

我来回答

mysql按组、时间间隔运行总计

2条答案

相关问题

热门标签

最新问答