Techniques SQL avancées
pour des opérations en bloc plus simple
À propos de cette présentation
- Trucs pour optimiser vos opérations en bloc
- Présentation du concept de table de nombre
- Présentation du concept de table calendrier
Mauvaise utilisation des serveurs
- Aller chercher toutes les données pertinantes via SQL
- Traiter chaque entrée une à une
- Appliquer le résultat (si applicable) via SQL
C'est un un travail en block, alors pourquoi le faire par unité?
Problèmes avec cette approche
- Aller chercher toutes les données et les copier en mémoire
- Traiter une unité à la fois au lieu de tout le bloc en même temps
- Très long à mettre à jour tous les index si les données sont traité une à la fois
Premier truc
Une table de nombre
Qu'est-ce qu'une table de nombres?
- Table très simple avec un seul champ
- Le champ est généralement primaire et auto-inc
- Contient les nombres de 0 ou 1 à 100 000 ou 1 000 000
Quoi faire avec une telle table?
- Trouver des items manquants
- Traiter/Séparer des données CSV
- Dupliquer des données
- Et plus encore
Créer la table
CREATE TABLE hlp_numbers (num INTEGER NOT NULL, PRIMARY KEY(num));
INSERT INTO hlp_numbers VALUES(1);
INSERT INTO hlp_numbers SELECT num + (SELECT MAX(num) FROM hlp_numbers) FROM hlp_numbers;
INSERT INTO hlp_numbers SELECT num + (SELECT MAX(num) FROM hlp_numbers) FROM hlp_numbers;
INSERT INTO hlp_numbers SELECT num + (SELECT MAX(num) FROM hlp_numbers) FROM hlp_numbers;
INSERT INTO hlp_numbers SELECT num + (SELECT MAX(num) FROM hlp_numbers) FROM hlp_numbers;
INSERT INTO hlp_numbers SELECT num + (SELECT MAX(num) FROM hlp_numbers) FROM hlp_numbers;
INSERT INTO hlp_numbers SELECT num + (SELECT MAX(num) FROM hlp_numbers) FROM hlp_numbers;
INSERT INTO hlp_numbers SELECT num + (SELECT MAX(num) FROM hlp_numbers) FROM hlp_numbers;
INSERT INTO hlp_numbers SELECT num + (SELECT MAX(num) FROM hlp_numbers) FROM hlp_numbers;
INSERT INTO hlp_numbers SELECT num + (SELECT MAX(num) FROM hlp_numbers) FROM hlp_numbers;
INSERT INTO hlp_numbers SELECT num + (SELECT MAX(num) FROM hlp_numbers) FROM hlp_numbers;
INSERT INTO hlp_numbers SELECT num + (SELECT MAX(num) FROM hlp_numbers) FROM hlp_numbers;
INSERT INTO hlp_numbers SELECT num + (SELECT MAX(num) FROM hlp_numbers) FROM hlp_numbers;
INSERT INTO hlp_numbers SELECT num + (SELECT MAX(num) FROM hlp_numbers) FROM hlp_numbers;
INSERT INTO hlp_numbers SELECT num + (SELECT MAX(num) FROM hlp_numbers) FROM hlp_numbers;
INSERT INTO hlp_numbers SELECT num + (SELECT MAX(num) FROM hlp_numbers) FROM hlp_numbers;
INSERT INTO hlp_numbers SELECT num + (SELECT MAX(num) FROM hlp_numbers) FROM hlp_numbers;
INSERT INTO hlp_numbers SELECT num + (SELECT MAX(num) FROM hlp_numbers) FROM hlp_numbers;
INSERT INTO hlp_numbers VALUES(0);
DELETE FROM hlp_numbers WHERE num > 100000;
Générer une séquence
SELECT * FROM hlp_numbers
WHERE num BETWEEN 1 AND 500 AND num % 5 = 0
Trouver chaque 5e nombre entre 1 et 500:
Nous donne...
5, 10, 15, 20, 25, ...
Générer une séquence
SELECT DATE('2015-02-05', '+' || num || ' days') FROM hlp_numbers
WHERE num < 365 AND num % 3 = 0
Trouver chaque 3ème jour de l'année en cours
Nous donne...
2015-02-05, 2015-02-08, 2015-02-11, ...
Trouver des items manquants
SELECT num
FROM hlp_numbers n
LEFT JOIN invoices i ON i.invoice_no = n.num
WHERE i.invoice_no IS NULL
AND n.num BETWEEN
(SELECT MIN(invoice_no) FROM invoices)
AND (SELECT MAX(invoice_no) FROM invoices)
Collez les nombres à une liste numérique pour trouver les éléments qui n'existent plus :
Trouver des items manquants
SELECT
DATE('2015-02-05', '+' || num || ' days') AS date, room_number
FROM
rooms r, hlp_numbers n
WHERE
DATE('2015-02-05', '+' || num || ' days') NOT IN (
SELECT reservation_date FROM reservations
WHERE room_number = r.room_number
)
AND num BETWEEN 1 AND 5
Pouvez-vous immaginer ce à quoi ressemble un SQL pour trouver les chambres d'un hotel qui ne sont pas réservées?
Traiter/Séparer des données CSV
1,2,3,4,5
Mathieu,Eric,Valerie,Martin,John
Parfois, les vieux systèmes ont des données sérialisées en CSV ou en un autre format :
hello:9|world:17
i:6|love:4|programming:3
Traiter/Séparer des données CSV
SELECT
CASE
WHEN INSTR(SUBSTR(d.textdata, n.num + 1), ',') = 0
THEN SUBSTR(SUBSTR(d.textdata, n.num + 1), 0)
ELSE SUBSTR(SUBSTR(d.textdata, n.num + 1), 0,
INSTR(SUBSTR(d.textdata, n.num + 1), ','))
END as data
FROM
csvdata AS d, hlp_numbers AS n
WHERE
SUBSTR(d.textdata, n.num, 1) = ',' num = 0
ORDER BY textdata, n.num
Séparez les données en lignes en utilisant une table de nombres :
Data:
"1,2,3,4,5"
Result:
"1"
"2"
"3"
"4"
"5"
Traiter/Séparer des données CSV
SELECT
textdata, num,
SUBSTR(SUBSTR(d.textdata, n.num + 1), 0, INSTR(SUBSTR(d.textdata, n.num + 1), ':')) AS label,
CASE
WHEN INSTR(SUBSTR(d.textdata, n.num + 1), '|') = 0
THEN SUBSTR(SUBSTR(d.textdata, n.num + 1), INSTR(SUBSTR(d.textdata, n.num + 1), ':') + 1)
ELSE SUBSTR(SUBSTR(d.textdata, n.num + 1), INSTR(SUBSTR(d.textdata, n.num + 1), ':') + 1,
INSTR(SUBSTR(d.textdata, n.num + 1), '|') - INSTR(SUBSTR(d.textdata, n.num + 1), ':') - 1)
END as value
FROM
csvdata AS d, hlp_numbers AS n
WHERE
SUBSTR(d.textdata, n.num, 1) = '|'
OR num = 0
ORDER BY textdata, n.num
Séparer sur plusieurs caractère est plus complexe :
Original data:
"hello:9|world:17" "i:6|love:4|programming:3"
Results in:
hello 9 i 6
world 17 love 4 programming 3
Dupliquer des données
INSERT INTO actions
SELECT action_fields, invoice_id
FROM actions, invoices
WHERE actions.id IN (1,2,3)
AND invoices.id BETWEEN 1 AND 10;
Déjà vu :
- Utiliser des tables au hasard = chercher le trouble
- Vous ne savez pas quand la structure ou les données changerons...
INSERT INTO actions
SELECT action_fields, num
FROM actions, hlp_numbers
WHERE actions.id IN (1,2,3)
AND hlp_numbers.num BETWEEN 1 AND 10;
C'est mieux :
- Vous savez que votre table sera toujours une table de nombre de 0 à 100 000 (par exemple)
Dupliquer des données
Autres utilisations?
- Tout ce que vous pouvez boucler autant en procédure stockée qu'en code PHP (Par exemple) peux être remplacé par cette technique! Attention à ne pas abuser, certains cas sont trop complexe et sont mieux unité par unité
- Rapellez-vous que la réindexation c'est long, surtout un élément à la fois!
- Difficile de s'y mettre, mais très efficace une fois qu'on prends le tour...
Second truc
La table calendrier
Une table calendrier c'est...
- Comme une table de nombre
- Avec plus d'information
- Sert à traiter des espaces de temps (time frames)
Que pouvez-vous faire avec un calendrier?
- Calculer ou planifier des événements
- Trouver des dates manquantes (Déjà couvert)
- Calculer des séries
- Calculer des dates de livraison par jour ou heure
- Et plus encore
Créer la table
CREATE TABLE IF NOT EXISTS `hlp_calendar` (
`dt` date NOT NULL,
`isweekday` bit(1) DEFAULT NULL, `isholiday` bit(1) DEFAULT NULL,
`yr` smallint(6) DEFAULT NULL, `qtr` tinyint(4) DEFAULT NULL,
`mt` tinyint(4) DEFAULT NULL, `dy` tinyint(4) DEFAULT NULL,
`dyw` tinyint(4) DEFAULT NULL, `wk` tinyint(4) DEFAULT NULL,
`dywo` tinyint(4) DEFAULT NULL, PRIMARY KEY (`dt`)
);
CREATE INDEX `isweekday` ON hlp_calendar(`isweekday`);
CREATE INDEX `isholiday` ON hlp_calendar(`isholiday`);
CREATE INDEX `yr` ON hlp_calendar(`yr`);
CREATE INDEX `qtr` ON hlp_calendar(`qtr`);
CREATE INDEX `mt` ON hlp_calendar(`mt`);
CREATE INDEX `dy` ON hlp_calendar(`dy`);
CREATE INDEX `dyw` ON hlp_calendar(`dyw`);
CREATE INDEX `dywo` ON hlp_calendar(`dywo`);
CREATE INDEX `wk` ON hlp_calendar(`wk`);
- Ce n'est pas tout, il faut la remplir
- Nous aurons besoin d'une table de nombres
- Cet exemple travaille avec +50 ans, mais il est mieux d'avoir -100 ans et +100 ans par rapport à aujourd'hui!
Créer la table
INSERT INTO hlp_calendar (dt) SELECT DATE('2000-01-01', '+' || num || ' days')
FROM hlp_numbers WHERE num BETWEEN 0 AND 365*50;
UPDATE hlp_calendar SET yr = STRFTIME('%Y', dt), mt = STRFTIME('%m', dt),
dy = STRFTIME('%d', dt), dyw = STRFTIME('%w', dt),
wk = STRFTIME('%W', dt), isweekday = 0, isholiday = 0;
UPDATE hlp_calendar SET isweekday = 1 WHERE dyw NOT IN (1, 7);
UPDATE hlp_calendar SET dywo = 1 WHERE dy BETWEEN 1 AND 7;
UPDATE hlp_calendar SET dywo = 2 WHERE dy BETWEEN 8 AND 14;
UPDATE hlp_calendar SET dywo = 3 WHERE dy BETWEEN 15 AND 21;
UPDATE hlp_calendar SET dywo = 4 WHERE dy BETWEEN 22 AND 28;
UPDATE hlp_calendar SET dywo = 5 WHERE dy BETWEEN 29 AND 31;
UPDATE hlp_calendar SET qtr = 1 WHERE mt IN (1,2,3);
UPDATE hlp_calendar SET qtr = 2 WHERE mt IN (4,5,6);
UPDATE hlp_calendar SET qtr = 3 WHERE mt IN (7,8,9);
UPDATE hlp_calendar SET qtr = 4 WHERE mt IN (10,11,12);
UPDATE hlp_calendar SET isholiday = 1 WHERE
(mt = 12 AND dy IN (25, 26)) OR (mt = 1 AND dy = 1)
OR (mt = 6 AND dy = 24);
Informations importantes non couvertes ici
- D'autres colonnes sont pratiques mais pas créés ici: isworkday, openhour, closehour, totalworktime
- Il est pratique d'avoir plusieurs calendriers en entreprise, donc l'ajout d'un calendar_id est importante voire même vitale
Calculer le xème du mois
Pour bien calculer les jours fériés, il est souvent nécessaire de trouver le 1er dimanche de tel mois ou le 3e mardi de tel mois. Pour y arriver, simplement utiliser "dywo"...
SELECT dt FROM hlp_calendar
WHERE dt BETWEEN '2015-09-01' AND '2015-09-30'
AND dyw = 1 AND dywo = 1
La fête du travail!
Qu'allez vous obtenir avec ce SQL?
Planifier des événements
Certains événements de la vie reviennent sans cesse comme les anniversaires ou le "Aller chercher les enfants à la garderie". Une table calendrier peux vous aider :
SELECT
firstname, birthdate, c2.dt AS next_birthday
FROM
birthdays r join hlp_calendar c1 on c1.dt = r.birthdate, hlp_calendar c2
WHERE
c1.mt = c2.mt AND c1.dy = c2.dy AND
c2.dt BETWEEN DATE('now') AND DATE('now', '+180 days')
firstname birthdate next_birthday
-----------------------------------------
John 2001-03-02 2015-03-02
Sue 2006-06-12 2015-06-12
Planifier des événements
Planifier des événements récurrents à l'aide d'une table de récurrence est très simple :
SELECT
name, starting_date, ending_date, repeats_on, c2.dt AS event_date
FROM
events e join hlp_calendar c1 ON c1.dt = e.starting_date,
hlp_calendar c2
WHERE
c2.dt between e.starting_date and e.ending_date
AND (
(repeats_on = 'daily')
OR (repeats_on = 'weekly' AND c1.dyw = c2.dyw)
OR (repeats_on = 'weekday' AND c2.isweekday = 1)
OR (repeats_on = 'monthly' AND c1.dy = c2.dy)
)
Calculer des séries
Le calcul de séries peux être assez lourd et complexe car il requiert beaucoup de sous-requêtes :
SELECT *
FROM (
SELECT
c1.dt AS from_date,
c2.dt AS to_date,
(SELECT COUNT(amount) AS meet FROM sales WHERE
amount >= 4000 AND dt BETWEEN c1.dt AND c2.dt) AS meets,
(SELECT COUNT(amount) AS meet FROM sales WHERE
amount < 4000 AND dt BETWEEN c1.dt AND c2.dt) AS fails
FROM
sales i1 LEFT JOIN hlp_calendar c1 ON c1.dt = i1.dt,
sales i2 LEFT JOIN hlp_calendar c2 ON c2.dt = i2.dt
WHERE
i1.id <> i2.id
AND i1.dt <= i2.dt
) as sourcedata
WHERE meets > 0 AND fails = 0
ORDER BY meets DESC
Calculer des livrables par jour
Pour calculer les livrables par jour, il vous faut au minimum les vacances, jours fériés et les jours ouvrables :
SELECT MIN(dt), days_expired
FROM (
SELECT c2.dt, julianday(c2.dt) - julianday(c1.dt)
- (
SELECT COUNT(*) FROM hlp_calendar c3
WHERE c3.dt BETWEEN c1.dt AND c2.dt
AND c3.isworkday = 0
) AS days_expired
FROM hlp_calendar c1, hlp_calendar c2
WHERE c1.dt = '2015-01-01'
AND c2.dt >= c1.dt AND c2.dt < '2015-02-01'
) a
WHERE days_expired >= 6
GROUP BY days_expired
ORDER BY days_expired
Calculer les livrables à l'heure
Pour calculer des livrables à l'heure, il vous faudra en plus les heures d'ouverture et de fermeture ainsi que le temps passé à travailler par jour :
SELECT
DATETIME(dt, '+' || (openhour + (hours_expired - 46)) || ' hours') AS dt
FROM (
SELECT c2.dt, c2.openhour, c2.totalworktime,
(
SELECT SUM(totalworktime)
FROM hlp_calendar c3
WHERE c3.dt >= c1.dt AND c3.dt <= c2.dt
AND c3.isworkday = 1
) AS hours_expired
FROM hlp_calendar c1, hlp_calendar c2
WHERE c1.dt = '2015-01-01' AND c2.dt >= c1.dt
AND c2.dt <= '2015-02-01' AND c2.isworkday = 1
) AS datasource
WHERE hours_expired >= 46
LIMIT 1
Autres utilisations?
- Dès que vous devez calculer quelque chose basé sur 2 dates (un espace temps), il est très probable qu'une table calendrier soit indiqué.
- Difficile à intégrer au départ mais offre des possibilités très intéressantes à long terme. Investissez vous et cela deviendra une nouvelle nature pour vous.
Merci
Questions?
Mathieu Dumoulin
Programmeur-Analyste consultant sénior
Twitter: @crazycoders
LinkedIn: crazycoders
Git-hub: crazycodr
Site web et courriel : crazyone@crazycoders.net
Projets actifs :
Standard-Exceptions: GitHub
Infect! (Jeu de société): Facebook page
Techniques SQL avancées pour des opérations en bloc n bloc plus simples
By Mathieu Dumoulin
Techniques SQL avancées pour des opérations en bloc n bloc plus simples
Présentations de quelques trucs pour mieux calculer et préparer vos données en bloc dans un serveur SQL : Voici les tables de nombres et les calendriers!
- 971