Jak uzyskać agregację funkcji okna w Postgres?

Mam tabelę zawierającą dwie kolumny permutacji / kombinacji tablic liczb całkowitych oraz trzecią kolumnę zawierającą wartość, taką jak:

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' )

Chcę znaleźć średnią i odchylenie standardowe dla każdej permutacji, a także dla każdej kombinacji. Mogę to zrobić za pomocą tego zapytania:

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 );

Jednak to zapytanie może być dość wolne, gdy mam dużo danych, ponieważ tabela „foo” (która w rzeczywistości składa się z 14 partycji z około 4 milionami wierszy) musi zostać przeskanowana dwukrotnie.

Niedawno dowiedziałem się, że Postgres obsługuje „Funkcje okien”, które są zasadniczo jak GROUP BY dla określonej kolumny. Zmodyfikowałem moje zapytanie, aby użyć ich w następujący sposób:

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 );

Chociaż działa to w przypadku kolumny „combo_count”, kolumny „combo_average_value” i „combo_stddev” nie są już dokładne. Wydaje się, że średnia jest pobierana dla każdej permutacji, a następnie uśredniana po raz drugi dla każdej kombinacji, co jest niepoprawne.

Jak mogę to naprawić? Czy funkcje okna mogą być tutaj użyte jako optymalizacja?

Państwo może mieć funkcje Okno na skutek zagregowanych funkcji w jednym poziomie zapytań.

To wszystko działałoby ładnie po kilku modyfikacjach - z wyjątkiem tego, że nie udaje się to w przypadku standardowego odchylenia od zasady matematycznej . Wymagane obliczenia nie są liniowe, więc nie można po prostu łączyć standardowych odchyleń subpopulacji.

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);

Bo combo_average_valuepotrzebujesz tego wyrażenia

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

Ponieważ potrzebujesz średniej ważonej . (Średnia grupa z 10 członkami waży więcej niż średnia grupa z zaledwie 2 członkami!)

Działa to :

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);

Używam tutaj dwóch różnych okien i zmniejszam wiersze, z DISTINCTktórymi jest stosowany, nawet po funkcjach okna.

Ale poważnie wątpię, że będzie to szybsze niż twoje oryginalne zapytanie. Jestem prawie pewien, że tak nie jest.

Lepsza wydajność dzięki zmienionemu układowi stołu

Tablice mają narzut 24 bajtów (niewielkie różnice w zależności od typu). Ponadto wydaje się, że masz sporo elementów na tablicę i wiele powtórzeń. W przypadku ogromnego stołu, takiego jak twój, opłacałoby się znormalizować schemat. Przykładowy układ:

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
);

Jeśli nie potrzebujesz integralności referencyjnej, możesz pominąć ograniczenia klucza obcego.

Połączenie z combo_idmożna również umieścić w tabeli perm, ale w tym scenariuszu zapisałbym je (nieco zdenormalizowane) w valuecelu uzyskania lepszej wydajności.

Spowodowałoby to rozmiar wiersza 32 bajty (krotka nagłówek + dopełnianie: 24 bajty, 2 x int (8 bajtów), bez dopełniania), a także nieznany rozmiar numerickolumny. (Jeśli nie potrzebujesz ekstremalnej precyzji, może to zrobić double precisionnawet realkolumna).

Więcej informacji na temat pamięci fizycznej znajduje się w tej pokrewnej odpowiedzi na stronie SO lub tutaj:
Konfigurowanie PostgreSQL pod kątem wydajności odczytu

Tak czy inaczej, to tylko ułamek tego, co masz teraz i sprawiłoby, że twoje zapytanie byłoby znacznie szybsze pod względem samego rozmiaru. Grupowanie i sortowanie według prostych liczb całkowitych jest również znacznie szybsze.

Byś najpierw kruszywo w podkwerendzie a następnie dołączyć do permi combodla uzyskania najlepszej wydajności.