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:
- od 2006-12-01 do 2006-12-30
- 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)
