时间戳

首页 > 解决方案 > SQL - 使用联合对 2 个或更多列进行分组计数需要超过 2 秒

sql - SQL - 使用联合对 2 个或更多列进行分组计数需要超过 2 秒

问题描述

给定一个表，我们称它为 performance6a - 表结构 >> student_id | 数学 | 历史 | 语言 | 科学

表格内容 - 性能 6a

我需要编写一个生成如下报告的查询 -

预期的查询输出

组数基于 2 个类别，在这种情况下，通过或失败，然后是主题。

请注意，这些操作是在无法规范化或重新设计的系统表上执行的。两个类别的数量都可以增长（类别 - 通过/失败可以增加到 100 和类别 - 主题可以增加到 ~5k）

我试过的：

(SELECT 'PASS' AS STATUS, 'MATH', COUNT(ID) FROM PERFORMANCE6A WHERE MATH > 30) UNION
(SELECT 'FAIL' AS STATUS, 'MATH', COUNT(ID) FROM PERFORMANCE6A WHERE MATH <= 30) UNION
(SELECT 'PASS' AS STATUS, 'HISTORY', COUNT(ID) FROM PERFORMANCE6A WHERE HISTORY > 30) UNION
(SELECT 'FAIL' AS STATUS, 'HISTORY', COUNT(ID) FROM PERFORMANCE6A WHERE HISTORY <= 30) UNION
(SELECT 'PASS' AS STATUS, 'LANGUAGE', COUNT(ID) FROM PERFORMANCE6A WHERE LANGUAGE > 30) UNION
(SELECT 'FAIL' AS STATUS, 'LANGUAGE', COUNT(ID) FROM PERFORMANCE6A WHERE LANGUAGE <= 30) UNION
(SELECT 'PASS' AS STATUS, 'SCIENCE', COUNT(ID) FROM PERFORMANCE6A WHERE SCIENCE > 30) UNION
(SELECT 'FAIL' AS STATUS, 'SCIENCE', COUNT(ID) FROM PERFORMANCE6A WHERE SCIENCE <= 30);

这个查询给了我正确的输出，但是执行时间超过了 2s。我正在寻找优化查询的想法。

标签： sqldb2system-tables

解决方案

如果您先取消透视然后聚合，可能会更快：

select (case when score > 30 then 'PASS' else 'FAIL' end) as status, subject, count(*)
from ((select 'math' as subject, math as score from PERFORMANCE6A) union all
      (select 'history' as subject, history as score from PERFORMANCE6A) union all
      (select 'language' as subject, language as score from PERFORMANCE6A) union all
      (select 'science' as subject, science as score from PERFORMANCE6A)
     ) ss
group by (case when score > 30 then 'PASS' else 'FAIL' end), subject

推荐阅读