sql - 识别 NULL 并分配位图

Question

我有一张 table

表1

u_a_id  e_id   e_nm    e_val    e_seq
1       104    test    100      4
1       102    test2   x        2
1       102    test2   (null)   1
1       104    test    (null)   1

2       102    test2   (null)   2
2       102    test2   (null)   1
2       104    test    101      1
2       104    test    102      2

我需要先按 e_id、e_seq 对每个 u_a_id 进行排序，并将每个 (null) 标识为 1，然后创建位图为下面。

例如。

• u_a_id = 1 - 第一个 e_id = 102, e_seq = 1, e_val = (null), 所以赋值 1
• u_a_id = 1 - 第一个e_id = 102，e_seq = 2，e_val = x，所以赋值0
• u_a_id = 1 - 第一个 e_id = 104, e_seq = 1, e_val = (null), 所以赋值 1
• u_a_id = 1 - 第一个e_id = 104，e_seq = 4，e_val = 100，所以赋值0

因此，对于 u_a_id = 1，添加一个新行 - EMPTY = 1010

输出将是:

u_a_id  e_id   e_nm    e_val    e_seq
1       104    test    100      4
1       102    test2   x        2
1       102    test2   (null)   1
1       104    test    (null)   1
1       (null) EMPTY   1010     (null)

2       102    test2   (null)   2
2       102    test2   (null)   1
2       104    test    101      1
2       104    test    102      2
2       (null) EMPTY   1100     (null)

Oracle SQL 中有办法做到这一点吗？

Answer 1

您可以使用 LISTAGG 进行字符串聚合和 GROUPING SETS:

SELECT U_A_ID, E_ID
 ,CASE WHEN GROUPING_ID(U_A_ID, E_ID, E_NM, E_VAL, E_SEQ) = 15 
       THEN 'EMPTY' 
       ELSE E_NM END AS E_NM
 ,CASE WHEN GROUPING_ID(U_A_ID, E_ID, E_NM, E_VAL, E_SEQ) = 15 
       THEN LISTAGG(NVL2(E_VAL, '1', '0'),'') WITHIN GROUP (ORDER BY E_ID DESC, E_SEQ DESC) 
       ELSE E_VAL END AS E_VAL
 ,E_SEQ
FROM E
GROUP BY GROUPING SETS ((U_A_ID, E_ID, E_NM, E_VAL, E_SEQ), (U_A_ID))
ORDER BY U_A_ID, E_ID, E_SEQ;

db<>fiddle demo