如果在滞后中使用的列具有相同的值，则在使用滞后函数时分配相同的值 [英] Assign same value when using lag function if column used in lag has same value

问题描述

我在下面的sql内容中有一个表

I have a table in sql contents are below

+---+----------+----------+----------+--------+
| 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

Now I want to update the to_d column where row_num > 1 using some conditions like below

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函数

I have used the LAG function like below

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;

这给了我类似下面的结果

This is giving me a result like below

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列是不同的，我希望它们具有相同的值.

In the above for PK 111 where row_num = 2 the to_d column is different I want them to be same value.

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;

即使在lag中使用了order by之后，我仍然得到相同的结果

Even after using order by in lag I am still getting the same result

推荐答案

对于每个pk，构建一个map<row_num, from_date>.然后，您可以使用地图从日期中查找row_num-1并减去1天.

For each pk, build a map<row_num, from_date>. Then you can use map for finding row_num-1 from_date and subtract 1 day.

演示:

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 

这篇关于如果在滞后中使用的列具有相同的值，则在使用滞后函数时分配相同的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！