SQL拆分concat拼接列order_type并统计指定值出现次数

现有数据表中存储拼接字段order_type，字段以分号为分隔符存储多个枚举值，示例存储内容如下：

OrderType
Data;Let;Data;Data;Let

需求为统计该字段中Data和Let各自的出现次数，最终输出两个统计列，预期结果如下：

Data Let
 3    2

实现思路 #

有两种常用实现路径：

• 拆分统计：先把分隔符拼接的字符串拆为单行单个值，再分组聚合统计，适合需要统计的枚举值不固定的场景
• 直接计算：通过字符串长度差计算目标值出现次数，不需要拆分，性能更高，适合枚举值固定的场景

不同数据库实现代码 #

简化通用计算方案（所有支持字符串函数的数据库都适用） #

SELECT
    (LENGTH(order_type) - LENGTH(REPLACE(order_type, 'Data', ''))) / LENGTH('Data') AS Data,
    (LENGTH(order_type) - LENGTH(REPLACE(order_type, 'Let', ''))) / LENGTH('Let') AS Let
FROM 你的表名;

MySQL 拆分统计实现 #

WITH RECURSIVE split_str AS (
    SELECT 
        SUBSTRING_INDEX(SUBSTRING_INDEX(order_type, ';', n), ';', -1) AS val
    FROM 你的表名
    -- 这里的数字序列要覆盖单条字段最多的分隔值数量，可按需扩展
    JOIN (SELECT 1 n UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) nums
    ON n <= LENGTH(order_type) - LENGTH(REPLACE(order_type, ';', '')) + 1
)
SELECT
    SUM(CASE WHEN val = 'Data' THEN 1 ELSE 0 END) AS Data,
    SUM(CASE WHEN val = 'Let' THEN 1 ELSE 0 END) AS Let
FROM split_str;

PostgreSQL 拆分统计实现 #

SELECT
    COUNT(*) FILTER (WHERE val = 'Data') AS Data,
    COUNT(*) FILTER (WHERE val = 'Let') AS Let
FROM 你的表名,
unnest(string_to_array(order_type, ';')) AS val;

Hive SQL/Spark SQL 拆分统计实现 #

SELECT
    sum(if(val='Data',1,0)) as Data,
    sum(if(val='Let',1,0)) as Let
FROM 你的表名
LATERAL VIEW explode(split(order_type,';')) tmp AS val;

内容的提问来源于stack exchange，提问作者bot9123