我有一個大表（在雪花中），其中包含多個物體在多個時間段內以多種貨幣表示的金額。但是，缺少金額為 0 的實體，需要添加 - 從第一個日期到當前日期。資料樣本如下所示：
| 姓名 | 貨幣 | 日期 | 數量 |
|---|---|---|---|
| 一種 | 美元 | 2020-01-01 | 3 |
| 一種 | 美元 | 2020-01-03 | 4 |
| 一種 | 美元 | 2020-01-04 | 2 |
| 一種 | 加元 | 2021-01-04 | 5 |
| 一種 | 加元 | 2021-01-06 | 6 |
| 一種 | 加元 | 2020-01-07 | 1 |
| 乙 | 美元 | 2019-01-01 | 3 |
| 乙 | 美元 | 2019-01-03 | 4 |
| 乙 | 美元 | 2019-01-04 | 5 |
| 乙 | 加元 | 2017-01-04 | 3 |
| 乙 | 加元 | 2017-01-06 | 2 |
| 乙 | 加元 | 2017-01-07 | 2 |
我看到的大多數通過生成日期表并加入的解決方案的問題是：
- 他們只會填寫一次日期，而不是填寫所有磁區/對（A 和 USD、A 和 CAD、B 和 USD、B 和 CAD）的日期
- they use a fixed starting date from a pre-generated table, while the earliest date varies depending on which pair of Name and Currency are being considered
Oracle DB supports something like a partitioned outer join, which would get me at least part way there. However Snowflake has no such ability.
I am not too concerned with efficiency.
uj5u.com熱心網友回復：
WITH fake_data AS (
SELECT * FROM VALUES
('A','USD','2020-01-01'::date,3)
,('A','USD','2020-01-03'::date,4)
,('A','USD','2020-01-04'::date,2)
,('A','CAD','2021-01-04'::date,5)
,('A','CAD','2021-01-06'::date,6)
,('A','CAD','2020-01-07'::date,1)
,('B','USD','2019-01-01'::date,3)
,('B','USD','2019-01-03'::date,4)
,('B','USD','2019-01-04'::date,5)
,('B','CAD','2017-01-04'::date,3)
,('B','CAD','2017-01-06'::date,2)
,('B','CAD','2017-01-07'::date,2)
d(Name,Currency,Date,Amount)
), partition_ranges AS (
SELECT name,
currency,
min(date) as min_date,
max(date) as max_date,
datediff('days', min_date, max_date) as span
FROM fake_data
GROUP BY 1,2
), huge_range as (
SELECT ROW_NUMBER() OVER(order by true)-1 as rn
FROM table(generator(ROWCOUNT => 10000000))
), in_fill as (
SELECT pr.name,
pr.currency,
dateadd('day', hr.rn, pr.min_date) as date
FROM partition_ranges as pr
JOIN huge_range as hr ON pr.span >= hr.rn
)
SELECT
i.name,
i.currency,
i.date,
nvl(d.amount, 0) as amount
from in_fill as i
left join fake_data as d on d.name = i.name and d.currency = i.currency and d.date = i.date
order by 1,2,3;
| 姓名 | 貨幣 | 日期 | 數量 |
|---|---|---|---|
| 一種 | 加元 | 2020-01-07 | 1 |
| 一種 | 加元 | 2020-01-08 | 0 |
| 一種 | 加元 | 2020-01-09 | 0 |
| 一種 | 加元 | 2020-01-10 | 0 |
| 一種 | 加元 | 2020-01-11 | 0 |
| 一種 | 加元 | 2020-01-12 | 0 |
| 一種 | 加元 | 2020-01-13 | 0 |
| 一種 | 加元 | 2020-01-14 | 0 |
| 一種 | 加元 | 2020-01-15 | 0 |
| 一種 | 加元 | 2020-01-16 | 0 |
| 一種 | 加元 | 2020-01-17 | 0 |
| 一種 | 加元 | 2020-01-18 | 0 |
| 一種 | 加元 | 2020-01-19 | 0 |
| 一種 | 加元 | 2020-01-20 | 0 |
| 一種 | 加元 | 2020-01-21 | 0 |
| 一種 | 加元 | 2020-01-22 | 0 |
| 一種 | 加元 | 2020-01-23 | 0 |
| 一種 | 加元 | 2020-01-24 | 0 |
| 一種 | 加元 | 2020-01-25 | 0 |
| 一種 | 加元 | 2020-01-26 | 0 |
| 一種 | 加元 | 2020-01-27 | 0 |
| 一種 | 加元 | 2020-01-28 | 0 |
| 一種 | 加元 | 2020-01-29 | 0 |
| 一種 | 加元 | 2020-01-30 | 0 |
| 一種 | 加元 | 2020-01-31 | 0 |
| 一種 | 加元 | 2020-02-01 | 0 |
| 一種 | 加元 | 2020-02-02 | 0 |
| 一種 | 加元 | 2020-02-03 | 0 |
| 一種 | 加元 | 2020-02-04 | 0 |
| 一種 | 加元 | 2020-02-05 | 0 |
| 一種 | 加元 | 2020-02-06 | 0 |
| 一種 | 加元 | 2020-02-07 | 0 |
| 一種 | 加元 | 2020-02-08 | 0 |
| 一種 | 加元 | 2020-02-09 | 0 |
| 一種 | 加元 | 2020-02-10 | 0 |
| 一種 | 加元 | 2020-02-11 | 0 |
| 一種 | 加元 | 2020-02-12 | 0 |
| 一種 | 加元 | 2020-02-13 | 0 |
| 一種 | 加元 | 2020-02-14 | 0 |
| 一種 | 加元 | 2020-02-15 | 0 |
| 一種 | 加元 | 2020-02-16 | 0 |
| 一種 | 加元 | 2020-02-17 | 0 |
| 一種 | 加元 | 2020-02-18 | 0 |
| 一種 | 加元 | 2020-02-19 | 0 |
| 一種 | 加元 | 2020-02-20 | 0 |
| 一種 | 加元 | 2020-02-21 | 0 |
| 一種 | 加元 | 2020-02-22 | 0 |
| 一種 | 加元 | 2020-02-23 | 0 |
| 一種 | 加元 | 2020-02-24 | 0 |
| 一種 | 加元 | 2020-02-25 | 0 |
| 一種 | 加元 | 2020-02-26 | 0 |
| 一種 | 加元 | 2020-02-27 | 0 |
| 一種 | 加元 | 2020-02-28 | 0 |
| 一種 | 加元 | 2020-02-29 | 0 |
| 一種 | 加元 | 2020-03-01 | 0 |
| 一種 | 加元 | 2020-03-02 | 0 |
| 一種 | 加元 | 2020-03-03 | 0 |
| 一種 | 加元 | 2020-03-04 | 0 |
| 一種 | 加元 | 2020-03-05 | 0 |
| 一種 | 加元 | 2020-03-06 | 0 |
| 一種 | 加元 | 2020-03-07 | 0 |
| 一種 | 加元 | 2020-03-08 | 0 |
| 一種 | 加元 | 2020-03-09 | 0 |
| 一種 | 加元 | 2020-03-10 | 0 |
| A | CAD | 2020-03-11 | 0 |
| A | CAD | 2020-03-12 | 0 |
| A | CAD | 2020-03-13 | 0 |
| A | CAD | 2020-03-14 | 0 |
| A | CAD | 2020-03-15 | 0 |
| A | CAD | 2020-03-16 | 0 |
| A | CAD | 2020-03-17 | 0 |
| A | CAD | 2020-03-18 | 0 |
| A | CAD | 2020-03-19 | 0 |
| A | CAD | 2020-03-20 | 0 |
| A | CAD | 2020-03-21 | 0 |
| A | CAD | 2020-03-22 | 0 |
| A | CAD | 2020-03-23 | 0 |
| A | CAD | 2020-03-24 | 0 |
| A | CAD | 2020-03-25 | 0 |
| A | CAD | 2020-03-26 | 0 |
| A | CAD | 2020-03-27 | 0 |
| A | CAD | 2020-03-28 | 0 |
| A | CAD | 2020-03-29 | 0 |
| A | CAD | 2020-03-30 | 0 |
| A | CAD | 2020-03-31 | 0 |
| A | CAD | 2020-04-01 | 0 |
| A | CAD | 2020-04-02 | 0 |
| A | CAD | 2020-04-03 | 0 |
| A | CAD | 2020-04-04 | 0 |
| A | CAD | 2020-04-05 | 0 |
| A | CAD | 2020-04-06 | 0 |
| A | CAD | 2020-04-07 | 0 |
| A | CAD | 2020-04-08 | 0 |
| A | CAD | 2020-04-09 | 0 |
| A | CAD | 2020-04-10 | 0 |
| A | CAD | 2020-04-11 | 0 |
| A | CAD | 2020-04-12 | 0 |
| A | CAD | 2020-04-13 | 0 |
| A | CAD | 2020-04-14 | 0 |
| A | CAD | 2020-04-15 | 0 |
| A | CAD | 2020-04-16 | 0 |
| A | CAD | 2020-04-17 | 0 |
| A | CAD | 2020-04-18 | 0 |
| A | CAD | 2020-04-19 | 0 |
| A | CAD | 2020-04-20 | 0 |
| A | CAD | 2020-04-21 | 0 |
| A | CAD | 2020-04-22 | 0 |
| A | CAD | 2020-04-23 | 0 |
| A | CAD | 2020-04-24 | 0 |
| A | CAD | 2020-04-25 | 0 |
| A | CAD | 2020-04-26 | 0 |
| A | CAD | 2020-04-27 | 0 |
| A | CAD | 2020-04-28 | 0 |
| A | CAD | 2020-04-29 | 0 |
| A | CAD | 2020-04-30 | 0 |
| A | CAD | 2020-05-01 | 0 |
| A | CAD | 2020-05-02 | 0 |
| A | CAD | 2020-05-03 | 0 |
| A | CAD | 2020-05-04 | 0 |
| A | CAD | 2020-05-05 | 0 |
| A | CAD | 2020-05-06 | 0 |
| A | CAD | 2020-05-07 | 0 |
| A | CAD | 2020-05-08 | 0 |
| A | CAD | 2020-05-09 | 0 |
| A | CAD | 2020-05-10 | 0 |
| A | CAD | 2020-05-11 | 0 |
| A | CAD | 2020-05-12 | 0 |
| A | CAD | 2020-05-13 | 0 |
| A | CAD | 2020-05-14 | 0 |
| A | CAD | 2020-05-15 | 0 |
| A | CAD | 2020-05-16 | 0 |
| A | CAD | 2020-05-17 | 0 |
| A | CAD | 2020-05-18 | 0 |
| A | CAD | 2020-05-19 | 0 |
| A | CAD | 2020-05-20 | 0 |
| A | CAD | 2020-05-21 | 0 |
| A | CAD | 2020-05-22 | 0 |
| A | CAD | 2020-05-23 | 0 |
| A | CAD | 2020-05-24 | 0 |
| A | CAD | 2020-05-25 | 0 |
| A | CAD | 2020-05-26 | 0 |
| A | CAD | 2020-05-27 | 0 |
| A | CAD | 2020-05-28 | 0 |
| A | CAD | 2020-05-29 | 0 |
| A | CAD | 2020-05-30 | 0 |
| A | CAD | 2020-05-31 | 0 |
| A | CAD | 2020-06-01 | 0 |
| A | CAD | 2020-06-02 | 0 |
| A | CAD | 2020-06-03 | 0 |
| A | CAD | 2020-06-04 | 0 |
| A | CAD | 2020-06-05 | 0 |
| A | CAD | 2020-06-06 | 0 |
| A | CAD | 2020-06-07 | 0 |
| A | CAD | 2020-06-08 | 0 |
| A | CAD | 2020-06-09 | 0 |
| A | CAD | 2020-06-10 | 0 |
| A | CAD | 2020-06-11 | 0 |
| A | CAD | 2020-06-12 | 0 |
| A | CAD | 2020-06-13 | 0 |
| A | CAD | 2020-06-14 | 0 |
| A | CAD | 2020-06-15 | 0 |
| A | CAD | 2020-06-16 | 0 |
| A | CAD | 2020-06-17 | 0 |
| A | CAD | 2020-06-18 | 0 |
| A | CAD | 2020-06-19 | 0 |
| A | CAD | 2020-06-20 | 0 |
| A | CAD | 2020-06-21 | 0 |
| A | CAD | 2020-06-22 | 0 |
| A | CAD | 2020-06-23 | 0 |
| A | CAD | 2020-06-24 | 0 |
| A | CAD | 2020-06-25 | 0 |
| A | CAD | 2020-06-26 | 0 |
| A | CAD | 2020-06-27 | 0 |
| A | CAD | 2020-06-28 | 0 |
| A | CAD | 2020-06-29 | 0 |
| A | CAD | 2020-06-30 | 0 |
| A | CAD | 2020-07-01 | 0 |
| A | CAD | 2020-07-02 | 0 |
| A | CAD | 2020-07-03 | 0 |
| A | CAD | 2020-07-04 | 0 |
| A | CAD | 2020-07-05 | 0 |
| A | CAD | 2020-07-06 | 0 |
| A | CAD | 2020-07-07 | 0 |
| A | CAD | 2020-07-08 | 0 |
| A | CAD | 2020-07-09 | 0 |
| A | CAD | 2020-07-10 | 0 |
| A | CAD | 2020-07-11 | 0 |
| A | CAD | 2020-07-12 | 0 |
| A | CAD | 2020-07-13 | 0 |
| A | CAD | 2020-07-14 | 0 |
| A | CAD | 2020-07-15 | 0 |
| A | CAD | 2020-07-16 | 0 |
| A | CAD | 2020-07-17 | 0 |
| A | CAD | 2020-07-18 | 0 |
| A | CAD | 2020-07-19 | 0 |
| A | CAD | 2020-07-20 | 0 |
| A | CAD | 2020-07-21 | 0 |
| A | CAD | 2020-07-22 | 0 |
| A | CAD | 2020-07-23 | 0 |
| A | CAD | 2020-07-24 | 0 |
| A | CAD | 2020-07-25 | 0 |
| A | CAD | 2020-07-26 | 0 |
| A | CAD | 2020-07-27 | 0 |
| A | CAD | 2020-07-28 | 0 |
| A | CAD | 2020-07-29 | 0 |
| A | CAD | 2020-07-30 | 0 |
| A | CAD | 2020-07-31 | 0 |
| A | CAD | 2020-08-01 | 0 |
| A | CAD | 2020-08-02 | 0 |
| A | CAD | 2020-08-03 | 0 |
| A | CAD | 2020-08-04 | 0 |
| A | CAD | 2020-08-05 | 0 |
| A | CAD | 2020-08-06 | 0 |
| A | CAD | 2020-08-07 | 0 |
| A | CAD | 2020-08-08 | 0 |
| A | CAD | 2020-08-09 | 0 |
| A | CAD | 2020-08-10 | 0 |
| A | CAD | 2020-08-11 | 0 |
| A | CAD | 2020-08-12 | 0 |
| A | CAD | 2020-08-13 | 0 |
| A | CAD | 2020-08-14 | 0 |
| A | CAD | 2020-08-15 | 0 |
| A | CAD | 2020-08-16 | 0 |
| A | CAD | 2020-08-17 | 0 |
| A | CAD | 2020-08-18 | 0 |
| A | CAD | 2020-08-19 | 0 |
| A | CAD | 2020-08-20 | 0 |
| A | CAD | 2020-08-21 | 0 |
| A | CAD | 2020-08-22 | 0 |
| A | CAD | 2020-08-23 | 0 |
| A | CAD | 2020-08-24 | 0 |
| A | CAD | 2020-08-25 | 0 |
| A | CAD | 2020-08-26 | 0 |
| A | CAD | 2020-08-27 | 0 |
| A | CAD | 2020-08-28 | 0 |
| A | CAD | 2020-08-29 | 0 |
| A | CAD | 2020-08-30 | 0 |
| A | CAD | 2020-08-31 | 0 |
| A | CAD | 2020-09-01 | 0 |
| A | CAD | 2020-09-02 | 0 |
| A | CAD | 2020-09-03 | 0 |
| A | CAD | 2020-09-04 | 0 |
| A | CAD | 2020-09-05 | 0 |
| A | CAD | 2020-09-06 | 0 |
| A | CAD | 2020-09-07 | 0 |
| A | CAD | 2020-09-08 | 0 |
| A | CAD | 2020-09-09 | 0 |
| A | CAD | 2020-09-10 | 0 |
| A | CAD | 2020-09-11 | 0 |
| A | CAD | 2020-09-12 | 0 |
| A | CAD | 2020-09-13 | 0 |
| A | CAD | 2020-09-14 | 0 |
| A | CAD | 2020-09-15 | 0 |
| A | CAD | 2020-09-16 | 0 |
| A | CAD | 2020-09-17 | 0 |
| A | CAD | 2020-09-18 | 0 |
| A | CAD | 2020-09-19 | 0 |
| A | CAD | 2020-09-20 | 0 |
| A | CAD | 2020-09-21 | 0 |
| A | CAD | 2020-09-22 | 0 |
| A | CAD | 2020-09-23 | 0 |
| A | CAD | 2020-09-24 | 0 |
| A | CAD | 2020-09-25 | 0 |
| A | CAD | 2020-09-26 | 0 |
| A | CAD | 2020-09-27 | 0 |
| A | CAD | 2020-09-28 | 0 |
| A | CAD | 2020-09-29 | 0 |
| A | CAD | 2020-09-30 | 0 |
| A | CAD | 2020-10-01 | 0 |
| A | CAD | 2020-10-02 | 0 |
| A | CAD | 2020-10-03 | 0 |
| A | CAD | 2020-10-04 | 0 |
| A | CAD | 2020-10-05 | 0 |
| A | CAD | 2020-10-06 | 0 |
| A | CAD | 2020-10-07 | 0 |
| A | CAD | 2020-10-08 | 0 |
| A | CAD | 2020-10-09 | 0 |
| A | CAD | 2020-10-10 | 0 |
| A | CAD | 2020-10-11 | 0 |
| A | CAD | 2020-10-12 | 0 |
| A | CAD | 2020-10-13 | 0 |
| A | CAD | 2020-10-14 | 0 |
| A | CAD | 2020-10-15 | 0 |
| A | CAD | 2020-10-16 | 0 |
| A | CAD | 2020-10-17 | 0 |
| A | CAD | 2020-10-18 | 0 |
| A | CAD | 2020-10-19 | 0 |
| A | CAD | 2020-10-20 | 0 |
| A | CAD | 2020-10-21 | 0 |
| A | CAD | 2020-10-22 | 0 |
| 一種 | 加元 | 2020-10-23 | 0 |
| 一種 | 加元 | 2020-10-24 | 0 |
| 一種 | 加元 | 2020-10-25 | 0 |
| 一種 | 加元 | 2020-10-26 | 0 |
| 一種 | 加元 | 2020-10-27 | 0 |
| 一種 | 加元 | 2020-10-28 | 0 |
| 一種 | 加元 | 2020-10-29 | 0 |
| 一種 | 加元 | 2020-10-30 | 0 |
| 一種 | 加元 | 2020-10-31 | 0 |
| 一種 | 加元 | 2020-11-01 | 0 |
| 一種 | 加元 | 2020-11-02 | 0 |
| 一種 | 加元 | 2020-11-03 | 0 |
| 一種 | 加元 | 2020-11-04 | 0 |
| 一種 | 加元 | 2020-11-05 | 0 |
| 一種 | 加元 | 2020-11-06 | 0 |
| 一種 | 加元 | 2020-11-07 | 0 |
| 一種 | 加元 | 2020-11-08 | 0 |
| 一種 | 加元 | 2020-11-09 | 0 |
| 一種 | 加元 | 2020-11-10 | 0 |
| 一種 | 加元 | 2020-11-11 | 0 |
| 一種 | 加元 | 2020-11-12 | 0 |
| 一種 | 加元 | 2020-11-13 | 0 |
| 一種 | 加元 | 2020-11-14 | 0 |
| 一種 | 加元 | 2020-11-15 | 0 |
| 一種 | 加元 | 2020-11-16 | 0 |
| 一種 | 加元 | 2020-11-17 | 0 |
| 一種 | 加元 | 2020-11-18 | 0 |
| 一種 | 加元 | 2020-11-19 | 0 |
| 一種 | 加元 | 2020-11-20 | 0 |
| 一種 | 加元 | 2020-11-21 | 0 |
| 一種 | 加元 | 2020-11-22 | 0 |
| 一種 | 加元 | 2020-11-23 | 0 |
| 一種 | 加元 | 2020-11-24 | 0 |
| 一種 | 加元 | 2020-11-25 | 0 |
| 一種 | 加元 | 2020-11-26 | 0 |
| 一種 | 加元 | 2020-11-27 | 0 |
| 一種 | 加元 | 2020-11-28 | 0 |
| 一種 | 加元 | 2020-11-29 | 0 |
| 一種 | 加元 | 2020-11-30 | 0 |
| 一種 | 加元 | 2020-12-01 | 0 |
| 一種 | 加元 | 2020-12-02 | 0 |
| 一種 | 加元 | 2020-12-03 | 0 |
| 一種 | 加元 | 2020-12-04 | 0 |
| 一種 | 加元 | 2020-12-05 | 0 |
| 一種 | 加元 | 2020-12-06 | 0 |
| 一種 | 加元 | 2020-12-07 | 0 |
| 一種 | 加元 | 2020-12-08 | 0 |
| 一種 | 加元 | 2020-12-09 | 0 |
| 一種 | 加元 | 2020-12-10 | 0 |
| 一種 | 加元 | 2020-12-11 | 0 |
| 一種 | 加元 | 2020-12-12 | 0 |
| 一種 | 加元 | 2020-12-13 | 0 |
| 一種 | 加元 | 2020-12-14 | 0 |
| 一種 | 加元 | 2020-12-15 | 0 |
| 一種 | 加元 | 2020-12-16 | 0 |
| 一種 | 加元 | 2020-12-17 | 0 |
| 一種 | 加元 | 2020-12-18 | 0 |
| 一種 | 加元 | 2020-12-19 | 0 |
| 一種 | 加元 | 2020-12-20 | 0 |
| 一種 | 加元 | 2020-12-21 | 0 |
| 一種 | 加元 | 2020-12-22 | 0 |
| 一種 | 加元 | 2020-12-23 | 0 |
| 一種 | 加元 | 2020-12-24 | 0 |
| 一種 | 加元 | 2020-12-25 | 0 |
| 一種 | 加元 | 2020-12-26 | 0 |
| 一種 | 加元 | 2020-12-27 | 0 |
| 一種 | 加元 | 2020-12-28 | 0 |
| 一種 | 加元 | 2020-12-29 | 0 |
| 一種 | 加元 | 2020-12-30 | 0 |
| 一種 | 加元 | 2020-12-31 | 0 |
| 一種 | 加元 | 2021-01-01 | 0 |
| 一種 | 加元 | 2021-01-02 | 0 |
| 一種 | 加元 | 2021-01-03 | 0 |
| 一種 | 加元 | 2021-01-04 | 5 |
| 一種 | 加元 | 2021-01-05 | 0 |
| 一種 | 加元 | 2021-01-06 | 6 |
| 一種 | 美元 | 2020-01-01 | 3 |
| 一種 | 美元 | 2020-01-02 | 0 |
| 一種 | 美元 | 2020-01-03 | 4 |
| 一種 | 美元 | 2020-01-04 | 2 |
| 乙 | 加元 | 2017-01-04 | 3 |
| 乙 | 加元 | 2017-01-05 | 0 |
| 乙 | 加元 | 2017-01-06 | 2 |
| 乙 | 加元 | 2017-01-07 | 2 |
| 乙 | 美元 | 2019-01-01 | 3 |
| 乙 | 美元 | 2019-01-02 | 0 |
| 乙 | 美元 | 2019-01-03 | 4 |
| 乙 | 美元 | 2019-01-04 | 5 |
uj5u.com熱心網友回復：
使用 Simeon 的假資料，另一種使用repeat和split_to_table浮現在腦海中。這個想法是為每個名稱和貨幣創建我們需要的確切數量的組合，然后使用基于集合的邏輯來創建最終輸出
with lookup (name, currency, min_date, day_count) as
(select name,currency, min(date), datediff(day,min(date),max(date))
from fake_data
group by name, currency)
select name, currency, date, amount --start with what you already have
from fake_data
union all
select name, currency, dateadd('day',b.index-1,min_date), 0 -- add in all the combinations
from lookup, lateral split_to_table(repeat('.',day_count), '.') b
minus
select name, currency, date, 0 --remove the combinations you already have the amounts for
from fake_data
order by name, currency, date
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/441134.html
標籤：sql date snowflake-cloud-data-platform
上一篇：來自2個日期物件的子字串日期