时间戳

首页 > 解决方案 > BigQuery 缺少 SUM OVER PARTITION BY 的行

sql - BigQuery 缺少 SUM OVER PARTITION BY 的行

问题描述

TL;博士：

鉴于此表：

WITH subscriptions AS (SELECT TIMESTAMP("2020-11-01") as date, "premium" as product, 50 as diff
  UNION ALL SELECT TIMESTAMP("2020-11-01"), "basic", 100
  UNION ALL SELECT TIMESTAMP("2020-11-02"), "basic", -10
  UNION ALL SELECT TIMESTAMP("2020-11-03"), "premium", 20
  UNION ALL SELECT TIMESTAMP("2020-11-03"), "basic", 40
)

如何获得一个表格，其中缺少的日期/产品组合 ( 2020-11-02 - premium) 包含在 of 的后备值diff中0。

理想情况下，适用于多种产品。所有产品的列表可以像这样得到：

SELECT ARRAY_AGG(DISTINCT product) FROM subscriptions

我希望能够获得所有产品或某些产品的每日订阅数。

我认为可以轻松实现这一点的方法是准备一个如下所示的数据库：

|---------------------|------------------|------------------|
|         date        |      product     |       total      |
|---------------------|------------------|------------------|
|      2020-11-01     |      premium     |        100       |
|---------------------|------------------|------------------|
|      2020-11-01     |       basic      |        50        |
|---------------------|------------------|------------------|

使用此表，我可以轻松地按日期和产品分组，或者仅按日期分组并求和。

在我得到结果表之前，我已经生成了一个表，我计算了每天和产品的订阅差异。每个产品有多少新订阅者，有多少不再订阅。

该表如下所示：

|---------------------|------------------|------------------|
|         date        |      product     |       diff       |
|---------------------|------------------|------------------|
|      2020-11-01     |      premium     |        50        |
|---------------------|------------------|------------------|
|      2020-11-01     |       basic      |       -20        |
|---------------------|------------------|------------------|

意味着 11 月 1 日，高级用户总数增加了 50 个，基本用户总数减少了 20 个。

现在的问题是，如果一个产品没有任何更改，则此临时表缺少日期点，请参见下面的示例。

当我开始时没有产品表，我只有日期和差异列。

为了从第二个表到第一个表，我使用了这个完美的查询：

WITH subscriptions AS (SELECT TIMESTAMP("2020-11-01") as date, 150 as diff
  UNION ALL SELECT TIMESTAMP("2020-11-02"), -10
  UNION ALL SELECT TIMESTAMP("2020-11-03"), 60
)
SELECT 
  *,
  SUM(diff) OVER (ORDER BY date) as total_subscriptions
FROM subscriptions
ORDER BY date

但是当我添加产品列并尝试计算每天和产品的总和时，会丢失一些数据点。

WITH subscriptions AS (SELECT TIMESTAMP("2020-11-01") as date, "premium" as product, 50 as diff
  UNION ALL SELECT TIMESTAMP("2020-11-01"), "basic", 100
  UNION ALL SELECT TIMESTAMP("2020-11-02"), "basic", -10
  UNION ALL SELECT TIMESTAMP("2020-11-03"), "premium", 20
  UNION ALL SELECT TIMESTAMP("2020-11-03"), "basic", 40
)
SELECT 
  *,
  SUM(diff) OVER (PARTITION BY product ORDER BY date) as total_subscriptions
FROM subscriptions
ORDER BY date

--

|---------------------|------------------|------------------|
|         date        |      product     |      total       |
|---------------------|------------------|------------------|
|      2020-11-01     |       basic      |       100        |
|---------------------|------------------|------------------|
|      2020-11-01     |      premium     |        50        |
|---------------------|------------------|------------------|
|      2020-11-02     |       basic      |        90        |
|---------------------|------------------|------------------|
|      2020-11-03     |       basic      |       130        |
|---------------------|------------------|------------------|
|      2020-11-03     |      premium     |        70        |
|---------------------|------------------|------------------|

如果我现在显示每天的订阅总数，我会得到：

150 -> 90 -> 200

但我希望：

150 -> 140 -> 200

每天的高级订阅总数也是如此：

50 -> 0 -> 70

但我希望：

50 -> 50 -> 70

我相信解决此问题的最佳选择是添加缺少的日期/产品组合。

我该怎么做？

标签： sqldatetimegoogle-bigquerysumrecursive-query

解决方案

如果我正确地遵循您，一种方法是可以生成您想要的时间段的固定日期列表，并将cross join其与产品列表一起生成。这为您提供了所有可能的组合。然后，您可以将订阅表带上left join，最后执行窗口求和：

select d.dt, p.product, sum(s.diff) over(partition by p.product order by d.dt) total
from unnest(generate_timestamp_array(
    timestamp('2020-11-01'), 
    timestamp('2020-11-03'), 
    interval 1 day)
) dt
cross join (
    select 'basic' product 
    union all select 'premium'
) p
left join subscriptions on s.product = p.product and s.date = dt

我们可以通过动态生成日期范围和产品列表来使查询更通用：

select d.dt, p.product, sum(s.diff) over(partition by p.product order by d.dt) total
from (select min(date) min_dt, max(date) max_dt from subscriptions) d0
cross join unnest(generate_timestamp_array(d0.min_dt, d0.max_dt, interval 1 day)) dt
cross join (select distinct product from subscriptions) p
left join subscriptions on s.product = p.product and s.date = dt

推荐阅读