Среднее значение за 12 предшествующих месяцев и недели ISO

У меня есть следующий запрос, чтобы получить среднее значение единиц за предыдущие 12 месяцев в интервале, но моя проблема в том, что 12 предыдущих месяцев не учитывают первую неделю ISO в году, скажем, этот пример:

SELECT
    *,
   avg(units) OVER (                                             
       ORDER BY to_date(year::text || '-' || week::text, 'IYYY-IW')     
       RANGE between interval '12 months' preceding and current row)
FROM
   rolling_year_table 
   order by year,week;

В основном 1-я неделя ISO 2020 года (фактически «2019-12-30» не учитывается в расчетах.

Есть ли способ указать 12 месяцев предыдущей и текущей строки, но с использованием недель ISO?

Спасибо,

sql postgresql datetime window-functions date-arithmetic

15.12.2020 11:44

ReactJs | Supabase | Добавление данных в базу данных

Это и есть ваш редактор таблиц в supabase.👇

Понимание Python и переход к SQL

Перед нами лабораторная работа по BloodOath:

449

Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

Разве это не делает то, что вы хотите?

select ry.*,
   avg(units) over (                                             
       order by year * 100 + week
       range between 100 preceding and current row
from rolling_year_table 
order by year,week;

Предположим, что текущая строка — это 2020 год и 45-я неделя, это приведет к тому, что строки с той же недели в 2019 году будут перенесены в текущую строку.

15.12.2020 12:11

Ответ принят как подходящий

Это слишком долго для комментария.

Я не думаю, что есть простой способ сделать это. Проблема в несоответствии "неделя" и "год". В 12-месячном периоде около 52,2 недели. Итак, вы спрашиваете, что иногда период «12 месяцев» имеет 52 недели, а иногда 53 недели.

Я думаю, вы могли бы сделать кумулятивный расчет на основе последних 52 недель, а затем использовать логику условий, чтобы включить предыдущую 53-ю неделю. Проблема в . . . Я не знаю, каковы точные правила возврата на 53 недели назад.

Если единственная проблема заключается в том, что в 53-ю неделю года следует включить весь год, то это будет довольно легко включить. Псевдокод для этого будет:

(case when isoweek = 53
      then avg() over (. . . range between '53 week' preceding and current row)
      else avg() over (. . . range between '52 week' preceding and current row)
 end)

Обновлено:

Я не уверен на 100%, сработает ли это для вашего варианта использования. Но у меня есть идея, которая может сделать то, что вы хотите. То есть перечислить недели года как части года. Таким образом, годы с 52 неделями будут иметь одно исчисление, а годы с 53 неделями — другое.

Это будет выглядеть так:

select . . .,
       avg(units) over (order by year + (isoweek - 1) / weeks_in_year
                        range between 1 preceding and current row
                       )
from (select t.*,
             extract(isoyear from dte) as isoyear,
             extract(week from dte) as isoweek,
             greatest(extract(week from date_trunc('year', dte) + interval '1 year - 1 day'), 53) as weeks_in_year
f      from t
     ) t;

Вам нужно будет проверить это, чтобы увидеть, делает ли он то, что вы действительно хотите. Как я сказал в начале этого ответа, «12 месяцев назад» не имеет четкого определения для недель ISO, но это может быть разумной интерпретацией.

Спасибо, единственная проблема, с которой я столкнулся, заключается в том, что возврат на 52 или 53 недели назад не соответствует, например, неделе 1 в 2020 году, поскольку он показан как декабрь 2019 года.

— 16.12.2020 11:12

@Матиас. . . Я не полностью понимаю ваш комментарий. Изогод не принимается во внимание при возврате назад на 52 или 53 недели.

— 16.12.2020 14:04

15.12.2020 12:42