sql - 如果滞后中使用的列具有相同的值,则在使用滞后函数时分配相同的值
问题描述
我在 sql 中有一个表,内容如下
+---+----------+----------+----------+--------+
| pk| from_d| to_d| load_date| row_num|
+---+----------+----------+----------+--------+
|111|2019-03-03|2019-03-03|2019-03-03| 1|
|111|2019-02-02|2019-02-02|2019-02-02| 2|
|111|2019-02-02|2019-02-02|2019-02-02| 2|
|111|2019-01-01|2019-01-01|2019-01-01| 3|
|222|2019-03-03|2019-03-03|2019-03-03| 1|
|222|2019-01-01|2019-01-01|2019-01-01| 2|
|333|2019-02-02|2019-02-02|2019-02-02| 1|
|333|2019-01-01|2019-01-01|2019-01-01| 2|
|444|2019-02-02|2019-02-02|2019-02-02| 1|
|555|2019-03-03|2019-03-03|2019-03-03| 1|
+---+----------+----------+----------+--------+
现在我想to_d
使用如下条件更新 row_num > 1 的列
when row_num = 2 then to_d column should have row_num 1 row's from_d - 1 day
when row_num = 3 then to_d column should have row_num 2 row's from_d - 1 day
and so on
if row_num =1 then to_d should not be updated
我已经使用了LAG
如下功能
select pk, from_d,
case when row_num = 1 then to_d else date_sub(lag(from_d) over (), 1) end as to_d,
row_num from table;
这给了我如下结果
output received
+---+----------+----------+-------------------+
| pk| from_d| to_d|row_number_window_0|
+---+----------+----------+-------------------+
|111|2019-03-03|2019-03-03| 1|
|111|2019-02-02|2019-03-02| 2|
|111|2019-02-02|2019-02-01| 2|
|111|2019-01-01|2019-02-01| 3|
|222|2019-03-03|2019-03-03| 1|
|222|2019-01-01|2019-03-02| 2|
|333|2019-02-02|2019-02-02| 1|
|333|2019-01-01|2019-02-01| 2|
|444|2019-02-02|2019-02-02| 1|
|555|2019-03-03|2019-03-03| 1|
+---+----------+----------+-------------------+
在上面对于PK
111
row_num =2
列to_d
不同的地方,我希望它们具有相同的值。
expected output
+---+----------+----------+-------------------+
| pk| from_d| to_d|row_number_window_0|
+---+----------+----------+-------------------+
|111|2019-03-03|2019-03-03| 1|
|111|2019-02-02|2019-03-02| 2|
|111|2019-02-02|2019-03-02| 2|
|111|2019-01-01|2019-02-01| 3|
|222|2019-03-03|2019-03-03| 1|
|222|2019-01-01|2019-03-02| 2|
|333|2019-02-02|2019-02-02| 1|
|333|2019-01-01|2019-02-01| 2|
|444|2019-02-02|2019-02-02| 1|
|555|2019-03-03|2019-03-03| 1|
+---+----------+----------+-------------------+
我怎样才能做到这一点
Edit
select pk, from_d,
case when row_num = 1 then to_d else date_sub(lag(from_d) over ( order by from_d), 1) end as to_d,
row_num from table;
即使在使用order by
后lag
我仍然得到相同的结果
解决方案
对于每个 pk,构建一个map<row_num, from_date>
. 然后您可以使用 map 查找row_num-1
from_date 并减去 1 天。
演示:
with mytable as (--test dataset. Use your table instead of this
select stack(10,
111,'2019-03-03','2019-03-03','2019-03-03',1,
111,'2019-02-02','2019-02-02','2019-02-02',2,
111,'2019-02-02','2019-02-02','2019-02-02',2,
111,'2019-01-01','2019-01-01','2019-01-01',3,
222,'2019-03-03','2019-03-03','2019-03-03',1,
222,'2019-01-01','2019-01-01','2019-01-01',2,
333,'2019-02-02','2019-02-02','2019-02-02',1,
333,'2019-01-01','2019-01-01','2019-01-01',2,
444,'2019-02-02','2019-02-02','2019-02-02',1,
555,'2019-03-03','2019-03-03','2019-03-03',1
) as (pk,from_d,to_d,load_date,row_num)
)
select pk, from_d,
case when row_num>1 then date_sub(row_num_from_d_map[row_num-1],1) else to_d end to_d,
row_num
from
(
SELECT pk,from_d, to_d, row_num,
--for each pk build a map<row_num, from_date>
str_to_map(concat_ws(',',collect_set(concat(row_num,':', from_d)) over(partition by pk))) row_num_from_d_map
FROM mytable
)s
order by pk, row_num --need this to have resultset in the same order for easy result check, remove for better performance
;
结果:
pk from_d to_d row_num
111 2019-03-03 2019-03-03 1
111 2019-02-02 2019-03-02 2
111 2019-02-02 2019-03-02 2
111 2019-01-01 2019-02-01 3
222 2019-03-03 2019-03-03 1
222 2019-01-01 2019-03-02 2
333 2019-02-02 2019-02-02 1
333 2019-01-01 2019-02-01 2
444 2019-02-02 2019-02-02 1
555 2019-03-03 2019-03-03 1
推荐阅读
- git - Git:在特性分支中压缩或变基时,为什么需要将它与另一个分支(如 master)进行比较?
- git - 是否可以将 git filter-branch 与脏工作目录一起使用?
- mysql - 可以在 MySQL 中使用“For Each”循环来生成带有变量的临时表吗?
- php - 从内部(第二级)页面提交表格
- bash - 如何计算属于集群的序列?(来自 cd-hit 输出)
- arduino - 我正在尝试使用电报机器人库配置电报机器人,但出现错误
- angular - 将字符串转换为类打字稿/角度
- java - 在 IntelliJ IDEA 中运行单个 Junit 测试
- python - 在 Python 中解析 XML 文件:检查标签或项目时没有结果
- sql - 运行联合查询时,')' 附近的语法不正确