查询运行过慢无法完成：求仪器测试整体通过率技术支持

嘿，针对你的问题，我整理了一套既准确计算通过率又能解决查询慢的方案，先理清楚业务逻辑再上代码：

先明确核心规则 #

每台仪器得跑107项测试：21项Linux测试，剩下86项是电压、光测试这类其他项。合格的标准（你提到的标准方式）是单次运行里所有107项全通过。

优化后的SQL写法（两种高效方案） #

方案1：用CTE+分组统计，逻辑清晰易维护 #

这种写法比你原来的思路减少了不必要的计算，可读性也更强：

-- 先找出所有单次运行全通过的仪器运行记录
WITH full_pass_runs AS (
    SELECT 
        serialnumber,
        entries
    FROM your_table
    WHERE test_result = 'PASS' -- 替换成你实际的"测试通过"标识
    GROUP BY serialnumber, entries
    -- 确保该运行下覆盖了全部107项不同测试且全通过
    HAVING COUNT(DISTINCT testcriteria) = 107
),
-- 去重得到所有合格的仪器
qualified_instruments AS (
    SELECT DISTINCT serialnumber FROM full_pass_runs
),
-- 统计所有参与测试的仪器总数
all_instruments AS (
    SELECT DISTINCT serialnumber FROM your_table
)
-- 计算整体通过率，保留两位小数
SELECT 
    ROUND((COUNT(qi.serialnumber)::FLOAT / COUNT(ai.serialnumber)) * 100, 2) AS overall_pass_rate
FROM all_instruments ai
LEFT JOIN qualified_instruments qi ON ai.serialnumber = qi.serialnumber;

方案2：用EXISTS提升查询速度，适合大数据量场景 #

如果你的测试数据量特别大，EXISTS的存在性判断通常比GROUP BY更高效，试试这个：

WITH all_instruments AS (
    SELECT DISTINCT serialnumber FROM your_table
),
qualified_instruments AS (
    SELECT DISTINCT serialnumber
    FROM your_table t
    WHERE EXISTS (
        SELECT 1
        FROM your_table t2
        WHERE t2.serialnumber = t.serialnumber
          AND t2.entries = t.entries
        -- 双重校验：该运行下无失败项，且覆盖全部107项测试
        HAVING SUM(CASE WHEN t2.test_result != 'PASS' THEN 1 ELSE 0 END) = 0
           AND COUNT(DISTINCT t2.testcriteria) = 107
    )
)
SELECT 
    ROUND((COUNT(qi.serialnumber)::FLOAT / COUNT(ai.serialnumber)) * 100, 2) AS overall_pass_rate
FROM all_instruments ai
LEFT JOIN qualified_instruments qi ON ai.serialnumber = qi.serialnumber;

关键性能优化点（解决查询慢的核心） #

• 给核心字段建联合索引：CREATE INDEX idx_test_perf ON your_table(serialnumber, entries, test_result, testcriteria);，索引能让数据库直接定位目标数据，避免全表扫描。
• 若数据量超大，考虑按serialnumber做表分区，把单表拆成小表，查询效率会大幅提升。
• 提前过滤无效数据：在WHERE里先排除测试未通过的记录（如果不需要统计失败项细节），减少后续分组计算的数据量。

补充 #

要是还有其他合格方式（比如多次运行累计通过所有测试），你把具体规则告诉我，我再调整SQL逻辑~

内容的提问来源于stack exchange，提问作者Drake .C