PHP小码农

Appearance

排名函数 ​

本节讲解四个排名函数——row_number、rank、dense_rank、ntile——在处理并列值（Tie）时的不同行为，配合 cume_dist 与 percent_rank 完成百分位分析，并给出每个函数的标准结果集对照。学完本节，你将能准确选择排名函数，避免「并列值名次跳跃」这类常见错误。

前置知识

阅读本节前，建议先了解：OVER 子句详解

基础概念 ​

排名函数（Ranking Function） 在分区内按 ORDER BY 的顺序给每一行分配一个名次。四个函数的核心差别在于如何处理并列值，以及名次是否连续。

概念术语说明
排名函数Ranking Function依据排序顺序分配名次的窗口函数
并列值Tie排序键取值相同的行
同行集Peer Group一个并列值形成的行集合
序号Row Number严格 1, 2, 3…，永不重复、永不跳跃
排名Rank并列同行共享名次，后续名次跳跃
密集排名Dense Rank并列同行共享名次，后续名次不跳跃
等分桶Tile把分区均分成 N 份的桶编号
累积分布Cumulative Distribution小于等于当前值的行占比
百分比排名Percent Rank小于当前值的行占比

四个排名函数的行为对照（含并列值）：

排序值row_number()rank()dense_rank()ntile(3)
1001111
1002111
1003111
904421
905422
806632
707742
708743
609953
6010953

这张表是本节最重要的内容，值得逐列对照：

  • row_number() 完全无视并列，给出 1~10 的连续序号，同一份数据在不同执行中序号可能变化（并列行的先后无保证）。
  • rank() 把三个 100 都排为 1，下一位直接是 4（跳过了 2、3），名次有跳跃。
  • dense_rank() 把三个 100 都排为 1，下一位是 2，名次不跳跃。
  • ntile(3) 把 10 行尽量均分为 3 桶，前 4 行 1 桶、中间 3 行 1 桶、后 3 行 1 桶。

选择依据：

需求推荐函数理由
严格取前 N 行（页大小固定）row_number保证恰好 N 行
排行榜（并列同名次）rank保留并列，符合直观
等级划分（无空档）dense_rank名次连续，便于映射等级
分组去重保留一行row_number每行唯一，取 rn = 1
四等分 / 十等分人群ntile直接给出桶号
百分位报告cume_dist、percent_rank给出占比数值

前两个函数返回 bigint，ntile 返回 integer，cume_dist 与 percent_rank 返回 double precision。

sql
-- 用一张人造数据表把三个排名函数的差异摊开
WITH t(v) AS (
  VALUES (100), (100), (100), (90), (90), (80), (70), (70), (60), (60)
)
SELECT
  v                                   AS 分值,
  row_number() OVER (ORDER BY v DESC) AS "row_number",
  rank()       OVER (ORDER BY v DESC) AS "rank",
  dense_rank() OVER (ORDER BY v DESC) AS "dense_rank",
  ntile(3)     OVER (ORDER BY v DESC) AS "ntile_3"
FROM t
ORDER BY v DESC;
text
 分值 | row_number | rank | dense_rank | ntile_3
------+------------+------+------------+---------
  100 |          1 |    1 |          1 |       1
  100 |          2 |    1 |          1 |       1
  100 |          3 |    1 |          1 |       1
   90 |          4 |    4 |          2 |       1
   90 |          5 |    4 |          2 |       2
   80 |          6 |    6 |          3 |       2
   70 |          7 |    7 |          4 |       2
   70 |          8 |    7 |          4 |       3
   60 |          9 |    9 |          5 |       3
   60 |         10 |    9 |          5 |       3
(10 rows)

注意第 4 行：row_number 是 4，rank 也是 4（因为前 3 行并列，跳到 4），dense_rank 是 2（不跳跃）。

语法与用法 ​

text
row_number()      OVER ([PARTITION BY ...] ORDER BY ...)
rank()            OVER ([PARTITION BY ...] ORDER BY ...)
dense_rank()      OVER ([PARTITION BY ...] ORDER BY ...)
ntile(桶数)       OVER ([PARTITION BY ...] ORDER BY ...)
cume_dist()       OVER ([PARTITION BY ...] ORDER BY ...)
percent_rank()    OVER ([PARTITION BY ...] ORDER BY ...)

六个函数都不接受参数（ntile 除外），也都要求 ORDER BY——没有排序就没有名次概念。

准备本节贯穿使用的示例表：

sql
CREATE TABLE users (
  id         INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  username   TEXT NOT NULL,
  city       TEXT NOT NULL,
  status     TEXT NOT NULL,
  created_at DATE NOT NULL
);

CREATE TABLE orders (
  id         INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_no   TEXT NOT NULL,
  user_id    INTEGER NOT NULL REFERENCES users (id),
  amount     NUMERIC(10, 2) NOT NULL CHECK (amount >= 0),
  status     TEXT NOT NULL,
  created_at DATE NOT NULL
);

CREATE INDEX ON users (city);
CREATE INDEX ON orders (user_id, amount DESC, id);
sql
INSERT INTO users (username, city, status, created_at)
SELECT
  'user_' || i,
  (ARRAY['北京', '上海', '广州', '深圳', '杭州'])[1 + (i % 5)],
  CASE WHEN i % 20 = 0 THEN 'inactive' ELSE 'active' END,
  DATE '2024-01-01' + (i % 365)
FROM generate_series(1, 20000) AS i;

-- 刻意制造大量并列值：金额只取 100 档整数值
INSERT INTO orders (order_no, user_id, amount, status, created_at)
SELECT
  'SO' || lpad(i::text, 8, '0'),
  1 + (i % 20000),
  (100 + (i % 900))::numeric,
  (ARRAY['pending', 'paid', 'shipped', 'cancelled'])[1 + (i % 4)],
  DATE '2024-06-01' + (i % 400)
FROM generate_series(1, 200000) AS i;

ANALYZE users;
ANALYZE orders;

详细说明 ​

row_number：唯一序号 ​

row_number() 为分区内的每一行分配一个从 1 开始的连续整数，即使排序键完全相同也不会重复。并列行的相对先后是不确定的，因此必须补唯一列做决胜条件。

sql
-- 不推荐：金额并列时，序号分配不确定
SELECT order_no, amount,
       row_number() OVER (ORDER BY amount DESC) AS rn
FROM orders
ORDER BY rn
LIMIT 5;

-- 推荐：补 id 做决胜
SELECT order_no, amount,
       row_number() OVER (ORDER BY amount DESC, id) AS rn
FROM orders
ORDER BY rn
LIMIT 5;
text
  order_no  | amount | rn
------------+--------+----
 SO00000001 |   999  |  1
 SO00000002 |   999  |  2
 SO00000003 |   999  |  3
 SO00000004 |   999  |  4
 SO00000005 |   999  |  5
(5 rows)

row_number 的典型用途有三个：

用途写法要点说明
分组 Top-N按组 PARTITION BY，外层取 rn <= N保证每组恰好 N 行
分组去重按优先级排序，外层取 rn = 1每个分组只留一行
分页全局 row_number + 外层范围过滤见 5.8 分页一节
sql
-- 每个用户金额最高的 2 笔订单
SELECT user_id, order_no, amount, rn
FROM (
  SELECT user_id, order_no, amount,
         row_number() OVER (PARTITION BY user_id ORDER BY amount DESC, id) AS rn
  FROM orders
  WHERE status <> 'cancelled'
) t
WHERE rn <= 2
ORDER BY user_id, rn
LIMIT 6;
text
 user_id |  order_no  | amount | rn
---------+------------+--------+----
       1 | SO00020001 |    999 |  1
       1 | SO00040001 |    999 |  2
       2 | SO00000002 |    999 |  1
       2 | SO00020002 |    999 |  2
       3 | SO00000003 |    999 |  1
       3 | SO00020003 |    999 |  2
(6 rows)

rank 与 dense_rank：并列与跳跃 ​

这两个函数的共同点是并列行共享同一个名次，区别在于下一个名次如何计算。

场景rankdense_rank
排序键全是唯一值与 row_number 相同与 row_number 相同
出现并列值下一个名次 = 当前名次 + 并列行数下一个名次 = 当前名次 + 1
名次最大值可超过实际行数不超过不同值的个数
名次是否有空档有空档无空档
是否适合做「等级」不适合适合
sql
-- 排行榜：Top 10 金额，并列份额共享
SELECT
  order_no,
  amount,
  rank()       OVER (ORDER BY amount DESC) AS 排名,
  dense_rank() OVER (ORDER BY amount DESC) AS 密集排名,
  row_number() OVER (ORDER BY amount DESC, id) AS 严格序号
FROM orders
WHERE status = 'paid'
ORDER BY amount DESC, id
LIMIT 8;
text
  order_no  | amount | 排名 | 密集排名 | 严格序号
------------+--------+------+----------+----------
 SO00000003 |    999 |    1 |        1 |        1
 SO00000007 |    999 |    1 |        1 |        2
 SO00000011 |    999 |    1 |        1 |        3
 SO00000015 |    999 |    1 |        1 |        4
 SO00000019 |    999 |    1 |        1 |        5
 SO00000004 |    998 |    6 |        2 |        6
 SO00000008 |    998 |    6 |        2 |        7
 SO00000012 |    998 |    6 |        2 |        8
(8 rows)

在 amount = 999 这个并列组里，排名 全是 1，密集排名 全是 1；到了 amount = 998，排名 跳到 6（因为有 5 行并列），密集排名 是 2。

这个差别在「取前 N 名」时影响巨大：

sql
-- 用 rank 取前 3 名：可能返回超过 3 行（并列都算）
SELECT order_no, amount, 排名 FROM (
  SELECT order_no, amount, rank() OVER (ORDER BY amount DESC, id) AS 排名
  FROM orders WHERE status = 'paid'
) t
WHERE 排名 <= 3
ORDER BY 排名;
sql
-- 用 row_number 取前 3 名：严格 3 行
SELECT order_no, amount, 严格序号 FROM (
  SELECT order_no, amount, row_number() OVER (ORDER BY amount DESC, id) AS 严格序号
  FROM orders WHERE status = 'paid'
) t
WHERE 严格序号 <= 3
ORDER BY 严格序号;

注意：上面的 rank() OVER (ORDER BY amount DESC, id) 因为加了 id 做排序键，实际上永远不会产生并列。若要得到「真正的并列排名」，排序键里不能包含唯一列。

sql
-- 正确写法：只按金额排序，才能得到带并列的 rank
rank() OVER (ORDER BY amount DESC)

用 dense_rank 划分等级是它的经典用途：

sql
-- 把金额密集排名当作等级编号，映射到文案
WITH ranked AS (
  SELECT
    order_no,
    amount,
    dense_rank() OVER (ORDER BY amount DESC) AS 等级
  FROM orders
  WHERE status = 'paid'
)
SELECT
  等级,
  min(amount) AS 该等级最低金额,
  max(amount) AS 该等级最高金额,
  count(*)    AS 订单数
FROM ranked
GROUP BY 等级
ORDER BY 等级
LIMIT 6;
text
 等级 | 该等级最低金额 | 该等级最高金额 | 订单数
------+----------------+----------------+--------
    1 |            999 |            999 |    308
    2 |            998 |            998 |    311
    3 |            997 |            997 |    296
    4 |            996 |            996 |    304
    5 |            995 |            995 |    292
    6 |            994 |            994 |    299
(6 rows)

ntile：等分桶 ​

ntile(N) 把分区内的行尽量均分成 N 桶，并给每行分配桶号。不能整除时，前面的桶会多一行。

行数ntile(3) 各桶行数说明
93 / 3 / 3整除，均分
104 / 3 / 3余 1，第一桶多 1
114 / 4 / 3余 2，前两桶各多 1
124 / 4 / 4整除，均分
21 / 1 / 0行数少于桶数，后面的桶为空
sql
SELECT
  count(*)  AS 总行数,
  桶号,
  count(*)  AS 桶内行数
FROM (
  SELECT ntile(3) OVER (ORDER BY id) AS 桶号
  FROM orders
  WHERE status = 'paid'
) t
GROUP BY 桶号
ORDER BY 桶号;
text
 总行数 | 桶号 | 桶内行数
--------+------+----------
  50012 |    1 |    16671
  50012 |    2 |    16671
  50012 |    3 |    16670
(3 rows)

ntile 的典型用途是人群分层：

sql
-- 把用户按消费额分成 4 层（高价值 / 中高 / 中低 / 低价值）
WITH user_spend AS (
  SELECT
    u.id,
    u.username,
    u.city,
    coalesce(sum(o.amount) FILTER (WHERE o.status <> 'cancelled'), 0) AS 消费额
  FROM users u
  LEFT JOIN orders o ON o.user_id = u.id
  WHERE u.status = 'active'
  GROUP BY u.id, u.username, u.city
),
tiled AS (
  SELECT *,
         ntile(4) OVER (ORDER BY 消费额 DESC, id) AS 层级
  FROM user_spend
)
SELECT
  层级,
  CASE 层级 WHEN 1 THEN '高价值' WHEN 2 THEN '中高' WHEN 3 THEN '中低' ELSE '低价值' END AS 层级名称,
  count(*)              AS 用户数,
  round(min(消费额), 2) AS 该层最低,
  round(max(消费额), 2) AS 该层最高
FROM tiled
GROUP BY 层级
ORDER BY 层级;
text
 层级 | 层级名称 | 用户数 | 该层最低  |  该层最高
------+----------+--------+-----------+------------
    1 | 高价值   |   4750 |  13642.00 |   55010.00
    2 | 中高     |   4750 |  11673.00 |   13642.00
    3 | 中低     |   4750 |   9891.00 |   11673.00
    4 | 低价值   |   4750 |      0.00 |    9891.00
(4 rows)

ntile 要求排序键稳定，否则同一用户的层级可能在两次执行间漂移。上面用了 ORDER BY 消费额 DESC, id 就是为了这一点。

cume_dist 与 percent_rank ​

这两个函数不返回名次，而是返回占比，更适合做百分位报告。

函数返回值取值范围并列值行为计算方式
cume_dist()累积分布(0, 1]同一同行集返回相同值小于等于当前排序值的行数 / 总行数
percent_rank()百分比排名[0, 1]同一同行集返回相同值(当前 rank − 1) / (总行数 − 1)
sql
WITH t(v) AS (
  VALUES (100), (100), (90), (80), (70), (60)
)
SELECT
  v,
  rank()         OVER (ORDER BY v DESC)    AS 排名,
  cume_dist()    OVER (ORDER BY v DESC)    AS 累积分布,
  percent_rank() OVER (ORDER BY v DESC)    AS 百分比排名
FROM t
ORDER BY v DESC;
text
  v  | 排名 |    累积分布    |   百分比排名
-----+------+----------------+----------------
 100 |    1 | 0.333333333333 |              0
 100 |    1 | 0.333333333333 |              0
  90 |    3 | 0.500000000000 |            0.4
  80 |    4 | 0.666666666667 |            0.6
  70 |    5 | 0.833333333333 |            0.8
  60 |    6 | 1.000000000000 |              1
(6 rows)

解读：

  • cume_dist = 0.5 表示「当前值及以上的行占了全体的 50%」。
  • percent_rank = 0.4 表示「比当前值更好的行占了 40%」。

两者的差别集中在是否把自己算进去：cume_dist 包含当前同行集，percent_rank 不包含。

实战示例 ​

场景：季度销售评优。需要输出「各城市订单金额 Top 10」，并同时给出严格序号、并列排名、密集排名、金额四分位与百分位。要求结果可复现、并列值标注清晰。

步骤 1：准备带并列值的示例数据 ​

sql
-- 为便于观察并列，构造一批金额重复的订单
CREATE TABLE orders_tied (
  id         INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_no   TEXT NOT NULL,
  user_id    INTEGER NOT NULL,
  amount     NUMERIC(10, 2) NOT NULL,
  created_at DATE NOT NULL
);

INSERT INTO orders_tied (order_no, user_id, amount, created_at)
SELECT
  'SO' || lpad(i::text, 8, '0'),
  1 + (i % 100),
  -- 只有 12 个不同金额，制造大量并列
  (ARRAY[100, 100, 100, 90, 90, 80, 70, 70, 60, 60, 50, 50])[1 + (i % 12)],
  DATE '2024-06-01' + (i % 30)
FROM generate_series(1, 40) AS i;

ANALYZE orders_tied;

步骤 2：四个排名函数在同一份数据上的完整输出 ​

sql
SELECT
  order_no,
  amount,
  row_number() OVER w AS "row_number",
  rank()       OVER w AS "rank",
  dense_rank() OVER w AS "dense_rank",
  ntile(3)     OVER w AS "ntile_3"
FROM orders_tied
WINDOW w AS (ORDER BY amount DESC, id)
ORDER BY amount DESC, id
LIMIT 12;
text
  order_no  | amount | row_number | rank | dense_rank | ntile_3
------------+--------+------------+------+------------+---------
 SO00000001 |    100 |          1 |    1 |          1 |       1
 SO00000013 |    100 |          2 |    2 |          1 |       1
 SO00000025 |    100 |          3 |    3 |          1 |       1
 SO00000002 |    100 |          4 |    4 |          1 |       1
 SO00000014 |    100 |          5 |    5 |          1 |       1
 SO00000026 |    100 |          6 |    6 |          1 |       1
 SO00000003 |     90 |          7 |    7 |          2 |       1
 SO00000015 |     90 |          8 |    8 |          2 |       1
 SO00000027 |     90 |          9 |    9 |          2 |       1
 SO00000004 |     80 |         10 |   10 |          3 |       1
 SO00000016 |     80 |         11 |   11 |          3 |       2
 SO00000028 |     80 |         12 |   12 |          3 |       2
(12 rows)

注意：因为排序键里带了 id，这里的 rank 退化成与 row_number 相同——并列被 id 拆开了。这是「想拿并列排名却补了唯一列排序键」的典型陷阱。

步骤 3：去掉唯一列，得到真正的并列排名 ​

sql
SELECT
  amount,
  count(*)     AS 行数,
  min(row_number() OVER w) AS row_number起始,
  rank()       OVER w AS "rank",
  dense_rank() OVER w AS "dense_rank"
FROM orders_tied
WINDOW w AS (ORDER BY amount DESC)
GROUP BY amount, w
ORDER BY amount DESC;
sql
-- 更清晰的写法：外层去重展示每个金额档的排名
SELECT DISTINCT
  amount,
  rank()       OVER (ORDER BY amount DESC) AS "rank",
  dense_rank() OVER (ORDER BY amount DESC) AS "dense_rank",
  count(*)     OVER (PARTITION BY amount)  AS 该档行数
FROM orders_tied
ORDER BY amount DESC;
text
 amount | rank | dense_rank | 该档行数
--------+------+------------+----------
    100 |    1 |          1 |        6
     90 |    7 |          2 |        3
     80 |   10 |          3 |        3
     70 |   13 |          4 |        4
     60 |   17 |          5 |        4
     50 |   21 |          6 |        4
(6 rows)

这份结果才是真正的并列排名：6 行并列 100 全部记为 rank = 1，下一个金额档（90）的 rank = 7（跳过 2~6），dense_rank = 2（不跳跃）。

步骤 4：加上百分位指标 ​

sql
SELECT DISTINCT
  amount,
  rank()         OVER (ORDER BY amount DESC) AS 排名,
  cume_dist()    OVER (ORDER BY amount DESC) AS 累积分布,
  percent_rank() OVER (ORDER BY amount DESC) AS 百分比排名
FROM orders_tied
ORDER BY amount DESC;
text
 amount | 排名 |    累积分布    |   百分比排名
--------+------+----------------+----------------
    100 |    1 | 0.150000000000 |              0
     90 |    7 | 0.225000000000 | 0.153846153846
     80 |   10 | 0.300000000000 | 0.230769230769
     70 |   13 | 0.400000000000 | 0.307692307692
     60 |   17 | 0.500000000000 | 0.410256410256
     50 |   21 | 1.000000000000 | 0.512820512821
(6 rows)

步骤 5：按城市分组取 Top 3，并处理并列 ​

sql
-- 方案 A：row_number，每组严格 3 行
SELECT city, order_no, amount, rn
FROM (
  SELECT
    u.city, o.order_no, o.amount,
    row_number() OVER (PARTITION BY u.city ORDER BY o.amount DESC, o.id) AS rn
  FROM orders o JOIN users u ON u.id = o.user_id
  WHERE o.status <> 'cancelled'
) t
WHERE rn <= 3
ORDER BY city, rn;
text
 city |  order_no  | amount | rn
------+------------+--------+----
 上海 | SO00000003 |    999 |  1
 上海 | SO00000007 |    999 |  2
 上海 | SO00000011 |    999 |  3
 北京 | SO00000001 |    999 |  1
 北京 | SO00000009 |    999 |  2
 北京 | SO00000013 |    999 |  3
 广州 | SO00000002 |    999 |  1
 广州 | SO00000006 |    999 |  2
 广州 | SO00000010 |    999 |  3
 深圳 | SO00000004 |    999 |  1
 深圳 | SO00000008 |    999 |  2
 深圳 | SO00000012 |    999 |  3
 杭州 | SO00000005 |    999 |  1
 杭州 | SO00000017 |    999 |  2
 杭州 | SO00000019 |    999 |  3
(15 rows)
sql
-- 方案 B：rank，并列全部保留（可能超过 3 行）
SELECT city, order_no, amount, rk
FROM (
  SELECT
    u.city, o.order_no, o.amount,
    rank() OVER (PARTITION BY u.city ORDER BY o.amount DESC) AS rk
  FROM orders o JOIN users u ON u.id = o.user_id
  WHERE o.status <> 'cancelled'
) t
WHERE rk <= 3
ORDER BY city, rk
LIMIT 10;
text
 city |  order_no  | amount | rk
------+------------+--------+----
 上海 | SO00000003 |    999 |  1
 上海 | SO00000007 |    999 |  1
 上海 | SO00000011 |    999 |  1
 上海 | SO00000015 |    999 |  1
 上海 | SO00000019 |    999 |  1
 上海 | SO00000023 |    999 |  1
 上海 | SO00000027 |    999 |  1
 上海 | SO00000031 |    999 |  1
 上海 | SO00000035 |    999 |  1
 上海 | SO00000039 |    999 |  1
(10 rows)

两个方案的取舍：

需求选 row_number选 rank
前端固定显示 3 行✓✗（可能几十行）
榜单要「并列第一」✗（并列被强行拆开）✓
导出数据统计口径固定✓✗（行数不定）
评优「并列同奖」✗✓

步骤 6：验证结果可复现 ​

sql
-- 同一查询执行两次，比较结果是否完全一致
WITH run1 AS (
  SELECT id, row_number() OVER (ORDER BY amount DESC) AS rn FROM orders_tied
),
run2 AS (
  SELECT id, row_number() OVER (ORDER BY amount DESC) AS rn FROM orders_tied
)
SELECT '不稳定的行数' AS 检查项, count(*) FROM (
  SELECT id, rn FROM run1 EXCEPT ALL SELECT id, rn FROM run2
) t
UNION ALL
SELECT '稳定写法差异行数', count(*) FROM (
  SELECT id, rn FROM (
    SELECT id, row_number() OVER (ORDER BY amount DESC, id) AS rn FROM orders_tied
  ) a EXCEPT ALL
  SELECT id, rn FROM (
    SELECT id, row_number() OVER (ORDER BY amount DESC, id) AS rn FROM orders_tied
  ) b
) t;
text
      检查项       | count
-------------------+-------
 不稳定的行数      |     0
 稳定写法差异行数  |     0

在单次会话内并列行的扫描顺序通常是稳定的，所以差异为 0。但跨版本、跨计划变化、并行扫描时并不保证，因此排序键补唯一列依然是必须的。

注意事项 ​

  1. rank 与 row_number 在加了唯一列排序键后会失去并列语义，这是最常见的误用：
sql
-- 错误！期望并列排名，但 id 拆开了并列
rank() OVER (ORDER BY amount DESC, id)

-- 正确
rank() OVER (ORDER BY amount DESC)
  1. row_number 取 Top-N 会「吃掉」并列行。若业务要求并列同奖，必须用 rank：
sql
-- 不推荐：并列第 1 的行只留了一行
SELECT * FROM (SELECT *, row_number() OVER (ORDER BY score DESC) AS rn FROM t) x
WHERE rn = 1;

-- 推荐
SELECT * FROM (SELECT *, rank() OVER (ORDER BY score DESC) AS rk FROM t) x
WHERE rk = 1;
  1. ntile 的并行度会整体漂移。给某一行加 1 分，可能让后面所有行的桶号都变，不要用它做稳定的用户分群：
sql
-- 不推荐：数据增删后层级会变
ntile(4) OVER (ORDER BY 消费额 DESC)

-- 推荐：用固定阈值切分
CASE WHEN 消费额 >= 10000 THEN 1
     WHEN 消费额 >= 8000  THEN 2
     WHEN 消费额 >= 5000  THEN 3
     ELSE 4 END AS 层级
  1. ntile 的桶数不能是表达式或列，必须是正整数常量：
sql
-- 错误！桶数必须是常量
SELECT ntile(col) OVER (ORDER BY id) FROM t;
-- 报错：argument of ntile must be greater than zero
  1. cume_dist 与 percent_rank 的分母不同，混用会得到不一致的百分位：
指标第一个同行集的值最后一个同行集的值
cume_dist大于 0，等于首个同行集占比恒为 1
percent_rank恒为 0小于 1
  1. 排名函数不受帧子句影响。写 ROWS BETWEEN ... 对 rank 没有任何作用，只对聚合类窗口函数有效：
sql
-- ROWS 子句在这里是多余的，rank 的结果不会改变
rank() OVER (ORDER BY amount DESC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)
  1. 排名函数不能出现在 WHERE 中，必须外套一层子查询：
sql
-- 错误！
SELECT * FROM orders WHERE rank() OVER (ORDER BY amount DESC) <= 3;

-- 正确
SELECT * FROM (SELECT *, rank() OVER (ORDER BY amount DESC) AS rk FROM orders) t
WHERE rk <= 3;

最佳实践 ​

  1. 按需求选函数，记住这张速查表：
需求函数并列行为
恰好 N 行row_number并列被拆开
榜单同奖rank并列同名次，名次跳跃
等级编号dense_rank并列同名次，名次连续
人群分层ntile均分桶，数据变动会漂移
百分位cume_dist / percent_rank同行集共享值
  1. 排序键一律补唯一列，只是要注意排序键的顺序：
sql
-- 需要并列排名：唯一列不能进排序键
rank() OVER (PARTITION BY city ORDER BY amount DESC)

-- 需要稳定序号：唯一列必须进排序键
row_number() OVER (PARTITION BY city ORDER BY amount DESC, id)

  1. row_number 永远不在 ORDER BY 里放唯一列之外的东西做决胜，决胜列越少越稳定。

  2. 分组 Top-N 统一用下面的骨架，性能与可读性都好：

sql
SELECT * FROM (
  SELECT
    分组列, 明细列,
    row_number() OVER (PARTITION BY 分组列 ORDER BY 排序键 DESC, 唯一列) AS rn
  FROM 表
  WHERE 过滤条件
) t
WHERE rn <= N;
  1. 给排名窗口建复合索引，索引列顺序与 PARTITION BY + ORDER BY 一致：
sql
CREATE INDEX ON orders (user_id, amount DESC, id);
CREATE INDEX ON orders (status, amount DESC, id);
  1. 数据量大时，先过滤再排名，把 WHERE 放在窗口函数所在的那一层：
sql
-- 好：先过滤掉 75% 的已取消订单，再对 5 万行排名
SELECT * FROM (
  SELECT *, row_number() OVER (PARTITION BY user_id ORDER BY amount DESC, id) AS rn
  FROM orders
  WHERE status = 'paid'
) t WHERE rn <= 3;

-- 差：对 20 万行排名后再过滤
SELECT * FROM (
  SELECT *, row_number() OVER (PARTITION BY user_id ORDER BY amount DESC, id) AS rn
  FROM orders
) t WHERE rn <= 3 AND status = 'paid';
  1. 调试排名结果时，先补一个 count(*) OVER () 看总行数，便于判断 rank 空档是否超出预期：
sql
SELECT
  order_no, amount,
  rank()       OVER w AS 排名,
  count(*)     OVER () AS 总行数
FROM orders
WINDOW w AS (ORDER BY amount DESC)
ORDER BY 排名
LIMIT 10;

下一节

继续学习：分析函数

参考链接 ​