Блог

Главная - Вопросы по программированию - Как вычислить среднее значение значений без включения последнего значения (sql)?

Как вычислить среднее значение значений без включения последнего значения (sql)?

#sql #tsql #window-functions

#sql #tsql #окно-функции

Вопрос:

У меня есть таблица. Я разделяю его по идентификатору и хочу вычислить среднее значение значений, предшествующих текущему, без учета текущего значения. Вот пример таблицы:

  ---- ------- ------------ 
| id | Value |    Date    |
 ---- ------- ------------ 
|  1 |    51 | 2020-11-26 |
|  1 |    45 | 2020-11-25 |
|  1 |    47 | 2020-11-24 |
|  2 |    32 | 2020-11-26 |
|  2 |    51 | 2020-11-25 |
|  2 |    45 | 2020-11-24 |
|  3 |    47 | 2020-11-26 |
|  3 |    32 | 2020-11-25 |
|  3 |    35 | 2020-11-24 |
 ---- ------- ------------

В данном случае это означает вычисление среднего значения значений для дат ДО 2020-11-26. Это ожидаемый результат

  ---- ------- 
| id | Value |
 ---- ------- 
|  1 |    46 |
|  2 |    48 |
|  3 |  33.5 |
 ---- -------

Я вычислил его, используя ROWS N PRECEDING , но похоже, что таким образом я усредняю N предыдущей последней строки, и я хочу исключить последнюю строку (которая в моем случае является самой последней датой).
Вот мой запрос:

 SELECT ID, 
  (avg(Value) OVER(
      PARTITION BY ID
      ORDER BY Date
      ROWS 9 PRECEDING )) as avg9
FROM t1

Ответ №1:

Затем определите свое окно полностью, используя как начало, так и конец с BETWEEN :

 SELECT ID,
       (AVG(Value) OVER (PARTITION BY ID ORDER BY Date ROWS BETWEEN 9 PRECEDING AND 1 PRECEDING)) AS avg9
FROM t1;

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

1. Это не может быть проще… И я просто страдал последние 30 минут.. Спасибо!

Ответ №2:

Почему бы просто не фильтровать:

 select id, avg(value)
from t1
where date < '2020-11-26'
group by id;

Если вы хотите, чтобы дата была гибкой — укажите самое последнее значение для каждой даты, затем:

 select id, avg(value)
from (select t1.*,
             max(date) over (partition by id) as max_date
      from t1
     ) t1
where date < max_date
group by id;

Ответ №3:

Выполните row_number() поверх (разделение по порядку идентификаторов ПО [Date] DESC). Это даст ранг = 1 строке с последней датой. Оберните его в CTE, а затем вычислите среднее значение для каждого раздела, где РАНГ> 1. Пожалуйста, проверьте синтаксис.

 ;with a as
(
select id, value, Date, row_number() over (partition by id order by date 
desc) as RN
)
select id, avg(Value) from a group by id where r.RN > 1
Метки: