Techniques SQL avancées

pour des opérations en bloc plus simple

À propos de cette présentation

  1. Trucs pour optimiser vos opérations en bloc
  2. Présentation du concept de table de nombre
  3. Présentation du concept de table calendrier

Mauvaise utilisation des serveurs

  1. Aller chercher toutes les données pertinantes via SQL
  2. Traiter chaque entrée une à une
  3. 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

  1. Aller chercher toutes les données et les copier en mémoire
  2. Traiter une unité à la fois au lieu de tout le bloc en même temps
  3. 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?

  1. Trouver des items manquants
  2. Traiter/Séparer des données CSV
  3. Dupliquer des données
  4. 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 :

  1. Utiliser des tables au hasard = chercher le trouble
  2. 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 :

  1. 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

Made with Slides.com