Есть ли более быстрый способ, чем «СТРОКИ МЕЖДУ НЕОГРАНИЧЕННЫМИ ПРЕДЫДУЩИМИ И ТЕКУЩИМИ РЯДАМИ»

У меня есть следующая таблица базы данных:

date        a   b   c   d   add result
23.02.07    A   B   C1  D1  1   1
24.02.07    A   B   C1  D1  0   1
25.02.07    A   B   C1  D1  1   2
26.02.07    A   B   C1  D1  1   3
27.02.07    A   B   C1  D1  1   4
28.02.07    A   B   C1  D1  0   4
01.03.07    A   B   C1  D1  0   4
02.03.07    A   B   C1  D1  0   4
03.03.07    A   B   C1  D1  1   5
04.03.07    A   B   C1  D1  0   5
05.03.07    A   B   C1  D1  0   5
06.03.07    A   B   C1  D1  0   5
07.03.07    A   B   C1  D1  2   7

17.02.07    A   B   C2  D2  1   1
18.02.07    A   B   C2  D2  0   1
19.02.07    A   B   C2  D2  0   1
20.02.07    A   B   C2  D2  0   1
21.02.07    A   B   C2  D2  0   1
22.02.07    A   B   C2  D2  0   1
23.02.07    A   B   C2  D2  0   1
24.02.07    A   B   C2  D2  0   1
25.02.07    A   B   C2  D2  1   2
26.02.07    A   B   C2  D2  3   5
27.02.07    A   B   C2  D2  1   6
28.02.07    A   B   C2  D2  0   6

Результат столбца (последний) не является частью фактического набора данных. В этой колонке показано, чего я пытаюсь достичь. По сути, я суммирую все предыдущие значения «добавить» и текущее значение данного раздела, используя аналитическую функцию, как показано ниже:

SUM(add) OVER(PARTITION BY
    A,
    B,
    C,
    D,
    ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS result

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

Еще немного информации:

  • По дате существует индекс btree (это не тип данных даты, а переменный символ).
  • Все остальные являются переменными символами вместо добавления, которое является целым числом.
  • A и B также имеют индекс btree.

Есть ли лучший/более эффективный способ сделать это?


postgresql sql postgresql-performance
person Dennis    schedule 06.02.2017    source источник
comment
Это уже должно быть самым быстрым подходом. Вы можете ускорить это с помощью этого покрывающего индекса, в котором уже есть данные в нужном порядке: create index idx_covering on mytable(a,b,c,d,date,add);.   -  person Thorsten Kettner    schedule 06.02.2017
comment
Пожалуйста, будьте более конкретными в отношении больших и медленных   -  person David דודו Markovitz    schedule 06.02.2017
comment
Как создается столбец добавления?   -  person Pரதீப்    schedule 06.02.2017

Ответы (1)


arrow_upward
0
arrow_downward

Определение оконной рамы

Ваш ORDER BY не является детерминированным (несколько строк имеют одинаковые date для каждого раздела). С определением фрейма ROWSBETWEEN UNBOUNDED PRECEDING AND CURRENT ROW результат вашего запроса может меняться между вызовами.

Внимательно прочитайте определения в руководстве.

Определение кадра по умолчанию — RANGEBETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, что, вероятно, имеет больше смысла для вашего случая: оно добавляет все одноранговые элементы текущей строки в порядке сортировки (один и тот же день в разделе), поэтому вы получаете одинаковую общую сумму. для всех строк в разделе с одним и тем же днем.

Либо сделайте порядок сортировки детерминированным (например, добавив ПК в качестве последнего элемента ORDER BY), либо переключитесь на RANGE. И поскольку RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW используется по умолчанию, вы можете упростить:

SUM(add) OVER(PARTITION BY A, B, C, D ORDER BY date)

Надлежащее date обращение

Это неправильно:

По дате существует индекс btree (это не тип данных даты, а переменный символ).

Это ошибка с последствиями. Никогда не сохраняйте даты как varchar, храните даты как date. Даже если ORDER BY date должно случаться правильно работать с вашим конкретным строковым форматом ('23.02.07', как вы указали в вопросе, нет), это еще медленнее. И подвержен ошибкам. И память больше (следовательно, опять же медленнее).

Оптимизация производительности

Помимо всего этого, многостолбцовый индекс со столбцами индекса в соответствующем порядке может повысить производительность (как прокомментировал Торстен):

CREATE INDEX foo ON tbl (A, B, C, D, date, add);

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

Детали зависят от полной ситуации: версия Postgres, определение таблицы, полный запрос,...

person Erwin Brandstetter    schedule 06.02.2017
comment
Вы уверены в рамке по умолчанию? Я предположил, что PostgreSQL поддерживает стандартный SQL по умолчанию, и ваша ссылка также показывает это: Опция кадрирования по умолчанию — RANGE UNBOUNDED PRECEDING. RANGE обычно намного дороже, чем ROWS (даже если результат тот же из-за уникального ORDER BY), поэтому ROWS UNBOUNDED PRECEDING должен быть лучше. - person dnoeth; 06.02.2017
comment
@dnoeth: Спасибо! Вы абсолютно правы, моя оплошность. RANGE по умолчанию. Я соответствующим образом адаптировал свой ответ. Однако лучшее определяется требованиями. - person Erwin Brandstetter; 06.02.2017