Группировать по концу периода вместо даты начала

Я хочу агрегировать данные по дате окончания набора данных с некоторым начальным периодом, а не началом. Например, я хочу запросить таблицу и вернуть количество совпадающих результатов за 30 дней до даты окончания даты, указанной в результатах. Исходная таблица будет содержать ТОЛЬКО дату совершения продажи (отметку времени). Пример:

sales_timestamp
------------------
2015-08-05 12:00:00
2015-08-06 13:00:00
2015-08-25 12:31:00
2015-08-26 01:02:00
2015-08-27 02:03:00
2015-08-29 04:23:00
2015-09-01 12:00:00
2015-09-02 12:00:00
2015-09-08 00:00:00

Пример вывода результирующего запроса:

date_period  |   count_of_sales
--------------------------------
2015-08-24   |        2
2015-08-31   |        6
2015-09-07   |        6

в котором дата_период 2015-09-07 будет означать, что компания продала 6 товаров за 30 дней, ЗАКОНЧИВАЮЩИЕСЯ 7 сентября 2015 года (и начиная с 7 августа 2015 года, если истинный 30-дневный период).

Я играл с вариантами функции date_trunc(), но, похоже, не могу добиться применения усечения к дате окончания вместо группировки по началу.

Эти данные будут размещены в PostgreSQL 9.1.


postgresql sql window-functions time-series postgresql-9.1
person JM4    schedule 09.09.2015    source источник
comment
Образцы данных - и скрипт SQL - действительно помогли бы передать то, что вы пытаетесь сделать. Например, почему у вас месячные с разницей в одну неделю, а вам нужны продажи в течение 30 дней?   -  person Gordon Linoff    schedule 09.09.2015
comment
Ваши результаты не будут точными, если вы храните данные о продажах за неделю. 7 плохо сочетается с 30.   -  person domdomcodecode    schedule 09.09.2015
comment
@d_ominic номера продаж не хранятся еженедельно. Это временные метки самой продажи.   -  person JM4    schedule 09.09.2015
comment
@GordonLinoff - postgresql - я просто никогда раньше не использовал SQLfiddle, поэтому хранил только некоторые данные схемы. В конце концов, это несколько бессмысленно, поскольку это можно рассматривать как гигантскую базу данных временных меток, регистрируемых, когда происходит продажа. Я просто хочу подсчитать количество групп продаж и дату окончания с некоторым определенным периодом опережения.   -  person JM4    schedule 09.09.2015
comment
Итак, вы хотите проверить последнюю дату, назовите это _1 _.... затем посчитайте в группе 7 дней до 30 дней назад с D?   -  person Juan Carlos Oropeza    schedule 09.09.2015
comment
@JuanCarlosOropeza точно (только на несколько недель, возможно, в зависимости от диапазона дат начала и окончания)   -  person JM4    schedule 09.09.2015
comment
И вы хотите делать это каждый понедельник в течение периода, за который у вас есть распродажи?   -  person Patrick    schedule 09.09.2015
comment
Версия Postgres и определение таблицы должны быть в вопросе.   -  person Erwin Brandstetter    schedule 09.09.2015
comment
Я обновил q, добавив еще несколько базовых образцов данных, поскольку SQLfiddle был несколько неактуальным, и это может больше выделить пример набора данных.   -  person JM4    schedule 09.09.2015

Ответы (1)


arrow_upward
1
arrow_downward

Этот запрос делает все, о чем вы просите:

SELECT day::date AS date_period, count_of_sales
FROM (
   SELECT *, sum(ct) OVER (ORDER BY day ROWS 30 PRECEDING) AS count_of_sales
   FROM   generate_series(date '2015-08-24' - 30  -- start 30 days earlier
                        , date '2015-09-07'
                        , interval '1 day') day
   LEFT JOIN (
      SELECT date_trunc('day', sales_timestamp) AS day, count(*)::int AS ct
      FROM   sales
      GROUP  BY 1
      ) s USING (day)
   ) sub
JOIN  generate_series(date '2015-08-24'
                    , date '2015-09-07 '
                    , interval '1 week') day USING (day);

SQL Fiddle.

Объяснение

  1. Создайте полный набор соответствующих дней (1-е _2 _ )
  2. LEFT JOIN к общему количеству за день. LEFT гарантирует одну строку в день, что позволяет нам использовать оконные функции на основе количества строк.

  3. Используйте sum() как оконную агрегатную функцию с настраиваемым фреймом за 30 дней до этого. (Вместо этого вы можете использовать 29, непонятно, как вы считаете.)

  4. Присоедините результат к фактическим дням, которые вы хотите получить. (2-е generate_series() с одним днем ​​в неделю).

Имейте в виду, что определение «дня» происходит от настройки текущего часового пояса вашего сеанса, если вы работаете с timestamptz. Результаты могут отличаться в разных часовых поясах. Не применяется только для timestamp, который не зависит от текущего часового пояса. Основы:

Связанный ответ с объяснением функции окна с настраиваемым определением фрейма:

person Erwin Brandstetter    schedule 09.09.2015
comment
Это круто. Спасибо. Я только что узнал, что БД, которую мы будем использовать для этого конкретного проекта, на самом деле находится на Vertica, но я посмотрю, есть ли у функции generate_series близкий родственник. - person JM4; 09.09.2015
comment
Не пользователь Vertica, а TIMESERIES Clause my.vertica.com/docs/7.1.x/HTML/index.htm#Authoring/ может оказаться полезным. Вы всегда можете создать свою собственную календарную таблицу вместо использования таких функций, как generate_series, что на самом деле может быть проще для вас в долгосрочной перспективе. - person Paul Maxwell; 09.09.2015