如何保留以前记录中的值

Question

我的原始表'T1'如下所示：

     ID  Date        Order_ind   Var2   Var3
      1  1/1/2015            1  .....  .....
      1  1/5/2015            1  .....  .....
      1  1/5/2015            2  .....  .....
      2  1/10/2015           1  .....  .....
      2  1/20/2015           1  .....  .....
      2  1/20/2015           2  .....  .....
      2  1/20/2015           3  .....  .....

我要创建的最后一个表是根据一些条件添加一个额外的变量"new_var"。正如您可能注意到的，有些记录具有相同的日期，并且这些条件仅适用于第一条记录（order_ind=1）。对于其他日期相同的记录，例如order_ind=2或3，new_var值应与order_ind=1记录相同。

     ID  Date        order_ind   Var1   Var2    new_var   
      1  1/1/2015            1  .....  .....    1
      1  1/5/2015            1  .....  .....    0
      1  1/5/2015            2  .....  .....    0
      2  1/10/2015           1  .....  .....    0
      2  1/20/2015           1  .....  .....    1
      2  1/20/2015           2  .....  .....    1
      2  1/20/2015           3  .....  .....    1

我写的SQL代码如下：

     SELECT *,
            CASE
            WHEN order_ind=1 and (criteria1....) THEN '1'
            WHEN order_ind=1 and (criteria2....) THEN '0' 
            WHEN order_ind<>1 .......(please advise how to code this) 
            END AS new_var
     FROM T1
     ;

了解如何编写order_ind<>记录的代码1.

Answer 1

我会在几次中完成这项工作。首先，创建一个仅包含order_ind = 1记录值的ind_1_new_var列。

select
  *
  ,case
    when order_ind = 1 and (criteria1...) then 1
    when order_ind = 1 and (criteria2...) then 0
    else null
  end ind_1_new_var
from
  t1;

然后参照此列构建您的new_var。

select
  *
  ,case
    when order_ind = 1 and (criteria1...) then 1
    when order_ind = 1 and (criteria2...) then 0
    else null
  end ind_1_new_var
  ,max(ind_1_new_var) over (
    partition by id, date
  ) new_var
from
  t1;

我不知道你的criteria1，但这是我的nz数据库中的一个工作示例，包含你提供的数据。

TEST_DB(ADMIN)=> select * from t1 order by 1,2,3;
 ID |  T1_DATE   | ORDER_IND | VAR1 | VAR2
----+------------+-----------+------+------
  1 | 2015-01-01 |         1 |    0 |    0
  1 | 2015-01-05 |         1 |    0 |    0
  1 | 2015-01-05 |         2 |    0 |    0
  2 | 2015-01-10 |         1 |    0 |    0
  2 | 2015-01-20 |         1 |    0 |    0
  2 | 2015-01-20 |         2 |    0 |    0
  2 | 2015-01-20 |         3 |    0 |    0
(7 rows)
TEST_DB(ADMIN)=> select
TEST_DB(ADMIN)->   *
TEST_DB(ADMIN)->   ,case
TEST_DB(ADMIN)->     when order_ind = 1 and (
TEST_DB(ADMIN)(>       (id = 1 and t1_date = '2015-01-01')
TEST_DB(ADMIN)(>       or (id = 2 and t1_date = '2015-01-20')
TEST_DB(ADMIN)(>     ) then 1
TEST_DB(ADMIN)->     when order_ind = 1 and (
TEST_DB(ADMIN)(>       (id = 1 and t1_date = '2015-01-05')
TEST_DB(ADMIN)(>       or (id = 2 and t1_date = '2015-01-10')
TEST_DB(ADMIN)(>     ) then 0
TEST_DB(ADMIN)->     else null
TEST_DB(ADMIN)->   end ind_1_new_var
TEST_DB(ADMIN)->   ,max(ind_1_new_var) over (
TEST_DB(ADMIN)(>     partition by id, t1_date
TEST_DB(ADMIN)(>   ) new_var
TEST_DB(ADMIN)-> from
TEST_DB(ADMIN)->   t1
TEST_DB(ADMIN)-> order by 1,2,3;
 ID |  T1_DATE   | ORDER_IND | VAR1 | VAR2 | IND_1_NEW_VAR | NEW_VAR
----+------------+-----------+------+------+---------------+---------
  1 | 2015-01-01 |         1 |    0 |    0 |             1 |       1
  1 | 2015-01-05 |         1 |    0 |    0 |             0 |       0
  1 | 2015-01-05 |         2 |    0 |    0 |               |       0
  2 | 2015-01-10 |         1 |    0 |    0 |             0 |       0
  2 | 2015-01-20 |         1 |    0 |    0 |             1 |       1
  2 | 2015-01-20 |         2 |    0 |    0 |               |       1
  2 | 2015-01-20 |         3 |    0 |    0 |               |       1
(7 rows)