时间戳

首页 > 解决方案 > 当多个日期系列必须在单个表中表示时生成完整的日期系列

google-bigquery - 当多个日期系列必须在单个表中表示时生成完整的日期系列

问题描述

我们收集有关我们团队使用的软件工具的数据。我们用来代表整个团队使用的工具的一张表简单地计算了该工具每天在整个团队中启动的次数：

SELECT
  day 
  , num_launches
  , tool_name
FROM
  dataset.by_tool_by_day
ORDER BY day ASC
 ;

给你：

| day         | num_launches | tool_name |
|-------------|--------------|-----------|
| 2019-12-20  | 1            | Tool A    |
| 2019-12-20  | 11           | Tool B    |
| 2019-12-20  | 30           | Tool C    |
| 2019-12-21  | 14           | Tool B    |
| 2019-12-22  | 19           | Tool C    |
| 2019-12-23  | 7            | Tool A    |
| 2019-12-23  | 4            | Tool B    |
| 2019-12-23  | 17           | Tool C    |

问题是，如果某一天没有工具启动，那么表中就没有记录表示当天有 0 次工具启动。在上面的示例中，2019 年 12 月 21 日没有工具 A 启动。缺少 0 次启动记录会破坏该数据的可视化（例如，在条形图中），因为没有表示 0 次启动的日期。

我无法提出使用日历表为by_tool_by_day 表中的每个工具生成“0 次启动”的查询。如果表中的所有记录都用于单个工具，则很容易做到。但是，如果有多个工具，因此需要“完整”的多个日期系列，我就无法做到这一点。

标签： google-bigquery

解决方案

#standardSQL

WITH 

DATES AS (
  SELECT 
    gen_date 
  FROM 
    UNNEST(GENERATE_DATE_ARRAY(DATE '2019-12-01', DATE '2019-12-23', INTERVAL 1 DAY)) AS gen_date
),

TOOLS AS (
  SELECT 'ToolA' tool_name UNION ALL
  SELECT 'ToolB' tool_name UNION ALL
  SELECT 'ToolC' tool_name 
),

AGG AS (
  SELECT DATE '2019-12-20' day,  1 num_launches, 'ToolA' tool_name UNION ALL
  SELECT DATE '2019-12-20', 11, 'ToolB' UNION ALL
  SELECT DATE '2019-12-20', 30, 'ToolC' UNION ALL
  SELECT DATE '2019-12-21', 14, 'ToolB' UNION ALL
  SELECT DATE '2019-12-22', 19, 'ToolC' UNION ALL
  SELECT DATE '2019-12-23',  7, 'ToolA' UNION ALL
  SELECT DATE '2019-12-23',  4, 'ToolB' UNION ALL
  SELECT DATE '2019-12-23', 17, 'ToolC'
)

SELECT
  D.gen_date AS day,
  T.tool_name,
  IFNULL(A.num_launches, 0) AS num_launches
FROM
  DATES D
CROSS JOIN 
  TOOLS T
LEFT JOIN
  AGG A
ON
  T.tool_name = A.tool_name 
  AND D.gen_date=A.day
ORDER BY
  D.gen_date

请让我知道这对你有没有用？

推荐阅读