Let’s explore this concept using the AdventureWorksDW2019 database.
—
Scenario: Analyzing Sales Territories
Imagine you’re tasked with understanding sales performance in different territories. You need a clear and concise query to display the hierarchical structure of sales territories (regions and countries) alongside their sales data.
In the AdventureWorksDW2019 database:
– The `DimSalesTerritory` table holds sales territory details.
– The `FactResellerSales` table contains sales data linked to territories.
We’ll use a CTE to:
1. Simplify querying hierarchical or recursive data.
2. Aggregate sales data at a higher level (e.g., by region).
—
What is a Common Table Expression (CTE)?
A Common Table Expression (CTE) is a temporary result set defined within the execution scope of a `SELECT`, `INSERT`, `UPDATE`, or `DELETE` statement. It allows you to:
– Break down complex queries into smaller, manageable parts.
– Improve readability and maintainability of your SQL code.
—
Step-by-Step Explanation with Examples
1. Creating a Simple CTE
Objective: Get a list of sales territories with their names and groups.
Query:
“`sql
WITH SalesTerritoryCTE AS (
SELECT
SalesTerritoryKey,
SalesTerritoryCountry,
SalesTerritoryRegion
FROM DimSalesTerritory
)
SELECT
FROM SalesTerritoryCTE;
“`
Explanation:
– CTE Definition: The part after `WITH` defines the temporary result set, `SalesTerritoryCTE`.
– Usage: The CTE is queried in the `SELECT` statement.
Result:
| SalesTerritoryKey | SalesTerritoryCountry | SalesTerritoryRegion |
|——————–|———————–|———————–|
| 1 | United States | North America |
| 2 | Canada | North America |
| 3 | France | Europe |
—
2. Using CTE for Aggregation
Objective: Find the total reseller sales for each region.
Query:
“`sql
WITH TerritorySalesCTE AS (
SELECT
st.SalesTerritoryRegion,
SUM(rs.SalesAmount) AS TotalSales
FROM FactResellerSales rs
JOIN DimSalesTerritory st
ON rs.SalesTerritoryKey = st.SalesTerritoryKey
GROUP BY st.SalesTerritoryRegion
)
SELECT SalesTerritoryRegion, TotalSales
FROM TerritorySalesCTE;
“`
Explanation:
– The CTE aggregates `SalesAmount` by `SalesTerritoryRegion`.
– The `JOIN` combines `FactResellerSales` and `DimSalesTerritory`.
Result:
| SalesTerritoryRegion | TotalSales |
|———————–|————|
| North America | 1,200,000 |
| Europe | 750,000 |
| Asia | 450,000 |
—
3. Recursive CTE for Hierarchical Data
Objective: Display hierarchical relationships between sales territories.
Recursive Query:
“`sql
WITH TerritoryHierarchyCTE AS (
SELECT
SalesTerritoryKey,
SalesTerritoryCountry,
SalesTerritoryRegion,
1 AS Level
FROM DimSalesTerritory
WHERE SalesTerritoryParentKey IS NULL — Start with top-level regions
UNION ALL
SELECT
st.SalesTerritoryKey,
st.SalesTerritoryCountry,
st.SalesTerritoryRegion,
Level + 1
FROM DimSalesTerritory st
JOIN TerritoryHierarchyCTE th
ON st.SalesTerritoryParentKey = th.SalesTerritoryKey
)
SELECT SalesTerritoryRegion, SalesTerritoryCountry, Level
FROM TerritoryHierarchyCTE;
“`
Explanation:
– The anchor query retrieves top-level territories (`SalesTerritoryParentKey IS NULL`).
– The recursive part adds child territories.
– The `Level` column tracks the depth of each node in the hierarchy.
Result:
| SalesTerritoryRegion | SalesTerritoryCountry | Level |
|———————–|———————–|——-|
| North America | United States | 1 |
| North America | Canada | 2 |
| Europe | France | 1 |
—
Why Use CTEs?
1. Simplify Complex Queries:
– CTEs allow you to break down intricate logic into smaller, modular queries.
2. Readability:
– Code is easier to read and maintain compared to nested subqueries.
3. Hierarchical Data:
– Recursive CTEs are perfect for working with parent-child relationships.
4. Reusability:
– The same CTE can be referenced multiple times within the query.
Ref: Microsoft Adventure Works, w3schools…
Leave a Reply