滑动窗口上的sql非重复计数

knpiaxh1 于 2021-07-26 发布在 Java

关注(0)|答案(1)|浏览(315)

我有3列[日期，状态，客户ID]。我需要一个优雅的方法来计算从星期二到每天的唯一客户，这意味着在星期二的计数将只包括该星期二的不同客户。周三的统计包括周二和周三的不同客户。在星期四，计数应包括星期二、星期三和星期四的不同客户。这将一直重复到下周一和周二，循环重新开始。如何使用sql处理这个逻辑？同时假设不支持count distinct over window函数。
假设这是源头

+----------+-----------+---------+                                              
|      date|customer_id|   status|
+----------+-----------+---------+
|2020-06-08|        001|AVAILABLE|
|2020-06-08|        001|  EXPIRED|
|2020-06-08|        001|AVAILABLE|
|2020-06-08|        002|AVAILABLE|
|2020-06-08|        002|  EXPIRED|
|2020-06-08|        003|  EXPIRED|
|2020-06-08|        003|AVAILABLE|
|2020-06-09|        001|AVAILABLE|
|2020-06-09|        001|AVAILABLE|
|2020-06-09|        002|  EXPIRED|
|2020-06-09|        003|AVAILABLE|
|2020-06-09|        003|  EXPIRED|
|2020-06-09|        003|  EXPIRED|
|2020-06-10|        001|  EXPIRED|
|2020-06-10|        001|  EXPIRED|
|2020-06-10|        001|AVAILABLE|
|2020-06-10|        001|AVAILABLE|
|2020-06-10|        002|AVAILABLE|
|2020-06-10|        002|AVAILABLE|
|2020-06-10|        002|  EXPIRED|
|2020-06-10|        002|AVAILABLE|
|2020-06-10|        002|  EXPIRED|
|2020-06-10|        003|  EXPIRED|
|2020-06-10|        003|AVAILABLE|
|2020-06-10|        003|AVAILABLE|
|2020-06-11|        001|  EXPIRED|
|2020-06-11|        001|  EXPIRED|
|2020-06-12|        001|AVAILABLE|
|2020-06-12|        001|  EXPIRED|
|2020-06-12|        003|  EXPIRED|
|2020-06-12|        003|AVAILABLE|
|2020-06-12|        004|AVAILABLE|
|2020-06-13|        001|AVAILABLE|
|2020-06-13|        002|AVAILABLE|
|2020-06-13|        002|AVAILABLE|
|2020-06-13|        002|AVAILABLE|
|2020-06-14|        001|  EXPIRED|
|2020-06-14|        003|  EXPIRED|
|2020-06-14|        004|  EXPIRED|
|2020-06-15|        001|  EXPIRED|
|2020-06-15|        001|AVAILABLE|
|2020-06-15|        001|  EXPIRED|
|2020-06-15|        003|  EXPIRED|
|2020-06-15|        003|AVAILABLE|
|2020-06-16|        001|AVAILABLE|
|2020-06-16|        001|  EXPIRED|
|2020-06-16|        002|AVAILABLE|
|2020-06-16|        002|AVAILABLE|
|2020-06-16|        002|  EXPIRED|
|2020-06-16|        002|  EXPIRED|
|2020-06-16|        003|  EXPIRED|
+----------+-----------+---------+

这是预期的结果

+----------+-----------+---------+                                              
|      date|      count|   status|
+----------+-----------+---------+
|2020-06-08|       NULL|     NULL|
|2020-06-09|          2|AVAILABLE|
|2020-06-09|          2|  EXPIRED|
|2020-06-10|          3|  EXPIRED|
|2020-06-10|          3|AVAILABLE|
|2020-06-11|          3|AVAILABLE|
|2020-06-11|          3|  EXPIRED|
|2020-06-12|          4|AVAILABLE|
|2020-06-12|          3|  EXPIRED|
|2020-06-13|          4|AVAILABLE|
|2020-06-14|          4|  EXPIRED|
|2020-06-15|          4|  EXPIRED|
|2020-06-15|          4|AVAILABLE|
|2020-06-16|          3|  EXPIRED|
|2020-06-16|          2|AVAILABLE|
+----------+-----------+---------+

sql presto apache-spark-sql

来源：https://stackoverflow.com/questions/62484952/distinct-count-over-a-sliding-window

1条答案

按热度按时间

t3psigkw1#

这回答了问题的原始版本。
你的问题有两部分。
一是确定正确的时间框架。在前几周，星期二不开始。我想星期一开始。
另一个是 count(distinct) . 我认为presto支持这个窗口函数。所以，你可以做：

select distinct date,
       count(distinct user_id) over (partition by date_trunc('week', date - interval '1 day')
                                     order by date
                                    ) as unique_users_since_tuesday
from t;

请注意，这是一个窗口函数，而不是聚合。

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

我来回答

滑动窗口上的sql非重复计数

1条答案

相关问题

热门标签

最新问答