对于下面的示例,如果我使用下面的相同数据,并且我希望 Mary 和 Peter 帐户在同一日期范围内,我将如何修改 hive sql 查询来执行此操作?例如,将日期范围设置在“2021-05-24”和“2021-06-03”之间,并填充此期间的所有余额。如果我们以 Mary 为例,我还希望看到 Mary 可用余额 ‘53028.1’ 向前填充到 ‘2021-06-03’ 并且如果 Mary 在 ‘2021-05-24’ 上没有值’ 将其填充 ‘50000’ 的余额。
with mytable as (--Demo dataset, use your table instead of this
select stack(10, --number of tuples
'Peter',float(50000),'2021-05-24',
'Peter',float(50035),'2021-05-25',
'Peter',float(50035),'2021-05-26',
'Peter',float(50610),'2021-05-28',
'Peter',float(51710),'2021-06-01',
'Peter',float(53028.1),'2021-06-02',
'Peter',float(53916.1),'2021-06-03',
'Mary',float(50000),'2021-05-24',
'Mary',float(50035),'2021-05-25',
'Mary',float(53028.1),'2021-05-30'
) as (account_name,available_balance,Date_of_balance)
) --use your table instead of this CTE
select
account_name, available_balance, date_add(Date_of_balance,e.i) as Date_of_balance
from
( --Get next_date to generate date range
select account_name,available_balance,Date_of_balance,
lead(Date_of_balance,1, Date_of_balance) over (partition by account_name order by Date_of_balance) next_date
from mytable d --use your table
) s
lateral view outer posexplode(split(space(datediff(next_date,Date_of_balance)-1),'')) e as i,x --generate rows
order by account_name desc, Date_of_balance --this is to have order of rows like in your Converted Table
实现方式:
另外计算整个数据集的最小和最大日期以确定所需的日期范围,还计算每个帐户的最小日期以检查最小日期是否需要修复。然后为两个日期添加额外的计算步骤:检查它是否是边界日期,如果它们不符合要求,则相应地分配最小值和最大值。
在此示例中,Peter 的开始日期是 2021-05-24,而 Mary 的开始日期是 2021-05-23,因此,范围被扩展并为 Peter 生成了 2021-05-23 记录。对于 Mary 的最后日期是 2021-05-30,在范围末尾生成的缺失行。
with mytable as (--Demo dataset, use your table instead of this
select stack(10, --number of tuples
'Peter',float(50000),'2021-05-24',
'Peter',float(50035),'2021-05-25',
'Peter',float(50035),'2021-05-26',
'Peter',float(50610),'2021-05-28',
'Peter',float(51710),'2021-06-01',
'Peter',float(53028.1),'2021-06-02',
'Peter',float(53916.1),'2021-06-03', -------------end date greater than Mary
'Mary',float(50000),'2021-05-23', ----------------start date Less than Peter
'Mary',float(50035),'2021-05-25',
'Mary',float(53028.1),'2021-05-30'
) as (account_name,available_balance,Date_of_balance)
) --use your table instead of this CTE
select account_name, available_balance, date_add(Date_of_balance,e.i) as Date_of_balance
from
(
select account_name, available_balance,
case when min_date < min_date_account and Date_of_balance = min_date_account then min_date
else Date_of_balance
end Date_of_balance,
case when (next_date is null) and (Date_of_balance = max_date) then Date_of_balance
when (Date_of_balance < max_date) then nvl(next_date,date_add(max_date,1))
end as next_date
from
( --Get next_date to generate date range
select account_name,available_balance,Date_of_balance,
lead(Date_of_balance,1) over (partition by account_name order by Date_of_balance) next_date,
max(Date_of_balance) over() max_date, --total min and max dates all accounts should align
min(Date_of_balance) over() min_date,
min(Date_of_balance) over(partition by account_name) min_date_account
from mytable d --use your table
) s
) s
lateral view outer posexplode(split(space(datediff(next_date,Date_of_balance)-1),'')) e as i,x --generate rows
order by account_name desc, Date_of_balance --this is to have order of rows like in your Converted Table
结果:
account_name available_balance date_of_balance
Peter 50000 2021-05-23
Peter 50000 2021-05-24
Peter 50035 2021-05-25
Peter 50035 2021-05-26
Peter 50035 2021-05-27
Peter 50610 2021-05-28
Peter 50610 2021-05-29
Peter 50610 2021-05-30
Peter 50610 2021-05-31
Peter 51710 2021-06-01
Peter 53028.1 2021-06-02
Peter 53916.1 2021-06-03
Mary 50000 2021-05-23
Mary 50000 2021-05-24
Mary 50035 2021-05-25
Mary 50035 2021-05-26
Mary 50035 2021-05-27
Mary 50035 2021-05-28
Mary 50035 2021-05-29
Mary 53028.1 2021-05-30
Mary 53028.1 2021-05-31
Mary 53028.1 2021-06-01
Mary 53028.1 2021-06-02
Mary 53028.1 2021-06-03
注意 lead 函数的计算方式也不同,它没有默认值,NULL 表示可用的结束日期
space(int n) : 返回n个空格