вычислите среднее значение(значение) для последних 10 записей postgresql

ProgramBox

вычислите среднее значение(значение) для последних 10 записей postgresql

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

#sql #postgresql #average #window-functions

Вопрос:

у меня сложная задача, давайте предположим, что у нас есть таблица «Гонки», и там у нас есть столбцы ТРЕК, АВТОМОБИЛЬ, ВРЕМЯ КРУГА. Вот пример того, как могут выглядеть данные:

ID	дорожка	Автомобиль	circle_time
10	1	10	15
9	1	10	14
8	1	10	16
7	1	10	15
6	1	10	13
5	2	10	7
4	2	10	4
3	2	10	5
2	3	10	8
1	3	10	10

что мне нужно, я должен добавить еще один coumn, такой как avg3_circle_time, который покажет мне среднее время за последние 3 круга с каждого трека, например:

ID	дорожка	Автомобиль	circle_time	avg3_circle_time
10	1	10	15	15
9	1	10	14	15
8	1	10	16	14.6
7	1	10	15	нулевой
6	1	10	13	нулевой
5	2	10	7	5.3
4	2	10	4	нулевой
3	2	10	5	нулевой
2	3	10	8	нулевой
1	3	10	10	нулевой

Я знаю, как это может работать в oracle, вы могли бы использовать что-то вроде rowid, но в случае postgresql я не знаю,у меня есть черновик, например…. avg(circle_time) (РАЗДЕЛ ПО дорожкам, автомобилям…..) как avg3_circle_time….. помогите мне решить эту задачу, пожалуйста

1. Как вы определяете «последние три»? Я имею в виду, что определяет, наступит ли время круга 15 раньше 14?

2. @SalmanA хорошо, я обновил свой вопрос и добавил идентификатор для таблицы, поэтому нам нужно отсортировать идентификатор по убыванию, поэтому, например, для дорожки 1 результат последнего круга равен 15 с идентификатором 10, первый-13 с идентификатором 6

3. Средние значения тоже не совпадают, но я предполагаю, что вы просто ввели случайные числа.

4. Добавьте явную спецификацию окна в OVER() , ROWS BETWEEN..

Ответ №1:

Вы можете использовать оконные функции для расчета скользящих средних:

 SELECT track, id, car, circle_time, AVG(circle_time) OVER (
  PARTITION BY track
  ORDER BY id
  ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
)
FROM t
ORDER BY track, id

В зависимости от вашего определения предыдущих трех, окно может быть ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING .

Ответ №2:

Если вам нужны только значения, когда доступно не менее 3 кругов

 select *
 , case when lag(id, 2) over(partition by TRACK, CAR order by id) is not null then 
        avg(CIRCLE_TIME) over(partition by TRACK, CAR order by id rows between 2 preceding and current row) end a
from Racing 
order by id desc;

db<>скрипка

Выход

 id  track   car circle_time a
10  1   10  15  15.0000000000000000
9   1   10  14  15.0000000000000000
8   1   10  16  14.6666666666666667
7   1   10  15  null
6   1   10  13  null
5   2   10  7   5.3333333333333333
4   2   10  4   null
3   2   10  5   null
2   3   10  8   null
1   3   10  10  null

1. current row это конец кадра по умолчанию, поэтому вы можете сократить rows between 2 preceding and current row до rows 2 preceding .

2. Аналогично, условие lag(id, 2) over(partition by TRACK, CAR order by id) is not null может быть сокращено до row_number() over(partition by TRACK, CAR order by id) > 2 , так как вам на самом деле все равно, какое значение id , вы просто хотите проверить, что в разделе есть по крайней мере две предыдущие строки.

3. Кроме того, в вопросе задается среднее значение за последние три круга трассы, независимо от автомобиля, поэтому, похоже CAR , не должно быть частью раздела — вопрос.

4. Вы также можете использовать это window предложение, чтобы избежать повторения определения окна.

Ответ №3:

Используйте LAED (), затем проверьте, является ли одна из следующих 2 строк пустой или нет. ЗАТЕМ суммируйте три значения для вычисления среднего значения.

 -- PostgreSQL
SELECT *
     , CASE WHEN next_circle_time IS NULL OR next_next_circle_time IS NULL
               THEN NULL
            ELSE ((t.circle_time   COALESCE(next_circle_time, 0)   COALESCE(next_next_circle_time, 0)) / 3 :: DECIMAL) :: DECIMAL(10, 1)
        END avg_circle_time
FROM (SELECT *
           , LEAD(circle_time, 1) OVER (PARTITION BY track ORDER BY id DESC) next_circle_time
           , LEAD(circle_time, 2) OVER (PARTITION BY track ORDER BY id DESC) next_next_circle_time
      FROM Racings) t

Другой способ Использовать AVG()

 SELECT *
     , CASE WHEN LEAD(circle_time, 2) OVER (PARTITION BY track ORDER BY id DESC) IS NULL
                 OR LEAD(circle_time, 1) OVER (PARTITION BY track ORDER BY id DESC) IS NULL
               THEN NULL
            ELSE AVG(circle_time) OVER (PARTITION BY track ORDER BY id DESC ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING)
       END :: DECIMAL(10, 2) avg_circle_time       
FROM Racings

Пожалуйста, проверьте по URL-адресу, где существуют оба запроса https://dbfiddle.uk/?rdbms=postgres_11amp;fiddle=f0cd868623725a1b92bf988cfb2deba3

Ответ №4:

Некоторые из опубликованных ответов заканчиваются повторением определения окна. Вы можете избежать этого с window помощью пункта:

 select *, 
   case when row_number() over(track_window) > 2 
        then trunc(avg(CIRCLE_TIME) over(track_window rows 2 preceding), 1)
   end a
from Racing 
window track_window as (partition by track order by id)
order by id desc

Обратите внимание, как в этом примере track_window определяется один раз, а затем повторно используется для обоих row_number и avg . В последнем случае предложение window также украшено рамкой ( rows 2 preceding ).

Метки: вычислите среднее значение(значение) для последних 10 записей postgresql