sql - 带有 GROUP BY 的 3 个 SELECT 语句的平均值

Question

我正在尝试执行以下操作

我有一个表 QA，其中包含以下内容:

TICKET_ID   SKILL_ID  SCORE         USER
###############################################
1           10          15          USER1
1           20           5          USER1
1           30          95          USER1
2           40          20          USER1
2           50          40          USER1
3           60          70          USER1
3           70          15          USER1

一张 table 技能由:

SKILL_ID   SKILL_NAME   AREA_ID
   10       SKILL1        1
   20       SKILL2        1
   30       SKILL3        2
   40       SKILL4        2
   50       SKILL5        2
   60       SKILL6        3
   70       SKILL7        3

和一张 table TICKETS 由:

  TICKET_ID   TICKET_NUMBER   
    1           AAA
    2           BBB
    3           CCC

QA 有使用 TICKET_ID 的 FK 到 TICKETS 和使用 SKILL_ID 的 SKILLS 的 FK

我需要做的是:

对于表 QA 中的每张票，检查每个区域的得分总和不超过 100，如果超过计为 0，则对 3 个结果进行平均并按另一个表中的票号分组
因此，对于每种情况，我都需要计算每个区域的分数，如果 > 100 然后 0 否则保留原始值，然后取 3 个值的平均值并按票号分组

不确定这是否可能

所以也许为了计算 area_1 的分数，我执行以下操作:

SELECT DECODE(100 - SUM(SCORE),
100,100,
95,95,
90,90,
85,85,
80,80,
75,75,
70,70,
65,65,
60,60,
55,55,
50,50,
45,45,
40,40,
35,35,
30,30,
25,25,
20,20,
15,15,
10,10,
5,5,
0) SCORE_A1 FROM QA WHERE SKILL_ID IN(SELECT SKILL_ID FROM SKILLS WHERE AREA_ID = 1) AND TICKET_NUMBER = :P2_TICKET_NUMBER AND QA.USER = :P2_USER GROUP BY 1

计算 area_2 同样的事情的分数，但改变了 WHERE 条件(这次 WHERE AREA_ID = 2):

SELECT DECODE(100 - SUM(SCORE),
100,100,
95,95,
90,90,
85,85,
80,80,
75,75,
70,70,
65,65,
60,60,
55,55,
50,50,
45,45,
40,40,
35,35,
30,30,
25,25,
20,20,
15,15,
10,10,
5,5,
0) SCORE_A1 FROM QA WHERE SKILL_ID IN(SELECT SKILL_ID FROM SKILLS WHERE AREA_ID = 2) AND TICKET_NUMBER = :P2_TICKET_NUMBER AND QA.USER = :P2_USER GROUP BY 1

并计算 area_3 的分数相同但改变 WHERE 条件( WHERE AREA_ID = 3 ):

SELECT DECODE(100 - SUM(SCORE),
100,100,
95,95,
90,90,
85,85,
80,80,
75,75,
70,70,
65,65,
60,60,
55,55,
50,50,
45,45,
40,40,
35,35,
30,30,
25,25,
20,20,
15,15,
10,10,
5,5,
0) SCORE_A1 FROM QA WHERE SKILL_ID IN(SELECT SKILL_ID FROM SKILLS WHERE AREA_ID = 3) AND TICKET_NUMBER = :P2_TICKET_NUMBER AND QA.USER = :P2_USER GROUP BY 1

每个块都给出 1 个值作为输出

我想要实现的是将 3 个块的 AVG 作为ticket_number 的输出

我试图将 3 个块加在一起，但它不允许我:

SELECT DECODE(100 - SUM(SCORE),
100,100,
95,95,
90,90,
85,85,
80,80,
75,75,
70,70,
65,65,
60,60,
55,55,
50,50,
45,45,
40,40,
35,35,
30,30,
25,25,
20,20,
15,15,
10,10,
5,5,
0) SCORE_A1 FROM QA WHERE SKILL_ID IN(SELECT SKILL_ID FROM SKILLS WHERE AREA_ID = 1) AND TICKET_NUMBER = :P2_TICKET_NUMBER AND QA.USER = :P2_USER GROUP BY 1
+
SELECT DECODE(100 - SUM(SCORE),
100,100,
95,95,
90,90,
85,85,
80,80,
75,75,
70,70,
65,65,
60,60,
55,55,
50,50,
45,45,
40,40,
35,35,
30,30,
25,25,
20,20,
15,15,
10,10,
5,5,
0) SCORE_A1 FROM QA WHERE SKILL_ID IN(SELECT SKILL_ID FROM SKILLS WHERE AREA_ID = 2) AND TICKET_NUMBER = :P2_TICKET_NUMBER AND QA.USER = :P2_USER GROUP BY 1
+
SELECT DECODE(100 - SUM(SCORE),
100,100,
95,95,
90,90,
85,85,
80,80,
75,75,
70,70,
65,65,
60,60,
55,55,
50,50,
45,45,
40,40,
35,35,
30,30,
25,25,
20,20,
15,15,
10,10,
5,5,
0) SCORE_A1 FROM QA WHERE SKILL_ID IN(SELECT SKILL_ID FROM SKILLS WHERE AREA_ID = 3) AND TICKET_NUMBER = :P2_TICKET_NUMBER AND QA.USER = :P2_USER GROUP BY 1

谢谢

Answer 1

请尝试以下查询:

SELECT
    AREAS_SCORES.USER,
    AREAS_SCORES.TICKET_ID,
    AREAS_SCORES.TICKET_NUMBER,
    AVG(CASE 
            WHEN AREAS_SCORES.AREA_SUM_SCORE > 100 THEN 0
            ELSE AREAS_SCORES.AREA_SUM_SCORE
        END) AVG_SCORE
FROM
(
    SELECT
        QA.USER,
        QA.TICKET_ID,
        QA.TICKET_NUMBER,
        SKILLS.AREA_ID,
        SUM(QA.SCORE) AREA_SUM_SCORE
    FROM 
        QA 
        INNER JOIN SKILLS ON SKILLS.SKILL_ID = QA.SKILL_ID
        INNER JOIN TICKETS ON TICKETS.TICKET_ID = QA.TICKET_ID
    GROUP BY
        QA.USER,
        QA.TICKET_ID,
        QA.TICKET_NUMBER,
        SKILLS.AREA_ID
) AREAS_SCORES

子查询为每个用户的每张票计算每个区域内的分数总和。然后再次汇总信息以计算平均值，但限制为特定区域的分数总和超过 100 ，那么应该算作 0 .

我希望它以某种方式有所帮助(假设我很好地理解了您的问题)。