January 11th, 2007 by depesz | Tags: | 3 comments »
Did it help? If yes - maybe you can help me?

kto z was wie o istnieniu funkcji generate_series() w postgresie? a kto z niej kiedykolwiek (nie testowo!) skorzystał?
wydaje mi się, że jest to najbardziej niedoceniania funkcja istniejąca w tym systemie bazodanowym.
co daje? zobacz:

select * from generate_series(0, 100);

na pierwszy rzut oka nic rewolucyjnego? taki tam sobie liczniczek?
zobaczmy więc kilka przykładowych zastosowań.
pierwsze:
mamy tabelkę, z polem id (z seriala), i chcemy sprawdzić czy są dziury w numeracji. tradycyjna metoda to jakieś joiny czy skanowanie sekwencyjne ze zliczaniem dziur w aplikacji klienckiej.
a zobaczmy coś takiego:

create table x as select * from generate_series(1, 10000) as x (i);
delete from x where random() < 0.0010;

w ten sposób uzyskaliśmy tabelę x, z numerycznym polem i. a w numeracji tego pola są dziury.
jak je wylistować? tak:

select * from generate_series(1, 10000) except select i from x;

voila. działa. ślicznie. i szybko 🙂

to było proste i oczywiste.
teraz przykład trochę mniej oczywisty – przykład jest z mojej pracy, ale użyję trochę uproszczonej wersji struktur danych 🙂
stwórzmy sobie tabelkę z użytkownikami:

CREATE TABLE users (id serial PRIMARY KEY, username TEXT, registered date);

ok. teraz – wstawmy do niej 10 użytkowników, z losowymi unikalnymi username'ami, i różnymi, losowymi datami rejestracji:

insert into users (username, registered) select 'user: ' || x.i, '2000-01-01'::date + cast(random()*1000 as int4) from generate_series(1, 10) as x(i);

mamy teraz w users 10 użytkowników. daty rejestracji są losowane z zakresu ‘2000-01-01' do ‘2002-09-27';
teraz. to co potrzebowałem to musiałem wygenerować "faktury" dla użytkowników. w tym zaległe.
idea była taka, że w tabelce z fakturami:

CREATE TABLE invoices (id serial PRIMARY KEY, user_id INT4, paid_from date, paid_to date, status INT4);

są trzymane faktury użytkowników. każda faktura jest na 30 dni. musimy wstawić każdemu użytkownikowi tyle faktur by obejmowały cały okres od daty rejestracji użytkownika do chwili obecnej.
przykładowo:
jeśli mielibyśmy użytkownika który zarejestrował się 2006-12-01 (a dziś jest 2007-01-11) to musielibyśmy wygenerować dla niego 2 faktury:

  1. od 2006-12-01 do 2006-12-30
  2. od 2007-12-31 do 2007-01-29

dalszych nie, bo musimy zakończyć faktury na fakturze "aktualnej".
pierwsze co potrzebujemy to wiedzieć ile maksymalnie nam się może faktur wygenerować dla pojedynczego użytkownika. w naszym przypadku sprawdzamy to poprzez:

select ceil((now()::date - min(registered))/30.0) from users;

u mnie pokazało się 83.
ponieważ lubię proste liczby, zaokrąglę sobie to do 100. nie ma żadnego matematycznego uzasadnienia – po prostu tak wolę.
tak więc. to co nam się przyda to generate_series(1,100), a dokładniej generate_series(0,100).
pierwsze zapytanie które napiszemy zwróci nam dla danego użytkownika (na razie tylko jednego) listę 101 faktur (od 0 do 100):

SELECT u.registered + (x.i * 30), u.registered + (x.i*30) + 29 FROM users u, generate_series(0, 100) as x(i) WHERE u.id = 1;

zwracam uwagę na to, że nie mamy żadnego warunku na połączenie users i generate_series – nie jest to join, ale tzw. kartezjan.
jak widać – zapytanie zadziałało.
teraz musimy je ograniczyć, by zwróciło dane tylko do aktualnych:

SELECT
    u.registered + (x.i * 30), u.registered + (x.i*30) + 29
FROM
    users u, generate_series(0, 100) as x(i)
WHERE
    u.id = 1
    AND now()::date >= u.registered + (x.i * 30)
;

wow. działa.
to teraz. ponieważ mamy do czynienia z kartezjanem a nie joinem, to usunięcie "where u.id = 1" nie spowoduje uszkodzenia danych, tylko wypełnienie ich dla wszystkich userów.
dzięki czemu możemy zapisać finalne zapytanie w postaci:

INSERT INTO invoices (user_id, paid_from, paid_to, status)
SELECT
    u.id,
    u.registered + (x.i * 30),
    u.registered + (x.i*30) + 29,
    0
FROM
    users u, generate_series(0, 100) as x(i)
WHERE
    now()::date >= u.registered + (x.i * 30)
;

czy na pewno zadziałało dobrze?
zobaczmy:

SELECT
    u.id,
    u.registered,
    ( SELECT count(*) FROM invoices i WHERE i.user_id = u.id ) as invoice_count,
    i1.paid_from as invoice1_from,
    i1.paid_to as invoice1_to,
    i2.paid_from as invoice2_from,
    i2.paid_to as invoice2_to,
    now()::date as "now"
FROM
    users u,
    invoices i1,
    invoices i2
WHERE
    i1.id = (SELECT min(t.id) FROM invoices t WHERE t.user_id = u.id)
    AND
    i2.id = (SELECT max(t.id) FROM invoices t WHERE t.user_id = u.id)
;

u mnie wszystko zadziałało ok.
jak widać (mam nadzieję) – generate_series potrafi sporo. generowanie list liczb pozwala na robienie np. robienie zestawień, włącznie z wyświetlaniem sum danych nieistniejących, sprawdzanie braków, "zgadywanie" pewnych wartości.
pozostaje zachęcić was do testowania i bawienia się – to czym i jak się bawicie jest niejednokrotnie ważniejsze od finalnego efektu – efekt jest jednorazowy. zabawa owocuje wiedzą która zostaje i kiedyś sie przyda.
aha. ważna notatka – generate_series zostało dodane w 8.0. no ale przecież chyba nikt już nie używa baz starszych 🙂 (żartuję oczywiście)

  1. 3 comments

  2. # Acid
    Jan 12, 2007

    chyba zacznę czytać manuala do poduszki :]

  3. # Marcin Stępnicki
    Jan 17, 2007

    A ja mam produkcyjne 7.1 na którym chodzą aplikacje do których zgubiono kod źródłowy (nie pytajcie…), wiem co trzeba poprawić (głównie rzutowania) i nie mogę. Szlag mnie trafia.

  4. Jan 18, 2007

    znaczy się, nagrzeszyłeś w poprzednim życiu 🙂

Sorry, comments for this post are disabled.