Pour les débutants en SQL, il existe une syntaxe un peu ésotérique nommée PARTITION BY, qui apparaît partout dans SQL. Il a toujours une signification similaire, bien que dans des contextes assez différents. La signification est similaire à celle de GROUP BYà savoir pour regrouper/partitionner des ensembles de données selon certains critères de regroupement/partitionnement.
Par exemple, lors de l’interrogation de la base de données Sakila :
SELECT actor_id, film_id
FROM film_actor
Quelque chose comme ci-dessous peut apparaître :
|actor_id|film_id| |--------|-------| |1 |1 | |2 |3 | |10 |1 | |20 |1 | |1 |23 | |1 |25 | |30 |1 | |19 |2 | |40 |1 | |3 |17 | |53 |1 | |19 |3 | |2 |31 |
Et nous pourrions partitionner les données comme ceci pour le ACTOR_ID = 1 cloison:
|actor_id|film_id|
|--------|-------|
+--> |1 |1 |
All ACTOR_ID = 1 | |2 |3 |
| |10 |1 |
| |20 |1 |
+--> |1 |23 |
+--> |1 |25 |
|30 |1 |
|19 |2 |
|40 |1 |
|3 |17 |
|53 |1 |
|19 |3 |
|2 |31 |
Pour le ACTOR_ID = 2 cloison:
|actor_id|film_id|
|--------|-------|
|1 |1 |
All ACTOR_ID = 2 +--> |2 |3 |
| |10 |1 |
| |20 |1 |
| |1 |23 |
| |1 |25 |
| |30 |1 |
| |19 |2 |
| |40 |1 |
| |3 |17 |
| |53 |1 |
| |19 |3 |
+--> |2 |31 |
Comment pouvons-nous utiliser ces partitions en SQL, en particulier ? Que signifient-ils? Bref:
Une partition sépare un ensemble de données en sous-ensembles, qui ne se chevauchent pas.
Cloisons de fenêtre
La première chose que nous pouvons faire est la fenêtre PARTITION clause, que nous utilisons lors du calcul des fonctions de fenêtre. Par exemple, nous pourrions calculer :
SELECT
actor_id,
film_id,
COUNT(*) OVER (PARTITION BY actor_id)
FROM film_actor
Si nous supposons que nous voyons l’intégralité de l’ensemble de données (la table réelle contient plus de lignes), le résultat suivant s’affichera :
|actor_id|film_id|count|
|--------|-------|-----|
|1 |1 |3 |
|2 |3 |2 |
|10 |1 |1 |
|20 |1 |1 |
|1 |23 |3 |
|1 |25 |3 |
|30 |1 |1 |
|19 |2 |2 |
|40 |1 |1 |
|3 |17 |1 |
|53 |1 |1 |
|19 |3 |2 |
|2 |31 |2 |
En d’autres termes, nous “comptons les lignes sur la cloison“. Cela fonctionne presque comme GROUP BYoù nous comptons les lignes du groupe, bien que le GROUP BY La clause transforme le jeu de résultats et les colonnes projetables, rendant les colonnes non groupées indisponibles :
SELECT actor_id, COUNT(*)
FROM film_actor
GROUP BY actor_id
Résultant en:
|actor_id|count|
|--------|-----|
|1 |3 |
|2 |2 |
|10 |1 |
|20 |1 |
|30 |1 |
|19 |2 |
|40 |1 |
|3 |1 |
|53 |1 |
Si vous le souhaitez, le contenu de la partition est maintenant réduit de sorte que chaque clé de partition/clé de groupe n’apparaisse qu’une seule fois dans le jeu de résultats. Cette différence rend les fonctions de fenêtre beaucoup plus puissantes que les fonctions d’agrégation et de regroupement ordinaires.
Voir aussi notre article sur GROUP BY pour plus de détails.
MATCH_RECOGNIZE partitions
MATCH_RECOGNIZE fait partie du standard SQL, inventé par Oracle, et l’envie de tous les autres SGBDR (bien que certains aient commencé à l’adopter). Il combine la puissance des expressions régulières, la correspondance de modèles, la génération de données et SQL. Il pourrait être sensible, qui sait.
Par exemple, examinons les clients qui effectuent de petits paiements en peu de temps. Regardez cette beauté. Il suffit de regarder!
SELECT
customer_id,
payment_date,
payment_id,
amount
FROM payment
MATCH_RECOGNIZE (
-- Partition the data set by customer_id
PARTITION BY customer_id
-- Order each partition by payment_date
ORDER BY payment_date
-- Return all the matched rows
ALL ROWS PER MATCH
-- Match rows with 3 occurrences of event "A" in a row
PATTERN (A {3})
-- Define the event "A" as...
DEFINE A AS
-- Being a payment whose amount is less than 1
A.amount < 1
-- And whose payment date is less than 1 day after
-- the previous payment
AND A.payment_date - prev(A.payment_date) < 1
)
ORDER BY customer_id, payment_date
Ouf! Cela utilise tellement de mots-clés fantaisistes que le surligneur de syntaxe de ce blog bon marché ici ne peut même pas suivre à distance!
Le résultat est:
|CUSTOMER_ID|PAYMENT_DATE |PAYMENT_ID|AMOUNT| |-----------|-----------------------|----------|------| |72 |2005-08-18 10:59:04.000|1961 |0.99 | |72 |2005-08-18 16:17:54.000|1962 |0.99 | |72 |2005-08-19 12:53:53.000|1963 |0.99 | |152 |2005-08-20 01:16:52.000|4152 |0.99 | |152 |2005-08-20 19:13:23.000|4153 |0.99 | |152 |2005-08-21 03:01:01.000|4154 |0.99 | |207 |2005-07-08 17:14:14.000|5607 |0.99 | |207 |2005-07-09 01:26:22.000|5608 |0.99 | |207 |2005-07-09 13:56:56.000|5609 |0.99 | |244 |2005-08-20 11:54:01.000|6615 |0.99 | |244 |2005-08-20 17:12:28.000|6616 |0.99 | |244 |2005-08-21 09:31:44.000|6617 |0.99 |
Ainsi, nous pouvons confirmer que pour chacun de ces groupes de 3 paiements, il y a :
- Montants inférieurs à 1.
- Dates consécutives à moins d’un jour d’intervalle.
- Les groupes sont par clientqui est à nouveau la partition.
Vous voulez en savoir plus sur MATCH_RECOGNIZE? Je pense que cet article l’explique beaucoup mieux que n’importe quoi d’autre sur le web. Vous pouvez jouer gratuitement avec Oracle XE 21c, par exemple disponible sur Docker par Gerald Venzl.
MODÈLE cloisons
Encore plus mystérieux que MATCH_RECOGNIZE est spécifique à Oracle MODEL ou SPREADSHEET clause. Chaque application complexe doit avoir au moins un MODEL requête juste pour que vos collègues se demandent. Un exemple peut être trouvé dans notre article précédent. En bref, vous pouvez faire tout ce que vous pourriez faire autrement dans un logiciel de tableur, tel que MS Excel. Je vais donner un autre exemple ici, sans plonger profondément dans son fonctionnement :
SELECT
customer_id,
payment_date,
payment_id,
amount
FROM (
SELECT *
FROM (
SELECT p.*, 0 AS s, 0 AS n
FROM payment p
)
MODEL
-- We again partition our data set by customer_id
PARTITION BY (customer_id)
-- The "spreadsheet dimension" is the row number ordered
-- by payment date, within a partition
DIMENSION BY (
row_number () OVER (
PARTITION BY customer_id
ORDER BY payment_date
) AS rn
)
-- Measures is what we want to project, including
-- o Table columns
-- o Additional calculated values
MEASURES (payment_date, payment_id, amount, s, n)
-- These rules are the spreadsheet formulae
RULES (
-- S is the sum of previous amounts that are smaller than 1
-- and whose payment dates are less than 1 day apart
s[any] = CASE
WHEN amount[cv(rn)] < 1
AND payment_date[cv(rn)] - payment_date[cv(rn) - 1] < 1
THEN coalesce(s[cv(rn) - 1], 0) + amount[cv(rn)]
ELSE 0
END,
-- N is the number of consecutive amounts with these properties
n[any] = CASE
WHEN amount[cv(rn)] < 1
AND payment_date[cv(rn)] - payment_date[cv(rn) - 1] < 1
THEN coalesce(n[cv(rn) - 1], 0) + 1
ELSE 0
END
)
) t
-- Filter out only those rows where we had more than 3
-- consecutive events
WHERE n >= 3
ORDER BY customer_id, rn
Déposez-en un dans votre base de code de production vendredi avant le déploiement, et vous serez le chouchou de tout le monde, c’est garanti.
De toute façon, MATCH_RECOGNIZE était un peu mieux, je pense. Le résultat est:
|CUSTOMER_ID|PAYMENT_DATE |PAYMENT_ID|AMOUNT| |-----------|-----------------------|----------|------| |72 |2005-08-19 12:53:53.000|1963 |0.99 | |152 |2005-08-21 03:01:01.000|4154 |0.99 | |207 |2005-07-09 13:56:56.000|5609 |0.99 | |244 |2005-08-21 09:31:44.000|6617 |0.99 | |244 |2005-08-21 19:39:43.000|6618 |0.99 | |252 |2005-07-28 02:44:25.000|6800 |0.99 | |377 |2005-07-07 12:24:37.000|10211 |0.99 | |425 |2005-08-01 12:37:46.000|11499 |0.99 | |511 |2005-07-11 18:50:55.000|13769 |0.99 |
Si vous avez envie d’un frisson, essayez de modifier ma requête pour renvoyer les lignes triples habituelles qui forment un groupe, comme dans le MATCH_RECOGNIZE exemple, et laissez votre solution dans les commentaires. C’est certainement faisable !
Tables partitionnées
Au moins Oracle et PostgreSQL prennent en charge le partitionnement des tables au niveau du stockage, probablement d’autres aussi. La fonctionnalité aide à apprivoiser vos problèmes de stockage en séparant les données en plusieurs physique tables, tout en prétendant de manière transparente que vous avez un seul logique table dans votre application, et en introduisant d’autres types de problèmes.
L’exemple typique est le partitionnement des ensembles de données par plages de dates, par exemple, c’est ce qui est documenté dans PostgreSQL.
CREATE TABLE payment (
customer_id int not null,
amount numeric not null,
payment_date date not null
)
PARTITION BY RANGE (payment_date);
Maintenant, nous ne pouvons pas encore utiliser cette table, car elle n’existe que logiquement. Il ne sait pas encore comment stocker les données physiquement:
INSERT INTO payment (customer_id, amount, payment_date)
VALUES (1, 10, DATE '2000-01-01');
Cela produit :
Erreur SQL [23514]: ERREUR : aucune partition de la relation “paiement” trouvée pour la ligne
Détail : la clé de partition de la ligne défaillante contient (payment_date) = (2000-01-01).
Alors, créons un stockage physique pour une certaine plage de dates, par exemple :
CREATE TABLE payment_2000
PARTITION OF payment
FOR VALUES FROM (DATE '2000-01-01') TO (DATE '2000-12-31');
Maintenant, l’insert fonctionne. Cette interprétation de PARTITION correspond à nouveau à la fonction de fenêtre 1, où nous divisons notre ensemble de données en sous-ensembles, qui sont clairement séparés sans chevauchement.
Bizarre : partitions de jointure externe
La prochaine fonctionnalité de partitionnement fait partie de la norme SQL, mais je ne l’ai vue implémentée que dans Oracle, jusqu’à présent, qui l’a toujours eue : les jointures externes partitionnées. Ils ne sont pas triviaux à expliquer, et malheureusement, leurs partitions n’ont rien à voir avec les partitions de fenêtre. Ils sont plus comme CROSS JOIN sucre de syntaxe (ou vinaigre, selon vos goûts).
Pensez-y de cette façon, vous pouvez utiliser des jointures externes partitionnées pour combler les lacunes dans des données autrement clairsemées. Regardons un exemple :
SELECT
f.film_id,
f.title,
c.category_id,
c.name,
count(*) OVER ()
FROM film f
LEFT OUTER JOIN film_category fc
ON f.film_id = fc.film_id
LEFT OUTER JOIN category c
ON fc.category_id = c.category_id
ORDER BY f.film_id, c.category_id
Cette requête produit la catégorie par film. Si une catégorie n’apparaît pas avec un film, il n’y a pas d’enregistrement dans le résultat :
|FILM_ID|TITLE |CATEGORY_ID|NAME |COUNT(*)OVER()| |-------|----------------|-----------|-----------|--------------| |1 |ACADEMY DINOSAUR|6 |Documentary|1000 | |2 |ACE GOLDFINGER |11 |Horror |1000 | |3 |ADAPTATION HOLES|6 |Documentary|1000 | |4 |AFFAIR PREJUDICE|11 |Horror |1000 | |5 |AFRICAN EGG |8 |Family |1000 | |6 |AGENT TRUMAN |9 |Foreign |1000 | |7 |AIRPLANE SIERRA |5 |Comedy |1000 | |8 |AIRPORT POLLOCK |11 |Horror |1000 | |9 |ALABAMA DEVIL |11 |Horror |1000 | |10 |ALADDIN CALENDAR|15 |Sports |1000 |
Comme vous pouvez le voir, nous avons 1000 films, et parce que la base de données Sakila est si ennuyeuse, chaque film n’a qu’une seule catégorie, même si la relation plusieurs à plusieurs permettrait plus d’une affectation.
Que se passe-t-il si nous ajoutons un PARTITION BY clause à l’une des jointures externes ?
SELECT
f.film_id,
f.title,
c.category_id,
c.name,
count(*) OVER ()
FROM film f
LEFT OUTER JOIN film_category fc
ON f.film_id = fc.film_id
LEFT OUTER JOIN category c
PARTITION BY (c.category_id) -- Magic here
ON fc.category_id = c.category_id
ORDER BY f.film_id, c.category_id
Je ne montrerai pas tout le résultat, mais comme vous pouvez le voir avec le résultat de la fonction de fenêtre, nous avons maintenant 16 000 lignes au total, pas 1 000. C’est parce que nous avons 1 000 films x 16 catégories, donc un produit croisé avec des noms de catégories vides ( mais pas d’identifiants de catégorie vides) au cas où il n’y aurait pas de correspondance, si vous voulez :
|FILM_ID|TITLE |CATEGORY_ID|NAME |COUNT(*)OVER()| |-------|----------------|-----------|-----------|--------------| |1 |ACADEMY DINOSAUR|1 | |16000 | |1 |ACADEMY DINOSAUR|2 | |16000 | |1 |ACADEMY DINOSAUR|3 | |16000 | |1 |ACADEMY DINOSAUR|4 | |16000 | |1 |ACADEMY DINOSAUR|5 | |16000 | |1 |ACADEMY DINOSAUR|6 |Documentary|16000 | |1 |ACADEMY DINOSAUR|7 | |16000 | |1 |ACADEMY DINOSAUR|8 | |16000 | |1 |ACADEMY DINOSAUR|9 | |16000 | |1 |ACADEMY DINOSAUR|10 | |16000 | |1 |ACADEMY DINOSAUR|11 | |16000 | |1 |ACADEMY DINOSAUR|12 | |16000 | |1 |ACADEMY DINOSAUR|13 | |16000 | |1 |ACADEMY DINOSAUR|14 | |16000 | |1 |ACADEMY DINOSAUR|15 | |16000 | |1 |ACADEMY DINOSAUR|16 | |16000 | |2 |ACE GOLDFINGER |1 | |16000 | |2 |ACE GOLDFINGER |2 | |16000 | |2 |ACE GOLDFINGER |3 | |16000 | |2 |ACE GOLDFINGER |4 | |16000 | |2 |ACE GOLDFINGER |5 | |16000 | |2 |ACE GOLDFINGER |6 | |16000 | |2 |ACE GOLDFINGER |7 | |16000 | |2 |ACE GOLDFINGER |8 | |16000 | |2 |ACE GOLDFINGER |9 | |16000 | |2 |ACE GOLDFINGER |10 | |16000 | |2 |ACE GOLDFINGER |11 |Horror |16000 | |2 |ACE GOLDFINGER |12 | |16000 | |2 |ACE GOLDFINGER |13 | |16000 | |2 |ACE GOLDFINGER |14 | |16000 | |2 |ACE GOLDFINGER |15 | |16000 | |2 |ACE GOLDFINGER |16 | |16000 |
D’une certaine manière, cela est utile chaque fois que vous souhaitez créer un rapport basé sur des données éparses et générer des enregistrements pour ces lacunes. Une requête similaire sans PARTITION BY serait sur l’utilisation CROSS JOIN
SELECT
f.film_id,
f.title,
c.category_id,
NVL2(fc.category_id, c.name, NULL) AS name,
count(*) OVER ()
FROM film f
CROSS JOIN category c
LEFT JOIN film_category fc
ON fc.film_id = f.film_id
AND fc.category_id = c.category_id
ORDER BY f.film_id, c.category_id;
Je dois dire que je n’ai pas trouvé ces jointures externes partitionnées très utiles ou intelligibles dans le passé, et je ne suis pas convaincu que d’autres SGBDR manquent vraiment d’une fonctionnalité importante ici, bien qu’il s’agisse de SQL standard.
Jusqu’à présent, jOOQ n’émule pas encore la fonctionnalité dans d’autres RDBMS.