T-SQL: OVER clause

November 1, 2013 Leave a comment

OVER clause defines a window or user-specified set of rows within a query result set. A window function then computes a value for each row in the window. You can use the OVER clause with functions to compute aggregated values such as moving averages, cumulative aggregates, running totals, or a top N per group results.

The following is the script to show the usage of OVER clause (more in my T-SQL Script List).

-- Step 1: Open a new query window to the TSQL2012 database
USE TSQL2012;
GO

-- Step 2: Creating Views for OVER clause

-- Setup views for demo
IF OBJECT_ID('Production.CategorizedProducts','V') IS NOT NULL DROP VIEW Production.CategorizedProducts
GO
CREATE VIEW Production.CategorizedProducts
AS
    SELECT  Production.Categories.categoryid AS CatID,
			Production.Categories.categoryname AS CatName,
            Production.Products.productname AS ProdName,
            Production.Products.unitprice AS UnitPrice
    FROM    Production.Categories
            INNER JOIN Production.Products ON Production.Categories.categoryid=Production.Products.categoryid;
GO
IF OBJECT_ID('Sales.CategoryQtyYear','V') IS NOT NULL DROP VIEW Sales.CategoryQtyYear
GO
CREATE VIEW Sales.CategoryQtyYear
AS
SELECT  c.categoryname AS Category,
        SUM(od.qty) AS Qty,
        YEAR(o.orderdate) AS Orderyear
FROM    Production.Categories AS c
        INNER JOIN Production.Products AS p ON c.categoryid=p.categoryid
        INNER JOIN Sales.OrderDetails AS od ON p.productid=od.productid
        INNER JOIN Sales.Orders AS o ON od.orderid=o.orderid
GROUP BY c.categoryname, YEAR(o.orderdate);
GO

-- Step 3: Using OVER with ordering
-- Rank products by price from high to low
SELECT CatID, CatName, ProdName, UnitPrice,
	RANK() OVER(ORDER BY UnitPrice DESC) AS PriceRank
FROM Production.CategorizedProducts
ORDER BY PriceRank;

-- Rank products by price in descending order in each category.
-- Note the ties.
SELECT CatID, CatName, ProdName, UnitPrice,
	RANK() OVER(PARTITION BY CatID ORDER BY UnitPrice DESC) AS PriceRank
FROM Production.CategorizedProducts
ORDER BY CatID;

-- Step 4: Use framing to create running total
-- Display a running total of quantity per product category.
-- This uses framing to set boundaries at the start
-- of the set and the current row, for each partition
SELECT Category, Qty, Orderyear,
	SUM(Qty) OVER (
		PARTITION BY category
		ORDER BY orderyear
		ROWS BETWEEN UNBOUNDED PRECEDING
		AND CURRENT ROW) AS RunningQty
FROM Sales.CategoryQtyYear;

-- Display a running total of quantity per year.
SELECT Category, Qty, Orderyear,
	SUM(Qty) OVER (
		PARTITION BY orderyear
		ORDER BY Category
		ROWS BETWEEN UNBOUNDED PRECEDING
		AND CURRENT ROW) AS RunningQty
FROM Sales.CategoryQtyYear;

-- Show both side-by-side per category and per-year

SELECT Category, Qty, Orderyear,
	SUM(Qty) OVER (PARTITION BY orderyear ORDER BY Category	ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotalByYear,
	SUM(Qty) OVER (PARTITION BY Category ORDER BY OrderYear	ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotalByCategory
FROM Sales.CategoryQtyYear
ORDER BY Orderyear, Category;

-- Step 5: Clean up
IF OBJECT_ID('Production.CategorizedProducts','V') IS NOT NULL DROP VIEW Production.CategorizedProducts
IF OBJECT_ID('Sales.CategoryQtyYear','V') IS NOT NULL DROP VIEW Sales.CategoryQtyYear
GO

Filed under Microsoft SQL, T-SQL Tagged with , ,

T-SQL: Common Table Expression

November 1, 2013 Leave a comment

A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

A CTE can be used to create a recursive query, substitute a view etc. CTEs can be defined in user-defined routines, such as functions, stored procedures, triggers, or views.

The following are some of testing examples. (see my T-SQL script List for other examples):

-- Step 1: Open a new query window to the TSQL2012 database
USE TSQL2012;
GO
-- Step 2: Common Table Expressions
-- -- Select this query and execute it to show CTE Examples
WITH CTE_year AS
	(
	SELECT YEAR(orderdate) AS orderyear, custid
	FROM Sales.Orders
	)
SELECT orderyear, COUNT(DISTINCT custid) AS cust_count
FROM CTE_year
GROUP BY orderyear;

-- Step 3 Recursive CTE 
WITH EmpOrg_CTE AS
(SELECT empid, mgrid, lastname, firstname --anchor query
	FROM HR.Employees
WHERE empid = 5 -- starting "top" of tree. Change this to show other root employees

UNION ALL
SELECT child.empid, child.mgrid, child.lastname, child.firstname -- recursive member which refers back to CTE
	FROM EmpOrg_CTE AS parent
	JOIN HR.Employees AS child
	ON child.mgrid=parent.empid
)
SELECT empid, mgrid, lastname, firstname
FROM EmpOrg_CTE;

Filed under Microsoft SQL, T-SQL Tagged with , ,

T-SQL: ISNULL, COALESCE, and NULLIF

November 1, 2013 Leave a comment

The following test demonstartes the usage of ISNULL, COALESCE, and NULLIF of T-SQL. For the complete list of my other tests and the creation scripts of TSQL2012 database, please refer to my T-SQL Script List.

-- Step 1: Open a new query window to the TSQL2012 database
USE TSQL2012;
GO

-- Step 2: Select and execute the following query to illustrate
-- The ISNULL function
SELECT custid, city, ISNULL(region, 'N/A') AS region, country
FROM Sales.Customers;

-- Step 3: Select and execute the following query to illustrate the
-- COALESCE function
SELECT custid, country, region, city,
country + ',' + COALESCE(region, ' ') + ', ' + city as location
FROM Sales.Customers;

-- Step 4: Select and execute the following queries to illustrate the
-- NULLIF function

-- First, set up sample data
CREATE TABLE dbo.employee_goals(emp_id INT , goal int, actual int);

GO
-- Populate the sample data
INSERT INTO dbo.employee_goals
VALUES(1,100, 110), (2,90, 90), (3,100, 90), (4,100, 80);

-- Show the sample data
SELECT emp_id, goal, actual
FROM dbo.employee_goals;

-- Use NULLIF to show which employees have actual
-- values different from their goals
SELECT emp_id, NULLIF(actual,goal) AS actual_if_different
FROM dbo.employee_goals;

-- Step 5: Clean up demo table
DROP TABLE dbo.employee_goals;

Filed under Microsoft SQL, T-SQL Tagged with ,

Design a site like this with WordPress.com
Get started