“合併”分組行（按列，選擇第一個非空值，按另一列排序）

我想提前為這個糟糕的標題道歉，但我很難描述我的問題。

我有一張這樣的桌子：

+---+--------------+--------------+-------+-------+-------+
|   | RECORD_LEVEL | RECORD_GROUP | COL_A | COL_B | COL_C |
+---+--------------+--------------+-------+-------+-------+
| 1 |            1 | group1       | NULL  | 0     | bar1  |
| 2 |            2 | group1       | foo2  | -1    | bar2  |
| 3 |            2 | group2       | foo3  | NULL  | NULL  |
| 4 |            5 | group2       | foo4  | 5     | bar4  |
| 5 |            1 | group3       | NULL  | 1     | NULL  |
| 6 |            2 | group3       | foo6  | 1     | NULL  |
+---+--------------+--------------+-------+-------+-------+

我需要對它進行分組RECORD_GROUP，使用每列的第一個非 NULL 值。“第一”是指ORDER BY RECORD_LEVEL ASC. 我想要的結果是這樣的：

+---+--------------+-------+-------+-------+
|   | RECORD_GROUP | COL_A | COL_B | COL_C |
+---+--------------+-------+-------+-------+
| 1 | group1       | foo2  |     0 | bar1  |
| 2 | group2       | foo3  |     5 | bar4  |
| 3 | group3       | foo6  |     1 | NULL  |
+---+--------------+-------+-------+-------+

我試圖在這個小提琴中重現這個場景：http ://rextester.com/UMMP61375

請隨時詢問更多資訊或場所。

您可以FIRST_VALUE為此使用分析功能：

select distinct
 record_group,
 first_value(col_a ignore nulls) over
   (partition by record_group order by record_level
     rows between unbounded preceding and unbounded following) as col_a,
 first_value(col_b ignore nulls) over 
   (partition by record_group order by record_level
     rows between unbounded preceding and unbounded following) as col_b,
 first_value(col_c ignore nulls) over
   (partition by record_group order by record_level
     rows between unbounded preceding and unbounded following) as col_c
from
 tbl_record
order by
 record_group
;

RECORD_GROUP         COL_A                     COL_B COL_C              
-------------------- -------------------- ---------- --------------------
group1               foo2                          0 bar1                
group2               foo3                          5 bar4                
group3               foo6                          1  

我們根據記錄FIRST_VALUE級別（ignore nulls_ _帶有 的重複行。record_group``partition by record_group``rows between unbounded preceding and unbounded following``order by record_level``DISTINCT

引用自：https://dba.stackexchange.com/questions/155472