我有一張表，每個月都會給我資料，我需要那個時間范圍。我注意到有時我沒有 3/4 個月的資料，但我需要復制缺少時間戳的最后一行可用。
例子：
| product_id | 總收入 | 年月 |
|---|---|---|
| 1 | 50 | 202201 |
| 2 | 17 | 202201 |
| 3 | 30 | 202201 |
| 1 | 67 | 202202 |
| 2 | 31 | 202202 |
| 1 | 67 | 202203 |
| 2 | 31 | 202203 |
| 3 | 33 | 202203 |
但我需要一個像這樣的輸出：
| product_id | 總收入 | 年月 |
|---|---|---|
| 1 | 50 | 202201 |
| 2 | 17 | 202201 |
| 3 | 30 | 202201 |
| 1 | 67 | 202202 |
| 2 | 31 | 202202 |
| 3 | 30 | 202202 |
| 1 | 67 | 202203 |
| 2 | 31 | 202203 |
| 3 | 33 | 202203 |
我有一個選擇陳述句，例如：
select
product_id, total_revenue, yearmonth
from
revenue
我發現了一個類似的問題，（Postgresql 比較連續的行并在沒有值的情況下插入相同的行）但是在 Impala 中我沒有橫向連接，有人知道我該怎么做嗎？
uj5u.com熱心網友回復：
我做到了！
with crossed as
(
select
product_id,id_month,
rank() over (partition by product_id order by id_month asc) as r
from
(
select distinct cast(id_month as string) as id_month
from calendar d
where day_data <= date_sub(now(), interval 1 month)
) a
cross join
(select product_id, min(concat(year,month)) as minimum
from revenue
group by product_id
) b
where a.id_month >= b.minimum
)
, created as
(
select
coalesce(a.product_id,b.product_id) as product_id,
coalesce(concat(a.year,a.month),b.id_month) as id_month,
a.total_revenue,
b.r
from revenue a
full outer join crossed b
on a.product_id=b.product_id and concat(a.year,a.month)=b.id_month
where a.year is null
)
,
real as
(
select
coalesce(a.product_id,b.product_id) as product_id,
coalesce(concat(a.year,a.month),b.id_month) as id_month,
a.total_revenue,
b.r
from revenue a
full outer join crossed b
on a.product_id=b.product_id and concat(a.year,a.month)=b.id_month
where a.year is not null
)
select product_id,id_month,total_revenue,'CREATED' as tipe
from
(
select created.product_id,created.id_month,real.total_revenue,
rank () over (partition by created.product_id,created.id_month order by (created.r-real.r) asc) as r
from
created left join real on created.product_id=real.product_id
and created.id_month > real.id_month
)a
where r=1
union
select product_id,concat(year,month) as id_month,total_revenue,'REAL' as tipe
from revenue
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/474924.html
上一篇：遞回查詢以產生路徑的邊緣？