时间戳

首页 > 解决方案 > BigQuery：为表中的缺失列填充空值

sql - BigQuery：为表中的缺失列填充空值

问题描述

我有一个查询来显示基于某些指标的每日聚合。就像是

select date(timestamp), metric, count(*) from aggs GROUP BY 1,2 ORDER BY 1,2;

问题是有时某天缺少某些指标，例如：

date  | metric | count
03/01 |   B    |    50
03/02 |   A    |    60
03/02 |   B    |    10
03/02 |   C    |    70
03/03 |   C    |    10

我想为缺少的日期/指标对填写 0 或 null - 即我们如何执行以下操作：

date  | metric | count
03/01 |   A    |     0
03/01 |   B    |    50
03/01 |   C    |     0
03/02 |   A    |    60
03/02 |   B    |    10
03/02 |   C    |    70
03/03 |   A    |     0
03/03 |   B    |     0
03/03 |   C    |    10

标签： sqlgoogle-bigquery

解决方案

您可以使用 a 生成行cross join，然后使用 a 填充值left join：

select date, metric, coalesce(t.count, 0)
from (select distinct date from t) d cross join
     (select distinct metric from t) m left join
     t
     using (date, metric);

如果您没有想要的所有日期，可以使用：

unnest(generate_date_array(<date1>, <date2>, interval 1 day)) u(dte)

推荐阅读