دوال النوافذ: الترتيب والمجاميع المتراكمة

SELECT name, category_id, price,       round(avg(price) OVER (PARTITION BY category_id), 2) AS متوسط_التصنيف,       price - round(avg(price) OVER (PARTITION BY category_id), 2) AS الفرقFROM productsWHERE category_id IN (1, 2)ORDER BY category_id, price DESC;

SELECT c.name, count(o.id) AS orders,       row_number() OVER (ORDER BY count(o.id) DESC, c.id) AS row_number,       rank()       OVER (ORDER BY count(o.id) DESC)       AS rank,       dense_rank() OVER (ORDER BY count(o.id) DESC)       AS dense_rankFROM customers c LEFT JOIN orders o ON o.customer_id = c.idGROUP BY c.id, c.nameORDER BY row_number;

SELECT category_id, name, price FROM (  SELECT category_id, name, price,         row_number() OVER (PARTITION BY category_id ORDER BY price DESC) AS rn  FROM products) tWHERE rn = 1ORDER BY category_id;

WITH daily AS (  SELECT o.order_date AS day, sum(i.qty * i.unit_price) AS revenue  FROM orders o JOIN order_items i ON i.order_id = o.id  WHERE o.status <> 'cancelled'  GROUP BY o.order_date)SELECT day, revenue,       sum(revenue) OVER (ORDER BY day)  AS متراكم,       revenue - lag(revenue) OVER (ORDER BY day) AS عن_اليوم_السابق,       round(avg(revenue) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 0) AS متوسط_آخر_3FROM daily ORDER BY day;