高级 SQL

CREATE TABLE plays (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    show_name  text NOT NULL,
    city       text NOT NULL,
    finished   boolean NOT NULL,
    played_at  timestamptz NOT NULL
);

INSERT INTO plays (show_name, city, finished, played_at) VALUES
('代码夜话', '杭州', true,  '2026-09-01 08:10'),
('代码夜话', '杭州', true,  '2026-09-01 21:40'),
('代码夜话', '上海', false, '2026-09-02 12:00'),
('代码夜话', '深圳', true,  '2026-09-03 19:30'),
('早班机',   '北京', false, '2026-09-01 07:00'),
('早班机',   '北京', true,  '2026-09-02 07:05'),
('早班机',   '上海', true,  '2026-09-02 07:40'),
('闲话芯片', '杭州', false, '2026-09-03 22:10');

SELECT show_name,
       COUNT(*) AS plays,
       COUNT(*) FILTER (WHERE finished) AS finished_plays
FROM plays
GROUP BY show_name;

-- 混用：总数、完成数、完成单的平均收听时长条件（示意）
SELECT show_name,
       COUNT(*) FILTER (WHERE finished)                        AS fin,
       COUNT(*) FILTER (WHERE NOT finished)                    AS dropped,
       MIN(played_at) FILTER (WHERE finished)                  AS first_finish
FROM plays
GROUP BY show_name;

SELECT DISTINCT ON (show_name) show_name, played_at, city
FROM plays
ORDER BY show_name, played_at DESC;

SELECT s.show_name, top.city, top.cnt
FROM (SELECT DISTINCT show_name FROM plays) s
CROSS JOIN LATERAL (
    SELECT city, COUNT(*) AS cnt
    FROM plays p
    WHERE p.show_name = s.show_name     -- 引用了外层的列
    GROUP BY city
    ORDER BY cnt DESC
    LIMIT 3
) top;

WITH daily AS (
    SELECT date_trunc('day', played_at)::date AS d,
           COUNT(*) AS plays
    FROM plays
    GROUP BY 1
)
SELECT d, plays,
       plays - LAG(plays) OVER (ORDER BY d) AS day_diff
FROM daily
ORDER BY d;

WITH daily AS MATERIALIZED (SELECT ...) SELECT ... FROM daily a JOIN daily b ...;
-- 多次引用的 CTE 本来就只算一次；MATERIALIZED 用于单引用但想挡住优化器重写的场合

CREATE TABLE staff (id int PRIMARY KEY, name text, manager_id int REFERENCES staff(id));
INSERT INTO staff VALUES (1,'老板',NULL),(2,'内容负责人',1),(3,'主播A',2),(4,'主播B',2);

WITH RECURSIVE org AS (
    SELECT id, name, manager_id, 1 AS depth
    FROM staff WHERE manager_id IS NULL
  UNION ALL
    SELECT s.id, s.name, s.manager_id, org.depth + 1
    FROM staff s JOIN org ON s.manager_id = org.id
)
SELECT * FROM org ORDER BY depth;

SELECT d::date AS day,
       COALESCE(cnt, 0) AS plays
FROM generate_series('2026-09-01'::timestamptz,
                     '2026-09-30'::timestamptz,
                     interval '1 day') g(d)
LEFT JOIN (
    SELECT date_trunc('day', played_at) AS pd, COUNT(*) AS cnt
    FROM plays GROUP BY 1
) t ON t.pd = g.d
ORDER BY day;

SELECT * FROM (
  SELECT dept, name, salary,
    ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
  FROM employees
) t WHERE rn <= 3;

-- 第一步: 日期减去行号得到"分组键" grp。
-- 同一段连续日期的 grp 恒定（日期每天 +1，行号也 +1，相减不变）。
-- 注意: 必须先 DISTINCT 去重（同一天多次登录只算一天）。
SELECT user_id, COUNT(*) AS consecutive_days
FROM (
  SELECT user_id, login_date,
    login_date - (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date))::int AS grp
  FROM (SELECT DISTINCT user_id, login_date::date FROM user_logins) t1
) t2
GROUP BY user_id, grp
HAVING COUNT(*) >= 7;

WITH RECURSIVE bom AS (
  SELECT parent_id, child_id, quantity, 1 AS depth
  FROM bill_of_materials
  WHERE parent_id = 'PRODUCT-A'

  UNION ALL

  SELECT b.parent_id, m.child_id, b.quantity * m.quantity, b.depth + 1
  FROM bom b
  JOIN bill_of_materials m ON b.child_id = m.parent_id
)
SELECT child_id, SUM(quantity) AS total_qty, MAX(depth) AS max_depth
FROM bom
GROUP BY child_id;

-- ROLLUP: 层级汇总（region,product → region → 总计）
SELECT region, product, SUM(sales) AS total
FROM sales_data
GROUP BY ROLLUP (region, product);

-- GROUPING SETS: 自定义要哪些组合
SELECT region, product, SUM(sales) AS total
FROM sales_data
GROUP BY GROUPING SETS ((region, product), (region), (product), ());

-- GROUPING(): 区分"这行是数据还是小计"（NULL 行是汇总行）
SELECT region, product,
  GROUPING(region) AS is_region_total,
  SUM(sales) AS total
FROM sales_data
GROUP BY ROLLUP (region, product);