Ichki so‘rov va oyna funksiyalari

Ichki so‘rov

Ichki so‘rov — boshqa so‘rov ichidagi SELECT. Natijasi qiymat, ro‘yxat yoki jadval bo‘lishi mumkin.

-- o‘rtachadan yuqori maosh
SELECT name, salary FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- buyurtma bergan mijozlar
SELECT name FROM customers
WHERE id IN (SELECT customer_id FROM orders);

-- har bo‘lim rekordchisi (bog‘liq ichki so‘rov)
SELECT name, department_id, salary FROM employees e
WHERE salary = (SELECT MAX(salary) FROM employees WHERE department_id = e.department_id);

Ko‘p qadamli so‘rovlarni WITH (CTE) bilan o‘qilishi oson qilib yozing:

WITH totals AS (SELECT customer_id, SUM(amount) AS s FROM orders GROUP BY customer_id)
SELECT * FROM totals WHERE s > 100000;

Oyna funksiyalari

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).