Category: Uncategorized

Advanced MySQL

Posted By: Blog User
4 March

Advanced SQL

  • Union vs Union All

In brief : Union does not return duplicates between two queries, but Union All returns duplicates.

  • Recursive/Hieararchial Queries

PostgreSQL provides the WITH statement that allows you to construct auxiliary statements for use in a query. These statements are often referred to as common table expressions or CTEs. The CTEs are like temporary tables that only exist during the execution of the query.

A recursive query is a query that refers to a recursive CTE. 

For example:-

WITH RECURSIVE cte_name AS(

    CTE_query_definition — non-recursive term

    UNION [ALL]

    CTE_query definion  — recursive term

) SELECT * FROM cte_name;

A recursive CTE has three elements:

  • Non-recursive term: the non-recursive term is a CTE query definition that forms the base result set of the CTE structure.
  • Recursive term: the recursive term is one or more CTE query definitions joined with the non-recursive term using the UNION or UNION ALL operator. The recursive term references the CTE name itself.
  • Termination check: the recursion stops when no rows are returned from the previous iteration.

This is the below sample table

CREATE TABLE employees (

employee_id serial PRIMARY KEY,

full_name VARCHAR NOT NULL,

manager_id INT

);

INSERT INTO employees (employee_id, full_name,manager_id) VALUES (1, ‘Michael North’, NULL), (2, ‘Megan Berry’, 1), (3, ‘Sarah Berry’, 1), (4, ‘Zoe Black’, 1), (5, ‘Tim James’, 1), (6, ‘Bella Tucker’, 2), (7, ‘Ryan Metcalfe’, 2), (8, ‘Max Mills’, 2), (9, ‘Benjamin Glover’, 2), (10, ‘Carolyn Henderson’, 3), (11, ‘Nicola Kelly’, 3), (12, ‘Alexandra Climo’, 3), (13, ‘Dominic King’, 3);

Exercise:

  • Find the employees along with their manager names.

This is very simple as we all know that we can get by doing a simple join like below:-

select 

e3.employee_id ,e3.full_name , e3.manager_id

from 

employees e3 

left join employees eh 

on eh.employee_id = e3.manager_id

A screenshot of a cell phone

Description automatically generated

Exercise 2:-

How we can find the level of each employee compared with their highest position of CEO. So that we can find at which level that particular employee is. Here our hierarchial queries/recursive queries come into place

with recursive emp_hierarchy as (

select 

employee_id, full_name, manager_id, 1 as “level”               Anchor Query

from 

employees e2 

where manager_id is null

union all 

select 

e3.employee_id ,e3.full_name ,e3.manager_id , Recursive Query

eh.level+1 as “level”

from 

employees e3 

inner join emp_hierarchy eh 

on eh.employee_id = e3.manager_id

where e3.manager_id is not null

) select * from emp_hierarchy ;

A screenshot of a cell phone

Description automatically generated
  • Window Functions

 To begin with, let’s take the below example tables:-

CREATE TABLE product_groups (

group_id serial PRIMARY KEY,

group_name VARCHAR (255) NOT NULL

);

CREATE TABLE products (

product_id serial PRIMARY KEY,

product_name VARCHAR (255) NOT NULL,

price DECIMAL (11, 2),

group_id INT NOT NULL,

FOREIGN KEY (group_id) REFERENCES product_groups (group_id)

);

INSERT INTO product_groups (group_name)

VALUES (‘Smartphone’), (‘Laptop’), (‘Tablet’);

INSERT INTO products (product_name, group_id,price)

VALUES (‘Microsoft Lumia’, 1, 200), (‘HTC One’, 1, 400), (‘Nexus’, 1, 500), (‘iPhone’, 1, 900), (‘HP Elite’, 2, 1200), (‘Lenovo Thinkpad’, 2, 700), (‘Sony VAIO’, 2, 700), (‘Dell Vostro’, 2, 800), (‘iPad’, 3, 700), (‘Kindle Fire’, 3, 150), (‘Samsung Galaxy Tab’, 3, 200);

The easiest way to understand the window functions is to start by reviewing the aggregate functions. An aggregate function aggregates data from a set of rows into a single row.

SELECT AVG (price) FROM products;

Similary, to apply the aggregate function to subsets of rows, we use the Group By clause.

SELECT group_name, AVG (price) FROM products INNER JOIN product_groups USING (group_id) GROUP BY group_name;

Similar to an aggregate function, a window function operates on a set of rows. However, it does not reduce the number of rows returned by the query. 

The term window describes the set of rows on which the window function operates. A window function returns values from the rows in a window.

Exercise :-

Get the product name, the price, product group name, along with the average prices of each product group

SELECT product_name, price, group_name, AVG (price) OVER ( PARTITION BY group_name)

FROM products INNER JOIN product_groups USING (group_id);

 The AVG() function works as a window function that operates on a set of rows specified by the OVER clause. Each set of rows is called a window. A window function always performs the calculation on the result set after the JOIN, WHERE, GROUP BY and HAVING clause and before the final ORDER BY clause in the evaluation order. The syntax for window function is as below:-

window_function(arg1, arg2,..) OVER (

   [PARTITION BY partition_expression]

   [ORDER BY sort_expression [ASC | DESC] [NULLS {FIRST | LAST }]

In the above syntax:-

window_function(arg1,arg2,…)

The window_function is the name of the window function. Some window functions do not accept any argument.

PARTITION BY clause

The PARTITION BY clause divides rows into multiple groups or partitions to which the window function is applied. Like the example above, we used the product group to divide the products into groups (or partitions). The PARTITION BY clause is optional. If you skip the PARTITION BY clause, the window function will treat the whole result set as a single partition.

ORDER BY clause

The ORDER BY clause specifies the order of rows in each partition to which the window function is applied.

The ORDER BY clause uses the NULLS FIRST or NULLS LAST option to specify whether nullable values should be first or last in the result set. The default is NULLS LAST option.

 frame_clause

The frame_clause defines a subset of rows in the current partition to which the window function is applied. This subset of rows is called a frame.

We can also use window clause to shorten the query like below:-

SELECT 

   wf1() OVER w,

   wf2() OVER w,

FROM table_name

WINDOW w AS (PARTITION BY c1 ORDER BY c2);

List of WINDOW function list:-

NameDescription
CUME_DISTReturn the relative rank of the current row.
DENSE_RANKRank the current row within its partition without gaps.
CUME_DISTReturn the relative rank of the current row.
DENSE_RANKRank the current row within its partition without gaps.
FIRST_VALUEReturn a value evaluated against the first row within its partition.
LAGReturn a value evaluated at the row that is at a specified physical offset row before the current row within the partition.
LAST_VALUEReturn a value evaluated against the last row within its partition.
LEADReturn a value evaluated at the row that is offset rows after the current row within the partition.
NTILEDivide rows in a partition as equally as possible and assign each row an integer starting from 1 to the argument value.
NTH_VALUEReturn a value evaluated against the nth row in an ordered partition.
PERCENT_RANKReturn the relative rank of the current row (rank-1) / (total rows – 1)
RANKRank the current row within its partition with gaps.
ROW_NUMBERNumber the current row within its partition starting from 1.

ROW_NUMBER

Blog Categories


  • All
  • Uncategorized

  • Recently added ...


  • Advanced MySQL