Scribd
Upload
12 views

The Power of SQL Aggregate Window Functions

Uploaded by

Vanilla Sky
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
12 views

The Power of SQL Aggregate Window Functions

Uploaded by

Vanilla Sky
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 4

SOME SAMPLES OF OF WINDOW FUNCTION

AGGREGATION ON SQL
-- Running Totals
SELECT order_date,
sum(order_amount) OVER (ORDER BY order_date)
AS running_total
FROM orders;

-- Moving Average
SELECT order_date,
AVG(order_amount) OVER (ORDER BY order_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS
moving_average
FROM orders;

-- Rank
SELECT product_name, product_price, DENSE_RANK()
OVER (ORDER BY product_price DESC) AS rank
FROM products;

-- Percent Rank
SELECT product_name, product_price, PERCENT_RANK()
OVER (ORDER BY product_price DESC) AS
percent_rank
FROM products;

Sebastiano Gazzola Follow me on linkedin


@sebastianogazzola for the next tip!
SOME SAMPLES OF OF WINDOW FUNCTION
AGGREGATION ON SQL

-- Lead and Lag


SELECT order_date, order_amount,
LAG(order_amount) OVER (ORDER BY order_date)
AS prev_amount,
LEAD(order_amount) OVER (ORDER BY order_date)
AS next_amount
FROM orders;

-- First Value and Last Value


SELECT order_date, order_amount,
FIRST_VALUE(order_amount) OVER (ORDER BY
order_date) AS first_amount,
LAST_VALUE(order_amount) OVER (ORDER BY
order_date) AS last_amount
FROM orders;

-- Cumulative Distribution
SELECT product_name, product_price, CUME_DIST()
OVER (ORDER BY product_price) AS
cumulative_dist
FROM products;

Sebastiano Gazzola Follow me on linkedin


@sebastianogazzola for the next tip!
SOME SAMPLES OF OF WINDOW FUNCTION
AGGREGATION ON SQL

-- Ntile
SELECT product_name, product_price,
NTILE(4) OVER (ORDER BY product_price)
AS quartile
FROM products;

-- Percentile Cont
SELECT product_name, product_price,
PERCENTILE_CONT(0.5) WITHIN GROUP
(ORDER BY product_price) OVER ()
AS median_price
FROM products;

-- Concatenation
SELECT department, employee_name,
STRING_AGG(phone_number, ', ') WITHIN
GROUP (ORDER BY phone_number) AS
phone_list
FROM employees
GROUP BY department, employee_name;

Sebastiano Gazzola Follow me on linkedin


@sebastianogazzola for the next tip!
SOME SAMPLES OF OF WINDOW FUNCTION
AGGREGATION ON SQL

-- Dense Rank with Ties


SELECT employee_name, department,
DENSE_RANK() OVER (PARTITION BY
department ORDER BY salary DESC)
AS dense_rank
FROM employees;

-- Sum with Grouping Sets


SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY GROUPING SETS ((department), ());

Sebastiano Gazzola Follow me on linkedin


@sebastianogazzola for the next tip!

You might also like