mysql - 更高效的 SQL 来消除多个子查询？

Question

我的代码中有这个 SQL，子查询的重复让我担心它的编写效率不高。

我的数据库有一个表，其中包含特征、响应和参与者。参与者对每个功能进行评分(D、P、B、I、R、Q 之一)。

SELECT f.id, f.name,
(SELECT COUNT(r.id) FROM responses r LEFT JOIN participants p ON r.id_participant = p.id WHERE p.is_ignored IS NULL AND r.is_deleted IS NULL AND r.id_feature = f.id AND p.category LIKE :p_category) AS nr_r, 
(SELECT SUM(r.is_D) FROM responses r LEFT JOIN participants p ON r.id_participant = p.id WHERE p.is_ignored IS NULL AND r.is_deleted IS NULL AND r.id_feature = f.id AND p.category LIKE :p_category) AS is_D, 
(SELECT SUM(r.is_P) FROM responses r LEFT JOIN participants p ON r.id_participant = p.id WHERE p.is_ignored IS NULL AND r.is_deleted IS NULL AND r.id_feature = f.id AND p.category LIKE :p_category) AS is_P, 
(SELECT SUM(r.is_B) FROM responses r LEFT JOIN participants p ON r.id_participant = p.id WHERE p.is_ignored IS NULL AND r.is_deleted IS NULL AND r.id_feature = f.id AND p.category LIKE :p_category) AS is_B, 
(SELECT SUM(r.is_I) FROM responses r LEFT JOIN participants p ON r.id_participant = p.id WHERE p.is_ignored IS NULL AND r.is_deleted IS NULL AND r.id_feature = f.id AND p.category LIKE :p_category) AS is_I, 
(SELECT SUM(r.is_R) FROM responses r LEFT JOIN participants p ON r.id_participant = p.id WHERE p.is_ignored IS NULL AND r.is_deleted IS NULL AND r.id_feature = f.id AND p.category LIKE :p_category) AS is_R, 
(SELECT SUM(r.is_Q) FROM responses r LEFT JOIN participants p ON r.id_participant = p.id WHERE p.is_ignored IS NULL AND r.is_deleted IS NULL AND r.id_feature = f.id AND p.category LIKE :p_category) AS is_Q,
(SELECT (is_D + is_P)/(is_D + is_P + is_B + is_I)) as yay,
(SELECT (is_P + is_B)/(is_D + is_P + is_B + is_I)) as boo
FROM features f 
WHERE f.is_deleted IS NULL AND f.id_survey=:id_project
ORDER BY f.id ASC;

该查询的输出是一个总计表，看起来像这样

577 App registration    989   36   21   38  201  42   6  0.1926  0.1993
578 Login PIN           989  279  118  137  394  41  20  0.4278  0.2748
579 Manage all services 989  287  207  127  331  23  14  0.5189  0.3508
580 Rewards             989  344  157   64  386  19  19  0.5268  0.2324
581 Offers              989  226   93   37  542  72  19  0.3552  0.1448

对于包含 989 个参与者、14 个特征(因此有 13,846 个个人评分)的数据集，查询当前需要 4.4387 秒运行。很确定那很臭。

是否有更有效的方式来编写该查询？

GROUP BY 对此有用吗？

Answer 1

如果我正确理解您的查询，您在每个子查询中都有相同的谓词，然后您可以轻松地用联接替换所有子选择并进行分组依据。聚合(yay 和 boo)的计算是在外层计算的:

SELECT id, name, nr_r, is_D, ...
     , (is_D + is_P)/(is_D + is_P + is_B + is_I) yay
     , (is_P + is_B)/(is_D + is_P + is_B + is_I) boo
FROM (
     SELECT f.id, f.name
         , count(r.id) AS nr_r
         , sum(r.is_D) as is_D
         , ...
     FROM features f
     LEFT JOIN responses r 
         ON r.id_feature = f.id
        AND r.is_deleted IS NULL 
     LEFT JOIN participants p 
         ON r.id_participant = p.id
        AND p.is_ignored IS NULL
        AND p.category LIKE :p_category  
     WHERE f.is_deleted IS NULL 
       AND f.id_survey=:id_project
     GROUP BY f.id, f.name
) AS T
ORDER BY ...