Diferència entre revisions de la pàgina «Consultes recursives: bikeshop»

De wikijoan
Salta a la navegació Salta a la cerca
(Es crea la pàgina amb «=Introducció= (TBD) =Base de dades bikeshop= Mirem la tala '''CATEGORIA''': <pre> select id_cat, categoria, id_parent from CATEGORIA +--------+----------------------...».)
 
 
(Hi ha 3 revisions intermèdies del mateix usuari que no es mostren)
Línia 1: Línia 1:
 +
__TOC__
 
=Introducció=
 
=Introducció=
−
(TBD)
+
Les taules recursives són les que tenen una clau forànea que apunta a ella mateixa. És molt típic per establir relacions de pares i fills. Però no només es limiten a dos nivells (pares i fills), sinó que poden créixer fins a tenir un arbre amb moltes branques i subbranques.
 +
 
 +
Per exemple, les pots trobar en el CMS Wordpress, per definir les categories i subcategories (que poden tenir més nivells).
 +
 
 +
En les nostres bases de dades de treball les trobem a ''bikeshop.CATEGORIA'' i ''HR.employees''.
 +
 
 
=Base de dades bikeshop=
 
=Base de dades bikeshop=
 
Mirem la tala '''CATEGORIA''':
 
Mirem la tala '''CATEGORIA''':
Línia 46: Línia 52:
 
|    17 | Cuadros de Gravel                            |        15 |    15 | Cuadros                          |
 
|    17 | Cuadros de Gravel                            |        15 |    15 | Cuadros                          |
 
|    18 | Horquillas                                    |        15 |    15 | Cuadros                          |
 
|    18 | Horquillas                                    |        15 |    15 | Cuadros                          |
 +
...
 +
 
168 files
 
168 files
 
</pre>
 
</pre>
−
Amb un ''LEFT OUTER JOIN'' tornem a recuperar els 173 valors (les categories que no tenen pare)ç
+
Hem perdut 5 categories. Amb un ''LEFT OUTER JOIN'' tornem a recuperar els 173 valors (les categories que no tenen pare)
 
<pre>
 
<pre>
 
select C2.categoria cat_pare, C1.categoria cat_fill from CATEGORIA C1
 
select C2.categoria cat_pare, C1.categoria cat_fill from CATEGORIA C1
Línia 67: Línia 75:
 
| Bicicletas                        | Bicicletas de Ciclocross                      |
 
| Bicicletas                        | Bicicletas de Ciclocross                      |
 
| Bicicletas                        | Bicicletas Contrarreloj                       
 
| Bicicletas                        | Bicicletas Contrarreloj                       
 +
...
  
 
173 files
 
173 files
Línia 89: Línia 98:
 
| Transmisión  | Pedales                              |
 
| Transmisión  | Pedales                              |
 
| Transmisión  | Transmisión eléctrica                |
 
| Transmisión  | Transmisión eléctrica                |
 +
...
 +
 
33 files
 
33 files
 
</pre>
 
</pre>
Línia 123: Línia 134:
 
|    13 | Bicicletas Niños                              |        6 |      6 | Bicicletas                        |      1 | Carretera    |
 
|    13 | Bicicletas Niños                              |        6 |      6 | Bicicletas                        |      1 | Carretera    |
 
|    14 | Bicicletas eléctricas                        |        6 |      6 | Bicicletas                        |      1 | Carretera    |
 
|    14 | Bicicletas eléctricas                        |        6 |      6 | Bicicletas                        |      1 | Carretera    |
−
</pre>
+
...
 +
 
 +
173 files</pre>
  
 
<pre>
 
<pre>
Línia 143: Línia 156:
 
+---------+--------------+---------------------------------------+
 
+---------+--------------+---------------------------------------+
 
</pre>
 
</pre>
 +
=Base de dades HR. Taula employees=
 +
En la base de dades '''HR''' tenim la taula '''employees'' on també tenim una informació recursiva: d'un empleat sabem qui és el seu ''jefe'', que a l'hora pot ser empleat d'un altre jefe.
 +
<pre>
 +
mysql> DESC employees;
 +
+---------------+--------------+------+-----+---------+----------------+
 +
| Field        | Type        | Null | Key | Default | Extra          |
 +
+---------------+--------------+------+-----+---------+----------------+
 +
| employee_id  | int          | NO  | PRI | NULL    | auto_increment |
 +
| first_name    | varchar(20)  | YES  |    | NULL    |                |
 +
| last_name    | varchar(25)  | NO  |    | NULL    |                |
 +
...
 +
| manager_id    | int          | YES  | MUL | NULL    |                |
 +
+---------------+--------------+------+-----+---------+---------------
 +
</pre>
 +
<pre>
 +
select employee_id, CONCAT(first_name,' ',last_name), manager_id from employees;
 +
 +
+-------------+----------------------------------+------------+
 +
| employee_id | CONCAT(first_name,' ',last_name) | manager_id |
 +
+-------------+----------------------------------+------------+
 +
|        100 | Steven King                      |      NULL |
 +
|        101 | Neena Kochhar                    |        100 |
 +
|        102 | Lex De Haan                      |        100 |
 +
|        103 | Alexander Hunold                |        102 |
 +
|        104 | Bruce Ernst                      |        103 |
 +
|        105 | David Austin                    |        103 |
 +
|        106 | Valli Pataballa                  |        103 |
 +
|        107 | Diana Lorentz                    |        103 |
 +
...
 +
 +
40 files
 +
</pre>
 +
Igual que abans, podem mirar la llista d'empleats i ''jefes'':
 +
<pre>
 +
select e1.employee_id, CONCAT(e1.first_name,' ',e1.last_name) empleat, e1.manager_id, e2.employee_id, CONCAT(e2.first_name,' ',e2.last_name) jefe from employees e1
 +
LEFT OUTER JOIN employees e2
 +
ON e1.manager_id=e2.employee_id;
 +
 +
+-------------+-------------------+------------+-------------+-------------------+
 +
| employee_id | empleat          | manager_id | employee_id | jefe              |
 +
+-------------+-------------------+------------+-------------+-------------------+
 +
|        100 | Steven King      |      NULL |        NULL | NULL              |
 +
|        101 | Neena Kochhar    |        100 |        100 | Steven King      |
 +
|        102 | Lex De Haan      |        100 |        100 | Steven King      |
 +
|        103 | Alexander Hunold  |        102 |        102 | Lex De Haan      |
 +
|        104 | Bruce Ernst      |        103 |        103 | Alexander Hunold  |
 +
|        105 | David Austin      |        103 |        103 | Alexander Hunold  |
 +
|        106 | Valli Pataballa  |        103 |        103 | Alexander Hunold  |
 +
|        107 | Diana Lorentz    |        103 |        103 | Alexander Hunold  |
 +
|        108 | Nancy Greenberg  |        101 |        101 | Neena Kochhar    |
 +
...
 +
 +
40 files
 +
</pre>
 +
Quins són els subordinats del Alexander Hunold?
 +
<pre>
 +
select e1.employee_id, CONCAT(e1.first_name,' ',e1.last_name) empleat from employees e1
 +
LEFT OUTER JOIN employees e2
 +
ON e1.manager_id=e2.employee_id
 +
WHERE CONCAT(e2.first_name,' ',e2.last_name)='Alexander Hunold';
 +
 +
+-------------+-----------------+
 +
| employee_id | empleat        |
 +
+-------------+-----------------+
 +
|        104 | Bruce Ernst    |
 +
|        105 | David Austin    |
 +
|        106 | Valli Pataballa |
 +
|        107 | Diana Lorentz  |
 +
+-------------+-----------------+
 +
4 rows in set (0,00 sec)
 +
</pre>
 +
 +
Igual que abans, podem anar a visualitzar 3 nivells:
 +
<pre>
 +
select CONCAT(e3.first_name,' ',e3.last_name) superjefe, CONCAT(e2.first_name,' ',e2.last_name) jefe, CONCAT(e1.first_name,' ',e1.last_name) empleat from
 +
((employees e1 LEFT OUTER JOIN employees e2 ON e1.manager_id=e2.employee_id)
 +
LEFT OUTER JOIN employees e3 ON e2.manager_id=e3.employee_id);
 +
</pre>
 +
Informe de tothom que penja de ''Lex De Haan'' (aquesta persona pot actuar com a ''jefe'' o com a ''superjefe''):
 +
<pre>
 +
select CONCAT(e3.first_name,' ',e3.last_name) superjefe, CONCAT(e2.first_name,' ',e2.last_name) jefe, CONCAT(e1.first_name,' ',e1.last_name) empleat from
 +
((employees e1 LEFT OUTER JOIN employees e2 ON e1.manager_id=e2.employee_id)
 +
LEFT OUTER JOIN employees e3 ON e2.manager_id=e3.employee_id)
 +
WHERE CONCAT(e2.first_name,' ',e2.last_name)='Lex De Haan' OR CONCAT(e3.first_name,' ',e3.last_name)='Lex De Haan';
  
 +
+-------------+------------------+------------------+
 +
| superjefe  | jefe            | empleat          |
 +
+-------------+------------------+------------------+
 +
| Steven King | Lex De Haan      | Alexander Hunold |
 +
| Lex De Haan | Alexander Hunold | Bruce Ernst      |
 +
| Lex De Haan | Alexander Hunold | David Austin    |
 +
| Lex De Haan | Alexander Hunold | Valli Pataballa  |
 +
| Lex De Haan | Alexander Hunold | Diana Lorentz    |
 +
+-------------+------------------+------------------+
 +
5 rows in set (0,00 sec)
 +
</pre>
 
{{Autor}}, febrer 2022
 
{{Autor}}, febrer 2022

Revisió de 08:27, 7 feb 2022

Introducció

Les taules recursives són les que tenen una clau forànea que apunta a ella mateixa. És molt típic per establir relacions de pares i fills. Però no només es limiten a dos nivells (pares i fills), sinó que poden créixer fins a tenir un arbre amb moltes branques i subbranques.

Per exemple, les pots trobar en el CMS Wordpress, per definir les categories i subcategories (que poden tenir més nivells).

En les nostres bases de dades de treball les trobem a bikeshop.CATEGORIA i HR.employees.

Base de dades bikeshop

Mirem la tala CATEGORIA: 

select id_cat, categoria, id_parent from CATEGORIA

+--------+-----------------------------------------------+-----------+
| id_cat | categoria                                     | id_parent |
+--------+-----------------------------------------------+-----------+
|      1 | Carretera                                     |      NULL |
|      2 | MTB                                           |      NULL |
|      3 | E-BIKE                                        |      NULL |
|      4 | BMX                                           |      NULL |
|      5 | CIUDAD                                        |      NULL |
|      6 | Bicicletas                                    |         1 |
|      7 | Bicicletas de Carrera                         |         6 |
|      8 | Bicicletas de Gravel                          |         6 |
|      9 | Bicicletas de Ciclocross                      |         6 |
|     10 | Bicicletas Contrarreloj                       |         6 |
|     11 | Bicicletas de pista                           |         6 |
...

173 files

Volem visualitzar 2 nivells: 

select C1.id_cat, C1.categoria, C1.id_parent, C2.id_cat, C2.categoria from CATEGORIA C1
INNER JOIN CATEGORIA C2
ON C1.id_parent=C2.id_cat;

+--------+-----------------------------------------------+-----------+--------+-----------------------------------+
| id_cat | categoria                                     | id_parent | id_cat | categoria                         |
+--------+-----------------------------------------------+-----------+--------+-----------------------------------+
|      6 | Bicicletas                                    |         1 |      1 | Carretera                         |
|      7 | Bicicletas de Carrera                         |         6 |      6 | Bicicletas                        |
|      8 | Bicicletas de Gravel                          |         6 |      6 | Bicicletas                        |
|      9 | Bicicletas de Ciclocross                      |         6 |      6 | Bicicletas                        |
|     10 | Bicicletas Contrarreloj                       |         6 |      6 | Bicicletas                        |
|     11 | Bicicletas de pista                           |         6 |      6 | Bicicletas                        |
|     12 | Bicicletas para mujeres                       |         6 |      6 | Bicicletas                        |
|     13 | Bicicletas Niños                              |         6 |      6 | Bicicletas                        |
|     14 | Bicicletas eléctricas                         |         6 |      6 | Bicicletas                        |
|     15 | Cuadros                                       |         1 |      1 | Carretera                         |
|     16 | Cuadros de carretera                          |        15 |     15 | Cuadros                           |
|     17 | Cuadros de Gravel                             |        15 |     15 | Cuadros                           |
|     18 | Horquillas                                    |        15 |     15 | Cuadros                           |
...

168 files

Hem perdut 5 categories. Amb un LEFT OUTER JOIN tornem a recuperar els 173 valors (les categories que no tenen pare) 

select C2.categoria cat_pare, C1.categoria cat_fill from CATEGORIA C1
LEFT OUTER JOIN CATEGORIA C2
ON C1.id_parent=C2.id_cat;

+-----------------------------------+-----------------------------------------------+
| cat_pare                          | cat_fill                                      |
+-----------------------------------+-----------------------------------------------+
| NULL                              | Carretera                                     |
| NULL                              | MTB                                           |
| NULL                              | E-BIKE                                        |
| NULL                              | BMX                                           |
| NULL                              | CIUDAD                                        |
| Carretera                         | Bicicletas                                    |
| Bicicletas                        | Bicicletas de Carrera                         |
| Bicicletas                        | Bicicletas de Gravel                          |
| Bicicletas                        | Bicicletas de Ciclocross                      |
| Bicicletas                        | Bicicletas Contrarreloj                       
...

173 files

Volem filtrar el que penja de Bicicletas > CIUDAD > transmisión filtrem: 

select C2.categoria cat_pare, C1.categoria cat_fill from CATEGORIA C1
LEFT OUTER JOIN CATEGORIA C2
ON C1.id_parent=C2.id_cat
WHERE C2.categoria='Transmisión';

+--------------+---------------------------------------+
| cat_pare     | cat_fill                              |
+--------------+---------------------------------------+
| Transmisión  | Cables y fundas                       |
| Transmisión  | Cassettes y cadenas                   |
| Transmisión  | Cambios y desviadores                 |
| Transmisión  | Manetas                               |
| Transmisión  | Pedaliers                             |
| Transmisión  | Pedales                               |
| Transmisión  | Transmisión eléctrica                 |
...

33 files

Ho fem malament perquè la categoria Transmisión no és única.

33 files perquè hem trobat:

  • Bicicletas > carretera > Transmisión
  • Bicicletas > MTB > Transmisión
  • Bicicletas > BMX > BMX FreeStyle > Transmisión
  • Bicicletas > CIUDAD > Transmisión

Volem doncs ara visualitzar els 3 nivells 

select C1.id_cat, C1.categoria, C1.id_parent, C2.id_cat, C2.categoria, C3.id_cat, C3.categoria from 
((CATEGORIA C1 LEFT OUTER JOIN CATEGORIA C2 ON C1.id_parent=C2.id_cat)
LEFT OUTER JOIN CATEGORIA C3 ON C2.id_parent=C3.id_cat);

+--------+-----------------------------------------------+-----------+--------+-----------------------------------+--------+---------------+
| id_cat | categoria                                     | id_parent | id_cat | categoria                         | id_cat | categoria     |
+--------+-----------------------------------------------+-----------+--------+-----------------------------------+--------+---------------+
|      1 | Carretera                                     |      NULL |   NULL | NULL                              |   NULL | NULL          |
|      2 | MTB                                           |      NULL |   NULL | NULL                              |   NULL | NULL          |
|      3 | E-BIKE                                        |      NULL |   NULL | NULL                              |   NULL | NULL          |
|      4 | BMX                                           |      NULL |   NULL | NULL                              |   NULL | NULL          |
|      5 | CIUDAD                                        |      NULL |   NULL | NULL                              |   NULL | NULL          |
|      6 | Bicicletas                                    |         1 |      1 | Carretera                         |   NULL | NULL          |
|      7 | Bicicletas de Carrera                         |         6 |      6 | Bicicletas                        |      1 | Carretera     |
|      8 | Bicicletas de Gravel                          |         6 |      6 | Bicicletas                        |      1 | Carretera     |
|      9 | Bicicletas de Ciclocross                      |         6 |      6 | Bicicletas                        |      1 | Carretera     |
|     10 | Bicicletas Contrarreloj                       |         6 |      6 | Bicicletas                        |      1 | Carretera     |
|     11 | Bicicletas de pista                           |         6 |      6 | Bicicletas                        |      1 | Carretera     |
|     12 | Bicicletas para mujeres                       |         6 |      6 | Bicicletas                        |      1 | Carretera     |
|     13 | Bicicletas Niños                              |         6 |      6 | Bicicletas                        |      1 | Carretera     |
|     14 | Bicicletas eléctricas                         |         6 |      6 | Bicicletas                        |      1 | Carretera     |
...

173 files
select C3.categoria cat_avi, C2.categoria cat_pare, C1.categoria cat_fill from 
((CATEGORIA C1 LEFT OUTER JOIN CATEGORIA C2 ON C1.id_parent=C2.id_cat)
LEFT OUTER JOIN CATEGORIA C3 ON C2.id_parent=C3.id_cat)
WHERE C3.categoria='CIUDAD' AND C2.categoria='Transmisión';

+---------+--------------+---------------------------------------+
| cat_avi | cat_pare     | cat_fill                              |
+---------+--------------+---------------------------------------+
| CIUDAD  | Transmisión  | Cables y fundas                       |
| CIUDAD  | Transmisión  | Cubrecadenas                          |
| CIUDAD  | Transmisión  | Cassettes                             |
| CIUDAD  | Transmisión  | Cadenas                               |
| CIUDAD  | Transmisión  | Mandos de cambio                      |
| CIUDAD  | Transmisión  | Cambios                               |
| CIUDAD  | Transmisión  | Pedaliers, Cajas del pedalier, Platos |
+---------+--------------+---------------------------------------+

Base de dades HR. Taula employees

En la base de dades HR' tenim la taula employees on també tenim una informació recursiva: d'un empleat sabem qui és el seu jefe, que a l'hora pot ser empleat d'un altre jefe. 

mysql> DESC employees;
+---------------+--------------+------+-----+---------+----------------+
| Field         | Type         | Null | Key | Default | Extra          |
+---------------+--------------+------+-----+---------+----------------+
| employee_id   | int          | NO   | PRI | NULL    | auto_increment |
| first_name    | varchar(20)  | YES  |     | NULL    |                |
| last_name     | varchar(25)  | NO   |     | NULL    |                |
...
| manager_id    | int          | YES  | MUL | NULL    |                |
+---------------+--------------+------+-----+---------+---------------

select employee_id, CONCAT(first_name,' ',last_name), manager_id from employees;

+-------------+----------------------------------+------------+
| employee_id | CONCAT(first_name,' ',last_name) | manager_id |
+-------------+----------------------------------+------------+
|         100 | Steven King                      |       NULL |
|         101 | Neena Kochhar                    |        100 |
|         102 | Lex De Haan                      |        100 |
|         103 | Alexander Hunold                 |        102 |
|         104 | Bruce Ernst                      |        103 |
|         105 | David Austin                     |        103 |
|         106 | Valli Pataballa                  |        103 |
|         107 | Diana Lorentz                    |        103 |
...

40 files

Igual que abans, podem mirar la llista d'empleats i jefes: 

select e1.employee_id, CONCAT(e1.first_name,' ',e1.last_name) empleat, e1.manager_id, e2.employee_id, CONCAT(e2.first_name,' ',e2.last_name) jefe from employees e1
LEFT OUTER JOIN employees e2
ON e1.manager_id=e2.employee_id;

+-------------+-------------------+------------+-------------+-------------------+
| employee_id | empleat           | manager_id | employee_id | jefe              |
+-------------+-------------------+------------+-------------+-------------------+
|         100 | Steven King       |       NULL |        NULL | NULL              |
|         101 | Neena Kochhar     |        100 |         100 | Steven King       |
|         102 | Lex De Haan       |        100 |         100 | Steven King       |
|         103 | Alexander Hunold  |        102 |         102 | Lex De Haan       |
|         104 | Bruce Ernst       |        103 |         103 | Alexander Hunold  |
|         105 | David Austin      |        103 |         103 | Alexander Hunold  |
|         106 | Valli Pataballa   |        103 |         103 | Alexander Hunold  |
|         107 | Diana Lorentz     |        103 |         103 | Alexander Hunold  |
|         108 | Nancy Greenberg   |        101 |         101 | Neena Kochhar     |
...

40 files

Quins són els subordinats del Alexander Hunold? 

select e1.employee_id, CONCAT(e1.first_name,' ',e1.last_name) empleat from employees e1
LEFT OUTER JOIN employees e2
ON e1.manager_id=e2.employee_id
WHERE CONCAT(e2.first_name,' ',e2.last_name)='Alexander Hunold';

+-------------+-----------------+
| employee_id | empleat         |
+-------------+-----------------+
|         104 | Bruce Ernst     |
|         105 | David Austin    |
|         106 | Valli Pataballa |
|         107 | Diana Lorentz   |
+-------------+-----------------+
4 rows in set (0,00 sec)

Igual que abans, podem anar a visualitzar 3 nivells: 

select CONCAT(e3.first_name,' ',e3.last_name) superjefe, CONCAT(e2.first_name,' ',e2.last_name) jefe, CONCAT(e1.first_name,' ',e1.last_name) empleat from
((employees e1 LEFT OUTER JOIN employees e2 ON e1.manager_id=e2.employee_id)
LEFT OUTER JOIN employees e3 ON e2.manager_id=e3.employee_id);

Informe de tothom que penja de Lex De Haan (aquesta persona pot actuar com a jefe o com a superjefe): 

select CONCAT(e3.first_name,' ',e3.last_name) superjefe, CONCAT(e2.first_name,' ',e2.last_name) jefe, CONCAT(e1.first_name,' ',e1.last_name) empleat from
((employees e1 LEFT OUTER JOIN employees e2 ON e1.manager_id=e2.employee_id)
LEFT OUTER JOIN employees e3 ON e2.manager_id=e3.employee_id)
WHERE CONCAT(e2.first_name,' ',e2.last_name)='Lex De Haan' OR CONCAT(e3.first_name,' ',e3.last_name)='Lex De Haan';

+-------------+------------------+------------------+
| superjefe   | jefe             | empleat          |
+-------------+------------------+------------------+
| Steven King | Lex De Haan      | Alexander Hunold |
| Lex De Haan | Alexander Hunold | Bruce Ernst      |
| Lex De Haan | Alexander Hunold | David Austin     |
| Lex De Haan | Alexander Hunold | Valli Pataballa  |
| Lex De Haan | Alexander Hunold | Diana Lorentz    |
+-------------+------------------+------------------+
5 rows in set (0,00 sec)

creat per Joan Quintana Compte, febrer 2022

Obtingut de «http://wiki.joanillo.org/index.php?title=Consultes_recursives:_bikeshop&oldid=298448»