Оконные функции SQL: полное руководство с примерами

SELECT
  user_id,
  order_date,
  amount,
  SUM(amount) OVER (
    PARTITION BY user_id
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM orders;

SELECT
  campaign_name,
  ad_spend,
  conversions,
  ROW_NUMBER() OVER (ORDER BY conversions DESC) AS rn,
  RANK() OVER (ORDER BY conversions DESC) AS rnk,
  DENSE_RANK() OVER (ORDER BY conversions DESC) AS dense_rnk
FROM campaigns
WHERE month = '2026-03';

SELECT
  event_date,
  sessions,
  conversions,
  LAG(conversions, 1) OVER (ORDER BY event_date) AS prev_day_conv,
  conversions - LAG(conversions, 1) OVER (ORDER BY event_date) AS conv_diff,
  ROUND(
    (conversions - LAG(conversions, 1) OVER (ORDER BY event_date))::numeric
    / NULLIF(LAG(conversions, 1) OVER (ORDER BY event_date), 0) * 100,
    1
  ) AS conv_change_pct
FROM daily_metrics
WHERE event_date >= CURRENT_DATE - INTERVAL '30 days';

SELECT
  user_id,
  total_revenue,
  last_order_date,
  order_count,
  NTILE(5) OVER (ORDER BY total_revenue DESC) AS revenue_quintile,
  NTILE(5) OVER (ORDER BY last_order_date DESC) AS recency_quintile,
  NTILE(5) OVER (ORDER BY order_count DESC) AS frequency_quintile
FROM user_metrics;

WITH user_cohorts AS (
  SELECT
    user_id,
    MIN(DATE_TRUNC('month', first_visit_date)) AS cohort_month
  FROM users
  GROUP BY user_id
),
monthly_activity AS (
  SELECT
    uc.cohort_month,
    DATE_TRUNC('month', e.event_date) AS activity_month,
    COUNT(DISTINCT e.user_id) AS active_users
  FROM events e
  JOIN user_cohorts uc ON e.user_id = uc.user_id
  GROUP BY 1, 2
)
SELECT
  cohort_month,
  activity_month,
  active_users,
  FIRST_VALUE(active_users) OVER (
    PARTITION BY cohort_month
    ORDER BY activity_month
  ) AS cohort_size,
  ROUND(
    active_users::numeric /
    FIRST_VALUE(active_users) OVER (
      PARTITION BY cohort_month
      ORDER BY activity_month
    ) * 100, 1
  ) AS retention_pct
FROM monthly_activity
ORDER BY cohort_month, activity_month;