czwartek, 16 kwietnia 2015

Zapytania ze słowem WITH - zapytania CTE w PostgreSQL


Coś ostatnie kilka miesięcy za bardzo skupiłam się na MySQL. Pora to zmienić i napisać coś o PostgreSQL. Chciała bym się z Wami podzielić informacją na temat zapytań ze słowem kluczowym WITH czyli zapytań CTEs (Common Table Expressions, link do dokumentacji znajdziecie tutaj).

Zobaczmy jakiś przykład, aby zobrazować sobie problem, z którym mamy się zmierzyć. W tabeli table1 przechowuje informację o numerach telefonów, ich czasu pojawienia się i zweryfikowania, do jakiego areacodu, operatora i jakiej grupy należą. Ja na to miast potrzebuje wyciągnąć informację o 10 numerach telefonu, które zostały zweryfikowane najwcześniej, ale dla areacodów zweryfikowanych najpóźniej. Może mały realny przypadek, no ale chyba mi wybaczycie :-P

test=# WITH last10_areacode AS (
   select max(verified_at), areacode 
   from table1 
   group by areacode 
   order by 1 DESC 
   limit 10),
top_mobile_number AS (
   select min(verified_at) as min_verified_at, mobile_number 
   from table1 
   where areacode in (select areacode from last10_areacode) 
   group by mobile_number 
   order by 1 ASC
   limit 10)
select template_id as tid,template_info_id as tinfoid,mobile_number,appeared_at,verified_at,external_operator_id,areacode 
from table1 
where verified_at IN (select min_verified_at from top_mobile_number) 
limit 10;
 tid | tinfoid | mobile_number |     appeared_at     |     verified_at     | operator | areacode 
-----+---------+---------------+---------------------+---------------------+----------+----------
 239 |      10 | 18017255282   | 2007-07-13 19:55:09 | 2007-07-13 19:55:09 |    31002 | 801
  91 |      10 | 18012057665   | 2007-04-06 02:20:27 | 2007-04-06 02:20:27 |    31005 | 801
  91 |      10 | 17087032052   | 2007-04-06 18:16:04 | 2007-04-06 18:16:04 |    31002 | 708
  91 |      10 | 17086465706   | 2007-04-04 02:13:34 | 2007-04-04 02:13:34 |    31005 | 708
  91 |      10 | 17082885755   | 2007-04-11 16:18:08 | 2007-04-11 16:18:08 |    31002 | 708
  91 |      10 | 12144035942   | 2007-07-14 18:30:24 | 2007-07-14 18:30:24 |    31005 | 214
 138 |      10 | 19373612169   | 2007-04-16 16:23:25 | 2007-04-16 16:23:25 |    31005 | 937
 138 |      10 | 14059244193   | 2007-04-18 02:13:48 | 2007-04-18 02:13:48 |    31005 | 405
 138 |      10 | 16193475127   | 2007-04-23 19:31:51 | 2007-04-23 19:31:51 |    31005 | 619
  91 |      10 | 12147144210   | 2007-07-24 23:59:08 | 2007-07-24 23:59:08 |    31002 | 214
(10 rows)
Dzięki słowu WITH tworzymy zapytanie pomocnicze, którego wynik jest zapisywany do tabeli tymczasowej, istniejącej tylko i wyłącznie na czas działania całego zapytania. Jeśli dobrze zauważyliście, to już wiecie, że wynik zapytania last10_areacode został użyty do wygenerowania wyniku dla tabeli top_mobile_number, a ten wynik dla głównego zapytania. Idźmy jednak dalej.

 Zapytania RECURSIVE (ale tak na prawdę iteracyjne!)

Razem ze słowem WITH możemy użyć RECURSIVE. Dzięki temu będziemy mieli możliwość odwołania się do własnego wyniku.

Aby wytłumaczyć o co mi chodzi, stworzę prostą tabelę, zawierającą informację o kategoriach. Kategoria może być główną (kolumna parent jest pusta) lub podkategorią. Ilość poziomów podkategorii jest dowolna.

test=# create table category (id serial, name varchar(255), parent integer, created_at timestamp);
CREATE TABLE
test=# \d category
                                     Table "public.category"
   Column   |            Type             |                       Modifiers                       
------------+-----------------------------+-------------------------------------------------------
 id         | integer                     | not null default nextval('category_id_seq'::regclass)
 name       | character varying(255)      | 
 parent     | integer                     | 
 created_at | timestamp without time zone | 

test=# select * from category;
 id |      name      | parent |         created_at         
----+----------------+--------+----------------------------
  1 | top category 1 |        | 2015-04-14 18:31:32.312868
  2 | top category 2 |        | 2015-04-14 18:31:37.280778
  3 | top category 3 |        | 2015-04-14 18:31:40.952949
  4 | top category 4 |        | 2015-04-14 18:31:45.809776
  5 | top category 5 |        | 2015-04-14 18:31:49.808637
  6 | category 6     |      2 | 2015-04-14 18:32:30.0089
  7 | category 7     |      2 | 2015-04-14 18:32:39.504864
  8 | category 8     |      2 | 2015-04-14 18:32:43.632998
  9 | category 9     |      4 | 2015-04-14 18:32:59.016916
 10 | category 10    |      6 | 2015-04-14 18:33:36.224982
 11 | category 11    |      7 | 2015-04-14 18:34:05.088904
 12 | category 12    |     11 | 2015-04-14 18:35:00.69685
(12 rows)

Aby wybrać rekordy, które dotyczą tylko i wyłącznie kategorii głównej o id = 2 i jej dzieci, stworzyłam następujące zapytanie:

test=# WITH RECURSIVE get_parents_category(id, name, parent) AS (
  SELECT id, name, parent FROM category WHERE parent = 2 OR id = 2
UNION    
  SELECT c.id, c.name, c.parent 
  FROM get_parents_category gpc, category c 
  WHERE gpc.id = c.parent)
SELECT * FROM get_parents_category;
 id |      name      | parent 
----+----------------+--------
  2 | top category 2 |       
  6 | category 6     |      2
  7 | category 7     |      2
  8 | category 8     |      2
 10 | category 10    |      6
 11 | category 11    |      7
 12 | category 12    |     11
(7 rows)

Aby wytłumaczyć co się dzieję w tej konstrukcji zapytania, po patrzcie na poniższy psełdo kod:

WITH RECURSIVE RECURSIVE_QUERY_NAME(ARG1, ARG2) AS (
  START_QUERY
UNION
  LOOP_QUERY
)
MAIN_QUERY;

Aby zwrócić do MAIN_QUERY (to wywołanie RECURSIVE_QUERY_NAME) wynik działania, PostgreSQL wykonuje następujące kroki:
  1. Wywołanie zapytanie START_QUERY, a wynik zapisywany jest w tabeli pracującej (working table) i do zapytania LOOP_QUERY.
  2. Dopóki w tabeli pracującej (working table) są jakieś rekordy, powtarzane są czynności:
    1. Wywołanie zapytania LOOP_QUERY. Wynik jest zapisywany i w zależności od typu złączenia UNION [ALL], rekordy zduplikowane są usuwane lub nie. Wszystkie wiersze zostają wstawione jako wynik działania LOOP_QUERY oraz tymczasowej tabeli intermediate table.
    2. Zamiana zawartości tabeli pracującej z zawartością tabeli intermediate, a następnie opróżnienie tabeli intermediate. 
Zapytanie LOOP_QUERY jest wykonywane tyle razy, ile będzie zwracać jakiś wynik. W MAIN_QUERY możemy dodatkowo łączyć inne tabele lub po prostu filtrować wyniki.

 Modyfikacja danych

PostgreSQL daje nam także możliwość modyfikacji danych (INSERT, UPDATE lub DELETE) przy użyciu słowa kluczowego WITH.

Nie ma możliwości użycia polecenia modyfikującego w zapytaniu RECURSIVE, ale możemy zwrócić rekordy poszukiwane do tej operacji.

Spróbuję usunąć wszystkie wiersze z tabeli category, które dotyczą głównej kategorii o id=2. Zacznijmy od sprawdzenia zawartości całej tabeli:

test=# select * from category;
 id |      name      | parent |         created_at         
----+----------------+--------+----------------------------
  1 | top category 1 |        | 2015-04-14 18:31:32.312868
  2 | top category 2 |        | 2015-04-14 18:31:37.280778
  3 | top category 3 |        | 2015-04-14 18:31:40.952949
  4 | top category 4 |        | 2015-04-14 18:31:45.809776
  5 | top category 5 |        | 2015-04-14 18:31:49.808637
  6 | category 6     |      2 | 2015-04-14 18:32:30.0089
  7 | category 7     |      2 | 2015-04-14 18:32:39.504864
  8 | category 8     |      2 | 2015-04-14 18:32:43.632998
  9 | category 9     |      4 | 2015-04-14 18:32:59.016916
 10 | category 10    |      6 | 2015-04-14 18:33:36.224982
 11 | category 11    |      7 | 2015-04-14 18:34:05.088904
 12 | category 12    |     11 | 2015-04-14 18:35:00.69685
(12 rows)

test=# WITH RECURSIVE delete_old_category(id) AS (
   SELECT id FROM category WHERE parent = 2 OR id = 2
UNION
   SELECT c.id FROM delete_old_category foo, category c WHERE foo.id = c.parent)
DELETE FROM category WHERE id IN (SELECT id FROM delete_old_category);
DELETE 7

test=# select * from category;
 id |      name      | parent |         created_at         
----+----------------+--------+----------------------------
  1 | top category 1 |        | 2015-04-14 18:31:32.312868
  3 | top category 3 |        | 2015-04-14 18:31:40.952949
  4 | top category 4 |        | 2015-04-14 18:31:45.809776
  5 | top category 5 |        | 2015-04-14 18:31:49.808637
  9 | category 9     |      4 | 2015-04-14 18:32:59.016916
(5 rows)
Zapytanie usuwające stare kategorie jest bardzo podobne do powyższego SELECTa, bo wyciągnięte dane z zapytania RECURSIVE, są użyte w zapytaniu głównym do usunięcia interesujących nas rekordów po kluczu głównym.

Czasami jednak przydało by się zapisać gdzieś usunięte rekordy do jakiś logów (w końcu może jednak w przyszłości się okazać, że były to dość istotne informacje). W takim przypadku PostgreSQL także przyjdzie nam z pomocą w jednym zapytaniu.

test=# select * from table1 where areacode = '654';
 template_id | template_info_id | mobile_number |     appeared_at     |     verified_at     |  alias_name  |     created_at      |     updated_at      | fake_id | external_operator_id | areacode 
-------------+------------------+---------------+---------------------+---------------------+--------------+---------------------+---------------------+---------+----------------------+----------
        4019 |               10 | 16549684775   | 2010-03-11 07:23:53 | 2010-03-11 07:23:53 | OTHERS       | 2011-03-01 20:16:32 | 2011-03-01 20:16:32 | 1500323 |                31002 | 654
        3687 |               10 | 16543245678   | 2010-04-05 20:25:08 | 2010-04-05 20:25:08 | AIRBENDERVID | 2011-03-01 21:15:02 | 2011-03-01 21:15:02 | 2140545 |                    0 | 654
        2977 |               10 | 16546547654   | 2009-10-12 00:06:20 | 2009-10-12 00:06:20 | PARAVID      | 2011-03-01 19:17:56 | 2011-03-01 19:17:56 | 2396877 |                    0 | 654
(3 rows)

test=# 
  WITH move_rows AS (
    DELETE FROM table1 where areacode = '654'
    RETURNING * )
  INSERT INTO table1_log
  SELECT * FROM move_rows;
INSERT 0 3

test=# select * from table1_log;
 template_id | template_info_id | mobile_number |     appeared_at     |     verified_at     |  alias_name  |     created_at      |     updated_at      | fake_id | external_operator_id | areacode 
-------------+------------------+---------------+---------------------+---------------------+--------------+---------------------+---------------------+---------+----------------------+----------
        4019 |               10 | 16549684775   | 2010-03-11 07:23:53 | 2010-03-11 07:23:53 | OTHERS       | 2011-03-01 20:16:32 | 2011-03-01 20:16:32 | 1500323 |                31002 | 654
        3687 |               10 | 16543245678   | 2010-04-05 20:25:08 | 2010-04-05 20:25:08 | AIRBENDERVID | 2011-03-01 21:15:02 | 2011-03-01 21:15:02 | 2140545 |                    0 | 654
        2977 |               10 | 16546547654   | 2009-10-12 00:06:20 | 2009-10-12 00:06:20 | PARAVID      | 2011-03-01 19:17:56 | 2011-03-01 19:17:56 | 2396877 |                    0 | 654
(3 rows)

W zapytaniu ze słowem kluczowym WITH usuwane są rekordy z tabeli table1, a przy pomocy słowa RETURNING, są zwracane do zapytania głównego. Tam są wstawiane do tabeli table1_log.

Szkoda, że MySQL nie wspiera tego typu zapytań. Przydałyby mi się w pracy :-P

Autor: o 1 komentarz:
Etykiety: , , , , , , , ,

poniedziałek, 6 kwietnia 2015

Enum type w MySQLu

Jeśli kiedykolwiek używałeś w swojej bazie danych MySQL kolumn z typem ENUM, być może zauważyłeś kilka niemiłych rzeczy, których bez czytania dokumentacji nie spodziewałbyś się zobaczyć.

Przypuśćmy, że mamy następującą tak zdefiniowaną tabelę, z kolumnami typu enum:

mysql> show create table test_enum \G
*************************** 1. row ***************************
       Table: test_enum
Create Table: CREATE TABLE `test_enum` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `e1` enum('Y','F','') DEFAULT NULL,
  `e2` enum('Y','F') NOT NULL DEFAULT 'Y',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
Wstawmy kilka wierszy:

mysql> insert into test_enum (e1,e2) VALUES ('Y','Y'),('F','F'),('',''),(NULL,NULL),('N','N');
Query OK, 5 rows affected, 4 warnings (0.06 sec)
Records: 5  Duplicates: 0  Warnings: 4

mysql> show warnings;
+---------+------+-----------------------------------------+
| Level   | Code | Message                                 |
+---------+------+-----------------------------------------+
| Warning | 1265 | Data truncated for column 'e2' at row 3 |
| Warning | 1048 | Column 'e2' cannot be null              |
| Warning | 1265 | Data truncated for column 'e1' at row 5 |
| Warning | 1265 | Data truncated for column 'e2' at row 5 |
+---------+------+-----------------------------------------+
4 rows in set (0.00 sec)

mysql> select * from test_enum;
+----+------+----+
| id | e1   | e2 |
+----+------+----+
|  1 | Y    | Y  |
|  2 | F    | F  |
|  3 |      |    |
|  4 | NULL |    |
|  5 |      |    |
+----+------+----+
5 rows in set (0.00 sec)
Zauważyliście coś ciekawego? Dwa pierwsze wpisy raczej nikogo nie dziwią - w końcu są poprawne. Jednak gdy przyjrzymy się trzem następnym, zauważymy że:
  • insert stringa pustego '' do kolumny e2 powoduje wstawienie tej właśnie wartości, mimo iż nie ma jej zdefiniowanej jako wartość dozwolona,
  • insert NULLa do kolumny e2 powoduje wstawienie pustego stringa mimo iż mamy zdefiniowaną wartość domyślną,
  • insert wartości niepoprawnych powodują ustawienie na puste stringi.

Zanim omówię powyższe 'niespodzianki' wstawmy jeszcze dwa rekordy:

mysql> insert into test_enum (e1) VALUES (NULL);
Query OK, 1 row affected (0.02 sec)

mysql> insert into test_enum (id) VALUES (7);
Query OK, 1 row affected (0.05 sec)

mysql> select * from test_enum where id > 5;
+----+------+----+
| id | e1   | e2 |
+----+------+----+
|  6 | NULL | Y  |
|  7 | NULL | Y  |
+----+------+----+
2 rows in set (0.00 sec)
Tym razem nasze dane są takie jakich byśmy się spodziewali.

Cechy typu ENUM, których być może się nie spodziewaliście:
  • Typ ENUM jest obiektem, który definiuje wartości dozwolone w formie stringów, ale tak na prawdę wartość zapisywana jest przy pomocy indexów typu integer, zaczynając od 1. Weźmy pod uwagę kolumnę e1 naszej tabeli test_enum. Wartości ('Y','F','') są zapisywane jako (1,2,3). Poniżej przedstawię kilka przykładów SELECTów.
  • Wartość 0 jest zarezerwowana dla pustego stringa - wartość błędu. 
  • Wartość NULL jest zapisywana jako NULL lub wartość błędu (jeśli kolumna jest zdefiniowana jako NOT NULL).
  • Jeśli zdfiniujemy enum jako ciagi znaków numerycznych, to wartości wstawiane do takiej kolumny są traktowane jako indexy dopuszczalnych wartości (zobacz poniższy przykład 2, dla tablicy test_enum2).

Przykład 1: Przykłady wyszukiwania wierszy po wartościach lub indexach dopuszczalnych wartości:

mysql> SELECT * FROM test_enum WHERE e1 = '';
+----+------+----+
| id | e1   | e2 |
+----+------+----+
|  3 |      |    |
|  5 |      |    |
+----+------+----+
2 rows in set (0.00 sec)

mysql> SELECT * FROM test_enum WHERE e1 = 0;
+----+------+----+
| id | e1   | e2 |
+----+------+----+
|  5 |      |    |
+----+------+----+
1 row in set (0.00 sec)

mysql> SELECT * FROM test_enum WHERE e1 = 3;
+----+------+----+
| id | e1   | e2 |
+----+------+----+
|  3 |      |    |
+----+------+----+
1 row in set (0.00 sec)

Przykład 2: Inny przykład tablicy z columną typu enum:
mysql> show create table test_enum2 \G
*************************** 1. row ***************************
       Table: test_enum2
Create Table: CREATE TABLE `test_enum2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `e1` enum('0','1','2','3') DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> INSERT INTO test_enum2 (e1) VALUES (1),('1'),('4');
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from test_enum2;
+----+------+
| id | e1   |
+----+------+
|  1 | 0    |
|  2 | 1    |
|  3 | 3    |
+----+------+
3 rows in set (0.00 sec)

Przykład 3: Aby podejrzeć wartość string i jej index wystraczy:
mysql> select id, e1, e1+0 from test_enum2;
+----+------+------+
| id | e1   | e1+0 |
+----+------+------+
|  1 | 0    |    1 |
|  2 | 1    |    2 |
|  3 | 3    |    4 |
+----+------+------+
3 rows in set (0.00 sec)
Każdy nowy element wartości dopuszczalnej powinien być dopisany bez zmiany kolejności. W ten sposób nowa wartość będzie miała nadaną kolejną wartość indexu. W przeciwnym przypadku tablica będzieme musiała być przepisana aby starym wartościom nadać nowe indexy. Dla bardzo dużych tabel może być to czasochłonna opercja, a i chyba nie potrzebna.

Autor: o Brak komentarzy:
Etykiety: ,
Subskrybuj: Posty (Atom)