Блог

Главная - Вопросы по программированию - SQL вычисляет индивидуальное среднее значение на основе нескольких условий

SQL вычисляет индивидуальное среднее значение на основе нескольких условий

#sql #sql-server #average

#sql #sql-server #среднее

Вопрос:

У меня есть следующая таблица:

  ----------- ---------- ------------- ---------------- --------------- 
| FirstName | LastName | SessionDate |  SessionTitle  | TotalDistance |
 ----------- ---------- ------------- ---------------- --------------- 
| Player    | Striker  |  05.08.2020 | Entire Session |          10.0 |
| Player    | Striker  |  05.08.2020 | 1v1            |           5.0 |
| Player    | Striker  |  06.08.2020 | Entire Session |          12.0 |
| Coach     | Defender |  06.08.2020 | Entire Session |           7.0 |
 ----------- ---------- ------------- ---------------- ---------------

Что мне нужно сделать, так это рассчитать среднее значение для totalDistance для каждого игрока в отдельности. Среднее значение должно вычисляться только со значениями, где SessionTitle равен ‘EntireSession’ и только за последние 50 дней, включая дату строки для каждой строки. Таким образом, ожидаемый результат будет выглядеть следующим образом:

  ----------- ---------- ------------- ---------------- --------------- --------- 
| FirstName | LastName | SessionDate |  SessionTitle  | TotalDistance | average |
 ----------- ---------- ------------- ---------------- --------------- --------- 
| Player    | Striker  |  05.08.2020 | Entire Session |          10.0 |    10.0 |
| Player    | Striker  |  05.08.2020 | 1v1            |           5.0 |    10.0 |
| Player    | Striker  |  06.08.2020 | Entire Session |          12.0 |    11.0 |
| Coach     | Defender |  06.08.2020 | Entire Session |           7.0 |     7.0 |
 ----------- ---------- ------------- ---------------- --------------- ---------

Я пробовал что-то подобное, но это ужасно медленно, когда добавляется больше строк:

 SELECT t1.FirstName, 
t1.LastName, 
t1.SessionDate, 
t1.SessionTitle, 
t1.TotalDistance, 
(SELECT (AVG(t2.TotalDistance) FROM myTable t2 WHERE t1.FirstName = t2.FirstName AND t1.LastName = t2.LastName AND t2.SessionTitle = 'EntireSession' AND t2.SessionDate <= DATEADD(DAY,50,t1.SessionDate)) as average
FROM myTable t1

Он работает на SQL-сервере Azure
Каким будет самый быстрый способ сделать это в инструкции select?

Заранее спасибо!

Ответ №1:

Это ваш запрос:

 SELECT t1.FirstName, t1.LastName, t1.SessionDate, t1.SessionTitle, 
       t1.TotalDistance, 
       (SELECT AVG(t2.TotalDistance)
        FROM myTable t2
        WHERE t1.FirstName = t2.FirstName AND
              t1.LastName = t2.LastName AND
              t2.SessionTitle = 'EntireSession' AND
              t2.SessionDate <= DATEADD(DAY,50,t1.SessionDate)
       ) as average
FROM myTable t1;

Во-первых, вы можете попробовать добавить индекс на (FirstName, LastName, SessionDate, SessionTitle, TotalDistance) .

Во-вторых, для меня это немного похоже на функцию окна. Если у вас есть данные за каждый день, вы можете использовать:

 select t.*,
       avg(case when SessionTitle = 'EntireSession' then TotalDistance end) over (
           (partition by FirstName, LastName
            order by SessionDate
            rows between unbounded preceding and 50 following
           )
from myTable t;

Комментарии:

1. Оконная функция во втором запросе должна быть RANGE основана, а ROWS не основана. К сожалению, SQL Server не поддерживает RANGE временные интервалы. Однако первый запрос хорош.

2. @TheImpaler, да, это сложно сделать эффективно в SQL Server, потому что оконные функции не поддерживают RANGE оконный фрейм с интервалами. Это подробно обсуждалось на dba.se : Скользящая сумма диапазона дат с использованием оконных функций

3. @TheImpaler . , , (1) SQL Server doe не поддерживает это. (2) Предполагается, что в каждой строке есть данные (упомянутые непосредственно перед запросом).

Ответ №2:

Вы можете просто добиться этого,

 SELECT FirstName, LastName, avg([TotalDistance]) 
FROM myTable
WHERE SessionDate>= DATEADD(Day, -50, getdate())
AND SessionTitle = 'EntireSession'
GROUP BY FirstName, Last Name