sql - 希望为每个 ID 填充缺失的时间段为零（Athena SQL）

Question

在我的数据中，我正在查看每个用户的季度计数。对于所有缺失的季度，我想将它们归为 0。这是一个示例：

这是我当前的数据集：

ID Qtr    Count
1  2018Q1 1
1  2018Q3 1
1  2018Q4 2
2  2018Q1 4
2  2018Q2 6
2  2019Q3 2
2  2019Q4 1
3  2018Q1 8
3  2018Q2 5
3  2018Q3 2
3  2018Q4 4
3  2019Q1 5

更新表：

ID Qtr    Count
1  2018Q1 1
1  2018Q2 0
1  2018Q3 1
1  2018Q4 2
1  2019Q1 0
1  2019Q2 0
1  2019Q3 0
1  2019Q4 0
2  2018Q1 4
2  2018Q2 6
2  2018Q3 0
2  2018Q4 0
2  2019Q1 0
2  2019Q2 0
2  2019Q3 2
2  2019Q4 1
3  2018Q1 8
3  2018Q2 5
3  2018Q3 2
3  2018Q4 4
3  2019Q1 5
3  2019Q2 0
3  2019Q3 0
3  2019Q4 0

任何帮助将非常感激。

标签： sqlamazon-athena

Answer 1

您可以创建包含所有季度的表（例如quarters_），然后将数据（例如 some_data）加入其中。

create table some_data (year number, quarter number, data varchar2(10));
insert into some_data values(2018,3,'AAAA');

create table  quarters_ (signature varchar2(20), year number, quarter_number number);
insert into quarters_ (signature, year, quarter_number) values ('2018Q1', 2018, 1);
insert into quarters_ (signature, year, quarter_number) values ('2018Q2', 2018, 2);
insert into quarters_ (signature, year, quarter_number) values ('2018Q3', 2018, 3);
insert into quarters_ (signature, year, quarter_number) values ('2018Q4', 2018, 4);
insert into quarters_ (signature, year, quarter_number) values ('2019Q1', 2019, 1);
insert into quarters_ (signature, year, quarter_number) values ('2019Q2', 2019, 2);
insert into quarters_ (signature, year, quarter_number) values ('2019Q3', 2019, 3);
insert into quarters_ (signature, year, quarter_number) values ('2019Q4', 2019, 4);

select signature as Qtr, count (s.data) as count
from quarters_ q
left join some_data s on s.quarter = q.quarter_number and s.year = q.year
group by q.signature