[SQL] 使用 CTE 拆解逗號相隔的字串為資料列

2017-12-29

12373
0
T-SQL
2017-12-29

在處理 DB 資料時常會遇到以逗號分隔的字串資料，例如功能關鍵字就可能以特殊符號相隔存入資料庫，而當需要針對各關鍵字進行統計時，就需要將文字拆解出來為一筆筆的資料；本文藉由一個實際情境來說明如何透過 CTE 遞迴達到拆解字串的目的。

情境說明

目前有一張資料表如下，主要是在統計各功能連結 (MENU) 被用戶點選的次數 (CLICK_COUNT)，而每個功能都會有其關鍵字 (KEYWORD) 存在。

MENU_ID	KEYWORD	CLICK_COUNT
1	福利,補助,購車	33
2	薪資,福利說明	99
3	離職,離職訪談	11

需求是這樣的，客戶希望在輸入查詢關鍵字時可以提供「建議關鍵字」清單，而建議關鍵字清單出現的順序會依照功能項熱門程度作排列，例如用戶輸入「福利」後就會依序出現【福利說明】【福利】建議關鍵字清單供用戶點選；因此最好的方式就是將資料整理為以下樣式，讓單項功能的每一個關鍵字都存在一筆資料，讓我們在查詢及顯示關鍵字時較為直覺。

MENU_ID	KEYWORD	CLICK_COUNT
1	福利	33
1	補助	33
1	購車	33
2	薪資	99
2	福利說明	99
3	離職	11
3	離職訪談	11

實作說明

首先需要準備一下素材，為了方便測試就直接宣告一個資料表變數來使用。

-- 宣告資料表變數
DECLARE @MENU_CLICK_COUNTER TABLE (
  MENU_ID INT,
  KEYWORD NVARCHAR(100),
  CLICK_COUNT INT
)

-- 塞入測試資料
INSERT INTO @MENU_CLICK_COUNTER (MENU_ID, KEYWORD, CLICK_COUNT)
  VALUES (1, N'福利,補助,購車', 33);
INSERT INTO @MENU_CLICK_COUNTER (MENU_ID, KEYWORD, CLICK_COUNT)
  VALUES (2, N'薪資,福利說明', 99);
INSERT INTO @MENU_CLICK_COUNTER (MENU_ID, KEYWORD, CLICK_COUNT)
  VALUES (3, N'離職,離職訪談', 11);

-- 查看目前資料
SELECT * FROM @MENU_CLICK_COUNTER

接著使用 CTE 透過其遞迴的特性滾出各 MENU 關鍵字來成為獨立一筆資料；如果不了解 CTE 可以參考筆者 Common Table Expressions(CTE) 使用筆記文章有做說明。

-- 定義拆解字串符號
DECLARE @splitter VARCHAR(10) = ',';
DECLARE @splitterlength INT = LEN(@splitter) + 1;

-- 使用 CTE 搭配遞迴特性拆出關鍵字
;WITH
Split AS
(
  SELECT
    MENU_ID,
    1 AS startidx,
    CHARINDEX(@splitter, KEYWORD + @splitter) - 1 AS endidx
  FROM @MENU_CLICK_COUNTER
  WHERE LEN(KEYWORD) > 0

  UNION ALL

  SELECT
    s.MENU_ID,
    s.endidx + @splitterlength,  
    CHARINDEX(@splitter, m.KEYWORD + @splitter, s.endidx + 2) - 1
  FROM Split s -- 使用遞迴滾出每個 menu 被逗號分割的文字起始/結束字元位置
    JOIN @MENU_CLICK_COUNTER m 
      ON s.MENU_ID = m.MENU_ID 
      AND CHARINDEX(@splitter, m.KEYWORD + @splitter, s.endidx + 2) > 0
)
SELECT * FROM Split

目前已可將每個 MENU 關鍵字都拆出成單筆資料，並紀錄關鍵字於原始字串的相對起始/結束位置。

最後只要稍微調整 CTE 結果，直接以 MENU_ID 串回原始資料表即可獲得我們所需的資料囉！

-- 略 --
SELECT
  m.MENU_ID,
  SUBSTRING(m.KEYWORD, s.startidx, s.endidx - s.startidx + 1) AS KEYWORD,
  m.CLICK_COUNT
FROM @MENU_CLICK_COUNTER m
JOIN Split s ON m.MENU_ID = s.MENU_ID
ORDER BY m.MENU_ID

完整測試語法

最後附上完整的測試語法，有興趣的朋友可以玩看看。

-- 宣告資料表變數
DECLARE @MENU_CLICK_COUNTER TABLE (
  MENU_ID INT,
  KEYWORD NVARCHAR(100),
  CLICK_COUNT INT
)

-- 塞入測試資料
INSERT INTO @MENU_CLICK_COUNTER (MENU_ID, KEYWORD, CLICK_COUNT)
  VALUES (1, N'福利,補助,購車', 33);
INSERT INTO @MENU_CLICK_COUNTER (MENU_ID, KEYWORD, CLICK_COUNT)
  VALUES (2, N'薪資,福利說明', 99);
INSERT INTO @MENU_CLICK_COUNTER (MENU_ID, KEYWORD, CLICK_COUNT)
  VALUES (3, N'離職,離職訪談', 11);

-- 查看目前資料
SELECT * FROM @MENU_CLICK_COUNTER;

-- 定義拆解字串符號
DECLARE @splitter VARCHAR(10) = ',';
DECLARE @splitterlength INT = LEN(@splitter) + 1;

-- 使用 CTE 搭配遞迴特性拆出關鍵字
;WITH
Split AS
(
  SELECT
    MENU_ID,
    1 AS startidx,
    CHARINDEX(@splitter, KEYWORD + @splitter) - 1 AS endidx
  FROM @MENU_CLICK_COUNTER
  WHERE LEN(KEYWORD) > 0

  UNION ALL

  SELECT
    s.MENU_ID,
    s.endidx + @splitterlength,  
    CHARINDEX(@splitter, m.KEYWORD + @splitter, s.endidx + 2) - 1
  FROM Split s -- 使用遞迴滾出每個 menu 被逗號分割的文字起始/結束字元位置
    JOIN @MENU_CLICK_COUNTER m 
      ON s.MENU_ID = m.MENU_ID 
      AND CHARINDEX(@splitter, m.KEYWORD + @splitter, s.endidx + 2) > 0
)
SELECT
  m.MENU_ID,
  SUBSTRING(m.KEYWORD, s.startidx, s.endidx - s.startidx + 1) AS KEYWORD,
  m.CLICK_COUNT
FROM @MENU_CLICK_COUNTER m
JOIN Split s ON m.MENU_ID = s.MENU_ID
ORDER BY m.MENU_ID