Оптимизация нескольких агрегатов в SELECT

ProgramBox

Оптимизация нескольких агрегатов в SELECT

Post author:admin
Запись опубликована:20 марта, 2023
Post category:Вопросы по программированию

#sql #optimization #aggregation #correlated-subquery

#sql #оптимизация #агрегирование #коррелированный-подзапрос

Вопрос:

Я прочитал в техническом документе Microsoft T-SQL по настройке производительности, что коррелированные подзапросы могут быть дорогостоящими с точки зрения производительности на большой таблице:

… Сравните это с первым решением, которое будет сканировать всю таблицу и выполнять коррелированный подзапрос для каждой строки. Разница в производительности незначительна для небольшой таблицы. Но для большой таблицы это может составлять часы времени обработки…

Существует ли общий способ преобразования запроса с несколькими агрегатами на основе разных критериев в качестве коррелированных подзапросов в один запрос, который использует JOIN s вместо коррелированных подзапросов?

Рассмотрим пример:

Подготовьте схему:

 CREATE TABLE Student (
    ID INT NOT NULL PRIMARY KEY IDENTITY(1,1),
    Name NVARCHAR(255) NOT NULL
);

CREATE TABLE Grade (
    ID INT NOT NULL PRIMARY KEY IDENTITY(1,1),
    StudentID INT NOT NULL FOREIGN KEY REFERENCES Student(ID),
    Score INT NOT NULL,
    CONSTRAINT CK_Grade_Score CHECK (Score >= 0 AND Score <= 100)
);

INSERT INTO Student (Name) VALUES ('Steven');
INSERT INTO Student (Name) VALUES ('Timmy');
INSERT INTO Student (Name) VALUES ('Maria');
 
INSERT INTO Grade (StudentID, Score) VALUES (1, 90);
INSERT INTO Grade (StudentID, Score) VALUES (1, 81);
INSERT INTO Grade (StudentID, Score) VALUES (1, 82);
INSERT INTO Grade (StudentID, Score) VALUES (1, 82);

INSERT INTO Grade (StudentID, Score) VALUES (2, 99);
INSERT INTO Grade (StudentID, Score) VALUES (2, 63);
INSERT INTO Grade (StudentID, Score) VALUES (2, 97);
INSERT INTO Grade (StudentID, Score) VALUES (2, 90);

INSERT INTO Grade (StudentID, Score) VALUES (3, 66);
INSERT INTO Grade (StudentID, Score) VALUES (3, 61);
INSERT INTO Grade (StudentID, Score) VALUES (3, 60);

Рассматриваемый запрос:

 SELECT Name,
    (SELECT AVG(Score) FROM Grade WHERE StudentID = Student.ID AND Score < 65) AS 'F',
    (SELECT AVG(Score) FROM Grade WHERE StudentID = Student.ID AND Score >= 65 AND Score < 70) AS 'D',
    (SELECT AVG(Score) FROM Grade WHERE StudentID = Student.ID AND Score >= 70 AND Score < 80) AS 'C',
    (SELECT AVG(Score) FROM Grade WHERE StudentID = Student.ID AND Score >= 80 AND Score < 90) AS 'B',
    (SELECT AVG(Score) FROM Grade WHERE StudentID = Student.ID AND Score >= 90 AND Score <= 100) AS 'A'
FROM Student

Выдает следующий результат:

 Name    F     D     C     B     A
-----------------------------------------
Steven  NULL  NULL  NULL  81    90
Timmy   63    NULL  NULL  NULL  95
Maria   60    66    NULL  NULL  NULL

Я знаю о методе, который вы можете использовать, COUNT() когда вы выполняете одно SELECT с помощью a JOIN , а затем используете CASE оператор, чтобы при необходимости добавить 1 к счетчику, когда первичные ключи выстраиваются в линию между вашим соединением и вашим условием true . Я ищу аналогичный метод, который можно применять для разных типов агрегатов (в отличие от just COUNT ).

Есть ли эффективный способ преобразовать этот пример запроса для использования a `JOIN` вместо нескольких подзапросов?

Ответ №1:

Возможно, я что-то упускаю, но решение с использованием CASE работает и для агрегатов:

 SELECT st.name, 
       avg(CASE WHEN g.score < 65 THEN g.score ELSE NULL END) as F,
       avg(CASE WHEN g.score >= 65 AND g.score < 70 THEN g.score ELSE NULL END) as D,
       avg(CASE WHEN g.score >= 70 AND g.score < 80 THEN g.score ELSE NULL END) as C,
       avg(CASE WHEN g.score >= 80 AND g.score < 90 THEN g.score ELSE NULL END) as B,
       avg(CASE WHEN g.score >= 90 AND g.score <= 100 THEN g.score ELSE NULL END) as A
FROM Grade g
  JOIN Student st ON g.studentid = st.ID
GROUP BY st.name

1. Это работает хорошо, но когда student будет содержать несколько записей, вычисления могут немного отличаться. В этом вопросе case являются лучшими.

2. Почему? Это будет одно среднее значение для каждого учащегося, чего хочет OP — он также усредняет по StudentID.

3. Среднее значение будет отличаться, когда вместо таблицы Student запрос содержит произведение других соединений, и этот продукт содержит более одного идентичного в паре — в этом случае могут помочь несколько подзапросов, потому что есть место для исправления результата. Например, с использованием DISTINCT . Это всего лишь гипотеза — не входит в сферу вопроса.

4. @Max это повлияло sum бы, count но не avg потому, что при выполнении соединения будет одинаковое количество оценок Student . Не так, когда с Grade s

5. Я должен поставить этот флажок, потому что я думаю, что он обеспечивает наилучшую читаемость, обеспечивая при этом уменьшенный набор результатов из JOIN (и, таким образом, повышая производительность на большой таблице). Я даже не думал об использовании a CASE для возврата NULL , чтобы разрешить выборочное усреднение.

Ответ №2:

Я попробовал что-то вроде следующего, используя CTE, но результат немного отличается от того, что вы получили, потому что он вычисляет среднее значение по всем классам:

 ;WITH
Scores(ID,Score) AS(
    SELECT S.ID,AVG(Score)
    FROM Student S
    JOIN Grade G
        ON S.ID = G.StudentID
    GROUP BY S.ID)

SELECT ST.Name
    ,CASE WHEN S.Score  < 65 THEN S.Score ELSE NULL END AS 'F'
    ,CASE WHEN S.Score  BETWEEN 65 AND 70 THEN S.Score ELSE NULL END AS 'D'
    ,CASE WHEN S.Score  BETWEEN 70 AND 80 THEN S.Score ELSE NULL END AS 'C'
    ,CASE WHEN S.Score  BETWEEN 80 AND 90 THEN S.Score ELSE NULL END AS 'B'
    ,CASE WHEN S.Score  BETWEEN 90 AND 100 THEN S.Score ELSE NULL END AS 'A'
FROM Scores S
JOIN Student ST
    ON S.ID = ST.ID

Ответ №3:

Попробуйте это:

 SELECT s.Name
    ,SUM(CASE Score_g WHEN 'F' THEN Score_avg END) as 'F'
    ,SUM(CASE Score_g WHEN 'D' THEN Score_avg END) as 'D'
    ,SUM(CASE Score_g WHEN 'C' THEN Score_avg END) as 'C'
    ,SUM(CASE Score_g WHEN 'B' THEN Score_avg END) as 'B'
    ,SUM(CASE Score_g WHEN 'A' THEN Score_avg END) as 'A'
FROM Student s,
     (
      SELECT StudentId, score_g, avg(score) as score_avg
      FROM  (
            SELECT StudentID, Score
            CASE
              WHEN Score < 65                   THEN 'F'
              WHEN Score >= 65 AND Score < 70   THEN 'D'
              WHEN Score >= 70 AND Score < 80   THEN 'C'
              WHEN Score >= 80 AND Score < 90     THEN 'B'
              WHEN Score >= 90 AND Score <= 100 THEN 'A'
              ELSE 'X'
            END AS Score_g
            FROM Grade
        ) g
       GROUP BY StudentId, score_g
    ) t
WHERE s.ID = t.StudentID
GROUP BY s.Name

Если вы действительно ненавидите подзапросы, вы можете использовать:

 SELECT s.name
    ,AVG(CASE WHEN Score < 65                   THEN SCORE END) AS 'F'
    ,AVG(CASE WHEN Score >= 65 AND Score < 70   THEN SCORE  END) AS 'D'
    ,AVG(CASE WHEN Score >= 70 AND Score < 80   THEN SCORE  END) AS 'C'
    ,AVG(CASE WHEN Score >= 80 AND Score < 90     THEN SCORE END) AS 'B'
    ,AVG(CASE WHEN Score >= 90 AND Score <= 100 THEN SCORE  END) AS 'A'
FROM Grade g, Student s
WHERE g.StudentID = s.ID
GROUP BY s.name

Но в этом случае таблица Student должна содержать уникальные записи для одного объекта student.

1. Это немного сложнее, но делает то же самое. вместо использования коррелированных запросов используется трехпроходное вычисление. Быстрее для больших данных. Представленные вами данные менее удобочитаемы.

Ответ №4:

Если ваша СУБД поддерживает PIVOT , вы также можете попробовать что-то вроде этого:

 ;WITH marked AS (
  SELECT
    StudentID,
    Score,
    Mark = CASE
      WHEN Score < 65 THEN 'F'
      WHEN Score < 70 THEN 'D'
      WHEN Score < 80 THEN 'C'
      WHEN Score < 90 THEN 'B'
      ELSE 'A'
    END
  FROM Grade
),
pivoted AS (
  SELECT
    StudentID,
    F, D, C, B, A
  FROM marked m
  PIVOT (
    AVG(Score) FOR Mark IN (F, D, C, B, A)
  ) p
)
SELECT
  s.Name,
  p.F,
  p.D,
  p.C,
  p.B,
  p.A
FROM Student s
  INNER JOIN pivoted p ON s.ID = p.StudentID