"""03_window_functions.py —— 窗口函数实战

演示：
  1) 每个类目的商品价格排名（ROW_NUMBER / RANK / DENSE_RANK / NTILE）
  2) 销售额的 3 个月移动平均 + 累计 + 同比
  3) 每个用户最近一笔订单（DISTINCT ON）
"""
from __future__ import annotations

from _common import connect, print_table, section


def rank_demo(cur) -> None:
    section("1. 每个类目的商品价格 TOP 3（4 种排名对比）")
    cur.execute(
        """
        WITH t AS (
          SELECT category, name, price,
                 ROW_NUMBER() OVER w AS rn,
                 RANK()       OVER w AS rk,
                 DENSE_RANK() OVER w AS drk,
                 NTILE(4)     OVER w AS bucket
          FROM ch5_products
          WINDOW w AS (PARTITION BY category ORDER BY price DESC)
        )
        SELECT category, name, price, rn, rk, drk, bucket
        FROM t WHERE rn <= 3
        ORDER BY category, rn
        """
    )
    print_table(
        cur.fetchall(),
        ["category", "name", "price", "ROW_NUMBER", "RANK", "DENSE_RANK", "NTILE(4)"],
    )


def moving_avg(cur) -> None:
    section("2. 月销售 3 个月移动平均 + 累计 + 同比")
    cur.execute(
        """
        SELECT
          month,
          revenue,
          ROUND(AVG(revenue) OVER (
            ORDER BY month
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
          ), 2) AS ma3,
          SUM(revenue) OVER (ORDER BY month) AS running_total,
          LAG(revenue, 12) OVER (ORDER BY month) AS same_month_last_year,
          ROUND(
            ((revenue - LAG(revenue, 12) OVER (ORDER BY month))
              / NULLIF(LAG(revenue, 12) OVER (ORDER BY month), 0) * 100)::NUMERIC,
            2
          ) AS yoy_pct
        FROM ch5_monthly_sales
        ORDER BY month
        """
    )
    print_table(
        cur.fetchall(),
        ["month", "revenue", "MA3", "cum", "YoY基数", "YoY%"],
    )


def latest_order_per_user(cur) -> None:
    section("3. 每个用户最近一笔订单（DISTINCT ON）")
    cur.execute(
        """
        SELECT DISTINCT ON (user_id)
               user_id, id AS order_id, amount, status, created_at
        FROM ch5_orders
        ORDER BY user_id, created_at DESC
        LIMIT 10
        """
    )
    print_table(
        cur.fetchall(),
        ["user_id", "order_id", "amount", "status", "created_at"],
    )


def main() -> None:
    with connect() as conn:
        with conn.cursor() as cur:
            rank_demo(cur)
            moving_avg(cur)
            latest_order_per_user(cur)


if __name__ == "__main__":
    main()