Generowanie danych, a właściwie serii w PostgreSQLu

Pracując z bazami danych przy generowaniu dużej i różnej ilości raportów, musieliście się kiedyś spotkać z funkcją generate_series, dzięki której np. numerowaliście rekordy. Ja używałam tej funkcji także do wytwarzania danych testowych. W tym poście przedstawię wam kilka przypadków, dla których ta funkcja stała się dla mnie bardzo pomocna. Zacznijmy od jej definicji.

Funkcja przyjmuje 2 lub 3 argumenty, które są liczbami lub datą. Trzeci argument (step) nie jest wymagany dla wartości typu biginteger (domyślnie jest równy 1), ale jest konieczny gdy chcemy stworzyć serie dla timestapów:

generate_series(start, stop, step)

Sprawdźmy kilka podstawowych przypadków:

test=# select generate_series(1,5) as d;
 d 
---
 1
 2
 3
 4
 5
(5 rows)

test=# select generate_series(5,1,-1) as d;
 d 
---
 5
 4
 3
 2
 1
(5 rows)

test=# select generate_series('2010-03-01'::timestamp, '2010-03-05', '24 hours');
   generate_series   
---------------------
 2010-03-01 00:00:00
 2010-03-02 00:00:00
 2010-03-03 00:00:00
 2010-03-04 00:00:00
 2010-03-05 00:00:00
(5 rows)

test=# select generate_series(1,2) as id , date(generate_series('2010-03-01'::timestamp, '2010-03-05', '24 hours'));
 id |    date    
----+------------
  1 | 2010-03-01
  2 | 2010-03-02
  1 | 2010-03-03
  2 | 2010-03-04
  1 | 2010-03-05
  2 | 2010-03-01
  1 | 2010-03-02
  2 | 2010-03-03
  1 | 2010-03-04
  2 | 2010-03-05
(10 rows)

Funkcja jest także bardzo pomocna przy generowaniu danych testowych. Wystarczy, że użyjemy razem z nią funkcji random:

test=# select r from (select generate_series(1,5) as id, round(random()*100) as r) as foo;
 r  
----
 76
 93
 91
 68
 34
(5 rows)

Generowanie kolejno zdefiniowanych ciągu znaków:

test=# SELECT myarray[i] as element
 FROM  (SELECT ARRAY['one','two','tree','four','five','six'] As myarray) as foo
  CROSS JOIN generate_series(1, 6) As i;
 element 
---------
 one
 two
 tree
 four
 five
 six
(6 rows)

A co z tekstami? Jeśli wystarczy, że będą to ciągi znaków w pełni losowych, to możemy użyć do tego jeszcze funkcji substring i md5:

test=# select c2 from (select generate_series(1,10) as id, substring(md5(random()::text) from 0 for 20) as c2) foo;
         c2          
---------------------
 81b7985f41be65f56cb
 dd516dc4e04d39b3356
 b40721cfc0738bf98ed
 5ca0de341103fbb87de
 2e8b3a407257743261f
 7e45b2df06fb93d0821
 cbec578dcd1a86366fb
 83fecb8953dc5b07813
 6317b12d53e5da59d87
 ad35dc89a0772e49684
(10 rows)

Przejdźmy jednak do konkretnego przypadku raportu, który potrzebuję wygenerować. W tabeli table1 trzymam informacje o klientach, którzy pojawili się w danym czasie (kolumna: appeared_at, typ timestamp). Ja jednak potrzebuję wiedzieć tylko ile było takich osób danego dnia. Raport wymaga aby policzyć klientów dla wszystkich dni (więc dla dni, gdzie klienci się nie pojawili potrzebuję wyświetlić 0). Dlatego potrzebuję użyć RIGHT JOINa z tabelką, która jest serią podstawowych danych o datach z wybranego przedziału i zwykłym iterowaniem (dla lepszego pokazaniu wyniku).

test=# SELECT seq.id, seq.date, concat(data.count,0) 
FROM (
  SELECT date(appeared_at) AS date, count(*) 
  FROM table1 
  WHERE appeared_at BETWEEN '2010-03-01' AND '2010-03-10' 
  GROUP BY DATE(appeared_at)
) data 
RIGHT JOIN (
  SELECT generate_series(1,10) as id, DATE(generate_series('2010-03-01'::timestamp, '2010-03-10', '24 hours')) AS date
) seq 
ON seq.date = data.date 
ORDER BY seq.id;
 id |    date    | concat 
----+------------+--------
  1 | 2010-03-01 | 20400
  2 | 2010-03-02 | 12330
  3 | 2010-03-03 | 29770
  4 | 2010-03-04 | 15500
  5 | 2010-03-05 | 15170
  6 | 2010-03-06 | 22150
  7 | 2010-03-07 | 140620
  8 | 2010-03-08 | 106960
  9 | 2010-03-09 | 34480
 10 | 2010-03-10 | 0
(10 rows)

Proste, prawda? :-)