sql - 甲骨文 SQL : How to show empty weeks/weeks without data?

Question

类似于this question ，但我的数据集有一个包含许多 ID 的附加列。每个 ID 都有一个可以追溯到固定时间范围的数据集，并且某些周可能会丢失数据 - 我想为缺失的周填写值。

例如，我想要这个:

ID      WEEKEND_DAY     VALUE
A00     2012-01-01      1
A00     2012-01-08      7
B00     2012-01-08      4
B00     2012-01-15      3

展开为:

ID      WEEKEND_DAY     VALUE
A00     2012-01-01      1
A00     2012-01-08      7
A00     2012-01-15      0
B00     2012-01-01      0
B00     2012-01-08      4
B00     2012-01-15      3

我已经有一个已知的 WEEKEND_DAY 范围(在上述情况下，每周从 2012-01-01 到 2012-01-15)我想填写。我该怎么做呢？

Answer 1

使用 partitioned outer join :

select data.id, weeks.weekend_day, nvl(value, 0) value
from
(
    select date '2012-01-01' weekend_day from dual union all
    select date '2012-01-08' weekend_day from dual union all
    select date '2012-01-15' weekend_day from dual
) weeks
left join
(
    select 'A00' id, date '2012-01-01' weekend_day, 1 value from dual union all
    select 'A00' id, date '2012-01-08' weekend_day, 7 value from dual union all
    select 'B00' id, date '2012-01-08' weekend_day, 4 value from dual union all
    select 'B00' id, date '2012-01-15' weekend_day, 3 value from dual
) data
    partition by (data.id)
    on weeks.weekend_day = data.weekend_day