#sql #postgresql #window-functions #moving-average
#sql #postgresql #окно-функции #скользящее среднее
Вопрос:
Я хочу вычислить сумму 29 предыдущих дней в строке 30-го дня, я использую функцию фильтра и окна, но FILTER
она не работает,
он по-прежнему суммируется от начала до конца, если я использую:
Select *, Sum(quantity) filter (where time between time - interval '29 day' and time) over ()
from t1
он показывает нулевой столбец, если я использую:
Select *, Sum(quantity) filter (where time between time - interval '29 day' and time - interval '1 day') over ()
from t1
Данные, я уменьшаю столбцы для простоты
Time sum_quantity
2020-01-01 1
2020-01-02 2
2020-01-03 3
2020-01-04 6
....
2020-01-30 100
Тип данных: время — дата, а количество — целое число
Желаемый результат: должен иметь тот же столбец, что и первая таблица, и добавить этот столбец движущейся суммы
День 30 = общее количество дней с 1 по 29 день за каждые 30 дней
Как это исправить
Комментарии:
1. Пожалуйста, поделитесь с нами всем запросом. Также, пожалуйста, добавьте, что вы ожидаете получить в качестве конечного результата?
2. @VBoka я отредактировал
3. Я все еще не вижу ожидаемых результатов… Кроме того, пожалуйста, поделитесь с нами типами данных этих двух столбцов в вашей таблице t1
4. Пожалуйста, исправьте свой пример с данными, потому что, если время равно дате, то Day1 на самом деле не является хорошим примером. Добавьте обычную дату, это не должно быть слишком сложно…
5. Отредактировано, как вы сказали @VBoka
Ответ №1:
Вам нужна оконная функция с определением рамки окна с помощью range
:
select t1.*,
sum(quantity) over (order by time
range between interval '29 day' preceding and current row
)
from t1 ;
Редактировать:
Если у вас есть данные для всех дат, вы можете использовать rows
:
select t1.*,
sum(quantity) over (order by time
rows between 29 preceding and current row
)
from t1 ;
ПРАВКА II:
Если вам нужно разобраться с пропущенными днями в старых версиях Postgres, которые не поддерживаются range
, то расширение данных, вероятно, является самым простым методом:
select t1.*,
sum(quantity) over (order by time
rows between 29 preceding and current row
)
from (select generate_series(min(t1.time), max(t1.time), interval '1 day') as dte
from t1
) d left join
t1
on d.dte = t1.time;
Возможно, вы захотите отфильтровать дополнительные строки:
select t1.*
from (select t1.*,
sum(quantity) over (order by time
rows between 29 preceding and current row
) as running_sum
from (select generate_series(min(t1.time), max(t1.time), interval '1 day') as dte
from t1
) d left join
t1
on d.dte = t1.time
) t1
where t1.time is not null;
Комментарии:
1. Я запускаю запрос, и он показывает ошибку: есть способ исправить?
RANGE PRECEDING is only supported with UNBOUNDED
2. Это работает @HaoHao: dbfiddle.uk /…
3. @HaoHao . , , Какую версию Postgres вы используете?
4.
SELECT version()
он показывает ver10.6
, на dbfiddle он показывает12.4
@ GordonLinoff5. Я пытаюсь выполнить первый запрос с моими данными за 60 дней подряд, и он также суммируется от начала до конца …, поэтому я не знаю, для чего используется это окно
range between
. Я не используюrows
в случае отсутствия даты @GordonLinoff
Ответ №2:
Ваше filter (where)
предложение всегда истинно, а пустое over()
предложение — это окно, охватывающее весь набор результатов.
Вы должны указать окно в over
предложении, а не filter
в предложении. Возможно, вам нужно что-то вроде
sum(quantity) over (order by time rows between 29 preceding and current row)
или лучше range between...
.
Комментарии:
1. Я не хочу использовать диапазон строк, потому что время даты имеет значение @tomas, любым другим способом?
2. в случае, если отсутствует какая-то дата.
3.
rows
илиrange
это способ решить вашу проблему. Смотрите ответ Гордона.
Ответ №3:
Пожалуйста, используйте условие в where caluse, потому что вы используете функцию Windows, это похоже на условное выражение, например:
SUM(<expression>) FILTER(WHERE <condition>)
SUM(CASE WHEN <condition> THEN <expression> END)
Комментарии:
1. Это не ответ на мой вопрос, верно? пожалуйста, напишите полный запрос.
Ответ №4:
Это то, что вы хотите :
select m1.Time
, (select sum(sum_quantity)
from mytable m
where m.time between (m1.time - interval '29 day') and (m1.time)) sum_total
from mytable m1
group by m1.Time
order by m1.Time;
Или, может быть, это лучше:
select m1.Time
, sum(m.sum_quantity)
from mytable m
join mytable m1 on m.time between (m1.time - interval '29 day') and (m1.time)
group by m1.Time
order by m1.Time;
Вот демонстрация:
Комментарии:
1. 1-й запрос предназначен только для проверки данных,
2. 2-й запрос, который все еще нужно использовать
group by
, и он не соответствует моему случаю, я хочу рассчитать движущуюся сумму3. Я имею в виду, что я все еще хочу остаться столбцом
Time
, я добавлю к своему вопросу4. Мы могли бы использовать функцию window с методом заполнения данных в случае отсутствия даты, но я думаю, что это короче, спасибо. @vboka
5. Я использовал этот запрос, поскольку моя версия Postgre SQL равна 10