动态日期范围内的计数
动态日期范围内的计数
提问于 2019-03-01 02:10:21
我有一个表，其中包含以下字段：customer_id、start_trial_date、end_trial_date。我正在尝试编写一个查询，我可以用它来计算给定日期的customer_id。
|+-------------+------------+------------+
| customer_id | start_date | end_date |
+-------------+------------+------------+
| 1 | 2017-02-03 | 2017-05-01 |
| 2 | 2017-04-07 | 2017-09-01 |
| 3 | 2017-03-02 | 2018-03-04 |
| 4 | 2013-02-25 | 2015-01-22 |
| 5 | 2015-11-10 | 2016-03-25 |
| .... | .... | .... |
+-------------+------------+------------+我如何编写一个查询，以生成一个结果集，该结果集的所有日期都在某个范围内，并且其符合条件的周期包括该日期的customer_id的计数？
预期输出：
+------------+-----------+
| date | customers |
+------------+-----------+
| 2013-01-01 | 0 |
| …. | …. |
| 2017-04-20 | 3 |
| ….. | …. |
| 2018-12-31 | …. |
+------------+-----------+如果有必要的话，我会使用BigQuery。我曾考虑创建一个列出某个范围内所有日期的帮助表，然后尝试将其连接到我的表中并进行计数，但我在这种方法中没有一个好的连接键。
回答 2
Stack Overflow用户
回答已采纳
发布于 2019-03-01 02:21:55
创建一个日程表是一个很好的起点。一旦有了这个表(比如具有列calendar_date的表calendar )，就可以使用LEFT JOIN和aggregation：
SELECT c.calendar_date, COUNT(t.customer_id) customers
FROM calendar c
LEFT JOIN mytable t
ON c.calendar_date >= t.start_date AND c.calendar_date <= t.end_date
GROUP BY c.calendar_date注意:您可以根据自己的具体要求调整不等式条件(>=或>、<=或<)。
Stack Overflow用户
发布于 2019-03-02 00:48:30
下面是针对BigQuery标准SQL的说明
#standardSQL
WITH calendar AS (
SELECT day
FROM (
SELECT MIN(start_date) min_date, MAX(end_date) max_date
FROM `project.dataset.table`
), UNNEST(GENERATE_DATE_ARRAY(min_date, max_date)) day
)
SELECT day, COUNTIF(day BETWEEN start_date AND end_date) customers
FROM calendar, `project.dataset.table`
GROUP BY day您可以使用以下示例中的虚拟数据来测试和处理上面的内容
#standardSQL
WITH `project.dataset.table` AS (
SELECT 1 customer_id, DATE '2017-01-01' start_date, DATE '2017-01-05' end_date UNION ALL
SELECT 2, '2017-01-03', '2017-01-04' UNION ALL
SELECT 3, '2017-01-04', '2017-01-06' UNION ALL
SELECT 4, '2017-01-10', '2017-01-12' UNION ALL
SELECT 5, '2017-01-12', '2017-01-13'
), calendar AS (
SELECT day
FROM (
SELECT MIN(start_date) min_date, MAX(end_date) max_date
FROM `project.dataset.table`
), UNNEST(GENERATE_DATE_ARRAY(min_date, max_date)) day
)
SELECT day, COUNTIF(day BETWEEN start_date AND end_date) customers
FROM calendar, `project.dataset.table`
GROUP BY day
-- ORDER BY day 有结果
Row day customers
1 2017-01-01 1
2 2017-01-02 1
3 2017-01-03 2
4 2017-01-04 3
5 2017-01-05 2
6 2017-01-06 1
7 2017-01-07 0
8 2017-01-08 0
9 2017-01-09 0
10 2017-01-10 1
11 2017-01-11 1
12 2017-01-12 2
13 2017-01-13 1 页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/54931771
复制相关文章
相似问题
腾讯云开发者
