如何將Oracle中的連續列值合并為一個并保存初始順序？

我有下表。

Table 1

 --------- ----------- 
| col_key | col_value |
 --------- ----------- 
| key1    | value1    |
| key1    | value2    |
| key1    | value3    |
| key1    | value2    |
| key1    | value2    |
| key1    | value2    |
| key1    | value1    |
| key1    | value1    |
| key1    | value3    |
| key1    | value3    |
| key1    | value3    |
| key1    | value2    |
 --------- ----------- 

我想得到以下輸出：

Table 2

 --------- ------------------------------------------------------------------- 
| col_key | col_value                                                         |
 --------- ------------------------------------------------------------------- 
| key1    | value1 | value2 | value 3 | value 2 | value 1 | value 3 | value 2 |
 --------- ------------------------------------------------------------------- 

演算法 - 如果連續值相同 - 將它們合并為一個。

我正在使用Oracle和listagg功能：

select 
  col_key, 
  listagg(distinct col_value, ' | ') within group (order by col_key) as col_value
from
  sample_table
group by col_key
order by col_key

但是，listagg帶有distinct關鍵字的函式會洗掉重復項。

那么，是否可以在 Oracle 中進行此操作（如表 2 所示）？

Oracle 版本（12c 和 18c）

uj5u.com熱心網友回復：

從 Oracle 12 開始，您可以使用MATCH_RECOGNIZE來執行逐行比較并聚合相鄰的重復項，然后您可以使用LISTAGG來聚合唯一值：

SELECT col_key,
       LISTAGG(value, ' | ') WITHIN GROUP (ORDER BY mno) AS col_value
FROM   (SELECT t.*,
               ROWNUM AS rn -- You need to provide a way of getting this order!
        FROM table1 t)
MATCH_RECOGNIZE(
  PARTITION BY col_key
  ORDER     BY rn
  MEASURES
    MATCH_NUMBER()   AS mno,
    FIRST(col_value) AS value
  PATTERN (same_value )
  DEFINE
    same_value AS FIRST(col_value) = col_value
)
GROUP BY col_key;

其中，對于樣本資料：

CREATE TABLE Table1 (col_key, col_value) AS
SELECT 'key1', 'value1' FROM DUAL UNION ALL
SELECT 'key1', 'value2' FROM DUAL UNION ALL
SELECT 'key1', 'value3' FROM DUAL UNION ALL
SELECT 'key1', 'value2' FROM DUAL UNION ALL
SELECT 'key1', 'value2' FROM DUAL UNION ALL
SELECT 'key1', 'value2' FROM DUAL UNION ALL
SELECT 'key1', 'value1' FROM DUAL UNION ALL
SELECT 'key1', 'value1' FROM DUAL UNION ALL
SELECT 'key1', 'value3' FROM DUAL UNION ALL
SELECT 'key1', 'value3' FROM DUAL UNION ALL
SELECT 'key1', 'value3' FROM DUAL UNION ALL
SELECT 'key1', 'value2' FROM DUAL;

輸出：

COL_KEY	COL_VALUE
鍵1	值1 | 值2 | 價值3 | 值2 | 值1 | 價值3 | 價值2

db<>在這里擺弄

uj5u.com熱心網友回復：

您說您將編輯問題并提供排序列；到目前為止，你沒有這樣做，所以我ROWID改用了。用你自己的專欄來改變它（一旦你發現要使用哪一個）。

閱讀代碼中的注釋。

SQL> with
  2  temp as
  3    -- find COL_VALUE and the value that follows it (NEXT_VALUE in this query.
  4    -- As I already said, I used ROWID for sorting purposes)
  5    (select col_key,
  6       col_value,
  7       rowid rid,
  8       lead(col_value) over (partition by col_key order by rowid) next_value
  9     from sample_table
 10    ),
 11  temp2 as
 12    -- "new" COL_VALUE will be the "original" COL_VALUE if it is different from its
 13    -- next value (or - for the last row - if there's no next value)
 14    (select col_key,
 15       rid,
 16       case when col_value <> next_value or next_value is null then col_value
 17            else null
 18       end col_value
 19     from temp
 20    )
 21  -- finally, aggregate the result
 22  select col_key,
 23    listagg(col_value, ' | ') within group (order by rid) col_value
 24  from temp2
 25  group by col_key;

   COL_KEY COL_VALUE
---------- ------------------------------------------------------------
         1 val1 | val2 | val3 | val2 | val1 | val3 | val2

SQL>

uj5u.com熱心網友回復：

這是另一種看法：

WITH
 cte1 AS (SELECT COL_KEY, 
                 COL_VALUE, 
                 LAG(COL_VALUE) OVER (ORDER BY ROWNUM) AS PREV_COL_VALUE
            FROM TEST_TAB),
 cte2 AS (SELECT COL_KEY,
                 COL_VALUE,
                 CASE
                   WHEN COL_VALUE = PREV_COL_VALUE THEN 0
                   ELSE 1
                 END AS FLAG
            FROM cte1),
 cte3 AS (SELECT COL_KEY,
                 COL_VALUE
            FROM cte2
            WHERE FLAG = 1)
SELECT COL_KEY,
       LISTAGG(COL_VALUE, ' | ') WITHIN GROUP (ORDER BY COL_KEY, ROWNUM) AS COL_VALUES
  FROM cte3
  GROUP BY COL_KEY

這會產生：

COL_KEY  COL_VALUES
-------  ------------------------------------------------------------
key1     value1 | value2 | value3 | value2 | value1 | value3 | value2

它與您所需的輸出相匹配，但在您的資料中沒有排序列，您將受資料庫以及它如何選擇回傳資料的支配。記住——關系資料庫的一個基本規則是表是行的無序集合，當查詢回傳這些行時，必須有一個 ORDER BY 子句來對這些行施加順序。

db<>在這里擺弄

標籤：甲骨文 合并

上一篇：如何在內部有異步等待api呼叫的Javascript回圈中限制for？

下一篇：Oracle案例WHERE子句