根据案例陈述从分组中选择

基于这样的表,我想按PRODID分组,然后根据case语句选择一行。案例陈述是,如果IND1 = 1选择行,否则,如果IND2 = 1选择行,否则选择最大DATE的行。如果组中的多个指标为1,则选择行,指标组中的最大日期为1。

PRODID   IND1       IND2       DATE
---------------------------------------------------
1          1         0      4/24/2020
1          0         0      1/2/2020
1          1         0      1/1/2020
2          0         1      4/24/2020
2          0         1      1/1/2020
3          0         0      4/24/2020
3          0         0      1/1/2020
4          1         0      2/2/2020
4          0         1      4/24/2020
4          0         1      4/24/2020
PRODID   IND1       IND2       DATE
---------------------------------------------------
1          1         0      4/24/2020
2          0         1      4/24/2020
3          0         0      4/24/2020
4          1         0      2/2/2020
评论
  • 笑狠徦
    笑狠徦 回复

    One method uses row_number():

    select t.*
    from (select t.*,
                 row_number() over (partition by prodid
                                    order by (case when ind1 > 0 then 1 else 2 end),
                                             (case when ind1 = 0 and ind2 > 0 then 1 else 2 end),
                                             date desc
                                   ) as seqnum
    
          from t
         ) t
    where seqnum = 1;
    

    如果您喜欢算术,可以将其简化为:

    select t.*
    from (select t.*,
                 row_number() over (partition by prodid
                                    order by ind1 desc, ind2 * (1 - ind1) desc, date desc
                                   ) as seqnum
    
          from t
         ) t
    where seqnum = 1;
    

    请注意,将逻辑组合在一起的一个技巧是确保在以下情况下使用第一行:

    1       0      0      2020-01-21
    1       1      0      2020-01-20
    1       1      1      2020-01-19
    

    This is why you can't use order by ind1 desc, ind2 desc, date desc (which is quite tempting).