为ID添加特定水果存在标记列，寻求高效SQL优化方案

原数据表 #

ID   Fruit
A    apple,orange,grape
A    apple,orange,cucumber
A    apple,orange
B    orange,grape
B    apple
B    grape
C    grape,banana
C    orange,banana
C    banana

期望结果 #

ID   Fruit                  apple  banana  orange
A    apple,orange,grape     yes    no      yes
A    apple,orange,cucumber  yes    no      yes
A    apple,orange           yes    no      yes
B    orange,grape           yes    no      yes
B    apple                  yes    no      yes
B    grape                  yes    no      yes
C    grape,banana           no     yes     yes
C    orange,banana          no     yes     yes
C    banana                 no     yes     yes

原方案问题 #

你之前使用窗口函数的方案，在数据量较大时会因每行重复计算窗口聚合导致资源占用高、运行缓慢。

优化方案：预聚合+关联 #

通过先预计算每个ID对应的水果存在标记，再与原表关联，能大幅减少计算量，提升性能：

方案1：基于字符串匹配的预聚合（适配多数SQL版本） #

WITH FruitFlags AS (
    SELECT 
        ID,
        MAX(CASE WHEN CHARINDEX('apple', Fruit) > 0 THEN 'yes' ELSE 'no' END) AS apple,
        MAX(CASE WHEN CHARINDEX('banana', Fruit) > 0 THEN 'yes' ELSE 'no' END) AS banana,
        MAX(CASE WHEN CHARINDEX('orange', Fruit) > 0 THEN 'yes' ELSE 'no' END) AS orange
    FROM MyTable
    GROUP BY ID
)
SELECT 
    t.*,
    ff.apple,
    ff.banana,
    ff.orange
FROM MyTable t
INNER JOIN FruitFlags ff ON t.ID = ff.ID;

方案2：基于字符串拆分的预聚合（适配SQL Server 2016+、PostgreSQL 14+等支持字符串拆分的数据库） #

该方案能避免CHARINDEX的部分匹配问题（比如不会把applepie误判为包含apple）：

WITH SplitFruits AS (
    SELECT 
        ID,
        TRIM(value) AS fruit
    FROM MyTable
    CROSS APPLY STRING_SPLIT(Fruit, ',') -- PostgreSQL替换为STRING_TO_TABLE(Fruit, ',')
),
FruitFlags AS (
    SELECT 
        ID,
        MAX(CASE WHEN fruit = 'apple' THEN 'yes' ELSE 'no' END) AS apple,
        MAX(CASE WHEN fruit = 'banana' THEN 'yes' ELSE 'no' END) AS banana,
        MAX(CASE WHEN fruit = 'orange' THEN 'yes' ELSE 'no' END) AS orange
    FROM SplitFruits
    GROUP BY ID
)
SELECT 
    t.*,
    ff.apple,
    ff.banana,
    ff.orange
FROM MyTable t
INNER JOIN FruitFlags ff ON t.ID = ff.ID;

性能优势说明 #

• 预聚合仅对每个ID执行一次计算，而非原方案中每行都执行窗口聚合，表中ID重复率越高，性能提升越明显。
• 关联操作基于ID列，若给ID列建立索引，能进一步加快关联速度。

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