Как получить совокупность оконной функции в Postgres?

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

CREATE TABLE foo
(
  perm integer[] NOT NULL,
  combo integer[] NOT NULL,
  value numeric NOT NULL DEFAULT 0
);
INSERT INTO foo
VALUES
( '{3,1,2}', '{1,2,3}', '1.1400' ),
( '{3,1,2}', '{1,2,3}', '0' ),
( '{3,1,2}', '{1,2,3}', '1.2680' ),
( '{3,1,2}', '{1,2,3}', '0' ),
( '{3,1,2}', '{1,2,3}', '1.2680' ),
( '{3,1,2}', '{1,2,3}', '0' ),
( '{3,1,2}', '{1,2,3}', '0' ),
( '{3,1,2}', '{1,2,3}', '1.2680' ),
( '{3,1,2}', '{1,2,3}', '0.9280' ),
( '{3,1,2}', '{1,2,3}', '0' ),
( '{3,1,2}', '{1,2,3}', '1.2680' ),
( '{3,1,2}', '{1,2,3}', '0' ),
( '{3,1,2}', '{1,2,3}', '0' ),
( '{3,1,2}', '{1,2,3}', '1.2680' ),
( '{3,1,2}', '{1,2,3}', '0' ),
( '{3,2,1}', '{1,2,3}', '0' ),
( '{3,2,1}', '{1,2,3}', '0.8000' )

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

SELECT
  f1.perm,
  f2.combo,
  f1.perm_average_value,
  f2.combo_average_value,
  f1.perm_stddev,
  f2.combo_stddev,
  f1.perm_count,
  f2.combo_count
FROM
(
  SELECT
    perm,
    combo,
    avg( value ) AS perm_average_value,
    stddev_pop( value ) AS perm_stddev,
    count( * ) AS perm_count
  FROM foo
  GROUP BY perm, combo
) AS f1
JOIN
(
  SELECT
    combo,
    avg( value ) AS combo_average_value,
    stddev_pop( value ) AS combo_stddev,
    count( * ) AS combo_count
  FROM foo
  GROUP BY combo
) AS f2 ON ( f1.combo = f2.combo );

Тем не менее, этот запрос может быть довольно медленным, когда у меня много данных, потому что таблица «foo» (которая на самом деле состоит из 14 разделов, каждая с примерно 4 миллионами строк) требуется дважды проверять.

Недавно я узнал, что Postgres поддерживает «Функции окна», который в основном похож на GROUP BY для определенного столбца. Я изменил свой запрос, чтобы использовать их так:

SELECT
  perm,
  combo,
  avg( value ) as perm_average_value,
  avg( avg( value ) ) over w_combo AS combo_average_value,
  stddev_pop( value ) as perm_stddev,
  stddev_pop( avg( value ) ) over w_combo as combo_stddev,
  count( * ) as perm_count,
  sum( count( * ) ) over w_combo AS combo_count
FROM foo
GROUP BY perm, combo
WINDOW w_combo AS ( PARTITION BY combo );

Пока это работает для столбца «combo_count», столбцы «combo_average_value» и «combo_stddev» больше не точны. Похоже, что среднее значение берется для каждой перестановки, а затем усредняется второй раз для каждой комбинации, что неверно.

Как я могу это исправить? Можно ли использовать функции окна в качестве оптимизации здесь?

10 голосов | спросил Scott Small 13 J000000Saturday13 2013, 03:56:16

1 ответ


8

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

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

SELECT perm
      ,combo
      ,avg(value)                 AS perm_average_value
      ,sum(avg(value) * count(*)) OVER w_combo /
       sum(count(*)) OVER w_combo AS combo_average_value
      ,stddev_pop(value)          AS perm_stddev
      ,0                          AS combo_stddev  -- doesn't work!
      ,count(*)                   AS perm_count
      ,sum(count(*)) OVER w_combo AS combo_count
FROM   foo
GROUP  BY perm, combo
WINDOW w_combo  AS (PARTITION BY combo);

Для combo_average_value вам понадобится это выражение

sum(avg(value) * count(*)) OVER w_combo / sum(count(*)) OVER w_combo

Так как вам нужен взвешенный средний. (В среднем группа с 10 членами весит больше, чем в среднем по группе с двумя членами!)

Это работает :

SELECT DISTINCT ON (perm, combo)
       perm
      ,combo
      ,avg(value)        OVER wpc AS perm_average_value
      ,avg(value)        OVER wc  AS combo_average_value
      ,stddev_pop(value) OVER wpc AS perm_stddev
      ,stddev_pop(value) OVER wc  AS combo_stddev
      ,count(*)          OVER wpc AS perm_count
      ,count(*)          OVER wc  AS combo_count
FROM   foo
WINDOW wc  AS (PARTITION BY combo)
      ,wpc AS (PARTITION BY perm, combo);

Здесь я использую два разных окна и уменьшаю строки с помощью DISTINCT, который применяется даже после оконных функций.

Но я серьезно сомневаюсь, что это будет быстрее, чем ваш первоначальный запрос. Я уверен, что это не так.

Лучшая производительность с измененным расположением таблиц

Массивы имеют накладные расходы в 24 байта (небольшие изменения в зависимости от типа). Кроме того, у вас, кажется, довольно много элементов на массив и много повторений. Для огромной таблицы, такой как ваша, она заплатит нормализовать схему. Пример макета:

CREATE TABLE combo ( 
  combo_id serial PRIMARY KEY
 ,combo    int[] NOT NULL
);

CREATE TABLE perm ( 
  perm_id  serial PRIMARY KEY
 ,perm     int[] NOT NULL
);

CREATE TABLE value (
  perm_id  int REFERENCES perm(perm_id)
 ,combo_id int REFERENCES combo(combo_id)
 ,value numeric NOT NULL DEFAULT 0
);

Если вам не нужна ссылочная целостность, вы можете опустить ограничения внешнего ключа.

Соединение с combo_id также может быть помещено в таблицу perm, но в этом случае я бы сохранил его (немного ненормированный) в value для лучшей производительности.

Это приведет к размеру строки в 32 байта (кортеж заголовка + заполнение: 24 байта, 2 x int (8 байт), без заполнения) плюс неизвестный размер вашего numeric . (Если вам не нужна предельная точность, double precision или даже real).

Подробнее о физическом хранении в этом связанном ответе на SO или здесь:
Настройка PostgreSQL для производительности чтения

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

В подзапросе первый агрегат и затем присоединяться к perm и combo для лучшей производительности.

ответил Erwin Brandstetter 13 J000000Saturday13 2013, 07:30:27

Похожие вопросы

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132