?在數據分析場景中，我們經常會遇到需要解析結構化字符串并統計特征出現次數的需求。本文將以常用數據庫為例，探討如何高效處理類似 [特征A][特征B][特征C] 格式的字符串數據，并實現特征頻率統計。以下是完整的實現思路和解決方案。

---

一、問題場景分析

假設某字段存儲多個特征值，采用以下格式：

• 每個特征用方括號包裹
• 多個特征連續排列
• 示例：[屬性A][屬性B][屬性X]

需要實現：

1. 解析所有特征值
2. 統計每個特征的全局出現次數

---

二、關鍵技術挑戰

1. 字符串分割限制

有些數據庫沒有內置的 SPLIT_STRING 函數，需要利用基礎字符串處理函數實現分割。

2. 動態長度處理

特征值的長度不固定，需動態定位分割點。

3. 遞歸處理需求

當單條記錄包含多個特征時，需要遞歸或迭代處理。

---

三、核心解決方案

方案一：遞歸 CTE 解析法（推薦）

WITH RECURSIVE feature_extractor(feature, remaining) AS (SELECT substr(data_field, 2, instr(substr(data_field, 2), ']') - 1),substr(data_field, instr(substr(data_field, 2), ']') + 2)FROM example_tableWHERE data_field LIKE '%[%]%'UNION ALLSELECT substr(remaining, 2, instr(substr(remaining, 2), ']') - 1),substr(remaining, instr(substr(remaining, 2), ']') + 2)FROM feature_extractorWHERE remaining LIKE '%[%]%'
)SELECT feature, COUNT(*) AS frequency
FROM feature_extractor
WHERE feature != ''
GROUP BY feature
ORDER BY frequency DESC;

實現原理：

1. 使用 WITH RECURSIVE 創建遞歸公共表達式
2. 初始查詢定位第一個特征：
   • substr(data_field, 2) 跳過首字符 [
   • instr() 定位第一個 ] 的位置
3. 遞歸部分持續處理剩余字符串
4. 終止條件：剩余字符串不再包含特征格式

優勢：

• 自動適應任意數量的特征
• 精確處理動態長度特征
• 完全遵循 SQL 標準

---

方案二：數字輔助表法

-- 創建數字輔助表（0-9）
WITH numbers AS (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
)SELECT substr(substr(data_field, n*5+1),  -- 5為特征平均長度估算值2, instr(substr(data_field, n*5+1), ']')-2) AS feature,COUNT(*) AS frequency
FROM example_table, numbers
WHERE n <= (length(data_field) - length(replace(data_field, '[', '')))AND substr(data_field, n*5+1, 1) = '['
GROUP BY feature
HAVING feature != ''
ORDER BY frequency DESC;

適用場景：

• 特征數量已知且較少
• 特征長度相對固定
• 需要避免遞歸查詢的情況

注意事項：

• 需要合理估算特征平均長度（示例中的5）
• 數字表范圍需覆蓋最大特征數量

---

四、方案對比

維度	遞歸CTE法	數字輔助表法
處理能力	任意數量特征	受數字表范圍限制
性能表現	大數據量時較慢	預計算更快
實現復雜度	需要理解遞歸邏輯	簡單易理解
格式適應性	嚴格依賴格式	需要長度估算
內存消耗	較高	較低

---

五、擴展：獲取不重復的全部特征

要從結構化字符串中提取所有不重復的特性，我們可以基于之前的解決方案稍作調整。以下是幾種在SQLite中實現的方案：

方案一：使用遞歸CTE提取唯一特性（推薦）

WITH RECURSIVE feature_extractor(feature, remaining) AS (SELECT substr(data_field, 2, instr(substr(data_field, 2), ']') - 1),substr(data_field, instr(substr(data_field, 2), ']') + 2)FROM example_tableWHERE data_field LIKE '%[%]%'UNION ALLSELECT substr(remaining, 2, instr(substr(remaining, 2), ']') - 1),substr(remaining, instr(substr(remaining, 2), ']') + 2)FROM feature_extractorWHERE remaining LIKE '%[%]%'
)SELECT DISTINCT feature
FROM feature_extractor
WHERE feature != ''
ORDER BY feature;

方案二：使用GROUP BY替代DISTINCT

-- 使用與方案一相同的CTE
WITH RECURSIVE feature_extractor AS (...)
SELECT feature
FROM feature_extractor
WHERE feature != ''
GROUP BY feature  -- GROUP BY也能實現去重
ORDER BY feature;

方案三：簡化的數字輔助表法

WITH numbers AS (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
)SELECT DISTINCTsubstr(substr(data_field, n*5+1),2, instr(substr(data_field, n*5+1), ']')-2) AS feature
FROM example_table, numbers
WHERE n <= (length(data_field) - length(replace(data_field, '[', '')))AND substr(data_field, n*5+1, 1) = '['AND feature != ''
ORDER BY feature;

六、注意事項

1. 格式嚴格性要求：

   • 禁止嵌套括號：[[特征A]]
   • 禁止未閉合括號：[特征A][特征B
   • 禁止空特征：[][]

2. 特殊字符處理：

   • 如果特征包含 ] 字符需轉義
   • 建議使用統一的分隔符（如 Unicode 控制字符）

3. 性能監控：

   • 遞歸深度限制：PRAGMA max_recursive_depth=1000;
   • 查詢執行計劃分析：EXPLAIN QUERY PLAN

---

通過本文介紹的兩種方法，開發者可以靈活應對不同場景下的結構化字符串處理需求。實際應用中建議先進行小數據量測試，再結合具體業務場景選擇合適的實現方案。