Тенденция суммы с течением времени

У меня есть таблица (в Postgres 9.1), которая выглядит примерно так:

CREATE TABLE actions (
  user_id: INTEGER,
  date:    DATE,
  action:  VARCHAR(255),
  count:   INTEGER
)

Например:

    user_id    |    date    |     action   | count
---------------+------------+--------------+-------
             1 | 2013-01-01 | Email        |     1
             1 | 2013-01-02 | Call         |     3
             1 | 2013-01-03 | Email        |     3
             1 | 2013-01-04 | Call         |     2
             1 | 2013-01-04 | Voicemail    |     2
             1 | 2013-01-04 | Email        |     2
             2 | 2013-01-04 | Email        |     2

Я хотел бы иметь возможность просматривать общее количество действий пользователя с течением времени для определенного набора действий; например, звонки + электронная почта:

  user_id  | date        |  count  
-----------+-------------+---------
         1 | 2013-01-01  |       1
         1 | 2013-01-02  |       4
         1 | 2013-01-03  |       7
         1 | 2013-01-04  |      11
         2 | 2013-01-04  |       2

Чудовище, которое я создал до сих пор, выглядит так:

SELECT
  date, user_id, SUM(count) OVER (PARTITION BY user_id ORDER BY date) AS count
FROM
  actions
WHERE
  action IN ('Call', 'Email') 
GROUP BY
  user_id, date, count;

Который работает для отдельных действий, но, кажется, ломается для нескольких действий, когда они происходят в один и тот же день, например, вместо ожидаемого 11 на 2013-01-04 мы получаем 9:

    date    |      user_id | count
------------+--------------+-------
 2013-01-01 | 1            |     1
 2013-01-02 | 1            |     4
 2013-01-03 | 1            |     7
 2013-01-04 | 1            |     9 <-- should be 11?
 2013-01-04 | 2            |     2

Можно ли изменить мой запрос, чтобы решить эту проблему? Я попытался удалить группировку на count, но Postgres, похоже, это не нравится:

column "actions.count" must appear in the GROUP BY clause
or be used in an aggregate function
LINE 2:      date, user_id, SUM(count) OVER (PARTITION BY user...
                                ^

postgresql sql window-functions aggregate-functions
person John Ledbetter    schedule 22.07.2013    source источник
comment
Как всегда: ваша версия Postgres, пожалуйста.   -  person Erwin Brandstetter    schedule 22.07.2013

Ответы (3)


arrow_upward
1
arrow_downward

Этот запрос дает результат, который вы ищете:

SELECT DISTINCT   
  date, user_id, SUM(count) OVER (PARTITION BY user_id ORDER BY date) AS count 
  FROM actions
WHERE
  action IN ('Call', 'Email');

Окно по умолчанию уже является тем, что вам нужно, в соответствии с официальные документы и «DISTINCT» устраняют повторяющиеся строки, когда и электронные письма, и звонки происходят в один и тот же день.

См. скрипт SQL.

person Mark Stosberg    schedule 22.07.2013

arrow_upward
1
arrow_downward

В таблице есть столбец с именем «count», а выражение в предложении SELECT имеет псевдоним «count», что неоднозначно.

Прочтите документацию: http://www.postgresql.org/docs/9.0/static/sql-select.html#SQL-GROUPBY< /а>

В случае неоднозначности имя GROUP BY будет интерпретироваться как имя входного столбца, а не как имя выходного столбца.

Это означает, что ваш запрос группируется не по «количеству», оцененному в предложении SELECT, а скорее группируется по значениям «количества», взятым из таблицы.

Этот запрос дает ожидаемые результаты, см. SQL Fiddle.

SELECT date, user_id, count
from (
   Select date, user_id, 
          SUM(count) OVER (PARTITION BY user_id ORDER BY date) AS count
  FROM actions
  WHERE
    action IN ('Call', 'Email') 
) alias
GROUP BY
  user_id, date, count;
person krokodilko    schedule 22.07.2013
comment
Ваш запрос правильный, ваша цитата верна, но объяснение покрывает только половину проблемы. Оконные функции применяются после агрегатных функций. Поэтому ссылки на выражения с оконными функциями вообще запрещены в GROUP BY. Ваш запрос позволяет избежать проблемы, помещая оконную функцию в подзапрос, но это не необходимо. У вас может быть оконная функция над результатом агрегатной функции на одном уровне запроса (как показано в моем ответе), что обычно быстрее (в 2,5 раза в моем тесте). - person Erwin Brandstetter; 22.07.2013

arrow_upward
1
arrow_downward

Утверждает

Неясно, хотите ли вы сортировать по user_id или date

Также непонятно, хотите ли вы включать в список результатов даты, для которых нет строки в базовой таблице. В этом случае обратитесь к этому тесно связанному ответу:
PostgreSQL: текущее количество строк для запроса "по минутам"

Восстановить имена

Во-первых, я использую эту тестовую таблицу вместо вашей проблемной таблицы:

CREATE TEMP TABLE actions (
  user_id integer,
  thedate    date,
  action  text,
  ct   integer
);

Использование вами зарезервированных слов и имена функций как идентификаторы (имена столбцов) являются частью проблемы.

Восстановить запрос

Комбинируйте агрегатные и оконные функции

Поскольку агрегатные функции применяются первыми, исходный запрос объединяет две строки, найденные для user_id = 1 и thedate = '2013-01-04', в одну. Вы должны умножить на count(*), чтобы получить фактическое количество пробежок.

Вы можете сделать это без подзапроса, так как вы можете комбинировать агрегатные функции и оконные функции. Сначала применяются агрегатные функции. Вы даже можете иметь функции окна над результатом агрегатных функций.

SELECT thedate
     , user_id
     , sum(ct * count(*)) OVER (PARTITION BY user_id
                                ORDER BY thedate) AS running_ct
FROM   actions
WHERE  action IN ('Call', 'Email') 
GROUP  BY user_id, thedate, ct
ORDER  BY user_id, thedate;

Или упростить до:

...
 , sum(sum(ct)) OVER (PARTITION BY user_id
                      ORDER BY thedate) AS running_ct
...

Это также должно быть самое быстрое из представленных решений.

Здесь внутренний sum() — это агрегатная функция, а внешний sum() — это оконная функция — над результатом агрегатной функции.

Или используйте DISTINCT

Другой способ — использовать DISTINCT или DISTINCT ON, так как это применяется после оконных функций:

DISTINCT - это возможно, так как running_ct в любом случае гарантированно будет одинаковым, так как все пиры суммируются сразу для определение фрейма оконных функций по умолчанию.

SELECT DISTINCT
       thedate
     , user_id
     , sum(ct) OVER (PARTITION BY user_id ORDER BY thedate) AS running_ct
FROM   actions
WHERE  action IN ('Call', 'Email')
ORDER  BY thedate, user_id;

Или упростите с помощью DISTINCT ON:

SELECT DISTINCT ON (thedate, user_id)
...

-›SQLfiddle демонстрирует все варианты .

person Erwin Brandstetter    schedule 22.07.2013
comment
Как именно использование зарезервированного слова вызвало проблему? - person Mark Stosberg; 22.07.2013
comment
@MarkStosberg: count — это зарезервированное слово в стандарте SQL. ОП, кажется, смущен неудачным использованием count в качестве имени функции и имени столбца. Вы также не должны использовать count в качестве идентификатора в своем ответе. - person Erwin Brandstetter; 22.07.2013
comment
Цените информацию. К сожалению, я не создал таблицу, но вижу, как это может вызвать проблемы. Можно ли это исправить, указав идентификатор? - person John Ledbetter; 22.07.2013
comment
@JohnLedbetter: Может, но лучше избегать зарезервированных слов в качестве идентификаторов для начала. Цитаты можно забыть... - person Erwin Brandstetter; 22.07.2013
comment
Я понимаю, что /лучшей практикой/ является избегать зарезервированных слов, я просто не заметил фактическую проблему, вызвавшую проблему в этом случае. - person Mark Stosberg; 22.07.2013
comment
@MarkStosberg: Что ж, в данном случае двойные кавычки не помогут, так как другие проблемы определяют результат. Видимость в ORDER BY предпочитает имена столбцов output (в отличие от GROUP BY!). Стандарт SQL в этом отношении довольно запутан. Подробнее см. в этот связанный ответ. Итак, для начала лучше избегать путаницы. КСТАТИ. sum(sum(ct)) в 2-3 раза быстрее, чем DISTINCT в моем тесте с 10 тыс. строк. - person Erwin Brandstetter; 22.07.2013