我的问题类似于redshift:通过窗口分区计算不同客户的数量 https://stackoverflow.com/questions/47736584/redshift-count-distinct-customers-over-window-partition但我有一个滚动窗隔断。
我的查询看起来像这样,但在 COUNT in 中不同Redshift https://docs.aws.amazon.com/redshift/latest/dg/r_WF_COUNT.html不支持
select p_date, seconds_read,
count(distinct customer_id) over (order by p_date rows between unbounded preceding and current row) as total_cumulative_customer
from table_x
我的目标是计算截至每个日期的唯一客户总数(因此是滚动窗口)。
我尝试使用dend_rank() 方法 https://stackoverflow.com/a/47736585/4046274但它会失败,因为我不能像这样使用窗口函数
select p_date, max(total_cumulative_customer) over ()
(select p_date, seconds_read,
dense_rank() over (order by customer_id rows between unbounded preceding and current row) as total_cumulative_customer -- WILL FAIL HERE
from table_x
任何解决方法或不同的方法都会有所帮助!
EDIT:
输入数据样本
+------+----------+--------------+
| Cust | p_date | seconds_read |
+------+----------+--------------+
| 1 | 1-Jan-20 | 10 |
| 2 | 1-Jan-20 | 20 |
| 4 | 1-Jan-20 | 30 |
| 5 | 1-Jan-20 | 40 |
| 6 | 5-Jan-20 | 50 |
| 3 | 5-Jan-20 | 60 |
| 2 | 5-Jan-20 | 70 |
| 1 | 5-Jan-20 | 80 |
| 1 | 5-Jan-20 | 90 |
| 1 | 7-Jan-20 | 100 |
| 3 | 7-Jan-20 | 110 |
| 4 | 7-Jan-20 | 120 |
| 7 | 7-Jan-20 | 130 |
+------+----------+--------------+
预期输出
+----------+--------------------------+------------------+--------------------------------------------+
| p_date | total_distinct_cum_cust | sum_seconds_read | Comment |
+----------+--------------------------+------------------+--------------------------------------------+
| 1-Jan-20 | 4 | 100 | total distinct cust = 4 i.e. 1,2,4,5 |
| 5-Jan-20 | 6 | 450 | total distinct cust = 6 i.e. 1,2,3,4,5,6 |
| 7-Jan-20 | 7 | 910 | total distinct cust = 6 i.e. 1,2,3,4,5,6,7 |
+----------+--------------------------+------------------+--------------------------------------------+