Sql 根据列规则 Select 第一个匹配项

发布于04月21日

Problem Statement:需要根据定义的hier规则列进行任何第一次匹配

Sample:

with cte_tab as (select column1 as hier, column2 as rule, column3 as sec_id from values
       (1, 'NO','COM'), (1, 'NO','COM'), (2, 'NO','COM'), (3, 'YES','COM')
    order by column1)
select * from cte_tab;

Expected Output:

注意:hier可能有多个条目，如果规则=‘是’对该组有效，则返回所有条目是有效的方案.

推荐答案

使用QAULIFY和DENSE_RANK:

with cte_tab as (
   select column1 as hier, column2 as rule, column3 as sec_id 
   from values
       (1, 'NO','COM'), (1, 'NO','COM'), (2, 'NO','COM'), (3, 'YES','COM')
   order by column1)
select * 
from cte_tab
QUALIFY DENSE_RANK() OVER(ORDER BY RULE='YES' DESC, HIER) = 1;

对于HIER 3，此版本仅返回YES

输出:

如果目标是获得整个HIER 3，而不考虑规则，那么:

with cte_tab as (
    select column1 as hier, column2 as rule, column3 as sec_id 
    from values
       (1, 'NO','COM'), (1, 'NO','COM'), (2, 'NO','COM'),
       (3, 'YES','COM'), (3, 'NO', 'A')
    order by column1)
select * 
from cte_tab
QUALIFY HIER=(ARRAY_AGG(HIER) WITHIN GROUP(ORDER BY RULE='YES' DESC) OVER())[0]::TEXT

输出: