Krocząca suma / liczba / średnia w przedziale dat

W bazie danych transakcji obejmującej 1000 podmiotów w ciągu 18 miesięcy chciałbym uruchomić zapytanie w celu grupowania każdego możliwego 30-dniowego okresu entity_idz sumą ich kwot transakcji i COUNT ich transakcji w tym 30-dniowym okresie, oraz zwrócić dane w sposób, który mogę następnie wykonać zapytanie. Po wielu testach ten kod osiąga wiele z tego, czego chcę:

SELECT id, trans_ref_no, amount, trans_date, entity_id,
    SUM(amount) OVER(PARTITION BY entity_id, date_trunc('month',trans_date) ORDER BY entity_id, trans_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS trans_total,
    COUNT(id)   OVER(PARTITION BY entity_id, date_trunc('month',trans_date) ORDER BY entity_id, trans_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS trans_count
  FROM transactiondb;

I użyję w większym zapytaniu o strukturze podobnej do:

SELECT * FROM (
  SELECT id, trans_ref_no, amount, trans_date, entity_id,
      SUM(amount) OVER(PARTITION BY entity_id, date_trunc('month',trans_date) ORDER BY entity_id, trans_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS trans_total,
      COUNT(id)   OVER(PARTITION BY entity_id, date_trunc('month',trans_date) ORDER BY entity_id, trans_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS trans_count
    FROM transactiondb ) q
WHERE trans_count >= 4
AND trans_total >= 50000;

Przypadek, którego to zapytanie nie obejmuje, ma miejsce, gdy transakcje liczą się przez wiele miesięcy, ale nadal występują w odstępie 30 dni od siebie. Czy tego typu zapytania są możliwe w Postgres? Jeśli tak, z zadowoleniem przyjmuję wszelkie uwagi. Wiele innych tematów omawia „ uruchomione ” agregaty, a nie ciągłe .

Aktualizacja

CREATE TABLESkrypt:

CREATE TABLE transactiondb (
    id integer NOT NULL,
    trans_ref_no character varying(255),
    amount numeric(18,2),
    trans_date date,
    entity_id integer
);

Przykładowe dane można znaleźć tutaj . Korzystam z PostgreSQL 9.1.16.

Wyjście idealny obejmowałyby SUM(amount)i COUNT()wszystkich transakcji ponad toczenia 30-dniowego okresu. Zobacz ten obraz, na przykład:

Podświetlenie zielonej daty wskazuje, co obejmuje moje zapytanie. Żółte podświetlanie wierszy wskazuje rekordy, które chciałbym stać się częścią zestawu.

Poprzednie czytanie:

• Porównanie funkcji okna Postgresql
• Grupuj według interwałów danych
• Najlepszy sposób na liczenie rekordów według dowolnych przedziałów czasowych ...
• Średnia ruchoma w Postgresql

Zapytanie, które masz

Możesz uprościć zapytanie, używając WINDOWklauzuli, ale to tylko skraca składnię, a nie zmienia planu zapytań.

SELECT id, trans_ref_no, amount, trans_date, entity_id
     , SUM(amount) OVER w AS trans_total
     , COUNT(*)    OVER w AS trans_count
FROM   transactiondb
WINDOW w AS (PARTITION BY entity_id, date_trunc('month',trans_date)
             ORDER BY trans_date
             ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING);

• Również używając nieco szybszego count(*), skoro na idpewno jest zdefiniowane NOT NULL?
• I nie musisz ORDER BY entity_idjuż tego robićPARTITION BY entity_id

Możesz jednak uprościć: w
ogóle nie dodawaj ORDER BYdo definicji okna, nie dotyczy to twojego zapytania. Wówczas nie musisz definiować niestandardowej ramy okna:

SELECT id, trans_ref_no, amount, trans_date, entity_id
     , SUM(amount) OVER w AS trans_total
     , COUNT(*)    OVER w AS trans_count
FROM   transactiondb
WINDOW w AS (PARTITION BY entity_id, date_trunc('month',trans_date);

Prostsze, szybsze, ale nadal po prostu lepsza wersja tego, co mają , ze statycznymi miesięcy.

Zapytanie, które możesz chcieć

... nie jest jasno zdefiniowane, więc oprę się na następujących założeniach:

Policz transakcje i kwoty za każdy 30-dniowy okres w ramach pierwszej i ostatniej transakcji entity_id. Wyklucz początkowe i końcowe okresy bez aktywności, ale uwzględnij wszystkie możliwe 30-dniowe okresy w tych zewnętrznych granicach.

SELECT entity_id, trans_date
     , COALESCE(sum(daily_amount) OVER w, 0) AS trans_total
     , COALESCE(sum(daily_count)  OVER w, 0) AS trans_count
FROM  (
   SELECT entity_id
        , generate_series (min(trans_date)::timestamp
                         , GREATEST(min(trans_date), max(trans_date) - 29)::timestamp
                         , interval '1 day')::date AS trans_date
   FROM   transactiondb 
   GROUP  BY 1
   ) x
LEFT JOIN (
   SELECT entity_id, trans_date
        , sum(amount) AS daily_amount, count(*) AS daily_count
   FROM   transactiondb
   GROUP  BY 1, 2
   ) t USING (entity_id, trans_date)
WINDOW w AS (PARTITION BY entity_id ORDER BY trans_date
             ROWS BETWEEN CURRENT ROW AND 29 FOLLOWING);

Zawiera listę wszystkich 30-dniowych okresów dla każdego entity_idz twoimi agregatami i trans_datejako pierwszy dzień (włącznie) okresu. Aby uzyskać wartości dla każdego wiersza, ponownie połącz się z tabelą bazową ...

Podstawowa trudność jest taka sama, jak omówiono tutaj:

• Odwołanie do bieżącego wiersza w klauzuli FILTER funkcji okna

Definicja ramki okna nie może zależeć od wartości bieżącego wiersza.

A raczej zadzwoń generate_series()z timestampwejściem:

• Generowanie szeregów czasowych między dwiema datami w PostgreSQL

Zapytanie, którego naprawdę chcesz

Po aktualizacji pytania i dyskusji:
kumuluj te same wiersze entity_idw 30-dniowym oknie, zaczynając od każdej faktycznej transakcji.

Ponieważ Twoje dane są rozproszone rzadko, powinno być bardziej wydajne przeprowadzenie samozłączenia z warunkiem zasięgu , tym bardziej, że Postgres 9.1 nie ma jeszcze LATERALsprzężeń:

SELECT t0.id, t0.amount, t0.trans_date, t0.entity_id
     , sum(t1.amount) AS trans_total, count(*) AS trans_count
FROM   transactiondb t0
JOIN   transactiondb t1 USING (entity_id)
WHERE  t1.trans_date >= t0.trans_date
AND    t1.trans_date <  t0.trans_date + 30  -- exclude upper bound
-- AND    t0.entity_id = 114284  -- or pick a single entity ...
GROUP  BY t0.id  -- is PK!
ORDER  BY t0.trans_date, t0.id

SQL Fiddle.

Kroczące okno może mieć sens (w odniesieniu do wydajności) tylko w przypadku danych przez większość dni.

To robi a nie zbiorcze duplikaty w (trans_date, entity_id)ciągu dnia, ale wszystkie wiersze tego samego dnia są zawsze włączone w oknie 30-dniowego.

W przypadku dużego stołu taki indeks pokrycia może nieco pomóc:

CREATE INDEX transactiondb_foo_idx
ON transactiondb (entity_id, trans_date, amount);

Ostatnia kolumna amountjest przydatna tylko wtedy, gdy wykonasz z niej tylko indeksy. W przeciwnym razie upuść.

Ale i tak nie będzie używany, gdy wybierzesz cały stół. Obsługuje zapytania dotyczące małego podzbioru.