数据库中的窗口函数

CREATE TABLE orders
(
    order_id    SERIAL PRIMARY KEY,
    customer_id INT            NOT NULL,
    order_date  DATE           NOT NULL,
    amount      NUMERIC(10, 2) NOT NULL,
    category    VARCHAR(20)    NOT NULL
);

-- 插入模拟数据（3个客户，跨3个月，含同金额并列场景）
INSERT INTO orders (customer_id, order_date, amount, category)
VALUES (1001, '2025-01-05', 200.00, '电子'),
       (1001, '2025-01-18', 350.00, '电子'),
       (1001, '2025-02-10', 350.00, '家居'),
       (1001, '2025-03-01', 500.00, '电子'),
       (1002, '2025-01-10', 150.00, '家居'),
       (1002, '2025-02-14', 600.00, '电子'),
       (1002, '2025-02-28', 400.00, '家居'),
       (1003, '2025-01-20', 800.00, '电子'),
       (1003, '2025-02-05', 300.00, '家居'),
       (1003, '2025-03-15', 900.00, '电子');

function_name() OVER (
    [PARTITION BY column]   -- 分组：将数据划分为多个分区（类似 GROUP BY，但不折叠行）
    [ORDER BY column]       -- 排序：定义窗口内的计算顺序
    [frame_clause]          -- 帧范围：精确定义"窗口"包含哪些行
)

SELECT customer_id,
       order_date,
       amount,
       RANK() OVER w       AS rank_jump,
       DENSE_RANK() OVER w AS rank_dense, -- 推荐写
       ROW_NUMBER() OVER w AS row_num -- 强制唯一: 1,2,3
FROM orders
WINDOW w AS (PARTITION BY customer_id ORDER BY amount DESC);

SELECT customer_id,
       order_date,
       amount,
-- 截至当前订单的累计消费 可以看出是每次递增
       SUM(amount) OVER w    AS running_total,
-- 上一笔订单金额 LAG()取前1个的值
       LAG(amount, 1) OVER w AS prev_amount,
-- 环比增长率(%) = (本期金额 - 上期金额) / 上期金额 * 100
       ROUND(
               (amount - LAG(amount, 1) OVER w) / NULLIF(LAG(amount, 1) OVER w, 0) * 100
           , 2)              AS mom_growth_pct
FROM orders
WINDOW w AS (PARTITION BY customer_id ORDER BY order_date)
ORDER BY customer_id, order_date;

-- 使用 ROW_NUMBER() 严格保证只有两条数据, RANK()不是严格递增
SELECT *,
       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rn
FROM orders;

-- 最后结果
WITH ranked AS (SELECT *,
                       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rn
                FROM orders)
SELECT customer_id, order_date, amount, category
FROM ranked
WHERE rn <= 2
ORDER BY customer_id, amount DESC;

SELECT customer_id,
       order_date,
       amount,
       SUM(amount) OVER (PARTITION BY customer_id ORDER BY amount DESC)     AS customer_total1,
       SUM(amount) OVER (PARTITION BY customer_id)                          AS customer_total2, -- 注意：无 ORDER BY = 整个分区
       ROUND(amount / SUM(amount) OVER (PARTITION BY customer_id) * 100, 2) AS pct_of_customer
FROM orders
ORDER BY customer_id, amount DESC;