📑 本页目录（点开跳转）

🎯 一句话
🕳️ 一、你早就写过帧了，只是没写出来
📐 二、帧的完整语法：三个部件
🧮 三、ROWS：累计、移动平均、还剩多少
🕳️ 四、RANGE：数的是值，不是行
🎭 五、LAST_VALUE 的默认帧陷阱，和命名窗口
🧪 六、GROUPS 和 EXCLUDE：知道有这两个就够了
📋 七、把帧接回真实需求：滚动特征，和「只看过去」
🧭 八、一张需求 → 帧的对照表
🔗 这一章连到哪里
✅ 检查点
🛑 可以停在这里

08 · 窗口帧：ROWS 数行，RANGE 数值

⏱ 106 分钟 ｜ ⭐ 实测：同一份数据，「最近 3 行」算出 16，「最近 3 天」算出 4 —— 数据有缺口时它们不是一回事，而缺口是常态

🎯 一句话

帧（frame）回答的是「这一行的窗里，到底装了哪些行」。 07 章一句帧子句都没写，但每一个 OVER (…) 都有帧 —— 数据库替你用了默认的那个。 ⭐ 而默认的那个，在排序列有并列值时的行为，和几乎所有人的直觉相反。

🕳️ 一、你早就写过帧了，只是没写出来

先看一句最普通的累计求和。五行数据，⭐ 03-02 那天有两笔：

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE pay (d TEXT, amt INTEGER)")
db.executemany("INSERT INTO pay VALUES (?,?)", [
    ("03-01", 10), ("03-02", 20), ("03-02", 30), ("03-03", 40), ("03-04", 50),
])   # ⭐ 03-02 有两行，这是全章最关键的一处安排

print("d       amt   默认帧   显式RANGE   显式ROWS")
for r in db.execute("""
    SELECT d, amt,
      SUM(amt) OVER (ORDER BY d)                                     AS f_default,
      SUM(amt) OVER (ORDER BY d
                     RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS f_range,
      SUM(amt) OVER (ORDER BY d
                     ROWS  BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS f_rows
    FROM pay ORDER BY d, amt"""):
    print(f"{r[0]}   {r[1]:3d}   {r[2]:5d}   {r[3]:7d}   {r[4]:7d}")

print("\n「累计和减掉自己」= 排在我前面的总额？")
for r in db.execute("""
    SELECT d, amt, SUM(amt) OVER (ORDER BY d) - amt AS 我以前的
    FROM pay ORDER BY d, amt"""):
    print("   ", r)

print("\n不写 ORDER BY 的窗口，帧是整个分区：")
for r in db.execute("SELECT d, amt, SUM(amt) OVER () AS 全组 FROM pay ORDER BY d, amt"):
    print("   ", r)

实测输出（Python 3.13.14 / SQLite 3.50.4）：

d       amt   默认帧   显式RANGE   显式ROWS
03-01    10      10        10        10
03-02    20      60        60        30
03-02    30      60        60        60
03-03    40     100       100       100
03-04    50     150       150       150

「累计和减掉自己」= 排在我前面的总额？
    ('03-01', 10, 0)
    ('03-02', 20, 40)
    ('03-02', 30, 30)
    ('03-03', 40, 60)
    ('03-04', 50, 100)

不写 ORDER BY 的窗口，帧是整个分区：
    ('03-01', 10, 150)
    ('03-02', 20, 150)
    ('03-02', 30, 150)
    ('03-03', 40, 150)
    ('03-04', 50, 150)

⭐ 看第二、三行：两条 03-02 的累计和都是 60。 不是 30 和 60，是 60 和 60 —— 第一条 03-02 的「累计和」里，已经包含了排在它后面那条 03-02。

原因是 SQL 标准规定的默认帧：

窗口里写了什么	默认帧
有 `ORDER BY`	`RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`
没有 `ORDER BY`	`ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING`（整个分区，实测全是 150）

⭐ 关键在 RANGE 这个词上：它数的不是「行」，是「值」。 CURRENT ROW 在 RANGE 里的意思不是「当前这一行」，是「所有和当前行排序值相等的行」（术语叫 peer / 并列组）。两条 03-02 互为并列，所以对它们俩来说，窗口的右边界是同一个位置 —— 都停在第二条 03-02 上。

换成 ROWS，数的就是实打实的行，于是给出 30 和 60。

💀 这个坑最贵的形态是「累计和减掉自己 = 排在我前面的总额」这个顺手的写法。 实测第一条 03-02 拿到 60 - 20 = 40，第二条拿到 60 - 30 = 30 —— ⚠️ 两个数都不是「排在我前面的总额」（应该是 10 和 30），而且第一条比第二条还大。 它不报错、不返回 NULL，只给你一个看起来像那么回事的数。

⭐ 判据一句话：只要你写了「聚合函数 + 窗口内 ORDER BY」，就已经在用帧了。 排名类（ROW_NUMBER / RANK / DENSE_RANK）和 LAG / LEAD 不受帧影响，所以 07 章一路无事。

📐 二、帧的完整语法：三个部件

函数(…) OVER (
  PARTITION BY …
  ORDER BY …
  ROWS | RANGE | GROUPS  BETWEEN  起点  AND  终点
)

第一个部件：怎么数。

关键字	数什么	一句话
`ROWS`	行	往回数 2 行，就是 2 行
`RANGE`	值	往回数 2，是排序列的值在 `[当前值-2, 当前值]` 里的所有行
`GROUPS`	并列组	往回数 2 组，一组 = 排序值相同的一批行

第二、三个部件：起点和终点。各有五种写法：

端点	含义	能当起点	能当终点
`UNBOUNDED PRECEDING`	分区的第一行	✅	❌
`n PRECEDING`	往回 n（行 / 值 / 组）	✅	✅
`CURRENT ROW`	当前行（`RANGE` 下是整个并列组）	✅	✅
`n FOLLOWING`	往后 n	✅	✅
`UNBOUNDED FOLLOWING`	分区的最后一行	❌	✅

⚠️ 起点不能排在终点后面，否则报错。 ⭐ 省略 BETWEEN 是合法的简写，终点默认是 CURRENT ROW：

ROWS 2 PRECEDING   ≡   ROWS BETWEEN 2 PRECEDING AND CURRENT ROW

⚠️ ⭐ PARTITION BY 是硬边界，帧永远不会越过它。 ROWS BETWEEN 2 PRECEDING … 在每个分区的第一行只能看到 1 行，第二行看到 2 行 —— 帧会在边界处自动缩水，而不是去隔壁分区借行。下一节有实测。

🧮 三、`ROWS`：累计、移动平均、还剩多少

三个最常用的形状，一次跑完。这是一张服务的日 p99 延迟表：

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE lat (svc TEXT, d TEXT, p99 REAL)")
db.executemany("INSERT INTO lat VALUES (?,?,?)", [
    ("api", "03-01", 120.0), ("api", "03-02", 140.0), ("api", "03-03", 132.0),
    ("api", "03-04", 210.0), ("api", "03-05", 190.0), ("api", "03-06", 150.0),
])

print("d       p99    累计    移动3    还剩    占全组")
for r in db.execute("""
    SELECT d, p99,
      ROUND(SUM(p99) OVER (PARTITION BY svc ORDER BY d
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 1)   AS 累计,
      ROUND(AVG(p99) OVER (PARTITION BY svc ORDER BY d
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 1)           AS 移动3,
      ROUND(SUM(p99) OVER (PARTITION BY svc ORDER BY d
            ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING), 1)   AS 还剩,
      ROUND(p99 * 100.0 / SUM(p99) OVER (PARTITION BY svc), 1)      AS 占比
    FROM lat ORDER BY d"""):
    print(f"{r[0]}  {r[1]:5.1f}  {r[2]:6.1f}  {r[3]:6.1f}  {str(r[4]):>6}  {r[5]:5.1f}%")

print("\n窗内到底有几行（帧在边界处会自己缩水）：")
for r in db.execute("""
    SELECT d,
      COUNT(*) OVER (ORDER BY d ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)  AS 往回3,
      COUNT(*) OVER (ORDER BY d ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)  AS 前后各1
    FROM lat ORDER BY d"""):
    print("   ", r)

实测输出：

d       p99    累计    移动3    还剩    占全组
03-01  120.0   120.0   120.0   822.0   12.7%
03-02  140.0   260.0   130.0   682.0   14.9%
03-03  132.0   392.0   130.7   550.0   14.0%
03-04  210.0   602.0   160.7   340.0   22.3%
03-05  190.0   792.0   177.3   150.0   20.2%
03-06  150.0   942.0   183.3    None   15.9%

窗内到底有几行（帧在边界处会自己缩水）：
    ('03-01', 1, 2)
    ('03-02', 2, 3)
    ('03-03', 3, 3)
    ('03-04', 3, 3)
    ('03-05', 3, 3)
    ('03-06', 3, 2)

四个形状各自的读法：

想要	帧	实测
累计（跑到今天为止一共多少）	`ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`	最后一行 942.0 = 全部之和
移动平均（近 3 天）	`ROWS BETWEEN 2 PRECEDING AND CURRENT ROW`	`03-04` 那天 160.7，被 210 拉高
往后看（今天之后还剩多少）	`ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING`	最后一行 `None`
占全组多少	⭐ 不写帧（省掉窗口内 `ORDER BY`，帧就是整个分区）	六行加起来 100%

⚠️ 最后一行的「还剩」是 None 不是 0。 帧里一行都没有时，SUM 给 NULL 而不是 0 —— 这是 04 章的老规矩： 「没有数据」不等于「数据是零」。想要 0 就套一层 COALESCE(…, 0)，⭐ 但套之前先想清楚这两件事在你的看板上是不是真的一样。

⭐ 注意「移动3」那一列的前两行：03-01 是 120.0（只有它自己），03-02 是 130.0（两行的均值）。 帧在分区开头自动缩水到 1 行、2 行，实测 COUNT 那张表是 1, 2, 3, 3, 3, 3。

⚠️ 于是头几行的「3 日均值」其实是 1 日、2 日均值 —— 它不报错，但画到趋势图上就是「开头那一段莫名其妙地平」。

要么在正文里说明，要么加一句 WHERE 把窗不满的行滤掉（拿 COUNT(*) OVER (…) = 3 当条件，⚠️ 记得它得写在 CTE 外面 —— 这是 07 章第六节的规矩）。

🕳️ 四、`RANGE`：数的是值，不是行

这一节是全章的正题。

上面所有「近 3 天」其实都是骗人的 —— 我写的是 ROWS 2 PRECEDING，它是近 3 行。只要每天都恰好有一行，两者就一样。⚠️ 而「每天都有一行」几乎从不成立：服务器没请求、店铺没成交、客服休假、任务失败没写数 —— 那一天在表里根本没有行。

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE clk (day INTEGER, n INTEGER)")
# day = 第几天。⚠️ 第 4–8 天一次点击都没有，表里根本没有这几行
db.executemany("INSERT INTO clk VALUES (?,?)",
               [(1, 5), (2, 3), (3, 9), (9, 4), (10, 6), (11, 2)])

print("day   n   最近3行   最近3天   窗内行数")
for r in db.execute("""
    SELECT day, n,
      SUM(n)   OVER (ORDER BY day ROWS  BETWEEN 2 PRECEDING AND CURRENT ROW) AS 最近3行,
      SUM(n)   OVER (ORDER BY day RANGE BETWEEN 2 PRECEDING AND CURRENT ROW) AS 最近3天,
      COUNT(*) OVER (ORDER BY day RANGE BETWEEN 2 PRECEDING AND CURRENT ROW) AS 行数
    FROM clk ORDER BY day"""):
    print(f"{r[0]:3d}  {r[1]:2d}   {r[2]:5d}     {r[3]:5d}     {r[4]:5d}")

print("\nRANGE 带偏移量时，窗口里只能有一个 ORDER BY 列：")
try:
    db.execute("""SELECT SUM(n) OVER (ORDER BY day, n RANGE BETWEEN 2 PRECEDING AND CURRENT ROW)
                  FROM clk""").fetchall()
except Exception as e:
    print("   ", type(e).__name__, "|", e)

print("\n💀 ORDER BY 列是文本时，RANGE 偏移量【不报错】，但窗里只剩自己：")
db.execute("CREATE TABLE s (d TEXT, n INT)")
db.executemany("INSERT INTO s VALUES (?,?)",
               [("2026-03-01", 5), ("2026-03-02", 3), ("2026-03-03", 9)])
for r in db.execute("""
    SELECT d, n,
      SUM(n)   OVER (ORDER BY d RANGE BETWEEN 2 PRECEDING AND CURRENT ROW) AS 和,
      COUNT(*) OVER (ORDER BY d RANGE BETWEEN 2 PRECEDING AND CURRENT ROW) AS 窗内行数
    FROM s ORDER BY d"""):
    print("   ", r)

实测输出：

day   n   最近3行   最近3天   窗内行数
  1   5       5         5         1
  2   3       8         8         2
  3   9      17        17         3
  9   4      16         4         1
 10   6      19        10         2
 11   2      12        12         3

RANGE 带偏移量时，窗口里只能有一个 ORDER BY 列：
    OperationalError | RANGE with offset PRECEDING/FOLLOWING requires one ORDER BY expression

💀 ORDER BY 列是文本时，RANGE 偏移量【不报错】，但窗里只剩自己：
    ('2026-03-01', 5, 5, 1)
    ('2026-03-02', 3, 3, 1)
    ('2026-03-03', 9, 9, 1)

⭐ 看第 9 天那一行：ROWS 给 16，RANGE 给 4。

ROWS 2 PRECEDING 老老实实往回数两行 —— 拿到的是第 3 天和第 2 天，于是 9 + 3 + 4 = 16。 💀 那两行是一周前的数据，被当成「最近 3 天」加了进去。
RANGE 2 PRECEDING 找的是 day 落在 [7, 9] 的行 —— 一行都没有（第 7、8 天不存在），于是窗里只有它自己，得 4，COUNT 也诚实地告诉你是 1。

	「最近 3 行」`ROWS`	「最近 3 天」`RANGE`
数据没有缺口	一样	一样
⚠️ 数据有缺口	会把很久以前的行拉进来	✅ 窗真的只有 3 天宽
窗内行数	恒定（边界处除外）	会变（实测 1 / 2 / 3）
想要的语义是	「最近 3 次」	「最近 3 天」

⭐ 判据：问题里的单位是「次 / 条 / 单」就用 ROWS，是「天 / 小时 / 元」就用 RANGE。

⚠️ 两个附带的坑，第二个是不报错的那种：

RANGE 带偏移量时，窗口的 ORDER BY 只能有一个表达式（因为要拿它做减法）。写两列直接报错：RANGE with offset PRECEDING/FOLLOWING requires one ORDER BY expression。这个坑会自己撞出来，还算便宜。
💀 排序列是文本时，RANGE 2 PRECEDING 不报错，但窗里只剩当前行的并列组。 实测三行日期字符串，和 分别是 5 / 3 / 9，窗内行数全是 1 —— ⚠️ 它看起来在算滚动窗口，实际上每一行都在算自己。 而 '2026-03-01' 这种存法在真实数据里到处都是。

✅ 修法：让排序列变成数。 SQLite 用 julianday(ts)，⭐ 第七节有实测。

🛑 读到这里可以停 —— 前半章讲完了（约 38 分钟）。 后半章还有：LAST_VALUE 的默认帧陷阱，和命名窗口 · GROUPS 和 EXCLUDE：知道有这两个就够了 · 把帧接回真实需求：滚动特征，和「只看过去」 · 一张需求 → 帧的对照表回来的时候不用重读，直接从下一节接着看就行。

🎭 五、`LAST_VALUE` 的默认帧陷阱，和命名窗口

这是默认帧引发的第二起事故，比累计和那个更常见。

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE f (uid INTEGER, t INTEGER, v REAL)")
db.executemany("INSERT INTO f VALUES (?,?,?)",
               [(1, 1, 0.10), (1, 2, 0.20), (1, 3, 0.30), (2, 1, 0.70), (2, 2, 0.80)])

print("uid  t    v     FIRST_VALUE   LAST_VALUE(默认帧)   LAST_VALUE(全帧)   MAX(无排序)")
for r in db.execute("""
    SELECT uid, t, v,
      FIRST_VALUE(v) OVER w                                       AS 首值,
      LAST_VALUE(v)  OVER w                                       AS 末值_默认,
      LAST_VALUE(v)  OVER (PARTITION BY uid ORDER BY t
              ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS 末值_全帧,
      MAX(v)         OVER (PARTITION BY uid)                      AS 最大_无排序
    FROM f WINDOW w AS (PARTITION BY uid ORDER BY t)
    ORDER BY uid, t"""):
    print(f" {r[0]}   {r[1]}   {r[2]:.2f}      {r[3]:.2f}            {r[4]:.2f}"
          f"              {r[5]:.2f}            {r[6]:.2f}")

print("\n把「找末值」翻过来写成「找首值 + 倒序」，也是对的：")
for r in db.execute("""
    SELECT uid, t, FIRST_VALUE(v) OVER (PARTITION BY uid ORDER BY t DESC) AS 末值
    FROM f ORDER BY uid, t"""):
    print("   ", r)

实测输出：

uid  t    v     FIRST_VALUE   LAST_VALUE(默认帧)   LAST_VALUE(全帧)   MAX(无排序)
 1   1   0.10      0.10            0.10              0.30            0.30
 1   2   0.20      0.10            0.20              0.30            0.30
 1   3   0.30      0.10            0.30              0.30            0.30
 2   1   0.70      0.70            0.70              0.80            0.80
 2   2   0.80      0.70            0.80              0.80            0.80

把「找末值」翻过来写成「找首值 + 倒序」，也是对的：
    (1, 1, 0.3)
    (1, 2, 0.3)
    (1, 3, 0.3)
    (2, 1, 0.8)
    (2, 2, 0.8)

⭐ 看 LAST_VALUE(默认帧) 那一列：0.10 / 0.20 / 0.30 —— 它就是 v 本身。

因为默认帧的右边界是 CURRENT ROW，「窗里最后一行」当然就是当前行。你想问的是「这个分区里最后一个值是多少」，它回答的是「到我为止最后一个值是多少」。 ⚠️ 它不报错，还给了一列长得很像数据的数 —— 而 FIRST_VALUE 恰好没事（左边界本来就是分区第一行），于是同一句 SQL 里一半对一半错，最容易蒙混过关。

三种修法，按推荐顺序：

修法	写法	说明
⭐ 换个函数	`MAX(v) OVER (PARTITION BY uid)`	要的是最大值时最省事（实测 0.30 / 0.80）。⚠️ 只在「最后一个 = 最大的」时才等价
⭐ 倒过来取首值	`FIRST_VALUE(v) OVER (… ORDER BY t DESC)`	实测 0.3 / 0.8。最不容易写错，因为 `FIRST_VALUE` 没有这个陷阱
老实写全帧	`ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING`	实测 0.30 / 0.80。啰嗦但意思最直白

⭐ 顺带一个纯赚的语法：WINDOW 子句。 上面那句 SQL 里的 OVER w 和结尾的 WINDOW w AS (PARTITION BY uid ORDER BY t) 是一对 —— 窗口定义写一次，后面全用名字引用。

⚠️ 它不只是少打几个字：同一个窗口定义在一句 SQL 里抄三遍，改的时候就一定会漏改一处，而漏改之后两列用的是不同的窗口，结果照样出得来。⭐ 一句 SQL 里同一个 OVER (…) 出现两次以上，就该抽成命名窗口。

🧪 六、`GROUPS` 和 `EXCLUDE`：知道有这两个就够了

它俩用得少，但知道存在能省下「明明该有这个功能怎么没有」的半小时。

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE pay (day INTEGER, amt INTEGER)")
# ⭐ 第 2 天有两行（并列），第 4–6 天没有数据（缺口）
db.executemany("INSERT INTO pay VALUES (?,?)",
               [(1, 10), (2, 20), (2, 30), (3, 40), (7, 50)])

print("day  amt   ROWS 1P   RANGE 1P   GROUPS 1P")
for r in db.execute("""
    SELECT day, amt,
      SUM(amt) OVER (ORDER BY day ROWS   BETWEEN 1 PRECEDING AND CURRENT ROW) AS a,
      SUM(amt) OVER (ORDER BY day RANGE  BETWEEN 1 PRECEDING AND CURRENT ROW) AS b,
      SUM(amt) OVER (ORDER BY day GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW) AS c
    FROM pay ORDER BY day, amt"""):
    print(f" {r[0]}   {r[1]:3d}   {r[2]:5d}     {r[3]:6d}     {r[4]:7d}")

print("\nEXCLUDE：把自己 / 整个并列组从窗里踢出去（帧写成全分区）")
print("day  amt   NO OTHERS   CURRENT ROW   GROUP   TIES")
for r in db.execute("""
    SELECT day, amt,
      SUM(amt) OVER (ORDER BY day RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
                     EXCLUDE NO OTHERS)   AS a,
      SUM(amt) OVER (ORDER BY day RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
                     EXCLUDE CURRENT ROW) AS b,
      SUM(amt) OVER (ORDER BY day RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
                     EXCLUDE GROUP)       AS c,
      SUM(amt) OVER (ORDER BY day RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
                     EXCLUDE TIES)        AS d
    FROM pay ORDER BY day, amt"""):
    print(f" {r[0]}   {r[1]:3d}   {r[2]:7d}   {r[3]:9d}   {r[4]:7d}   {r[5]:4d}")

实测输出：

day  amt   ROWS 1P   RANGE 1P   GROUPS 1P
 1    10      10         10          10
 2    20      30         60          60
 2    30      50         60          60
 3    40      70         90          90
 7    50      90         50          90

EXCLUDE：把自己 / 整个并列组从窗里踢出去（帧写成全分区）
day  amt   NO OTHERS   CURRENT ROW   GROUP   TIES
 1    10       150         140       140    150
 2    20       150         130       100    120
 2    30       150         120       100    130
 3    40       150         110       110    150
 7    50       150         100       100    150

⭐ 最后一行（第 7 天）把 RANGE 和 GROUPS 的差别一次说清： RANGE 1 PRECEDING 找 day ∈ [6, 7]，第 6 天不存在，所以只有自己 → 50； GROUPS 1 PRECEDING 找「往回一个并列组」，也就是第 3 天那一组 → 40 + 50 = 90。

三者的一句话总结：

	往回 1 =	什么时候用它
`ROWS`	1 行	「最近 N 次」
`RANGE`	值差 ≤ 1 的行	「最近 N 天 / N 元」；⚠️ 缺口会让窗里行数变少 —— 那才是对的
`GROUPS`	前一个并列组（整组）	「往回 N 个有数据的日子」—— ⭐ 跳过缺口，不管缺了几天

EXCLUDE 的四个选项，把当前行和它的并列组挑出来处理：

写法	踢掉谁	实测第二行（`day=2, amt=20`，全组 150）
`EXCLUDE NO OTHERS`	谁也不踢（默认）	150
`EXCLUDE CURRENT ROW`	只踢自己	130（= 150 − 20）
`EXCLUDE GROUP`	踢掉整个并列组（含自己）	100（= 150 − 20 − 30）
`EXCLUDE TIES`	踢掉并列组里除自己以外的	120（= 150 − 30）

⭐ EXCLUDE CURRENT ROW 有一个真实用途：「这一组里除了我以外的均值」。它是 leave-one-out 编码的写法 —— ⚠️ 但请注意数据这一关 14 的结论：类别特征的目标编码标准解是 out-of-fold，不是 leave-one-out（那一章实测朴素目标编码把 CV 抬到 0.854、holdout 只有 0.577）。语法给你了，用不用是另一回事。

🗓️ 未实跑 —— 本机只有 SQLite 3.50.4：GROUPS 和 EXCLUDE 是 SQL:2011 的东西， PostgreSQL 从 11 开始支持，MySQL 8.0 的窗口帧只有 ROWS 和 RANGE。 ⭐ 用之前查一下你那个版本的文档 —— 这是本章唯一一处会随版本变的知识。

🛑 第二个休息点 —— 中段讲完了（约 23 分钟）。 最后一段还有：把帧接回真实需求：滚动特征，和「只看过去」 · 一张需求 → 帧的对照表这一章确实长，分三次读完全没问题 —— 回来直接从下一节接着看。

🛑 读到这里可以停 —— 已经读了约 62 分钟。 最后一段还有（约 25 分钟）：把帧接回真实需求：滚动特征，和「只看过去」 · 一张需求 → 帧的对照表回来的时候不用重读，直接从下一节接着看就行。

📋 七、把帧接回真实需求：滚动特征，和「只看过去」

现在把前面所有零件用在一个真问题上。一张客服工单评分表，⚠️ alice 在 3 月 4–8 日休假，一张单都没有：

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE tk (agent TEXT, ts TEXT, score REAL)")
db.executemany("INSERT INTO tk VALUES (?,?,?)", [
    ("alice", "2026-03-01", 4.0), ("alice", "2026-03-02", 5.0),
    ("alice", "2026-03-03", 3.0), ("alice", "2026-03-09", 2.0),
    ("alice", "2026-03-10", 5.0),
    ("bob",   "2026-03-01", 3.0), ("bob",   "2026-03-02", 3.0),
    ("bob",   "2026-03-03", 4.0), ("bob",   "2026-03-04", 4.0),
])

print("agent  ts           score  近3单均分  近3天均分  近3天单数")
for r in db.execute("""
    SELECT agent, ts, score,
      ROUND(AVG(score) OVER (PARTITION BY agent ORDER BY ts
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2)                  AS m_rows,
      ROUND(AVG(score) OVER (PARTITION BY agent ORDER BY julianday(ts)
            RANGE BETWEEN 2 PRECEDING AND CURRENT ROW), 2)                 AS m_range,
      COUNT(*) OVER (PARTITION BY agent ORDER BY julianday(ts)
            RANGE BETWEEN 2 PRECEDING AND CURRENT ROW)                     AS n_range
    FROM tk ORDER BY agent, ts"""):
    print(f"{r[0]:6} {r[1]}   {r[2]:.1f}     {r[3]:5.2f}      {r[4]:5.2f}       {r[5]}")

print("\n只看过去、不看现在：把右边界钉在 1 PRECEDING")
for r in db.execute("""
    SELECT agent, ts, score,
      ROUND(AVG(score) OVER (PARTITION BY agent ORDER BY ts
            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 2) AS prior
    FROM tk WHERE agent='alice' ORDER BY ts"""):
    print("   ", r)

print("\n💀 右边界写成 CURRENT ROW，本单自己的评分进了自己的特征：")
for r in db.execute("""
    SELECT agent, ts, score,
      ROUND(AVG(score) OVER (PARTITION BY agent ORDER BY ts
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 2) AS leaked
    FROM tk WHERE agent='alice' ORDER BY ts"""):
    print("   ", r)

实测输出：

agent  ts           score  近3单均分  近3天均分  近3天单数
alice  2026-03-01   4.0      4.00       4.00       1
alice  2026-03-02   5.0      4.50       4.50       2
alice  2026-03-03   3.0      4.00       4.00       3
alice  2026-03-09   2.0      3.33       2.00       1
alice  2026-03-10   5.0      3.33       3.50       2
bob    2026-03-01   3.0      3.00       3.00       1
bob    2026-03-02   3.0      3.00       3.00       2
bob    2026-03-03   4.0      3.33       3.33       3
bob    2026-03-04   4.0      3.67       3.67       3

只看过去、不看现在：把右边界钉在 1 PRECEDING
    ('alice', '2026-03-01', 4.0, None)
    ('alice', '2026-03-02', 5.0, 4.0)
    ('alice', '2026-03-03', 3.0, 4.5)
    ('alice', '2026-03-09', 2.0, 4.0)
    ('alice', '2026-03-10', 5.0, 3.5)

💀 右边界写成 CURRENT ROW，本单自己的评分进了自己的特征：
    ('alice', '2026-03-01', 4.0, 4.0)
    ('alice', '2026-03-02', 5.0, 4.5)
    ('alice', '2026-03-03', 3.0, 4.0)
    ('alice', '2026-03-09', 2.0, 3.5)
    ('alice', '2026-03-10', 5.0, 3.8)

三件事，一件比一件重要：

① bob 那四行 近3单 和 近3天 完全一样，alice 从 03-09 起开始分叉。 bob 天天有单，两种数法没差别；alice 休了五天假，ROWS 版把 3 月 2、3 日的分数拉了进来给出 3.33，RANGE 版诚实地只有她自己 2.00、近3天单数 是 1。 💀 这就是「同一段代码在 A 身上对、在 B 身上错」的来源 —— 而且 A 是大多数，所以抽查十个人可能九个都对。

② ORDER BY julianday(ts) 那一下是必须的。 ts 是文本，直接 ORDER BY ts RANGE BETWEEN 2 PRECEDING 会掉进第四节那个 💀（不报错，窗里只剩自己）。 julianday() 把日期变成一个数（单位是天），RANGE 2 PRECEDING 才真的是「往回两天」。

🗓️ 未实跑 —— SQLite 没有 INTERVAL：Postgres 和 MySQL 可以直接对时间戳列写 RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW（MySQL 8.0 写作 INTERVAL 7 DAY）。 ⭐ 语义是一样的：本机这版 SQLite 要先把时间变成数，别的库能直接对时间列做减法。

③ ⭐ 右边界写 CURRENT ROW 还是 1 PRECEDING，是「特征」和「泄漏」的分界线。

拿 alice 那两列并排看：

ts	score	只看过去（`AND 1 PRECEDING`）	含当前行（`AND CURRENT ROW`）
03-01	4.0	`None`	4.0
03-02	5.0	4.0	4.5
03-03	3.0	4.5	4.0
03-09	2.0	4.0	3.5
03-10	5.0	3.5	3.8

⭐ 把两列错开一行看：右边那列的第 i 行，恰好等于左边那列的第 i+1 行。 也就是说，含当前行的写法让每一行提前一格知道了未来 —— 它把「这一单的评分」揉进了「这一单之前的历史均分」里。如果你正在预测的就是这一单的评分，那这一列的重要性会排得很靠前，💀 离线指标好看，上线什么都不是。

⚠️ 这个坑不报错、不返回 NULL、数值范围也完全正常，唯一的痕迹是那个 None：正确的写法在每个分区的第一行必然是 NULL（没有「之前」可看）。 ⭐ 反过来当自查用：一列声称「历史均值」的特征，如果每个实体的第一行都不是 NULL，它大概率在偷看当前行。

这正是数据这一关 18 特征存储讲的 point-in-time 正确性在 SQL 里的样子 —— 那一章给的是 pandas 的实现和「该建不该建 Feature Store」的判据，⭐ 这里补的是同一件事的 SQL 写法：把右边界钉死在 1 PRECEDING。 ⚠️ 那一章还有一条更狠的：真正该对齐的是特征能被读到的时刻而不是它描述的时刻，那属于建模问题，不在本教程范围内。

🧭 八、一张需求 → 帧的对照表

写之前先把中文需求念一遍，然后照着抄：

中文需求	帧
到今天为止累计多少	`ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`
最近 3 次的均值	`ROWS BETWEEN 2 PRECEDING AND CURRENT ROW`
最近 3 天的均值	`RANGE BETWEEN 2 PRECEDING AND CURRENT ROW`（⚠️ 排序列必须是数）
到上一次为止的历史均值（做特征）	⭐ `ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING`
前后各一行的平滑	`ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING`
今天之后还剩多少	`ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING`
占本组的百分比	⭐ 不写窗口内 `ORDER BY`（帧自动是整个分区）
本组里除我之外的均值	`… UNBOUNDED FOLLOWING EXCLUDE CURRENT ROW`
本组最后一个值	⭐ `FIRST_VALUE(…) OVER (… ORDER BY t DESC)`，别用 `LAST_VALUE`

⭐ 一条能救你很多次的自查：任何一句带帧的 SQL，先加一列 COUNT(*) OVER (同一个窗口) 跑一遍。它会直接告诉你「每一行的窗里到底有几行」—— 本章三处实测（1,2,3,3,3,3 的边界缩水 / 缺口表的 1,2,3,1,2,3 / alice 的 1,2,3,1,2）全都是靠这一列看出问题的。看完再把它删掉。

🔗 这一章连到哪里

相关的地方	为什么
07-窗口函数.html	上一章。⭐ 它欠着的就是这一章：所有例子都没写帧，而帧一直在起作用。窗口函数不能写在 `WHERE` 里、组内 Top-N 的 CTE 写法都在那边
04-NULL与三值逻辑.html	帧里一行都没有时 `SUM` 给 `NULL` 不给 0（实测最后一行 `None`）；⭐ 而「历史均值第一行是不是 `NULL`」正是本章那条泄漏自查的判据
03-聚合与分组.html	帧只对聚合类窗口函数起作用，所以先得知道 `SUM` / `AVG` / `COUNT` 各自遇到 `NULL` 怎么办
09-计划里的连接与索引失效.html	下一章换个题目：同一句 SQL 语义没错但慢一百倍时，怎么看它到底在干什么
数据这一关 18 特征存储	⭐ 那一章 owns point-in-time 正确性是什么、要不要为它上一整套 Feature Store；本章补的是它在 SQL 里长什么样 —— 右边界钉在 `1 PRECEDING`
数据这一关 14 数据泄漏的七种来源	那一章把「SQL 对每一行样本用同一个固定窗口」列为最隐蔽的穿越型泄漏；⭐ 本章第七节那两列错开一行的对照，就是它的显微镜照片。目标编码该用 OOF 也在那边
推荐算法 07 特征工程	它要求统计特征同时喂 `ctr_1h / ctr_1d / ctr_7d / ctr_30d` 多个时间窗口却不给算法 —— ⭐ 算法就是四个不同宽度的 `RANGE` 帧
模型上线之后 04 训练推理一致性	那里用一段对照伪代码说明「同一个特征两套实现会对不齐」；⭐ 本章补上训练侧那一半的真写法长什么样

✅ 检查点

窗口里写了 ORDER BY 的聚合函数，默认帧是什么？不写 ORDER BY 呢？
五行数据里 03-02 有两笔（20 和 30），SUM(amt) OVER (ORDER BY d) 给这两行各是多少？换成 ROWS 版呢？为什么？
「累计和减掉自己」为什么算不出「排在我前面的总额」？实测那两个数是多少、正确答案又该是多少？
ROWS / RANGE / GROUPS 各自数的是什么？第 7 天那行的 RANGE 1 PRECEDING 和 GROUPS 1 PRECEDING 分别是多少、为什么差这么多？
⭐ 有缺口的点击表里，第 9 天的「最近 3 行」和「最近 3 天」分别算出多少？差别是怎么来的？
排序列是文本时写 RANGE 2 PRECEDING 会发生什么？怎么修？
LAST_VALUE(v) OVER (PARTITION BY uid ORDER BY t) 返回的是什么？实测那三个数是多少？三种修法分别是什么？
WINDOW w AS (…) 除了少打字还有什么价值？
⭐ 做「历史均分」特征时，帧的右边界写 CURRENT ROW 和写 1 PRECEDING 差在哪？实测那两列有什么关系？怎么一眼自查？
本章反复用的那个「一行 SQL 自查」是什么？

👀 答案

有 ORDER BY → RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW；没有 ORDER BY → ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING，也就是整个分区（实测五行全是 150）。
默认帧下两行都是 60；ROWS 版是 30 和 60。因为默认帧是 RANGE，而 RANGE 里的 CURRENT ROW 指的是所有排序值相同的行（并列组），不是「当前这一行」——两条 03-02 互为并列，右边界落在同一个位置。
因为累计和本身已经把并列的那一行算进来了。实测第一条 03-02 拿到 60 − 20 = 40，第二条拿到 60 − 30 = 30，⚠️ 正确答案应该是 10 和 30 —— 而且实测里第一条比第二条还大。💀 不报错，只给一个像那么回事的数。
ROWS 数行、RANGE 数值、GROUPS 数并列组。第 7 天：RANGE 1 PRECEDING 找 day ∈ [6,7]，第 6 天不存在 → 只有自己，50；GROUPS 1 PRECEDING 往回一个有数据的组（第 3 天）→ 40 + 50 = 90。
ROWS 给 16（9 + 3 + 4，把一周前第 2、3 天的数据拉了进来），RANGE 给 4（找 day ∈ [7,9]，第 7、8 天在表里根本不存在，窗内只剩自己，COUNT 也诚实地是 1）。判据：单位是「次」用 ROWS，单位是「天」用 RANGE。
💀 不报错，但窗里只剩当前行的并列组 —— 实测三行日期字符串的和分别是 5 / 3 / 9、窗内行数全是 1，看起来在算滚动窗口，其实每行都在算自己。✅ 修法：让排序列变成数，SQLite 用 ORDER BY julianday(ts)。（另一个坑会自己报错：RANGE 带偏移量时窗口只能有一个 ORDER BY 表达式，原文 RANGE with offset PRECEDING/FOLLOWING requires one ORDER BY expression。）
返回的就是 v 本身（实测 uid=1 那三行是 0.10 / 0.20 / 0.30），因为默认帧右边界是 CURRENT ROW，「窗里最后一行」当然是当前行。⚠️ 同一句里 FIRST_VALUE 却是对的（0.10），一半对一半错最容易蒙混。三种修法：换 MAX(v) OVER (PARTITION BY uid)（0.30/0.80，只在「最后一个 = 最大的」时等价）、FIRST_VALUE(v) OVER (… ORDER BY t DESC)（0.3/0.8，最不容易写错）、老实写 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。
它防的是改漏：同一个窗口定义抄三遍，改的时候一定会漏一处，而漏改之后两列用的是不同窗口、结果照样出得来。判据：同一个 OVER (…) 出现两次以上就抽成命名窗口。
CURRENT ROW 把这一行自己算进了「历史均值」。实测 alice 两列并排：正确列是 None / 4.0 / 4.5 / 4.0 / 3.5，泄漏列是 4.0 / 4.5 / 4.0 / 3.5 / 3.8 —— 错开一行完全重合，也就是泄漏版每一行提前一格知道了未来。自查：正确写法在每个实体的第一行必然是 NULL（没有「之前」可看），所以一列声称「历史均值」的特征如果第一行不是 NULL，它大概率在偷看当前行。
加一列 COUNT(*) OVER (同一个窗口)，它直接告诉你每行的窗里有几行。本章三处实测（1,2,3,3,3,3 的边界缩水、有缺口时的 1,2,3、alice 的 1,2,3,1,2）全是靠它看出来的，看完删掉即可。

🛑 可以停在这里

⚡ 走神救援

⭐ 帧回答「这一行的窗里装了哪些行」——ROWS 数行，RANGE 数值，GROUPS 数并列组。

上一章一句帧都没写，但每个 OVER (…) 都有帧。排名类和 LAG/LEAD 不受帧影响；只要写下「聚合 + 窗口内 ORDER BY」，就落进帧的地盘了。

💀 默认帧是 RANGE，而 RANGE 里的「当前行」指的是「整个并列组」不是「当前这一行」：同一时刻有两笔时，两行拿到的累计和一样。于是「累计和减自己 = 排我前面的总额」这个顺手写法会给出错的数——第一条甚至比第二条还大，而且不报错。

🕳️ 全章正题：数据有缺口时两种帧会分叉。 「最近 3 行」会把一周前的数据拉进来，「最近 3 天」则可能只剩自己一行。⭐ 判据：单位是「次/条/单」用 ROWS，是「天/小时/元」用 RANGE。

⚠️ 两个附带的坑：RANGE 带偏移量时只能有一个排序表达式（会报错，便宜）；💀 排序列是文本时 RANGE 2 PRECEDING 不报错，但窗里只剩自己——修法是让排序列变成数。

🎭 默认帧的第二起事故是 LAST_VALUE 不写帧时返回的是当前行的值，而同一句里的 FIRST_VALUE 恰好是对的——⭐ 一半对一半错最容易蒙混过去。

顺带：命名窗口 WINDOW w AS (…) 防的是改漏——同一个定义抄三遍，漏改一处后两列用的是不同窗口，而结果照样出得来。

下一节 👉 09-计划里的连接与索引失效.md

打卡记录保存在你的浏览器里，首页能看到总进度

08 · 窗口帧：ROWS 数行，RANGE 数值

🎯 一句话

🕳️ 一、你早就写过帧了，只是没写出来

📐 二、帧的完整语法：三个部件

🧮 三、ROWS：累计、移动平均、还剩多少

🕳️ 四、RANGE：数的是值，不是行

🎭 五、LAST_VALUE 的默认帧陷阱，和命名窗口

🧪 六、GROUPS 和 EXCLUDE：知道有这两个就够了

📋 七、把帧接回真实需求：滚动特征，和「只看过去」

🧭 八、一张需求 → 帧的对照表

🔗 这一章连到哪里

✅ 检查点

🛑 可以停在这里

🧮 三、`ROWS`：累计、移动平均、还剩多少

🕳️ 四、`RANGE`：数的是值，不是行

🎭 五、`LAST_VALUE` 的默认帧陷阱，和命名窗口

🧪 六、`GROUPS` 和 `EXCLUDE`：知道有这两个就够了