Lab 3

backback

Poprawiony skrypt zwierzeta.sql

zwierzeta.sql

Zaawansowane grupowanie - PARTITION BY & window functions

Grupowanie za pomocą GROUP BY pozwala nam zagregować rekordy w grupach i wypisać wyniki tych agregatów dla każdej grupy. Tracimy w ten sposób informacje o poszczególnych rekordach. Co jednak gdybyśmy chcieli policzyć coś w obrębie zgrupowań, ale wypisać wyniki dla wszystkich rekordów? Przykładowo, chcemy dostać wszystkich pracowników i średnią zarobków z departamentu, w którym pracują.

Możemy to zrobić klasycznie, korzystając z podzapytania:

WITH dept_avg AS (
  SELECT e.deptno, AVG(e.sal) AS sal
    FROM emp e 
  GROUP BY e.deptno
)
SELECT e.*, d.sal AS dept_avg
  FROM emp e
  JOIN dept_avg d
    ON e.deptno = d.deptno

Możemy jednak wykonać to zapytanie efektywniej, korzystając z konstrukcji PARTITION BY:

SELECT
    e.*,
    AVG(e.sal) OVER (PARTITION BY e.deptno)
  FROM emp e

Składnia jest następująca:

function(args) OVER (window_definition)

Gdzie w window_definition możemy wyspecyfikować, kolejno:

  1. Po czym pogrupować okienka do których stosujemy aggregate_function za pomocą PARTITION BY expression.
  2. Po czym posortować wyniki w obrębie okienka, jeśli kolejność wierszy jest ważna, za pomocą ORDER BY expression [ASC|DESC].
  3. Które wiersze z okienka wybrać do agregacji, używając konstrukcji ROWS BETWEEN from AND to.

Dobrym przykładem na ROWS jest następujący problem: załóżmy, że chcemy policzyć sumę prefiksową pewnych wartości, w naszym przypadku zarobków pracowników. To jest chcemy posortować pracowników po zarobkach i dla ii-tego z nich wypisać sumę zarobków jego i wszystkich j<ij < i. Da się to zrobić dość skomplikowanym JOIN-em :

SELECT 
    e.empno, 
    e.ename, 
    e.sal, 
    SUM(e2.sal) AS cumulative_sal
  FROM emp e
  JOIN emp e2
    ON e.sal > e2.sal OR (e.sal = e2.sal AND e.empno >= e2.empno)
GROUP BY e.empno, e.ename, e.sal
ORDER BY cumulative_sal

Przy użyciu window functions da się to zrobić prościej:

SELECT
    e.empno,
    e.ename,
    e.sal,
    SUM(e.sal) OVER (
      ORDER BY e.sal, e.empno 
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_sum
FROM emp e
ORDER BY cumulative_sum

Napis w klauzuli OVER mówi: posortuj wiersze po e.sal, e.empno i następnie w tej kolejności dla każdego rozważ wszystkie poprzednie i jego samego.

Plan wykonania tego zapytania będzie o wiele prostszy - łatwo wymyślić prostą pętlę, która liczy to zapytanie, za to poprzednie będzie musiało rzeczywiście wykonać kwadratowego JOINa.

Żeby zobrazować działanie PARTITION BY możemy skomplikować nasze zapytanie – nadal interesuje nas suma prefiksowa, ale tylko w obrębie departamentu.

SELECT
    e.deptno,
    e.empno,
    e.ename,
    e.sal,
    SUM(e.sal) OVER (
      PARTITION BY e.deptno
      ORDER BY e.sal, e.empno 
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_sum
FROM emp e
ORDER BY e.deptno, cumulative_sum

To zapytanie robi dokładnie to samo, ale niezależnie dla każdego departamentu wyznaczonego przez e.deptno.

W ROWS można podać:

Ćwiczenia

  1. Przepisz zapytanie na sumę prefiksową z JOIN-ami tak, aby liczyło sumę oddzielnie dla każdego departamentu.
  2. Przepisz zadanie 7 z labów używając PARTITION BY aby uniknąc JOIN-a. Dla przypomnienia: wypisz imiona oraz pensje wszystkich pracowników którzy nie mają zmiennika (osoby na tym samym stanowisku w tym samym departamencie) i posortuj ich według pensji malejąco.
  3. Dla każdego pracownika policz średnią zarobków w departamencie, w którym pracuje, a także jaka byłaby ta średnia, gdyby go zwolnić (PostgreSQL udostępnia konstrukcję EXCLUDE CURRENT ROW, Oracle SQL chyba nie, więc trzeba sprytnie wykorzystać dwa podobne okienka) (może przydać się funkcja COALESCE(args), która zwraca pierwszy argument nie będący NULLem, np. COALESCE(NULL, 0) zwraca 0.
  4. Stwórz doubly linked listę wszystkich pracowników posortowanych po pensji, t.j. dla każdego pracownika wypisz poprzednika i następnika na liście posortowanej po pensji. Na przykład, zakładając że mamy dane
empno, sal
1,     1000
2,     500
3,     1500
4,     1200

wynikiem powinno być

empno, next, prev
2,     1,    NULL
1,     4,    2
4,     3,    1
3,     NULL, 4

Rozwiązania

SELECT 
    e.empno,
    e.deptno,
    e.ename,
    e.sal, 
    SUM(e2.sal) AS cumulative_sal
  FROM emp e
  JOIN emp e2
    ON e.deptno = e2.deptno AND (e.sal > e2.sal OR (e.sal = e2.sal AND e.empno >= e2.empno))
GROUP BY e.empno, e.deptno, e.ename, e.sal
ORDER BY e.deptno, cumulative_sal;

WITH emp_replacements AS (
  SELECT 
      e.ename,
      e.sal,
      COUNT(e.empno) OVER (PARTITION BY e.deptno, e.job) AS replacement_count
    FROM emp e
)
SELECT e.ename, e.sal
  FROM emp_replacements e
  WHERE e.replacement_count = 1
ORDER BY e.sal DESC;

WITH emp_dept_statistics AS (
  SELECT 
      e.empno,
      e.ename,
      e.deptno,
      e.sal,
      AVG(e.sal) OVER (PARTITION BY e.deptno) AS dept_avg,
      COUNT(e.empno) OVER (PARTITION BY e.deptno) AS dept_count,
      SUM(e.sal) OVER (PARTITION BY e.deptno ORDER BY e.empno ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS pred_sum,
      SUM(e.sal) OVER (PARTITION BY e.deptno ORDER BY e.empno ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) AS succ_sum
    FROM emp e
)
SELECT 
    e.empno,
    e.ename,
    e.deptno,
    e.sal,
    e.dept_avg,
    e.pred_sum,
    e.succ_sum,
    e.dept_count,
    (COALESCE(e.pred_sum, 0) + COALESCE(e.succ_sum, 0)) / (e.dept_count - 1) AS dept_avg_without_emp
  FROM emp_dept_statistics e
ORDER BY dept_avg_without_emp;

SELECT 
    e.empno,
    RANK() OVER (ORDER BY e.sal) AS "rank",
    SUM(e.empno) OVER (ORDER BY e.sal, e.empno ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) AS "next",
    SUM(e.empno) OVER (ORDER BY e.sal, e.empno ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS "prev"
  FROM emp e
ORDER BY "rank", e.empno;

-- Elegancko.

SELECT 
    e.empno,
    RANK() OVER (ORDER BY e.sal) AS "rank",
    LEAD(e.empno, 1) OVER (ORDER BY e.sal, e.empno) AS "next",
    LAG(e.empno, 1) OVER (ORDER BY e.sal, e.empno) AS "prev"
  FROM emp e
ORDER BY "rank", e.empno;

backback