Calculs SQL avec des dates : age exact révolu et anniversaires…

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 :

CREATE TABLE T_PERSONNE_PRS
(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…

DECLARE @D DATE = '1900-01-01';
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 :

WITH TN AS (SELECT 1 AS N
            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) :

-- ceci est inexact... car imprécis jusqu'à 11 mois et 30 jours près !
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 :

SELECT *
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 :

-- les personnes qui ont leur date d'anniversaire aujourd'hui
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 :

-- exemple 1 : 20 février / 10 mars
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');
-- exemple 2 : 20 décembre / 10 janvier (an + 1)
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

-- avec dates du 20 décembre au 10 janvier (an + 1)
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 ?

Frédéric Brouard, alias SQLpro, ARCHITECTE DE DONNÉES
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

MVP Microsoft SQL Server

Une réflexion au sujet de « Calculs SQL avec des dates : age exact révolu et anniversaires… »

  1. Avatar de SQLproSQLpro Auteur de l’article

    Requête proposée par aieeeuuuuu pour le calcul d’âge :

    SELECT *,
           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é.

Laisser un commentaire