📚 Tutorial💾 DatabasePostgreSQLAdvance - Advance Query

Advance Technique

UNION ALL / UNION

Uset to combind two same table structures records as one table

  • UNION : No duplicate values (IF two records are the same)
  • UNION ALL : Return duplicate values

UNION ALL : Return duplicate values (Even are the same)

SELECT names, pw
FROM admin_user 
UNION ALL
SELECT names, pw
FROM normal_user

UNION : No duplicate values (IF names and pw are the same)

SELECT names, pw
FROM admin_user 
UNION
SELECT names, pw
FROM normal_user

Advance Usage

SELECT names, COUNT(*) as dup_names
FROM(
     SELECT names, pw
     FROM admin_user 
     UNION
     SELECT names, pw
     FROM normal_user 
)
GROUP BY names

OVER(PARTITION BY <col_name>)

Ref: https://www.postgresql.org/docs/current/tutorial-window.html

A Window Functions For Looping / Sliding data, usually combind with RANK() / SUM() / AVG()

  • RANK() Usage

Using each depname to run rank(), by biggest salary.

SELECT depname, empno, salary,
       rank() OVER (PARTITION BY depname ORDER BY salary DESC)
FROM empsalary;
  depname  | empno | salary | rank
-----------+-------+--------+------
 develop   |     8 |   6000 |    1
 develop   |    10 |   5200 |    2
 develop   |    11 |   5200 |    2
 develop   |     9 |   4500 |    4
 develop   |     7 |   4200 |    5
 personnel |     2 |   3900 |    1
 personnel |     5 |   3500 |    2
 sales     |     1 |   5000 |    1
 sales     |     4 |   4800 |    2
 sales     |     3 |   4800 |    2
(10 rows)
  • SUM() Usage

To have a cumulative for each salary sections.

SELECT salary, sum(salary) OVER (ORDER BY salary) FROM empsalary;
 salary |  sum
--------+-------
   3500 |  3500
   3900 |  7400
   4200 | 11600
   4500 | 16100
   4800 | 25700
   4800 | 25700
   5000 | 30700
   5200 | 41100
   5200 | 41100
   6000 | 47100
(10 rows)

Using each turn value, to have a cumulative value.

SELECT person_name, 
weight, 
sum(weight) OVER (ORDER BY turn) as total,
turn
FROM Queue
| person_name | weight | total | turn |
| ----------- | ------ | ----- | ---- |
| Alice       | 250    | 250   | 1    |
| Alex        | 350    | 600   | 2    |
| John Cena   | 400    | 1000  | 3    |
| Marie       | 200    | 1200  | 4    |
| Bob         | 175    | 1375  | 5    |
| Winston     | 500    | 1875  | 6    |
Basic - Get StartedBasic - Get Started