SQL - 查询以返回给定日期的事件订阅

Question

我有一个表格，显示用户何时注册订阅以及他们的成员(member)资格何时到期。即使当前订阅有效，用户也可以购买新订阅。

userid|purchasedate|expirydate
1     |2019-01-01  |2019-02-01
2     |2019-01-02  |2019-02-02
3     |2019-01-03  |2019-02-03
3     |2019-01-04  |2019-03-03

我需要一个 SQL 查询，它将按日期分组并返回该日期的事件订阅数。所以它会返回:

date      |count
2019-01-01|1
2019-01-02|2
2019-01-03|3
2019-01-04|3

Answer 1

下面是 BigQuery 标准 SQL

#standardSQL
SELECT day, COUNT(DISTINCT userid) active_subscriptions 
FROM (SELECT AS STRUCT MIN(purchasedate) min_date, MAX(expirydate) max_date FROM `project.dataset.table`),
UNNEST(GENERATE_DATE_ARRAY(min_date, max_date)) day 
JOIN `project.dataset.table`
ON day BETWEEN purchasedate AND expirydate
GROUP BY day  

您可以使用问题中的虚拟数据进行测试，玩上面的游戏，如下例所示

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 1 userid, DATE '2019-01-01' purchasedate, DATE '2019-02-01' expirydate UNION ALL
  SELECT 2, '2019-01-02', '2019-02-02' UNION ALL
  SELECT 3, '2019-01-03', '2019-02-03' UNION ALL
  SELECT 3, '2019-01-04', '2019-03-03' 
)
SELECT day, COUNT(DISTINCT userid) active_subscriptions 
FROM (SELECT AS STRUCT MIN(purchasedate) min_date, MAX(expirydate) max_date FROM `project.dataset.table`),
UNNEST(GENERATE_DATE_ARRAY(min_date, max_date)) day 
JOIN `project.dataset.table`
ON day BETWEEN purchasedate AND expirydate
GROUP BY day

具有以下输出

Row day         active_subscriptions     
1   2019-01-01  1    
2   2019-01-02  2    
3   2019-01-03  3    
4   2019-01-04  3    
5   2019-01-05  3    
6   2019-01-06  3    
... ...         ...
... ...         ...
31  2019-01-31  3    
32  2019-02-01  3    
33  2019-02-02  2    
34  2019-02-03  1    
35  2019-02-04  1    
... ...         ...
... ...         ...
61  2019-03-02  1    
62  2019-03-03  1