window-functions

Oyna funksiyalari qatorlarni guruhlamasdan, har qator yonida guruh bo‘yicha hisob qiladi.

-- bo‘lim ichida maosh bo‘yicha o‘rin
SELECT name, department_id, salary,
       RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk
FROM employees;

-- har bo‘limda top-2
SELECT * FROM (
    SELECT name, department_id, salary,
           DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS r
    FROM employees
) WHERE r <= 2;

-- yig‘ilib boruvchi yig‘indi va oldingi qiymat
SELECT day, amount,
       SUM(amount) OVER (ORDER BY day) AS running,
       LAG(amount) OVER (ORDER BY day) AS prev
FROM sales;

ROW_NUMBER — tenglarga ham turli raqam, RANK — teng o‘rin va keyin bo‘shliq (1, 1, 3), DENSE_RANK — bo‘shliqsiz (1, 1, 2).