sql-group使用union按2列或更多列计数需要2秒以上的时间

ttygqcqt 于 2021-07-24 发布在 Java

关注(0)|答案(2)|浏览(260)

给定一个表，让我们称之为performance6a-使用表结构>>学生id |数学|历史|语言|科学
表内容-性能6A
我需要写一个查询，产生一个报告如下-
预期的查询输出
组计数基于两个类别，在这种情况下，通过或失败，然后主题。
请注意，这些操作是在无法规范化或重新设计的系统表上执行的。两个类别都可以增加数量（类别-通过/不通过可以增加到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);

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

sql db2 system-tables

来源：https://stackoverflow.com/questions/62552616/sql-group-count-by-2-or-more-columns-using-union-takes-more-than-2-seconds

2条答案

按热度按时间

bvjxkvbb1#

如果先取消IVOT，然后再聚合：

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

赞(0）回复(0）举报 2021-07-24

piok6c0g2#

试试这个：

WITH PERFORMANCE6A (STUDENT_ID, MATH, HISTORY, LANGUAGE, SCIENCE) AS 
(
VALUES
  (1, 23, 42, 40, 35)
, (2, 45, 21, 25, 36)
, (3, 32, 12, 15, 27)
, (4, 49, 45, 38, 31)
, (5, 50, 43, 37, 41)
)
SELECT RESULT, NAME, COUNT(1) CNT
FROM
(
SELECT 
  D.NAME
, CASE
    WHEN D.NAME = 'MATH'     THEN CASE WHEN P.MATH     > 30 THEN 'PASS' ELSE 'FAIL' END 
    WHEN D.NAME = 'HISTORY'  THEN CASE WHEN P.HISTORY  > 30 THEN 'PASS' ELSE 'FAIL' END 
    WHEN D.NAME = 'LANGUAGE' THEN CASE WHEN P.LANGUAGE > 30 THEN 'PASS' ELSE 'FAIL' END 
    WHEN D.NAME = 'SCIENCE'  THEN CASE WHEN P.SCIENCE  > 30 THEN 'PASS' ELSE 'FAIL' END 
  END AS RESULT
FROM PERFORMANCE6A P
CROSS JOIN (VALUES 'MATH', 'HISTORY', 'LANGUAGE', 'SCIENCE') D (NAME)
)
GROUP BY RESULT, NAME
ORDER BY RESULT, NAME;

赞(0）回复(0）举报 2021-07-24

我来回答

sql-group使用union按2列或更多列计数需要2秒以上的时间

2条答案

相关问题

热门标签

最新问答