function() OVER (
    [PARTITION BY column]    -- أي حقل يتم التقسيم (التجميع) بحسبه
    [ORDER BY column]        -- كيفية الترتيب داخل التقسيم
    [ROWS/RANGE frame]       -- اختياري: تقييد نطاق الصفوف بشكل أكبر
)

-- ROW_NUMBER: تسلسل متزايد بصرامة، بدون تعادل (1, 2, 3, 4)
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC)

-- RANK: يسمح بالتعادل، مع فجوات (1, 2, 2, 4)
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC)

-- DENSE_RANK: يسمح بالتعادل، بدون فجوات (1, 2, 2, 3)
DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC)

-- تقسيم الموظفين إلى 4 مستويات حسب الراتب (أرباع)
NTILE(4) OVER (ORDER BY salary DESC)

-- LAG: الوصول إلى الصف السابق
LAG(salary, 1) OVER (PARTITION BY department_id ORDER BY hire_date)

-- LEAD: الوصول إلى الصف التالي
LEAD(salary, 1) OVER (PARTITION BY department_id ORDER BY hire_date)

-- كل صف يُظهر إجمالي راتب القسم
SUM(salary) OVER (PARTITION BY department_id)

-- كل صف يُظهر متوسط راتب القسم
AVG(salary) OVER (PARTITION BY department_id)

-- كل صف يُظهر عدد موظفي القسم
COUNT(*) OVER (PARTITION BY department_id)

-- PARTITION BY: تحديد نطاق النافذة (مشابه لـ GROUP BY، لكن لا يطيح الصفوف)
-- ORDER BY: الترتيب داخل النافذة، يؤثر على الترتيب والحسابات التراكمية

-- مثال: تجميع حسب القسم، ترتيب حسب الراتب تنازلياً داخل كل قسم
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC)

-- مثال: مجموع تراكمي (مرتب حسب تاريخ التوظيف، تراكم الراتب صف بصف)
SUM(salary) OVER (ORDER BY hire_date)

-- الصيغة العامة للدالة النافذة
function() OVER (
    [PARTITION BY col1, col2]
    [ORDER BY col3 [ASC|DESC]]
    [ROWS BETWEEN ... AND ...]
)

-- الترتيب
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC)
RANK()       OVER (PARTITION BY department_id ORDER BY salary DESC)
DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC)

-- الصفوف المجاورة
LAG(column, offset, default)  OVER (ORDER BY column)
LEAD(column, offset, default) OVER (ORDER BY column)

-- التجميع
SUM(column) OVER (PARTITION BY col)
AVG(column) OVER (PARTITION BY col)
COUNT(*)    OVER (PARTITION BY col)

-- التقسيم إلى دلاء
NTILE(n) OVER (ORDER BY column)

SELECT 
    CONCAT(e.first_name, e.last_name) AS name,
    d.department_name AS department,
    e.salary,
    ROW_NUMBER() OVER (PARTITION BY e.department_id ORDER BY e.salary DESC) AS row_num,
    RANK()       OVER (PARTITION BY e.department_id ORDER BY e.salary DESC) AS rank,
    DENSE_RANK() OVER (PARTITION BY e.department_id ORDER BY e.salary DESC) AS dense_rank
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
ORDER BY d.department_name, salary DESC;

name   | department | salary   | row_num | rank | dense_rank
-------+------------+----------+---------+------+-----------
Zhang Wei | Tech    | 12000.00 |       1 |    1 |         1
Li Na     | Tech    |  9500.00 |       2 |    2 |         2
Wang Qiang| Sales   |  8000.00 |       1 |    1 |         1
Zhao Min  | Sales   | 11000.00 |       2 |    2 |         2
Liu Yang  | Marketing| 7500.00 |       1 |    1 |         1
Chen Jing | NULL    |  9000.00 |       1 |    1 |         1

SELECT 
    CONCAT(e.first_name, e.last_name) AS name,
    d.department_name AS department,
    e.salary,
    ROUND(AVG(e.salary) OVER (PARTITION BY e.department_id), 2) AS dept_avg,
    ROUND(e.salary - AVG(e.salary) OVER (PARTITION BY e.department_id), 2) AS diff_from_avg
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
ORDER BY d.department_name, salary DESC;

name   | department | salary   | dept_avg | diff_from_avg
-------+------------+----------+----------+--------------
Zhang Wei | Tech    | 12000.00 | 10750.00 |      1250.00
Li Na     | Tech    |  9500.00 | 10750.00 |     -1250.00
Zhao Min  | Sales   | 11000.00 |  9500.00 |      1500.00
Wang Qiang| Sales   |  8000.00 |  9500.00 |     -1500.00
Liu Yang  | Marketing|  7500.00|  7500.00 |         0.00
Chen Jing | NULL    |  9000.00 |  9000.00 |         0.00

SELECT 
    o.order_id,
    o.order_date,
    o.total_amount,
    LAG(o.total_amount, 1) OVER (ORDER BY o.order_date) AS prev_order_amount,
    ROUND(
        (o.total_amount - LAG(o.total_amount, 1) OVER (ORDER BY o.order_date)) 
        / LAG(o.total_amount, 1) OVER (ORDER BY o.order_date) * 100, 
    2) AS growth_pct
FROM orders o
WHERE o.status != 'cancelled'
ORDER BY o.order_date;

order_id | order_date | total_amount | prev_order_amount | growth_pct
---------+------------+--------------+-------------------+-----------
    1001 | 2026-01-10 |      2500.00 |              NULL |       NULL
    1002 | 2026-02-15 |      1800.00 |           2500.00 |     -28.00
    1003 | 2026-02-20 |      3200.00 |           1800.00 |      77.78
    1004 | 2026-03-05 |       950.00 |           3200.00 |     -70.31
    1005 | 2026-03-10 |      4100.00 |            950.00 |     331.58
    ...

WITH emp_sales AS (
    SELECT 
        CONCAT(e.first_name, e.last_name) AS name,
        d.department_name AS department,
        COALESCE(SUM(o.total_amount), 0) AS total_sales,
        RANK() OVER (PARTITION BY d.department_id ORDER BY COALESCE(SUM(o.total_amount), 0) DESC) AS dept_rank
    FROM employees e
    LEFT JOIN departments d ON e.department_id = d.department_id
    LEFT JOIN orders o ON e.employee_id = o.employee_id AND o.status != 'cancelled'
    GROUP BY e.employee_id, e.first_name, e.last_name, d.department_id, d.department_name
)
SELECT name, department, total_sales, dept_rank
FROM emp_sales
WHERE dept_rank = 1
ORDER BY total_sales DESC;

SELECT 
    CONCAT(e.first_name, e.last_name) AS name,
    d.department_name AS department,
    e.hire_date,
    e.salary,
    LAG(e.salary, 1) OVER (PARTITION BY e.department_id ORDER BY e.hire_date) AS prev_salary,
    ROUND(e.salary - LAG(e.salary, 1) OVER (PARTITION BY e.department_id ORDER BY e.hire_date), 2) AS salary_change,
    ROW_NUMBER() OVER (PARTITION BY e.department_id ORDER BY e.hire_date) AS hire_order
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
ORDER BY d.department_name, e.hire_date;