我有一個這樣的資料集：
| 日期 | 帳戶 | 花費 |
|---|---|---|
| 2/1/21 | 一個 | 4 |
| 21 年 3 月 1 日 | 一個 | 6 |
| 21 年 5 月 1 日 | 一個 | 7 |
| 21 年 6 月 1 日 | 一個 | 2 |
| 21 年 4 月 1 日 | 乙 | 8 |
| 21 年 5 月 1 日 | 乙 | 2 |
| 21 年 6 月 1 日 | 乙 | 1 |
| 21 年 9 月 1 日 | 乙 | 7 |
請注意，帳戶跨越的日期不相同。我想為每個帳戶的最小日期和最大日期之間的缺失月份填寫零。決賽桌將如下所示：
| 日期 | 帳戶 | 花費 |
|---|---|---|
| 2/1/21 | 一個 | 4 |
| 21 年 3 月 1 日 | 一個 | 6 |
| 21 年 4 月 1 日 | 一個 | 0 |
| 21 年 5 月 1 日 | 一個 | 7 |
| 21 年 6 月 1 日 | 一個 | 2 |
| 21 年 4 月 1 日 | 乙 | 8 |
| 21 年 5 月 1 日 | 乙 | 2 |
| 21 年 6 月 1 日 | 乙 | 1 |
| 21 年 7 月 1 日 | 乙 | 0 |
| 21 年 8 月 1 日 | 乙 | 0 |
| 21 年 9 月 1 日 | 乙 | 7 |
在 Snowflake SQL 中解決這個問題的最佳方法是什么？
我想我可以將帳戶與另一個包含所有月份的表交叉加入。然后我可以加入原始表并用零填充 Spend 列中的任何缺失值。但我不確定如何處理由此產生的“滯后”和“領先”空值。例如，交叉連接后 2/1/21 和 B 的組合會有一個空值，但該日期發生在原始表中第一次出現 B 之前（21 年 4 月 1 日），所以我不會想要我最終資料集中的那一行。
uj5u.com熱心網友回復：
我們確實可以使用月份開始的日歷表來解決這個問題，例如calendar(date)。
我們可以在聚合子查詢中定義每個賬戶的日期范圍，然后cross join用日歷表；這為我們提供了所有可能的日期/帳戶元組。剩下要做的就是嘗試將相應的行（如果有）與left join.
select c.date, a.account, coalesce(t.spend, 0) spend
from (
select account, min(date) min_date, max(date) max_date
from mytable
group by account
) a
inner join calendar c on c.date >= a.min_date and c.date <= a.max_date
left join mytable t on t.date = c.date and t.account = a.account
uj5u.com熱心網友回復：
步驟和方法：
首先，您需要使用
first_value函式找到每個組的最大和最小日期您需要一個包含唯一月份和所有唯一帳戶的資料集 - 這就是
cross join進來的地方。您可以相應地調整表的名稱您需要將 max_date 和 min_date 值連接到每個帳戶值，這需要過濾掉 min_date 超出實際資料集范圍的行，就像您描述帳戶的情況一樣B 代表日期 2/1/21。要實作這一點，您只需要在密鑰上加入
accounts = accounts然后，您還需要根據日期和帳戶加入，即
m2根據日期和帳戶獲取支出值where最后，您可以過濾掉在子句中給定帳戶的原始日期的最大值之后和最小值之前出現的行
with main as (
select
date,
account,
coalesce(spend,0) as total_spend,
first_value(Date) over(partition by account order by date desc) as max_date,
first_value(Date) over(partition by account order by date) as min_date
from <table_name>
),
combining as (
-- make sure you have distinct accounts and months stored in these tables
select distinct accounts from <account_name_table>
cross join <calendar_month_table>
),
joining as (
select
c.date,
c.accounts,
coalesce(m2.total_spend,0) as new_spend,
main.max_date,
main.min_date
from combining
left join main
on combining.accounts = main.accounts
left join main as m2
on combining.accounts = m2.accounts
and combining.date = m2.date
)
select * from joining where min_date <= date and max_date >= date
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/529778.html
上一篇：將資料從一個表拆分到另一個表
下一篇：PHP：如何用多個選擇表沖刺？