La commande WITH en Transact-Sql
SQL
Jean-Baptiste Raulin - 02/02/2017 à 00:00:000 commentaire
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
Réalisé par
Commentaires :
Aucun commentaires pour le moment
Laissez un commentaire :