Login
SQL – Postgres – Few Advance Topics
The order of execution in a SQL query:
FROM and/or JOIN
WHERE
GROUP BY
HAVING
SELECT
DISTINCT
ORDER BY
LIMIT nad/or OFFSET
Command Types:
References : Aysha Beevi
CAST()
CAST is used to typecast or we can use ::target data type.
SELECT ‘The current date is: ‘ || CURRENT_DATE::TEXT;
SELECT ‘2024-12-21’::DATE::TEXT;
SELECT CAST(‘2024-12-21’ AS DATE);
|| –> Concatenation operator
DATE functions:
SELECT CURRENT_DATE; — Output: 2024-12-21
SELECT CURRENT_TIME; — Output: 09:15:34.123456+05:30
SELECT NOW(); — Output: 2024-12-21 09:15:34.123456+05:30
SELECT AGE(‘2020-01-01’, ‘2010-01-01’); — Output: 10 years 0 mons 0 days
SELECT AGE(‘1990-05-15’); — Output: 34 years 7 mons 6 days (calculated from NOW())
SELECT EXTRACT(YEAR FROM NOW()); — Output: 2024
SELECT EXTRACT(MONTH FROM CURRENT_DATE); — Output: 12
SELECT EXTRACT(DAY FROM TIMESTAMP ‘2024-12-25 10:15:00’); — Output: 25
The DATE_TRUNC() function truncates a date or timestamp to the specified precision. This means it “resets” smaller parts of the date/time to their starting values.
SELECT DATE_TRUNC(‘month’, TIMESTAMP ‘2024-12-21 10:45:30’);
— Output: 2024-12-01 00:00:00 –> The ‘month’ precision resets the day to the 1st, and the time to 00:00:00.
SELECT DATE_TRUNC(‘year’, TIMESTAMP ‘2024-12-21 10:45:30’);
— Output: 2024-01-01 00:00:00
SELECT DATE_TRUNC(‘day’, TIMESTAMP ‘2024-12-21 10:45:30’);
— Output: 2024-12-21 00:00:00
SELECT NOW() + INTERVAL ‘1 year’;
— Output: Current timestamp + 1 year
SELECT CURRENT_DATE – INTERVAL ’30 days’;
— Output: Today’s date – 30 days
SELECT NOW() + INTERVAL ‘2 hours’;
— Output: Current timestamp + 2 hours
SELECT NOW() + INTERVAL ‘1 year’ + INTERVAL ‘3 months’ – INTERVAL ’15 days’;
Window Functions
This is the function that will operate over the specified window. Common window functions include ROW_NUMBER(), RANK(), SUM(), AVG(), etc
.PARTITION BY: (Optional) Divides the result set into partitions to which the window function is applied. Each partition is processed separately.ORDER BY: (Optional) Orders the rows in each partition before the window function is applied.
window_function() OVER (--RANK() or SUM() etc. can come in window_function
PARTITION BY column_name(s)
ORDER BY column_name(s)
);
SELECT
department_id,
employee_id,
salary,
SUM(salary) OVER (PARTITION BY department_id ORDER BY salary) AS running_total
FROM employees;
CURSOR:
DO $$
DECLARE
emp_name VARCHAR;
emp_salary DECIMAL;
emp_cursor CURSOR FOR SELECT name, salary FROM employees;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO emp_name, emp_salary;
EXIT WHEN NOT FOUND; — Exit the loop when no rows are left
RAISE NOTICE ‘Employee: %, Salary: %’, emp_name, emp_salary;
END LOOP;
CLOSE emp_cursor;
Basic Data Types in PostgreSQL
TEXT, VARCHAR, CHAR: Working with strings. INTEGER, BIGINT, NUMERIC: Handling numbers. DATE, TIMESTAMP: Date and time handling.
OVER CLAUSE
In PostgreSQL, the OVER() clause is used in window functions to define a window of rows over which a function operates. Just create a serial number (Row_number) from 1 (Rows are already ordered by salary desc)
SELECT name, ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employees
WHERE row_num <= 5;
RANK()
Parition the table records based on the dept id, then inside each partition order by salary desc with rank 1,2,3… – In RANK() if same salary then RANK repeats.
SELECT department_id, name, salary,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
FROM employees
Output:
department_id name salary rank
101 Charlie 70,000 1
101 Alice 50,000 2
101 Frank 50,000 2
102 Eve 75,000 1
102 Bob 60,000 2
103 David 55,000 1
- Divides employees into 3 equal salary buckets (quartiles).
SELECT id, name, salary,
NTILE(3) OVER (ORDER BY salary DESC) AS quartile
FROM employees;
id name salary quartile
5 Eve 75,000 1
3 Charlie 70,000 1
2 Bob 60,000 2
4 David 55,000 2
1 Alice 50,000 3
6 Frank 50,000 3 - Retrieves the first name in each department based on descending salary.
SELECT department_id, name, salary,
FIRST_VALUE(name) OVER (PARTITION BY department_id ORDER BY salary DESC) AS top_earner
FROM employees;
Output:
department_id name salary top_earner
101 Charlie 70,000 Charlie
101 Alice 50,000 Charlie
101 Frank 50,000 Charlie
102 Eve 75,000 Eve
102 Bob 60,000 Eve
103 David 55,000 David
First from table will be taken, then WHERE condition will be applied
- In the WHERE clause directly you cannot call the RANK(), it should be stored in result set, from there only we can call it. So only RANK() will get executed ie Windows CTE (Common Table Expression), that’s why first the CTE will get executed and stored in a temp result set, then SELECT from that result set.
- Below we gave in the subquery, so it will get executed and then that value is getting used by the outer query.
In each dept top earner name with his name and salary (consider the above table employees)
SELECT department_id, name, salary
FROM (
SELECT department_id, name, salary,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
FROM employees
) ranked_employees
WHERE rank = 1;
department_id name salary
101 Charlie 70,000
102 Eve 75,000
103 David 55,000
Resultset – here RankedSalaries is Resultset
WITH RankedSalaries AS (
SELECT salary, RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees
)
SELECT salary
FROM RankedSalaries WHERE rank = 2;
Here, RankedSalaries is a temporary result set or CTE (Common Table Expression)
Reference: Learnt from ChatGPT and Picture from Ms.Aysha
