SQL
Gemeenschappelijke tabelexpressies

Syntaxis

• WITH QueryName [(ColumnName, ...)] AS (
  SELECTEER ...
  )
  SELECTEER ... VAN QueryName ...;

• MET RECURSIEVE QueryName [(ColumnName, ...)] AS (
  SELECTEER ...
  UNION [ALL]
  SELECTEER ... VAN QueryName ...
  )
  SELECTEER ... VAN QueryName ...;

Opmerkingen

Tijdelijke zoekopdracht

Deze gedragen zich op dezelfde manier als geneste subquery's, maar met een andere syntaxis.

WITH ReadyCars AS (
  SELECT *
  FROM Cars
  WHERE Status = 'READY'
)
SELECT ID, Model, TotalCost
FROM ReadyCars
ORDER BY TotalCost;

Gelijkwaardige syntaxis van de subquery

SELECT ID, Model, TotalCost
FROM (
  SELECT *
  FROM Cars
  WHERE Status = 'READY'
) AS ReadyCars
ORDER BY TotalCost

recursief omhoog in een boom

WITH RECURSIVE ManagersOfJonathon AS (
    -- start with this row
    SELECT *
    FROM Employees
    WHERE ID = 4

    UNION ALL

    -- get manager(s) of all previously selected rows
    SELECT Employees.*
    FROM Employees
    JOIN ManagersOfJonathon
        ON Employees.ID = ManagersOfJonathon.ManagerID
)
SELECT * FROM ManagersOfJonathon;

waarden genereren

De meeste databases hebben geen eigen manier om een reeks getallen voor ad-hocgebruik te genereren; algemene tabelexpressies kunnen echter met recursie worden gebruikt om dat type functie te emuleren.

In het volgende voorbeeld wordt een algemene tabelexpressie met de naam Numbers gegenereerd met een kolom i met een rij voor getallen 1-5:

--Give a table name `Numbers" and a column `i` to hold the numbers
WITH Numbers(i) AS (
    --Starting number/index
    SELECT 1
    --Top-level UNION ALL operator required for recursion
    UNION ALL
    --Iteration expression:
    SELECT i + 1
    --Table expression we first declared used as source for recursion
    FROM Numbers
    --Clause to define the end of the recursion
    WHERE i < 5
)
--Use the generated table expression like a regular table
SELECT i FROM Numbers;

Deze methode kan worden gebruikt met elk nummerinterval en met andere soorten gegevens.

recursief een subtree opsommen

WITH RECURSIVE ManagedByJames(Level, ID, FName, LName) AS (
    -- start with this row
    SELECT 1, ID, FName, LName
    FROM Employees
    WHERE ID = 1

    UNION ALL

    -- get employees that have any of the previously selected rows as manager
    SELECT ManagedByJames.Level + 1,
           Employees.ID,
           Employees.FName,
           Employees.LName
    FROM Employees
    JOIN ManagedByJames
        ON Employees.ManagerID = ManagedByJames.ID

    ORDER BY 1 DESC   -- depth-first search
)
SELECT * FROM ManagedByJames;

Oracle CONNECT BY-functionaliteit met recursieve CTE's

De CONNECT BY-functionaliteit van Oracle biedt veel nuttige en niet-triviale functies die niet zijn ingebouwd bij het gebruik van SQL-standaard recursieve CTE's. Dit voorbeeld repliceert deze functies (met enkele toevoegingen omwille van de volledigheid), met behulp van SQL Server-syntaxis. Het is vooral handig voor Oracle-ontwikkelaars die veel functies missen in hun hiërarchische query's in andere databases, maar het dient ook om te laten zien wat er met een hiërarchische query in het algemeen kan worden gedaan.

  WITH tbl AS (
       SELECT id, name, parent_id
         FROM mytable)
     , tbl_hierarchy AS (
       /* Anchor */
       SELECT 1 AS "LEVEL"
            --, 1 AS CONNECT_BY_ISROOT
            --, 0 AS CONNECT_BY_ISBRANCH
            , CASE WHEN t.id IN (SELECT parent_id FROM tbl) THEN 0 ELSE 1 END AS CONNECT_BY_ISLEAF
            , 0 AS CONNECT_BY_ISCYCLE
            , '/' + CAST(t.id   AS VARCHAR(MAX)) + '/' AS SYS_CONNECT_BY_PATH_id
            , '/' + CAST(t.name AS VARCHAR(MAX)) + '/' AS SYS_CONNECT_BY_PATH_name
            , t.id AS root_id
            , t.*
         FROM tbl t
        WHERE t.parent_id IS NULL                            -- START WITH parent_id IS NULL
       UNION ALL
       /* Recursive */
       SELECT th."LEVEL" + 1 AS "LEVEL"
            --, 0 AS CONNECT_BY_ISROOT
            --, CASE WHEN t.id IN (SELECT parent_id FROM tbl) THEN 1 ELSE 0 END AS CONNECT_BY_ISBRANCH
            , CASE WHEN t.id IN (SELECT parent_id FROM tbl) THEN 0 ELSE 1 END AS CONNECT_BY_ISLEAF
            , CASE WHEN th.SYS_CONNECT_BY_PATH_id LIKE '%/' + CAST(t.id AS VARCHAR(MAX)) + '/%' THEN 1 ELSE 0 END AS CONNECT_BY_ISCYCLE
            , th.SYS_CONNECT_BY_PATH_id   + CAST(t.id   AS VARCHAR(MAX)) + '/' AS SYS_CONNECT_BY_PATH_id
            , th.SYS_CONNECT_BY_PATH_name + CAST(t.name AS VARCHAR(MAX)) + '/' AS SYS_CONNECT_BY_PATH_name
            , th.root_id
            , t.*
         FROM tbl t
              JOIN tbl_hierarchy th ON (th.id = t.parent_id) -- CONNECT BY PRIOR id = parent_id
        WHERE th.CONNECT_BY_ISCYCLE = 0)                     -- NOCYCLE
SELECT th.*
     --, REPLICATE(' ', (th."LEVEL" - 1) * 3) + th.name AS tbl_hierarchy
  FROM tbl_hierarchy th
       JOIN tbl CONNECT_BY_ROOT ON (CONNECT_BY_ROOT.id = th.root_id)
 ORDER BY th.SYS_CONNECT_BY_PATH_name;                       -- ORDER SIBLINGS BY name

CONNECT BY-functies hierboven aangetoond, met uitleg:

• clausules
  • CONNECT BY: geeft de relatie aan die de hiërarchie definieert.
  • START MET: Specificeert de root nodes.
  • BESTEL SIBLINGS BIJ: Bestelt de resultaten correct.
• parameters
  • NOCYCLE: stopt het verwerken van een vertakking wanneer een lus wordt gedetecteerd. Geldige hiërarchieën zijn Gerichte Acyclische grafieken en circulaire verwijzingen schenden dit construct.
• operators
  • PRIOR: verkrijgt gegevens van de ouder van het knooppunt.
  • CONNECT_BY_ROOT: haalt gegevens op uit de root van het knooppunt.
• pseudokolommen
  • NIVEAU: geeft de afstand van het knooppunt tot zijn root aan.
  • CONNECT_BY_ISLEAF: geeft een knooppunt aan zonder kinderen.
  • CONNECT_BY_ISCYCLE: geeft een knooppunt met een cirkelvormige verwijzing aan.
• functies
  • SYS_CONNECT_BY_PATH: Retourneert een afgeplatte / aaneengeschakelde weergave van het pad naar het knooppunt vanaf de root.

Genereer recursief datums, uitgebreid met teamroostering als voorbeeld

DECLARE @DateFrom DATETIME = '2016-06-01 06:00'
DECLARE @DateTo DATETIME = '2016-07-01 06:00'
DECLARE @IntervalDays INT = 7

-- Transition Sequence = Rest & Relax into Day Shift into Night Shift
-- RR (Rest & Relax) = 1
-- DS (Day Shift) = 2
-- NS (Night Shift) = 3

;WITH roster AS
(
   SELECT @DateFrom AS RosterStart, 1 AS TeamA, 2 AS TeamB, 3 AS TeamC
   UNION ALL
   SELECT DATEADD(d, @IntervalDays, RosterStart),
          CASE TeamA WHEN 1 THEN 2 WHEN 2 THEN 3 WHEN 3 THEN 1 END AS TeamA,
          CASE TeamB WHEN 1 THEN 2 WHEN 2 THEN 3 WHEN 3 THEN 1 END AS TeamB,
          CASE TeamC WHEN 1 THEN 2 WHEN 2 THEN 3 WHEN 3 THEN 1 END AS TeamC
   FROM roster WHERE RosterStart < DATEADD(d, -@IntervalDays, @DateTo)
)

SELECT RosterStart,
       ISNULL(LEAD(RosterStart) OVER (ORDER BY RosterStart), RosterStart + @IntervalDays) AS RosterEnd,
       CASE TeamA WHEN 1 THEN 'RR' WHEN 2 THEN 'DS' WHEN 3 THEN 'NS' END AS TeamA,
       CASE TeamB WHEN 1 THEN 'RR' WHEN 2 THEN 'DS' WHEN 3 THEN 'NS' END AS TeamB,
       CASE TeamC WHEN 1 THEN 'RR' WHEN 2 THEN 'DS' WHEN 3 THEN 'NS' END AS TeamC
FROM roster

Resultaat

Dat wil zeggen voor week 1 TeamA is op R&R, TeamB is op Day Shift en TeamC is op Night Shift.

Een query opnieuw uitvoeren om gemeenschappelijke tabelexpressies te gebruiken

Stel dat we alle productcategorieën willen krijgen met een totale omzet van meer dan 20.

Hier is een zoekopdracht zonder Common Table Expressions:

SELECT category.description, sum(product.price) as total_sales
FROM sale
LEFT JOIN product on sale.product_id = product.id
LEFT JOIN category on product.category_id = category.id
GROUP BY category.id, category.description
HAVING sum(product.price) > 20

En een vergelijkbare zoekopdracht met Common Table Expressions:

WITH all_sales AS (
  SELECT product.price, category.id as category_id, category.description as category_description
  FROM sale
  LEFT JOIN product on sale.product_id = product.id
  LEFT JOIN category on product.category_id = category.id
)
, sales_by_category AS (
  SELECT category_description, sum(price) as total_sales
  FROM all_sales
  GROUP BY category_id, category_description
)
SELECT * from sales_by_category WHERE total_sales > 20

Voorbeeld van een complexe SQL met Common Table Expression

Stel dat we de "goedkoopste producten" uit de "topcategorieën" willen opvragen.

Hier is een voorbeeld van een zoekopdracht met Common Table Expressions

-- all_sales: just a simple SELECT with all the needed JOINS
WITH all_sales AS (
  SELECT
  product.price as product_price,
  category.id as category_id,
  category.description as category_description
  FROM sale
  LEFT JOIN product on sale.product_id = product.id
  LEFT JOIN category on product.category_id = category.id
)
-- Group by category
, sales_by_category AS (
  SELECT category_id, category_description,
  sum(product_price) as total_sales
  FROM all_sales
  GROUP BY category_id, category_description
)
-- Filtering total_sales > 20
, top_categories AS (
  SELECT * from sales_by_category WHERE total_sales > 20
)
-- all_products: just a simple SELECT with all the needed JOINS
, all_products AS (
  SELECT
  product.id as product_id,
  product.description as product_description,
  product.price as product_price,
  category.id as category_id,
  category.description as category_description
  FROM product
  LEFT JOIN category on product.category_id = category.id
)
-- Order by product price
, cheapest_products AS (
  SELECT * from all_products
  ORDER by product_price ASC
)
-- Simple inner join 
, cheapest_products_from_top_categories AS (
  SELECT product_description, product_price
  FROM cheapest_products
  INNER JOIN top_categories ON cheapest_products.category_id = top_categories.category_id
)
--The main SELECT
SELECT * from cheapest_products_from_top_categories