窗口函数

-- 聚合:每个城市的用户数(折叠为 4 行)
SELECT city, COUNT(*) FROM users GROUP BY city;

-- 窗口函数:每个用户保留,同时显示所在城市的用户数(仍然 8 行)
SELECT username, city,
       COUNT(*) OVER (PARTITION BY city) AS city_user_count
FROM users;

函数名(...) OVER (
  [PARTITION BY 分组列]       -- 可选:按什么分窗口
  [ORDER BY 排序列 [ASC|DESC]] -- 可选:窗口内排序
  [帧定义]                    -- 可选:计算范围
)

-- 全部商品按价格降序排名
SELECT name, price,
       ROW_NUMBER() OVER (ORDER BY price DESC) AS rn,
       RANK()       OVER (ORDER BY price DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY price DESC) AS drnk
FROM products;

-- 每个分类内,按价格降序排名
SELECT c.name AS category, p.name AS product, p.price,
       RANK() OVER (PARTITION BY c.name ORDER BY p.price DESC) AS rank_in_cat
FROM products p
JOIN categories c ON c.id = p.category_id;

-- 每个分类中最贵的 2 个商品
SELECT * FROM (
  SELECT c.name AS category, p.name AS product, p.price,
         ROW_NUMBER() OVER (PARTITION BY c.id ORDER BY p.price DESC) AS rn
  FROM products p
  JOIN categories c ON c.id = p.category_id
) ranked
WHERE rn <= 2;

-- 每个用户的订单,显示上一单金额和环比变化
SELECT
  u.username,
  o.created_at::date AS order_date,
  o.total,
  LAG(o.total) OVER (PARTITION BY o.user_id ORDER BY o.created_at) AS prev_total,
  o.total - LAG(o.total) OVER (PARTITION BY o.user_id ORDER BY o.created_at) AS diff
FROM orders o
JOIN users u ON u.id = o.user_id
ORDER BY u.username, o.created_at;

-- 月度收入环比增长率(用 LAG 改写上一篇的自连接方案)
WITH monthly AS (
  SELECT TO_CHAR(created_at, 'YYYY-MM') AS month,
         SUM(total) AS revenue
  FROM orders
  GROUP BY TO_CHAR(created_at, 'YYYY-MM')
)
SELECT month, revenue,
       LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
       ROUND(
         (revenue - LAG(revenue) OVER (ORDER BY month))
         / LAG(revenue) OVER (ORDER BY month) * 100, 1
       ) AS growth_pct
FROM monthly;

-- 按时间顺序显示订单的累计金额
SELECT id, created_at::date, total,
       SUM(total) OVER (ORDER BY created_at) AS running_total
FROM orders
WHERE status = 'done'
ORDER BY created_at;

-- 每行显示该商品价格占同分类总价的百分比
SELECT c.name AS category, p.name, p.price,
       SUM(p.price) OVER (PARTITION BY c.id) AS cat_total,
       ROUND(p.price / SUM(p.price) OVER (PARTITION BY c.id) * 100, 1) AS pct
FROM products p
JOIN categories c ON c.id = p.category_id
ORDER BY category, pct DESC;

-- 每个用户的订单序号(第几单)
SELECT u.username, o.created_at::date, o.total,
       ROW_NUMBER() OVER (PARTITION BY o.user_id ORDER BY o.created_at) AS order_seq
FROM orders o
JOIN users u ON u.id = o.user_id
ORDER BY u.username, order_seq;

-- 帧语法
ROWS BETWEEN 起点 AND 终点

-- 常用起点/终点:
--   UNBOUNDED PRECEDING    - 窗口第一行
--   N PRECEDING            - 当前行往前 N 行
--   CURRENT ROW            - 当前行
--   N FOLLOWING            - 当前行往后 N 行
--   UNBOUNDED FOLLOWING    - 窗口最后一行

-- 3 天移动平均(当前行 + 前 2 行)
WITH daily_revenue AS (
  SELECT created_at::date AS day, SUM(total) AS revenue
  FROM orders GROUP BY created_at::date
)
SELECT day, revenue,
       ROUND(AVG(revenue) OVER (
         ORDER BY day
         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ), 2) AS moving_avg_3
FROM daily_revenue
ORDER BY day;

-- 累计到当前行的最大订单金额
SELECT id, created_at::date, total,
       MAX(total) OVER (ORDER BY created_at ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_max
FROM orders ORDER BY created_at;

SELECT username, city, balance,
       RANK() OVER w AS balance_rank,
       SUM(balance) OVER w AS running_balance,
       balance - LAG(balance) OVER w AS diff_from_prev
FROM users
WINDOW w AS (PARTITION BY city ORDER BY balance DESC)
ORDER BY city, balance_rank;

-- 每个用户只保留最近一笔订单
SELECT * FROM (
  SELECT o.*,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
  FROM orders o
) t WHERE rn = 1;

-- 思路:日期减去行号 = 同一个常数 → 说明是连续的
-- 假设有 login_log(user_id, login_date) 表
WITH numbered AS (
  SELECT user_id, login_date,
         login_date - (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date))::int AS grp
  FROM login_log
)
SELECT user_id, MIN(login_date) AS streak_start,
       MAX(login_date) AS streak_end,
       COUNT(*) AS streak_days
FROM numbered
GROUP BY user_id, grp
HAVING COUNT(*) >= 3;  -- 连续登录 3 天以上

-- 每个商品的销售额占比和累计占比
WITH product_revenue AS (
  SELECT p.name, SUM(oi.quantity * oi.unit_price) AS revenue
  FROM order_items oi
  JOIN products p ON p.id = oi.product_id
  GROUP BY p.id, p.name
)
SELECT name, revenue,
       ROUND(revenue / SUM(revenue) OVER () * 100, 1) AS pct,
       ROUND(SUM(revenue) OVER (ORDER BY revenue DESC) / SUM(revenue) OVER () * 100, 1) AS cum_pct
FROM product_revenue
ORDER BY revenue DESC;