如何移除生成success字段的子查询以提升SQL查询效率？

核心需求回顾 #

当tag_source = 'plc'时，若存在**其他device_name**下相同type_id和relative_tag_path的行且该行success = true，当前行success显示为'true*'；否则显示自身关联的success值。

优化后查询语句（CTE方式） #

WITH qualified_groups AS (
    -- 预计算存在多个不同device_name且success为true的type_id+relative_tag_path分组
    SELECT type_id, relative_tag_path
    FROM dat_commissioning_test_log
    WHERE success = true
    GROUP BY type_id, relative_tag_path
    HAVING COUNT(DISTINCT device_name) > 1
)
SELECT
    ct.*,
    CASE
        WHEN ct.tag_source = 'plc' 
             AND qg.type_id IS NOT NULL
        THEN 'true*'
        ELSE ct.success::TEXT
    END AS success
FROM cfg_commissioning_tags ct
LEFT JOIN qualified_groups qg
    ON ct.type_id = qg.type_id 
    AND ct.relative_tag_path = qg.relative_tag_path

另一种实现（窗口函数方式） #

SELECT
    DISTINCT ct.*,
    CASE
        WHEN ct.tag_source = 'plc' 
             AND MAX(CASE WHEN dctl.device_name != ct.device_name AND dctl.success = true THEN 1 ELSE 0 END) 
                 OVER (PARTITION BY ct.type_id, ct.relative_tag_path) = 1
        THEN 'true*'
        ELSE ct.success::TEXT
    END AS success
FROM cfg_commissioning_tags ct
LEFT JOIN dat_commissioning_test_log dctl
    ON ct.type_id = dctl.type_id 
    AND ct.relative_tag_path = dctl.relative_tag_path

性能优化补充：复合索引建议 #

为进一步提升查询效率，创建以下复合索引：

1. 针对dat_commissioning_test_log的分组查询：

CREATE INDEX idx_dctl_type_tag_success_device 
ON dat_commissioning_test_log (type_id, relative_tag_path, success) 
INCLUDE (device_name);

2. 针对cfg_commissioning_tags的关联查询：

CREATE INDEX idx_cct_type_tag_source 
ON cfg_commissioning_tags (type_id, relative_tag_path, tag_source);

逻辑验证 #

• 当tag_source不为plc时，直接返回自身success值，符合需求
• 当tag_source为plc时：
  • 若type_id+relative_tag_path分组下存在至少2个不同device_name的success=true行，返回'true*'
  • 否则返回自身success值，完全匹配原业务逻辑

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