Parmi les requêtes les plus difficile à établir en SQL se trouve généralement les calculs temporels. Voici deux problématiques résolues, celle du calcul de l’âge révolu et celle de la recherche de personnes sont l’anniversaire est à souhaiter… pas si simple !
Tout d’abord, voici les données de nos exemples…
D’abord la table :
(PRS_ID INT IDENTITY PRIMARY KEY,
PRS_DATE_NAISSANCE DATE)
GO
Ensuite son alimentation, d’abord avec plus de 40 000 lignes de personnes ayant des dates de naissances continues du 1/1/1900 Ã nos jours…
WHILE @D <= CURRENT_TIMESTAMP
BEGIN
INSERT INTO T_PERSONNE_PRS VALUES (@D);
SET @D = DATEADD(DAY, 1, @D);
END;
Enfin, on y ajoutant des doublons pour la charge afin d'effectuer des tests de performances :
UNION ALL
SELECT N + 1
FROM TN
WHERE N < 24)
INSERT INTO T_PERSONNE_PRS
SELECT PRS_DATE_NAISSANCE
FROM TN
CROSS JOIN T_PERSONNE_PRS AS TP;
Ce qui remplit notre table avec plus d'un million de lignes.
Le calcul de l’âge révolu
Il fut calculer l’âge en tenant compte du jour près par rapport à la date courante.
Voici quelques approches malheureuses (fonctions SQL Server) :
SELECT *, DATEDIFF(YEAR, PRS_DATE_NAISSANCE, GETDATE()) AS AGE
FROM T_PERSONNE_PRS;
-- ceci est inexact... car imprécis jusqu'à 30 jours près !
SELECT *, DATEDIFF(MONTH, PRS_DATE_NAISSANCE, GETDATE()) / 12 AS AGE
FROM T_PERSONNE_PRS;
-- ceci est inexact... car imprécis à 1 jours près !
SELECT *, FLOOR(DATEDIFF(DAY, PRS_DATE_NAISSANCE, GETDATE()) / 365.25) AS AGE
FROM T_PERSONNE_PRS;
Voici maintenant une formulation qui donne les réponses exactes :
Requête 1 :
FROM T_PERSONNE_PRS
WHERE ((MONTH(PRS_DATE_NAISSANCE) -1 ) * 31 + (DAY(PRS_DATE_NAISSANCE) -1 ) ) % (31 * 12)
BETWEEN ((MONTH('2013-11-20') - 1 ) * 31 + (DAY('2013-11-20') - 1) ) % (31 * 12)
AND ((MONTH('2013-12-10') - 1 ) * 31 + (DAY('2013-12-10') + 371) ) % (31 * 12);
Les métriques de performances relevées (4 UC), sont les suivantes :
— 1) Temps UC = 748 ms, temps écoulé = 8031 ms.
— 2) Temps UC = 515 ms, temps écoulé = 7994 ms.
— 3) Temps UC = 717 ms, temps écoulé = 7948 ms.
— 4) Temps UC = 624 ms, temps écoulé = 7932 ms.
La recherche des anniversaires
Voici tout d’abord quelques requêtes simples pour des cas triviaux :
SELECT *
FROM T_PERSONNE_PRS
WHERE MONTH(PRS_DATE_NAISSANCE) = MONTH(GETDATE())
AND DAY(PRS_DATE_NAISSANCE) = DAY(GETDATE());
-- les personnes qui ont leur date de naissance dans 15 jours
SELECT *
FROM T_PERSONNE_PRS
WHERE MONTH(PRS_DATE_NAISSANCE) = MONTH(DATEADD(DAY, 15, GETDATE()))
AND DAY(PRS_DATE_NAISSANCE) = DAY(DATEADD(DAY, 15, GETDATE()));
-- les personnes qui ont leur date d'anniversaire dans le mois courant
SELECT *
FROM T_PERSONNE_PRS
WHERE MONTH(PRS_DATE_NAISSANCE) = MONTH(GETDATE());
-- les personnes qui ont leur date d'anniversaire dans le mois suivant le mois courant
SELECT *
FROM T_PERSONNE_PRS
WHERE MONTH(PRS_DATE_NAISSANCE) = (MONTH(GETDATE()) + 1) % 13 + MONTH(GETDATE()) / 12;
-- notez le + 1 pour rechercher le mois suivant
-- les personnes qui ont leur date d'anniversaire dans le 6e mois suivant le mois courant
SELECT *
FROM T_PERSONNE_PRS
WHERE MONTH(PRS_DATE_NAISSANCE) = (MONTH(GETDATE()) + 6) % 13 + MONTH(GETDATE()) / 12;
-- notez le + 6...
Nous allons maintenant rechercher les personnes qui ont leur date d’anniversaire entre deux dates…
Voici tout d’abord quelques cas triviaux :
SELECT *
FROM T_PERSONNE_PRS
WHERE MONTH(PRS_DATE_NAISSANCE) * 31
+ DAY(PRS_DATE_NAISSANCE) >= MONTH('2014-02-20') * 31
+ DAY('2014-02-20')
AND MONTH(PRS_DATE_NAISSANCE) * 31
+ DAY(PRS_DATE_NAISSANCE) <= MONTH('2014-03-10') * 31
+ DAY('2014-03-10');
SELECT *
FROM T_PERSONNE_PRS
WHERE MONTH(PRS_DATE_NAISSANCE) * 31
+ DAY(PRS_DATE_NAISSANCE) >= MONTH('2014-12-20') * 31
+ DAY('2014-12-20')
OR MONTH(PRS_DATE_NAISSANCE) * 31
+ DAY(PRS_DATE_NAISSANCE) <= MONTH('2015-01-10') * 31
+ DAY('2015-01-10');
-- notez la présence du OR à la place du AND, car nous avons changé d'année (l'intervalle s'est "retourné") !
Et maintenant la solution générique :
Requête 2
SELECT *
FROM T_PERSONNE_PRS
WHERE (MONTH(PRS_DATE_NAISSANCE) * 31 + DAY(PRS_DATE_NAISSANCE) ) % (31 * 12)
BETWEEN (MONTH('2014-12-20') * 31 + DAY('2014-12-20')) % (31 * 12)
AND (MONTH('2015-01-10') * 31 + DAY('2015-01-10') + 372) % (31 * 12);
-- avec dates du 20 février au 10 mars
SELECT *
FROM T_PERSONNE_PRS
WHERE (MONTH(PRS_DATE_NAISSANCE) * 31 + DAY(PRS_DATE_NAISSANCE) ) % (31 * 12)
BETWEEN (MONTH('2014-02-20') * 31 + DAY('2014-02-20')) % (31 * 12)
AND (MONTH('2014-03-10') * 31 + DAY('2014-03-10') + 372) % (31 * 12);
Les métriques de performances relevées (4 UC), sont les suivantes :
–> 1) Temps UC = 951 ms, temps écoulé = 677 ms.
–> 2) Temps UC = 967 ms, temps écoulé = 641 ms.
–> 3) Temps UC = 890 ms, temps écoulé = 638 ms.
–> 4) Temps UC = 905 ms, temps écoulé = 663 ms.
La question est maintenant… Y a t-il des formulations plus performantes ?
Expert S.G.B.D relationnelles et langage S.Q.L
Moste Valuable Professionnal Microsoft SQL Server
Société SQLspot : modélisation, conseil, formation,
optimisation, audit, tuning, administration SGBDR
Enseignant: CNAM PACA, ISEN Toulon, CESI Aix en Prov.
L’entreprise SQL Spot
Le site web sur le SQL et les SGBDR
Requête proposée par aieeeuuuuu pour le calcul d’âge :
DATEDIFF(MONTH, PRS_DATE_NAISSANCE, GETDATE()) / 12
- CASE
WHEN MONTH(PRS_DATE_NAISSANCE) = MONTH(GETDATE())
AND DAY(PRS_DATE_NAISSANCE) > DAY(GETDATE())
THEN 1
ELSE 0
END AS AGE
FROM T_PERSONNE_PRS;
Temps de calculs comparés (sur plus de 1 millions de lignes) :
1) solution SQLpro :
— Temps UC = 687 ms, temps écoulé = 8997 ms.
— Temps UC = 577 ms, temps écoulé = 8839 ms.
— Temps UC = 499 ms, temps écoulé = 9092 ms.
— Temps UC = 406 ms, temps écoulé = 8962 ms.
— Temps UC = 484 ms, temps écoulé = 9100 ms.
– MOY : 530,6 8998
2) solution aieeeuuuuu :
— Temps UC = 452 ms, temps écoulé = 8816 ms.
— Temps UC = 546 ms, temps écoulé = 8662 ms.
— Temps UC = 608 ms, temps écoulé = 8822 ms.
— Temps UC = 546 ms, temps écoulé = 8554 ms.
— Temps UC = 499 ms, temps écoulé = 8781 ms
– MOY : UC = 530,2 8727
Conclusion : la requête est plus simple à écrire, et l’on utilise moins de fonctions globalement. Cependant le gain est inexistant. Il est probable que SQL Server réutilise le résultats des fonctions qu’il a déjà calculé.