sql - Redshift：在可变日期范围内构建累积和

Question

我正在努力构建一个使用日期范围动态构建累积总和的查询。

打个比方，我想计算每位客人每天订购的客房服务盘子的平均数量。以以下示例数据集为例：

客人ID	most_recent_plate_ordered_date	累积盘子排序
1	2020 年 10 月 1 日	1
1	2020 年 10 月 2 日	2
1	2020 年 10 月 4 日	3
2	2020 年 10 月 1 日	1
2	2020 年 10 月 2 日	1
3	2020 年 10 月 3 日	1
3	2020 年 10 月 4 日	2

这是我想要实现的期望输出：

日期	累积盘子排序	number_of_people
2020 年 10 月 1 日	2	2
2020 年 10 月 2 日	3	2
2020 年 10 月 3 日	4	3
2020 年 10 月 4 日	6	3

本质上，我需要构建两个数字：每人订购的最大盘子数量和每天的人数之和。我已经生成了每天的人数——这很容易。我正在努力构建一个查询，该查询可以随着日期范围的扩大而动态求和。

我能够生成查询，该查询为我提供给定日期最大值所需的数字。我的问题是将其转换为在一个查询中在所有可能的日期中生成此数字的东西。这是一个范围从 10/1 到 10/1 的示例查询：

select sum(max_cumulative_plates_ordered) from (
  select guest_id, max(cumulative_plates_ordered) as max_cumulative_plates_ordered
  from raw_data
  where most_recent_plate_ordered_date <= '2020-10-01'
  group by 1
)

有什么想法吗？感觉这是一个很容易解决的问题。

标签： sqldatetimeamazon-redshiftaggregate-functions

Answer 1

如果我理解正确，你想要：

• 在特定日期之前订购的不同人员的数量。
• cumulative_plates_ordered当天最大订单数的总和。

但是，这表明 2020-10-03 上的值实际上是 4 而不是 5。

一种方法是相关子查询：

select dte::date,
       (select count(distinct guest_id)
        from t
        where t.most_recent_place_ordered <= gs.dte
       ) as num_guests,
       (select sum(plates)
        from (select t.guest_id, max(t.cumulative_plates_ordered) as plates
              from t
              where most_recent_place_ordered <= gs.dte
              group by t.guest_id
             ) t
       ) as num_plates
from (select distinct most_recent_place_ordered as dte from t) gs;

使您的数据具有挑战性的是累积和。您可以使用lag()来获取特定日期的更改。使用这些数据，使用窗口函数和聚合获得所需的结果要简单得多：

with net as (
     select t.*,
            row_number() over (partition by guest_id order by most_recent_place_ordered) as seqnum,
            cumulative_plates_ordered - coalesce(lag(cumulative_plates_ordered) over (partition by guest_id order by most_recent_place_ordered), 0) as new_plates
      from t
     )
select most_recent_place_ordered,
       sum(sum( (seqnum = 1)::int )) over (order by most_recent_place_ordered rows between unbounded preceding and current row) as num_guests,
       sum(sum( new_plates )) over (order by most_recent_place_ordered rows between unbounded preceding and current row) as num_plates
from net
group by most_recent_place_ordered
order by most_recent_place_ordered;

这是一个 db<>fiddle。