La commande WITH en Transact-Sql
02/02/2017
jb.raulin

La commande WITH permet d’obtenir un résultat temporaire connu sous le nom de CTE (table d’expression commune).

Elle peut être utilisée dans le cadre de requêtes SELECT, INSERT, UPDATE, DELETE ou d’un CREATE VIEW.

Une CTE peut inclure des auto références pour utiliser la récursivité.

Contexte

La base de données que nous utiliserons pour exemple représente le réseau du métro parisien.

Elle possède 3 tables :

-         La table Line qui décrit les lignes de métro

-         La table Station qui décrit les stations de métro

-         La table Way qui décrit les chemins entre les stations de métro

 

CREATE TABLE [dbo].[Line](

    [Id] [int] IDENTITY(1,1) NOT NULL,

    [Name] [nvarchar](16) NOT NULL,

    [Color] [nvarchar](8) NULL,

 CONSTRAINT [PK_Line] PRIMARY KEY CLUSTERED

(

    [Id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

 

CREATE TABLE [dbo].[Station](

    [Id] [int] NOT NULL,

    [Name] [nvarchar](64) NOT NULL,

    [Comment] [nvarchar](256) NULL,

    [Position] [geography] NULL,

    [Postcode] [nvarchar](64) NOT NULL,

    [Department] [nvarchar](64) NOT NULL,

 CONSTRAINT [PK_Station] PRIMARY KEY CLUSTERED

(

       [Id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

 

CREATE TABLE [dbo].[Way](

    [Id] [int] IDENTITY(1,1) NOT NULL,

    [StationStart] [int] NULL,

    [StationEnd] [int] NOT NULL,

    [Line] [int] NOT NULL,

 CONSTRAINT [PK_Way] PRIMARY KEY CLUSTERED

(

       [Id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY] 

 

Utilisation simple de WITH

On souhaite connaitre la distance la plus courte à vol d’oiseau entre 2 stations consécutives d’une ligne donnée. On utilisera la commande suivante :

WITH StationDistance(IdStationA, NameStationA, IdStationB, NameStationB, Distance)

AS

(

   SELECT s1.Id, s1.Name, s2.id,

   s2.Name, s1.Position.STDistance(s2.Position)

   FROM Station s1

   INNER JOIN Station s2

   ON s1.Id <> s2.Id

   AND s1.Id < s2.Id

)

SELECT NameStationA, NameStationB, Distance FROM StationDistance sd

INNER JOIN Way w ON

   w.StationStart IN (sd.IdStationA, sd.IdStationB)

   AND w.StationEnd IN (sd.IdStationA, sd.IdStationB)

INNER JOIN Line l

ON l.Id = w.Line

WHERE l.Name='M1'

 

WITH permet la création temporaire d’une table StationDistance qui liste toutes les distances entre les stations. On se sert ensuite de cette instruction comme d’une table temporaire. Cette table nous permet de calculer la distance entre 2 stations consécutives.

Une alternative possible est l’utilisation d’une sous-requête pour obtenir le même résultat :

SELECT NameStationA, NameStationB, Distance FROM

    (SELECT s1.Id IdStationA, s1.Name NameStationA, s2.id IdStationB,

      s2.Name NameStationB, s1.Position.STDistance(s2.Position) Distance

      FROM Station s1

      INNER JOIN Station s2

      ON s1.Id <> s2.Id

      AND s1.Id < s2.Id

    ) AS sd

INNER JOIN Way w ON

    w.StationStart IN (sd.IdStationA, sd.IdStationB)

    AND w.StationEnd IN (sd.IdStationA, sd.IdStationB)

INNER JOIN Line l

ON l.Id = w.Line

WHERE l.Name='M1'

 

WITH Multiple

On peut enchainer WITH pour avoir plusieurs tables temporaires qui pourront référencer les WITH précédents

La commande WITH nous permet également d’utiliser une même table temporaire plusieurs fois dans une requête.

 

Si, par exemple, on souhaite connaitre la distance entre une station donnée et les stations a 2 chemins de stations consécutifs de distance, on écrira la requête suivante :

 

WITH StationDistance(IdStationA, NameStationA, IdStationB, NameStationB, Distance)

AS

(

       SELECT s1.Id, s1.Name, s2.id, s2.Name, s1.Position.STDistance(s2.Position)

       FROM Station s1

       INNER JOIN Station s2

       ON s1.Id <> s2.Id AND s1.Id < s2.Id

)

SELECT NameStationA, NameStationB, Distance FROM StationDistance sd

INNER JOIN Way w ON

       w.StationStart IN (sd.IdStationA, sd.IdStationB)

       AND w.StationEnd IN (sd.IdStationA, sd.IdStationB)

INNER JOIN Line l

ON l.Id = w.Line

WHERE l.Name='M1'

GO

 

WITH StationDistance(IdStationA, NameStationA, IdStationB, NameStationB, Distance)

AS

(

-- Récupère la distance entre 2 stations données

    SELECT s1.Id, s1.Name, s2.id, s2.Name, s1.Position.STDistance(s2.Position)

    FROM Station s1

    INNER JOIN Station s2

        ON s1.Id <> s2.Id AND s1.Id < s2.Id

),

StationDistanceWay(IdStationA, NameStationA, IdStationB, NameStationB, Distance)

AS

(

-- Récupère la distance entre 2 stations connectés

   SELECT  IdStationA, NameStationA, IdStationB, NameStationB, Distance

   FROM StationDistance sd

   INNER JOIN Way w ON

       w.StationStart = sd.IdStationA AND w.StationEnd = sd.IdStationB

)

-- Récupère la distance entre 2 stations connectés à la station spécifiée

SELECT sd1.NameStationB, sd2.NameStationB, sd1.distance + sd2.Distance  FROM StationDistanceWay sd1

INNER JOIN StationDistanceWay sd2

       ON (sd1.IdStationA = sd2.IdStationA and sd1.IdStationB <> sd2.IdStationB)

              WHERE sd1.NameStationB = 'Villiers' OR sd1.NameStationA = 'Villiers'

 

On remarquera ici que la CTE StationDistanceWay est utilisée 2 fois par notre requête.

WITH récursif

Si l’on souhaite connaitre le parcours avec le moins de stations entre un départ et une arrivée, on créera la procédure stockée suivante.

Membre d’ancrage

Afin de faire fonctionner un WITH récursif, il faut d’abord réfléchir à la requête d’ancrage. Ici :

WITH WayParser(Id, Steps, StationEnd, PathDone)

AS

(

-- Définition du membre d’ancrage

SELECT DISTINCT start.Id, start.StationEnd, ',' + CAST(@stationStartId AS VARCHAR(MAX)) + ',' + CAST(start.StationEnd AS VARCHAR(MAX)) + ','

    FROM Way as start

    WHERE start.StationStart = @stationStartId

)

-- Execution de la CTE

SELECT PathDone FROM WayParser w

WHERE StationEnd = @stationEndId;

 

Soit, on récupère l’id du chemin, la liste des stations d’arrivée, stations de départ de la table des chemins qui contiennent la station de départ définie.

Membre récursif

Une fois cette partie définie, on ajoute la partie récursive unie par un UNION ALL

WITH WayParser(Id, StationEnd, PathDone)

AS

(

-- Définition du membre d’ancrage

    SELECT DISTINCT start.Id,

start.StationEnd, ',' + CAST(@stationStartId AS VARCHAR(MAX)) + ',' + CAST(start.StationEnd AS VARCHAR(MAX)) + ','

    FROM Way as start

    WHERE start.StationStart = @stationStartId

    UNION ALL

-- Définition du membre récursif

    SELECT

        arrival.Id, arrival.StationEnd, start.PathDone + CAST(arrival.StationEnd AS VARCHAR(MAX)) + ','

    FROM Way as arrival

    INNER JOIN WayParser as start

        ON start.StationEnd = arrival.StationStart

    WHERE start.PathDone NOT LIKE '%,' + CAST(arrival.StationEnd AS VARCHAR(MAX))+ ',%'

)

-- Execution de la CTE

SELECT PathDone FROM WayParser w

WHERE StationEnd = @stationEndId;

 

On récupère la liste des identifiants, la station de fin, une concaténation de l’arrivée de fin à la liste des autres arrivées depuis la liste des chemins auquel on joint l’ensemble de la requête courante.

En effet, le WITH permet d’utiliser en son sein la CTE elle-même, ce qui permet la récursivité.

La jointure se fait entre la station de début d’un SELECT et la station de fin de l’autre.

Etapes

On rajoute la notion d’étapes, ce qui nous permettra de sélectionner le chemin le plus court limiter la récursivité.

On aurait pu additionner le temps qu’il faut entre 2 stations à la place pour un modele plus réaliste mais il est préférable simplifier d’utiliser Steps

 Ce qui nous donne :

WITH WayParser(Id, Steps, StationEnd, PathDone)

AS

(

-- Définition du membre d’ancrage

    SELECT DISTINCT start.Id, 0 as Steps, 

        start.StationEnd, ',' + CAST(@stationStartId AS VARCHAR(MAX)) + ',' + CAST(start.StationEnd AS VARCHAR(MAX)) + ','

    FROM Way as start

    WHERE start.StationStart = @stationStartId

    UNION ALL

-- Définition du membre récursif

    SELECT

        arrival.Id, start.Steps + 1,

        arrival.StationEnd, start.PathDone + CAST(arrival.StationEnd AS VARCHAR(MAX)) + ','

    FROM Way as arrival

    INNER JOIN WayParser as start

        ON start.StationEnd = arrival.StationStart

    WHERE start.PathDone NOT LIKE '%,' + CAST(arrival.StationEnd AS VARCHAR(MAX))+ ',%'

        AND start.Steps < @stepMax

)

,

 short (Steps)

   AS (SELECT MIN(Steps) FROM WayParser WHERE StationEnd = @stationEndId)

-- Execution de la CTE

SELECT PathDone FROM WayParser w

INNER JOIN short s

   ON w.Steps = s.Steps

WHERE StationEnd = @stationEndId;

 

Par défaut, le nombre de récursivités est limitée à 100. Il est cependant possible de lever le niveau de récursivité. Pour se faire on utilisera l’option MAXRECURSION de la façon suivante :

OPTION (MAXRECURSION 1000)

On pourra limiter le niveau de récursivité entre 0 et 32767 niveaux.

Xml

Afin de mieux présenter les données, on utilise @xml.nodes qui nous permettra d’obtenir la liste des stations consécutives à la place d’une chaine de caractères :

DECLARE @xml as xml;

 

WITH WayParser(Id, Steps, StationEnd, PathDone)

AS

(

-- Définition du membre d’ancrage

    SELECT DISTINCT start.Id, 0 as Steps, 

        start.StationEnd, ',' + CAST(@stationStartId AS VARCHAR(MAX)) + ',' + CAST(start.StationEnd AS VARCHAR(MAX)) + ','

    FROM Way as start

    WHERE start.StationStart = @stationStartId

    UNION ALL

-- Définition du membre récursif

    SELECT

        arrival.Id, start.Steps + 1,

        arrival.StationEnd, start.PathDone + CAST(arrival.StationEnd AS VARCHAR(MAX)) + ','

    FROM Way as arrival

    INNER JOIN WayParser as start

        ON start.StationEnd = arrival.StationStart

    WHERE start.PathDone NOT LIKE '%,' + CAST(arrival.StationEnd AS VARCHAR(MAX))+ ',%'

        AND start.Steps < @stepMax

)

,

 short (Steps)

   AS (SELECT MIN(Steps) FROM WayParser WHERE StationEnd = @stationEndId)

-- Execution de la CTE

SELECT @xml = cast(('<X>'+replace(substring(PathDone, 2, LEN(PathDone) - 2),',','</X><X>')+'</X>') as xml)

FROM WayParser w

INNER JOIN short s

   ON w.Steps = s.Steps

WHERE StationEnd = @stationEndId;

 

SELECT N.value('.', 'integer') as Id FROM @xml.nodes('X') as T(N);

 

Résultat final

Ci-dessous la procédure stockée utilisée

CREATE PROCEDURE [dbo].[PathCalculate](@stationStartId int, @stationEndId int, @stepMax int = 30)

AS

BEGIN

DECLARE @xml as xml;

 

WITH WayParser(Id, Steps, StationEnd, PathDone)

AS

(

-- Définition du membre d’ancrage

    SELECT DISTINCT start.Id, 0 as Steps, 

        start.StationEnd, ',' + CAST(@stationStartId AS VARCHAR(MAX)) + ',' + CAST(start.StationEnd AS VARCHAR(MAX)) + ','

    FROM Way as start

    WHERE start.StationStart = @stationStartId

    UNION ALL

-- Définition du membre récursif

    SELECT

        arrival.Id, start.Steps + 1,

        arrival.StationEnd, start.PathDone + CAST(arrival.StationEnd AS VARCHAR(MAX)) + ','

    FROM Way as arrival

    INNER JOIN WayParser as start

        ON start.StationEnd = arrival.StationStart

    WHERE start.PathDone NOT LIKE '%,' + CAST(arrival.StationEnd AS VARCHAR(MAX))+ ',%'

        AND start.Steps < @stepMax

)

,

 short (Steps)

   AS (SELECT MIN(Steps) FROM WayParser WHERE StationEnd = @stationEndId)

-- Execution de la CTE

SELECT @xml = cast(('<X>'+replace(substring(PathDone, 2, LEN(PathDone) - 2),',','</X><X>')+'</X>') as xml)

FROM WayParser w

INNER JOIN short s

   ON w.Steps = s.Steps

WHERE StationEnd = @stationEndId;

 

SELECT N.value('.', 'integer') as Id FROM @xml.nodes('X') as T(N)

END;

Sources

Un petit logiciel a été développé afin de mettre en évidence l’utilisation du WITH récursif.

Les sources ainsi que le script de création de la base sont disponibles ici.

Ressources

Sur les requêtes récursives : https://technet.microsoft.com/en-us/library/ms186243(v=sql.105).aspx

Sur la limite de récursivité : https://sqlwithmanoj.com/2011/12/23/recursive-cte-maximum-recursion-100-has-been-exhausted/

Sur Xml Nodes :https://msdn.microsoft.com/en-us/library/ms188282.aspx