Techniques SQL avancées
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;
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, ...
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, ...
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 :
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?
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
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"
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
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 :
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 :
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`);
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);
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?
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 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)
)
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
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
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
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