Impala比较连续行，并插入相同的行，如果没有值

Question

我有一个表格，每个月都会为我提供数据，我需要这段时间。我注意到有时我有3/4个月没有数据，但是我需要复制丢失的时间戳的最后一行。

示例：

product_id	total_revenue	eymonth
1	50	202201
2	17	202201
3	30	202201
1	67	202202
2	31	202202
1 67 202202 1	67	202203
2	31	202203
3	33	202203，

但我需要一个输出，例如：

product_ID	tote_id tote_id tote_ide_revenue	yearth
1	3	502201 2 17 202201
302201 3	302201	3 302201 3 302201
3	67 67 7 67 7	302201 3 302201 3 302201 3 302201
3	302201 1 67 67	67 7 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

我找到了类似的问题， （如果没有值）插入相同的行，但是在Impala中，我没有横向连接，有人知道我该怎么做吗？

Answer 1

我做到了！

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