如何将一个记录范围转换为sql中该范围之后的记录值?

col17t5w  于 2021-07-24  发布在  Java
关注(0)|答案(3)|浏览(247)

我正在尝试用正确的记录替换特定日期范围内的一些错误输入记录。然而,我不确定是否有一个有效的方法来做到这一点。因此,我的问题是如何在sql中将(静态)记录范围转换为该范围之后的记录值?下面你会发现一个例子来阐明我试图实现什么。

在本例中,您可以看到1号客户在2020年6月25日至2020年6月29日期间属于0号组(无)。从2020年6月30日到2020年7月5日,1号客户的组号从0变为11。此静态周期包含错误的记录,应更改为在2020年7月6日有效的值(组号==10)。有办法吗?

vfwfrxfs

vfwfrxfs1#

如果我理解正确,您可以使用窗口函数获取特定日期和时间的数据 case 分配给特定日期范围的逻辑:

select t.*,
       (case when date >= '2020-07-01' and date <= '2020-07-05'
             then max(case when date = '2020-07-06' then group_number end) over (partition by customer_number)
             else group_number
        end) as imputed_group_number,
       (case when date >= '2020-07-01' and date <= '2020-07-05'
             then max(case when date = '2020-07-06' then role end) over (partition by customer_number)
             else role
        end) as imputed_role
from t;

如果要更新值,可以使用 JOIN :

update t
    set group_number = tt.group_number,
        role = tt.role
    from tt
    where tt.customer_number = t.customer_number and tt.date = '2020-07-06'
9njqaruj

9njqaruj2#

我认为Windows的功能 first_value() 你想要什么:

select 
    date,
    customer_number,
    first_value(group_number) over(partition by customer_number order by date) group_number,
    first_value(role)         over(partition by customer_number order by date) role
from mytable
ss2ws0br

ss2ws0br3#

你可以做下面的例子。这里我选择了一个条件,即如果role='leader'是一个坏记录,那么您将应用groupnumber1和role1列中的下一个可用组编号-->。
我在excel示例中使用了较小的行子集。

select date1
        ,customer_number
        ,group_number
        ,case when role='Leader' then 
                  (select t1.group_number
                     from t t1
                    where t1.date1>t.date1
                      and t1.role<>'Leader'
                   order by t1.date1 asc
                   limit 1
                  ) 
             else group_number 
         end as group_number1
        ,role
       ,case when role='Leader' then 
                  (select t1.role
                     from t t1
                    where t1.date1>t.date1
                      and t1.role<>'Leader'
                   order by t1.date1 asc
                   limit 1
                  ) 
             else role 
         end as role1
   from t
order by 1   

+------------+-----------------+--------------+---------------+--------+--------+
|   DATE1    | CUSTOMER_NUMBER | GROUP_NUMBER | GROUP_NUMBER1 |  ROLE  | ROLE1  |
+------------+-----------------+--------------+---------------+--------+--------+
| 2020-06-25 |               1 |            0 |             0 | None   | None   |
| 2020-06-26 |               1 |            0 |             0 | None   | None   |
| 2020-06-27 |               1 |            0 |             0 | None   | None   |
| 2020-06-28 |               1 |            0 |             0 | None   | None   |
| 2020-06-29 |               1 |            0 |             0 | None   | None   |
| 2020-06-30 |               1 |           11 |            10 | Leader | Member |
| 2020-07-01 |               1 |           11 |            10 | Leader | Member |
| 2020-07-06 |               1 |           10 |            10 | Member | Member |
+------------+-----------------+--------------+---------------+--------+--------+

db小提琴链接https://dbfiddle.uk/?rdbms=db2_11.1&fiddle=c95d12ced067c1df94947848b5a94c14

相关问题