Window Function

GROUP BY ยุบหลาย row เป็น row สรุปหนึ่ง row และบางครั้งนั่นผิดพอดี: คุณอยากได้ทั้งสรุป และ รายละเอียดเดิมข้าง ๆ กัน window function ทำอย่างนั้น window function มองชุด row ที่เกี่ยวข้องกับ row ปัจจุบัน — window ของมัน — แล้วคำนวณค่า แต่ทุก row ยังอยู่ในผลลัพธ์ วิธีนี้เพิ่ม rank, ยอดสะสม หรือค่าเฉลี่ยต่อกลุ่มเป็น column เพิ่มได้โดยไม่เสียรายละเอียด

ทุกอย่างด้านล่างรันบน PostgreSQL 18.6 (postgres:18.6, aarch64, Docker Desktop บน Mac, กันยายน 2026) คัดลอกจาก psql fence sql รันจากบนลงล่างในฐานข้อมูลเปล่าหนึ่งตัว ตัวเลขเวลามาจาก Mac เครื่องนี้

ชุดข้อมูล

ชุดข้อมูลมาตรฐานของ module นี้ region West มี เสมอ: สองยอดขายที่ 400 เล่ม เสมอนี้ไม่ได้มีไว้ประดับ ผลหลายอย่างข้างล่างมีความหมายเพราะมัน

-- sql/setup.sql
CREATE TABLE authors (
  id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL,
  born int
);
CREATE TABLE books (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title       text NOT NULL,
  author_id   bigint REFERENCES authors (id),
  published   int,
  copies_sold int NOT NULL DEFAULT 0
);
CREATE TABLE sales (
  id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  book_id bigint REFERENCES books (id),
  region  text NOT NULL,
  copies  int NOT NULL
);
INSERT INTO authors (name, born) VALUES ('Mara Linde', 1968), ('Owen Pryce', 1975), ('Sela Vance', 1990);
INSERT INTO books (title, author_id, published, copies_sold) VALUES
  ('Quiet Harbors', 1, 2003, 450), ('The Glass Orchard', 1, 2018, 1250), ('Tidal Notes', 2, 2011, 600), ('Unlinked Draft', NULL, 2020, 0);
INSERT INTO sales (book_id, region, copies) VALUES
  (2, 'East', 900), (3, 'West', 400), (1, 'West', 400), (3, 'East', 200), (2, 'West', 150), (1, 'East', 50);

clause OVER

เปลี่ยนฟังก์ชันธรรมดาให้เป็น window function ด้วยการเพิ่ม clause OVER ข้างใน PARTITION BY แบ่ง row เป็นกลุ่ม (เหมือน GROUP BY แต่ไม่ยุบ) และ ORDER BY กำหนดลำดับในแต่ละ partition เริ่มจาก aggregate ธรรมดาบน window: แต่ละยอดขายข้างยอดรวมของ region

psql

-- sql/over-partition.sql
SELECT s.region, b.title, s.copies,
       sum(s.copies) OVER (PARTITION BY s.region) AS region_total
FROM sales AS s
JOIN books AS b ON b.id = s.book_id
ORDER BY s.region, s.copies DESC, b.title;

Node.js

const res = await pool.query(`
  SELECT s.region, b.title, s.copies,
         sum(s.copies) OVER (PARTITION BY s.region) AS region_total
  FROM sales AS s
  JOIN books AS b ON b.id = s.book_id
  ORDER BY s.region, s.copies DESC, b.title
`);

Python

cur.execute("""
    SELECT s.region, b.title, s.copies,
           sum(s.copies) OVER (PARTITION BY s.region) AS region_total
    FROM sales AS s
    JOIN books AS b ON b.id = s.book_id
    ORDER BY s.region, s.copies DESC, b.title
""")
rows = cur.fetchall()

Go

rows, err := conn.Query(ctx, `
  SELECT s.region, b.title, s.copies,
         sum(s.copies) OVER (PARTITION BY s.region) AS region_total
  FROM sales AS s
  JOIN books AS b ON b.id = s.book_id
  ORDER BY s.region, s.copies DESC, b.title`)
defer rows.Close()

Rust

let rows = sqlx::query(
    "SELECT s.region, b.title, s.copies,
            sum(s.copies) OVER (PARTITION BY s.region) AS region_total
     FROM sales AS s
     JOIN books AS b ON b.id = s.book_id
     ORDER BY s.region, s.copies DESC, b.title",
)
.fetch_all(&pool)
.await?;

 region |       title       | copies | region_total
--------+-------------------+--------+--------------
 East   | The Glass Orchard |    900 |         1150
 East   | Tidal Notes       |    200 |         1150
 East   | Quiet Harbors     |     50 |         1150
 West   | Quiet Harbors     |    400 |          950
 West   | Tidal Notes       |    400 |          950
 West   | The Glass Orchard |    150 |          950
(6 rows)

เทียบกับ GROUP BY ที่ให้หนึ่ง row ต่อ region และเสียรายละเอียด:

-- sql/group-by-vs-window.sql
-- GROUP BY: one row per region, the detail is gone
SELECT region, sum(copies) AS region_total FROM sales GROUP BY region ORDER BY region;

 region | region_total
--------+--------------
 East   |         1150
 West   |          950
(2 rows)

เพราะ window เก็บทุก row คุณจึงคำนวณสัดส่วนของกลุ่มใน query เดียวกันได้:

-- sql/share-of-region.sql
SELECT s.region, b.title, s.copies,
       round(100.0 * s.copies / sum(s.copies) OVER (PARTITION BY s.region), 1) AS pct_of_region
FROM sales AS s
JOIN books AS b ON b.id = s.book_id
ORDER BY s.region, s.copies DESC, b.title;

 region |       title       | copies | pct_of_region
--------+-------------------+--------+---------------
 East   | The Glass Orchard |    900 |          78.3
 East   | Tidal Notes       |    200 |          17.4
 East   | Quiet Harbors     |     50 |           4.3
 West   | Quiet Harbors     |    400 |          42.1
 West   | Tidal Notes       |    400 |          42.1
 West   | The Glass Orchard |    150 |          15.8
(6 rows)

ROW_NUMBER, RANK และ DENSE_RANK

ฟังก์ชันให้เลขทั้งสามต่างกันที่การจัดการกับ row เสมอ ตรงนี้อยู่เคียงกันบน window เดียวกัน clause WINDOW w AS (...) ตั้งชื่อให้ครั้งเดียวจึงไม่ต้องเขียนซ้ำ:

psql

-- sql/rank-functions.sql
SELECT s.region, b.title, s.copies,
       row_number() OVER w AS row_number,
       rank()       OVER w AS rank,
       dense_rank() OVER w AS dense_rank
FROM sales AS s
JOIN books AS b ON b.id = s.book_id
WINDOW w AS (PARTITION BY s.region ORDER BY s.copies DESC)
ORDER BY s.region, s.copies DESC, b.title;

Node.js

const res = await pool.query(`
  SELECT s.region, b.title, s.copies,
         row_number() OVER w AS row_number,
         rank()       OVER w AS rank,
         dense_rank() OVER w AS dense_rank
  FROM sales AS s
  JOIN books AS b ON b.id = s.book_id
  WINDOW w AS (PARTITION BY s.region ORDER BY s.copies DESC)
  ORDER BY s.region, s.copies DESC, b.title
`);

Python

cur.execute("""
    SELECT s.region, b.title, s.copies,
           row_number() OVER w AS row_number,
           rank()       OVER w AS rank,
           dense_rank() OVER w AS dense_rank
    FROM sales AS s
    JOIN books AS b ON b.id = s.book_id
    WINDOW w AS (PARTITION BY s.region ORDER BY s.copies DESC)
    ORDER BY s.region, s.copies DESC, b.title
""")
rows = cur.fetchall()

Go

rows, err := conn.Query(ctx, `
  SELECT s.region, b.title, s.copies,
         row_number() OVER w AS row_number,
         rank()       OVER w AS rank,
         dense_rank() OVER w AS dense_rank
  FROM sales AS s
  JOIN books AS b ON b.id = s.book_id
  WINDOW w AS (PARTITION BY s.region ORDER BY s.copies DESC)
  ORDER BY s.region, s.copies DESC, b.title`)
defer rows.Close()

Rust

let rows = sqlx::query(
    "SELECT s.region, b.title, s.copies,
            row_number() OVER w AS row_number,
            rank()       OVER w AS rank,
            dense_rank() OVER w AS dense_rank
     FROM sales AS s
     JOIN books AS b ON b.id = s.book_id
     WINDOW w AS (PARTITION BY s.region ORDER BY s.copies DESC)
     ORDER BY s.region, s.copies DESC, b.title",
)
.fetch_all(&pool)
.await?;

 region |       title       | copies | row_number | rank | dense_rank
--------+-------------------+--------+------------+------+------------
 East   | The Glass Orchard |    900 |          1 |    1 |          1
 East   | Tidal Notes       |    200 |          2 |    2 |          2
 East   | Quiet Harbors     |     50 |          3 |    3 |          3
 West   | Quiet Harbors     |    400 |          2 |    1 |          1
 West   | Tidal Notes       |    400 |          1 |    1 |          1
 West   | The Glass Orchard |    150 |          3 |    3 |          2
(6 rows)

ดูที่คู่เสมอของ West (400 และ 400):

• row_number() ไม่เคยซ้ำ: 1 และ 2 ตัวไหน ในสอง row ที่เสมอกันได้ 1 นั้น query ไม่ได้กำหนด ตรงนี้ Tidal Notes ได้ 1 และ Quiet Harbors ได้ 2 plan อื่นอาจสลับกัน เพิ่มตัวตัดสินเสมอใน ORDER BY ของ window เมื่อเลขต้องทำซ้ำได้
• rank() ให้ทั้งคู่ rank เท่ากัน (1) แล้วข้าม: ตัวถัดไปคือ 3
• dense_rank() ให้ทั้งคู่ 1 และไม่ข้าม: ตัวถัดไปคือ 2

ผลลัพธ์จาก driver สำหรับ query ในบทนี้ แต่ละอันรันหนึ่งครั้ง (pg คืนผล bigint เช่น row_number() และ sum(int) เป็น string):

$ (each snippet run once against the dataset above)
[rank]     Python (psycopg 3.3.6): [('East', 'The Glass Orchard', 900, 1, 1, 1), ('East', 'Tidal Notes', 200, 2, 2, 2), ('East', 'Quiet Harbors', 50, 3, 3, 3), ('West', 'Quiet Harbors', 400, 2, 1, 1), ('West', 'Tidal Notes', 400, 1, 1, 1), ('West', 'The Glass Orchard', 150, 3, 3, 2)]
[rank]     Node.js (pg 8.23.0), first West row: { region: 'West', title: 'Quiet Harbors', copies: 400, row_number: '2', rank: '1', dense_rank: '1' }
[running]  Python:                 [('East', 'The Glass Orchard', 900, 900), ('East', 'Tidal Notes', 200, 1100), ('East', 'Quiet Harbors', 50, 1150), ('West', 'Quiet Harbors', 400, 400), ('West', 'Tidal Notes', 400, 800), ('West', 'The Glass Orchard', 150, 950)]
[running]  Node.js, first West row: { region: 'West', title: 'Quiet Harbors', copies: 400, running_rows: '400' }
[all]      Go (pgx v5.11.0) and Rust (sqlx 0.9.0) returned the same rows as Python for all three queries

ยอดสะสมและ default frame

window ที่มี ORDER BY มี frame: ส่วนย่อยของ partition ที่ฟังก์ชันมองเห็น ถ้าไม่เขียน frame ค่าเริ่มต้นคือ RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ซึ่งรวมทุก peer ของ row ปัจจุบัน คือทุก row ที่เรียงเท่ากับมัน เมื่อค่าเรียงไม่ซ้ำ มันทำตัวเป็น “ทุกอย่างถึง row นี้” เมื่อมีเสมอ มันไม่เป็นอย่างนั้น:

-- sql/running-total-default.sql
-- default frame: RANGE ... CURRENT ROW, which includes all peers (rows with an equal ORDER BY value)
SELECT s.region, b.title, s.copies,
       sum(s.copies) OVER (PARTITION BY s.region ORDER BY s.copies DESC) AS running_default
FROM sales AS s
JOIN books AS b ON b.id = s.book_id
ORDER BY s.region, s.copies DESC, b.title;

 region |       title       | copies | running_default
--------+-------------------+--------+-----------------
 East   | The Glass Orchard |    900 |             900
 East   | Tidal Notes       |    200 |            1100
 East   | Quiet Harbors     |     50 |            1150
 West   | Quiet Harbors     |    400 |             800
 West   | Tidal Notes       |    400 |             800
 West   | The Glass Orchard |    150 |             950
(6 rows)

West แสดง 800, 800, 950 ไม่ใช่ 400, 800, 950: สอง row ที่ 400 เป็น peer กัน แต่ละตัวจึง “เห็น” อีกตัวอยู่แล้ว นั่นคือยอดสะสมที่ผิดแบบเงียบ ๆ เขียน frame ให้ชัดด้วย ROWS และเพิ่มตัวตัดสินเสมอเพื่อให้ลำดับถูกกำหนด:

psql

-- sql/running-total-rows.sql
SELECT s.region, b.title, s.copies,
       sum(s.copies) OVER (
         PARTITION BY s.region
         ORDER BY s.copies DESC, b.title
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_rows
FROM sales AS s
JOIN books AS b ON b.id = s.book_id
ORDER BY s.region, s.copies DESC, b.title;

Node.js

const res = await pool.query(`
  SELECT s.region, b.title, s.copies,
         sum(s.copies) OVER (
           PARTITION BY s.region
           ORDER BY s.copies DESC, b.title
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
         ) AS running_rows
  FROM sales AS s
  JOIN books AS b ON b.id = s.book_id
  ORDER BY s.region, s.copies DESC, b.title
`);

Python

cur.execute("""
    SELECT s.region, b.title, s.copies,
           sum(s.copies) OVER (
             PARTITION BY s.region
             ORDER BY s.copies DESC, b.title
             ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
           ) AS running_rows
    FROM sales AS s
    JOIN books AS b ON b.id = s.book_id
    ORDER BY s.region, s.copies DESC, b.title
""")
rows = cur.fetchall()

Go

rows, err := conn.Query(ctx, `
  SELECT s.region, b.title, s.copies,
         sum(s.copies) OVER (
           PARTITION BY s.region
           ORDER BY s.copies DESC, b.title
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
         ) AS running_rows
  FROM sales AS s
  JOIN books AS b ON b.id = s.book_id
  ORDER BY s.region, s.copies DESC, b.title`)
defer rows.Close()

Rust

let rows = sqlx::query(
    "SELECT s.region, b.title, s.copies,
            sum(s.copies) OVER (
              PARTITION BY s.region
              ORDER BY s.copies DESC, b.title
              ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
            ) AS running_rows
     FROM sales AS s
     JOIN books AS b ON b.id = s.book_id
     ORDER BY s.region, s.copies DESC, b.title",
)
.fetch_all(&pool)
.await?;

 region |       title       | copies | running_rows
--------+-------------------+--------+--------------
 East   | The Glass Orchard |    900 |          900
 East   | Tidal Notes       |    200 |         1100
 East   | Quiet Harbors     |     50 |         1150
 West   | Quiet Harbors     |    400 |          400
 West   | Tidal Notes       |    400 |          800
 West   | The Glass Orchard |    150 |          950
(6 rows)

ROWS, RANGE และ GROUPS วัด frame ต่างกัน บนหกยอดขายที่เรียงตาม copies frame ย้อนหลังหนึ่งขั้นให้สามผลรวมที่ต่างกันสำหรับ row เดียวกัน: ROWS นับ row จริง, RANGE นับตามระยะของค่า (ที่นี่: row ที่ copies ต่ำกว่า row ปัจจุบันไม่เกิน 100), GROUPS นับเป็นกลุ่มของ peer (docs):

-- sql/frames.sql
-- the same 6 rows, ordered by copies, under the three frame modes
SELECT copies,
       sum(copies) OVER (ORDER BY copies ROWS   BETWEEN 1 PRECEDING AND CURRENT ROW) AS rows_frame,
       sum(copies) OVER (ORDER BY copies RANGE  BETWEEN 100 PRECEDING AND CURRENT ROW) AS range_frame,
       sum(copies) OVER (ORDER BY copies GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW) AS groups_frame
FROM sales
ORDER BY copies, id;

 copies | rows_frame | range_frame | groups_frame
--------+------------+-------------+--------------
     50 |         50 |          50 |           50
    150 |        200 |         200 |          200
    200 |        350 |         350 |          350
    400 |        600 |         800 |         1000
    400 |        800 |         800 |         1000
    900 |       1300 |         900 |         1700
(6 rows)

first_value และ last_value

default frame เดียวกันจับ last_value ด้วย เมื่อมี ORDER BY และไม่มี frame frame หยุดที่ row ปัจจุบัน (และ peer ของมัน) “ค่าสุดท้าย” จึงเป็นค่าปัจจุบัน:

-- sql/last-value-trap.sql
SELECT region, copies,
       last_value(copies) OVER (PARTITION BY region ORDER BY copies DESC) AS last_default,
       last_value(copies) OVER (
         PARTITION BY region ORDER BY copies DESC
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS last_real,
       first_value(copies) OVER (PARTITION BY region ORDER BY copies DESC) AS first_val
FROM sales
ORDER BY region, copies DESC, id;

 region | copies | last_default | last_real | first_val
--------+--------+--------------+-----------+-----------
 East   |    900 |          900 |        50 |       900
 East   |    200 |          200 |        50 |       900
 East   |     50 |           50 |        50 |       900
 West   |    400 |          400 |       150 |       400
 West   |    400 |          400 |       150 |       400
 West   |    150 |          150 |       150 |       400
(6 rows)

last_default เท่ากับ copies ทุก row ส่วน last_real ที่ใช้ ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING คือยอดขายน้อยที่สุดของ region จริง ๆ (50 และ 150)

LAG, LEAD, NTILE และพวกเดียวกัน

lag และ lead อ่าน column จาก row ก่อนหน้าและถัดไปของ window: ไม่ต้อง self-join row แรกและสุดท้ายไม่มีอะไรให้อ่าน จึงได้ NULL:

-- sql/lag-lead.sql
SELECT title, published,
       lag(title)  OVER (ORDER BY published) AS previous_title,
       lead(title) OVER (ORDER BY published) AS next_title,
       published - lag(published) OVER (ORDER BY published) AS years_since_previous
FROM books
ORDER BY published;

       title       | published |  previous_title   |    next_title     | years_since_previous
-------------------+-----------+-------------------+-------------------+----------------------
 Quiet Harbors     |      2003 |                   | Tidal Notes       |
 Tidal Notes       |      2011 | Quiet Harbors     | The Glass Orchard |                    8
 The Glass Orchard |      2018 | Tidal Notes       | Unlinked Draft    |                    7
 Unlinked Draft    |      2020 | The Glass Orchard |                   |                    2
(4 rows)

ntile(n) ตัด row ที่เรียงแล้วเป็น n ถัง และ percent_rank() ให้ตำแหน่งสัมพัทธ์ของ row (0 สำหรับตัวแรก 1 สำหรับตัวสุดท้าย):

-- sql/ntile-percent.sql
SELECT title, copies_sold,
       ntile(2)       OVER (ORDER BY copies_sold DESC) AS half,
       percent_rank() OVER (ORDER BY copies_sold)      AS pct_rank
FROM books
ORDER BY copies_sold DESC;

       title       | copies_sold | half |      pct_rank
-------------------+-------------+------+--------------------
 The Glass Orchard |        1250 |    1 |                  1
 Tidal Notes       |         600 |    1 | 0.6666666666666666
 Quiet Harbors     |         450 |    2 | 0.3333333333333333
 Unlinked Draft    |           0 |    2 |                  0
(4 rows)

Window เก็บ row; GROUP BY ยุบ row

window function ถูกประเมินหลัง WHERE, GROUP BY และ HAVING ก่อน ORDER BY และ LIMIT เล็กน้อย ผลที่ตามมาคือกรองด้วย window function ใน WHERE ไม่ได้ เพราะ ณ จุดนั้นมันยังไม่มีอยู่:

-- @expect-error 42P20
SELECT region, copies FROM sales
WHERE rank() OVER (PARTITION BY region ORDER BY copies DESC) = 1;

ERROR:  window functions are not allowed in WHERE
LINE 2: WHERE rank() OVER (PARTITION BY region ORDER BY copies DESC)...
              ^

เพื่อกรองด้วย rank (“อันดับหนึ่งต่อกลุ่ม” แบบคลาสสิก) ให้คำนวณใน subquery หรือ CTE แล้วกรองข้างนอก:

-- sql/top-n-per-group.sql
SELECT region, title, copies
FROM (
  SELECT s.region, b.title, s.copies,
         row_number() OVER (PARTITION BY s.region ORDER BY s.copies DESC, b.title) AS rn
  FROM sales s JOIN books b ON b.id = s.book_id
) AS ranked
WHERE rn = 1
ORDER BY region;

 region |       title       | copies
--------+-------------------+--------
 East   | The Glass Orchard |    900
 West   | Quiet Harbors     |    400
(2 rows)

plan หน้าตาเป็นอย่างไร

plan จริงต้องใช้ row จริง หัวข้อนี้สร้าง big_sales ที่มี 500 000 row ใน 5 region:

-- sql/window-plans-setup.sql
CREATE TABLE big_sales (
  id     int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  region text NOT NULL,
  copies int NOT NULL
);
INSERT INTO big_sales (region, copies)
SELECT (ARRAY['East', 'West', 'North', 'South', 'Central'])[1 + g % 5], (g::bigint * 7919 % 100003)::int
FROM generate_series(1, 500000) AS g;
ANALYZE big_sales;

CREATE TABLE
INSERT 0 500000
ANALYZE

-- sql/window-plan.sql
EXPLAIN (ANALYZE, COSTS OFF)
SELECT id, region, copies,
       row_number() OVER (PARTITION BY region ORDER BY copies DESC) AS rn
FROM big_sales;

                                      QUERY PLAN
--------------------------------------------------------------------------------------
 WindowAgg (actual time=376.569..545.433 rows=500000.00 loops=1)
   Window: w1 AS (PARTITION BY region ORDER BY copies ROWS UNBOUNDED PRECEDING)
   Storage: Memory  Maximum Storage: 17kB
   Buffers: shared hit=2709, temp read=1591 written=1597
   ->  Sort (actual time=376.558..463.592 rows=500000.00 loops=1)
         Sort Key: region, copies DESC
         Sort Method: external merge  Disk: 12728kB
         Buffers: shared hit=2709, temp read=1591 written=1597
         ->  Seq Scan on big_sales (actual time=0.010..32.729 rows=500000.00 loops=1)
               Buffers: shared hit=2703
 Planning:
   Buffers: shared hit=85
 Planning Time: 0.272 ms
 Execution Time: 561.400 ms
(14 rows)

window function คือ node WindowAgg ที่รับจาก Sort บน (region, copies DESC): การ sort คือราคาของ PARTITION BY ... ORDER BY และที่นี่มันครอบงำ (node Sort กินราว 460 ms จาก 560 ms; Sort Method: external merge Disk: 12728kB เพราะการ sort 12 MB ไม่พอดีกับ work_mem ค่าเริ่มต้น 4 MB และ buffer temp read/written ยืนยัน) เพิ่ม work_mem สำหรับ transaction เดียวช่วยกำจัดการ spill:

-- sql/window-plan-work-mem.sql
BEGIN;
SET LOCAL work_mem = '64MB';
EXPLAIN (ANALYZE, COSTS OFF)
SELECT id, region, copies,
       row_number() OVER (PARTITION BY region ORDER BY copies DESC) AS rn
FROM big_sales;
ROLLBACK;

BEGIN
SET
                                      QUERY PLAN
--------------------------------------------------------------------------------------
 WindowAgg (actual time=453.913..560.648 rows=500000.00 loops=1)
   Window: w1 AS (PARTITION BY region ORDER BY copies ROWS UNBOUNDED PRECEDING)
   Storage: Memory  Maximum Storage: 17kB
   Buffers: shared hit=2709
   ->  Sort (actual time=453.899..478.110 rows=500000.00 loops=1)
         Sort Key: region, copies DESC
         Sort Method: quicksort  Memory: 27914kB
         Buffers: shared hit=2709
         ->  Seq Scan on big_sales (actual time=0.009..31.541 rows=500000.00 loops=1)
               Buffers: shared hit=2703
 Planning:
   Buffers: shared hit=85
 Planning Time: 0.232 ms
 Execution Time: 576.866 ms
(14 rows)

ROLLBACK

มันกลายเป็น quicksort Memory: 27914kB และไม่มี temp buffer และ ไม่ได้เร็วขึ้น บน Mac เครื่องนี้ (577 ms เทียบกับ 561 ms รันครั้งเดียว อยู่ในระดับสัญญาณรบกวน): การ sort ครึ่งล้าน row ครอบงำทั้งสองแบบ index บน (region, copies DESC) ให้ลำดับได้และตัด sort ออก ซึ่ง module เรื่อง indexes ครอบคลุม

เมื่อต้องการแค่ row บนสุดของแต่ละ partition PostgreSQL 15+ หยุดก่อนได้ WHERE rn <= 3 ด้านนอกปรากฏเป็น Run Condition บน WindowAgg และ node ปล่อย 15 row (3 ต่อ region) แทน 500 000:

-- sql/window-plan-top3.sql
EXPLAIN (ANALYZE, COSTS OFF)
SELECT * FROM (
  SELECT id, region, copies,
         row_number() OVER (PARTITION BY region ORDER BY copies DESC) AS rn
  FROM big_sales
) AS ranked
WHERE rn <= 3;

                                             QUERY PLAN
----------------------------------------------------------------------------------------------------
 WindowAgg (actual time=376.753..488.553 rows=15.00 loops=1)
   Window: w1 AS (PARTITION BY big_sales.region ORDER BY big_sales.copies ROWS UNBOUNDED PRECEDING)
   Run Condition: (row_number() OVER w1 <= 3)
   Storage: Memory  Maximum Storage: 17kB
   Buffers: shared hit=2709, temp read=1591 written=1597
   ->  Sort (actual time=376.737..463.120 rows=500000.00 loops=1)
         Sort Key: big_sales.region, big_sales.copies DESC
         Sort Method: external merge  Disk: 12728kB
         Buffers: shared hit=2709, temp read=1591 written=1597
         ->  Seq Scan on big_sales (actual time=0.008..31.975 rows=500000.00 loops=1)
               Buffers: shared hit=2703
 Planning:
   Buffers: shared hit=89
 Planning Time: 0.244 ms
 Execution Time: 489.599 ms
(15 rows)

การ sort ยังต้องอ่านทุกอย่าง แต่ไม่มีอะไรด้านล่างที่เห็นอีก 499 985 row ที่เหลือ

Reading AI-generated code

ผู้ช่วย AI ถูกขอยอดสะสมของ copies ต่อ region โดยเริ่มจากยอดขายสูงสุด และเขียนเวอร์ชันที่ทุก tutorial เขียน:

🐛 หา bug ในโค้ดนี้

-- AI comment: "running total of copies per region, biggest sale first"
SELECT region, copies,
     SUM(copies) OVER (PARTITION BY region ORDER BY copies DESC) AS running_total
FROM sales
ORDER BY region, copies DESC;

เฉลย

ดูถูก และ East ถูก แต่ West ผิด: ด้วย frame RANGE เริ่มต้น สอง row ที่ 400 เป็น peer ทั้งคู่จึงแสดง 800 และยอดสะสมไม่เคยผ่าน 400 ไม่มี error:

-- sql/stb-wrong.sql
SELECT region, copies,
       SUM(copies) OVER (PARTITION BY region ORDER BY copies DESC) AS running_total
FROM sales
ORDER BY region, copies DESC;

 region | copies | running_total
--------+--------+---------------
 East   |    900 |           900
 East   |    200 |          1100
 East   |     50 |          1150
 West   |    400 |           800
 West   |    400 |           800
 West   |    150 |           950
(6 rows)

สองการเปลี่ยนแก้ได้: ตัวตัดสินเสมอใน ORDER BY เพื่อให้ลำดับสมบูรณ์ และ frame ROWS ที่ชัดเจนเพื่อให้แต่ละ row เห็นเฉพาะตัวเองกับ row ก่อนหน้า

-- sql/stb-fixed.sql
SELECT region, copies,
       SUM(copies) OVER (
         PARTITION BY region
         ORDER BY copies DESC, id
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM sales
ORDER BY region, copies DESC, id;

 region | copies | running_total
--------+--------+---------------
 East   |    900 |           900
 East   |    200 |          1100
 East   |     50 |          1150
 West   |    400 |           400
 West   |    400 |           800
 West   |    150 |           950
(6 rows)

Mental Model

window function คำนวณค่าให้แต่ละ row โดยมองที่ frame ของ row รอบ ๆ แล้ววางค่ากลับข้าง row นั้น สามการตั้งค่ากำหนดสิ่งที่มันเห็น: PARTITION BY (row ไหนเกี่ยวข้องกัน), ORDER BY (ลำดับใด) และ frame (มองไกลแค่ไหนในลำดับนั้น) frame มีค่าเริ่มต้นโดยนัย และค่านั้นคือ RANGE ... CURRENT ROW ที่รวม peer เมื่อไรที่ได้ตัวเลขผิดจาก window function ผู้ต้องสงสัยอันดับแรกคือ frame

เคล็ดลับและข้อควรระวัง

• OVER ของ window function คือสิ่งที่ทำให้มันเป็น window function: sum(x) เดี่ยว ๆ คือ aggregate, sum(x) OVER (...) คือ window
• ใช้ window function ใน WHERE (42P20), GROUP BY หรือ HAVING ไม่ได้ ห่อ query ใน subquery หรือ CTE แล้วกรองข้างนอก
• เมื่อมี ORDER BY และไม่มี frame ชัดเจน frame คือ RANGE ... CURRENT ROW (รวม peer) เขียน ROWS BETWEEN ... สำหรับยอดสะสมและ last_value
• row_number() บน row ที่เสมอกันเป็นแบบสุ่ม เพิ่มตัวตัดสินใน ORDER BY ของ window ถ้าต้องการเลขที่เสถียร
• PARTITION BY ... ORDER BY มีต้นทุนเป็น sort ตรวจ EXPLAIN บน table ใหญ่ ระวัง external merge และดูว่า index ให้ลำดับได้ไหม

🐘 Window หรือ GROUP BY?

ต้องการหนึ่ง row ต่อกลุ่ม? GROUP BY ต้องการทุก row พร้อมสิ่งที่คำนวณทั้งกลุ่ม (rank, ยอดสะสม, สัดส่วนของกลุ่ม, row ก่อนหน้า)? window function ต้องการทั้งสอง? aggregate ก่อนใน subquery แล้ว window ทับผลลัพธ์

ข้อแลกเปลี่ยน

ตัวเลือก	Benefit	Cost
Window function	เก็บทุก row; ไม่ต้อง self-join สำหรับ rank, ยอดสะสม, lag	ต้อง sort ต่อ PARTITION BY/ORDER BY ที่ต่างกัน (sort ราว 460 ms จาก 560 ms ข้างบน); default frame ทำให้ประหลาดใจ
GROUP BY + join กลับ	ใช้ได้ทุกที่ ชัดเจน	สองรอบและ join เพื่อแปะยอดรวมกลับ
correlated subquery ต่อ row	อ่านง่ายสำหรับตัวเลขเดียว	รันซ้ำต่อ row (ดูบท subquery)
จัดอันดับฝั่งแอป	ยืดหยุ่น	ดึงทุก row ผ่านเครือข่าย

Common Misconceptions

• “SUM() OVER (ORDER BY x) คือยอดสะสมทีละ row” เมื่อ x ซ้ำ มันคือยอดสะสมบน peer: 800, 800, 950 ข้างบน
• “last_value ให้ row สุดท้ายของ partition” เฉพาะเมื่อ frame ไปถึงจุดสิ้นสุด โดยค่าเริ่มต้นมันคืน row ปัจจุบัน
• “row_number() ตัดสินเสมอได้อย่างเสถียร” ลำดับของ row ที่เสมอกันไม่ถูกกำหนด นอกจากเพิ่มตัวตัดสิน
• “Window function ฟรี” แต่ละ window ที่ต่างกันต้อง sort ตัวอย่าง 500 000 row spill ลงดิสก์ที่ work_mem ค่าเริ่มต้น
• “กรองด้วย rank ใน WHERE ได้” มันถูกประเมินหลัง WHERE ใช้ subquery หรือ CTE

window function ต่างจาก GROUP BY ที่ประเด็นหลักอะไร?

window function เร็วกว่า window function เก็บทุก row ของ input; GROUP BY ยุบมัน GROUP BY เก็บทุก row ไม่ต่างกัน

West มีสองยอดขาย 400 และหนึ่ง 150 ฟังก์ชันไหนให้ค่า 1, 1, 3?

row_number() rank() dense_rank() ntile(3)

SUM(copies) OVER (PARTITION BY region ORDER BY copies DESC) คืน 800, 800, 950 สำหรับ West (400, 400, 150) ทำไมไม่ใช่ 400, 800, 950?

PostgreSQL มี bug default frame คือ RANGE ... CURRENT ROW จึงรวม peer ที่เสมอกันไว้ด้วยกัน PARTITION BY รวมทั้ง partition DESC เปลี่ยน frame

ทำไม WHERE rank() OVER (...) = 1 ล้มด้วย SQLSTATE 42P20?

rank() ไม่มีอยู่ window function ถูกประเมินหลัง WHERE จึงไม่อนุญาตตรงนั้น ต้องมี semicolon ใช้ได้เฉพาะกับ HAVING

ใน plan 500 000 row Sort Method: external merge Disk: 12728kB บอกอะไร?

table เสียหาย การ sort ไม่พอดี work_mem (ค่าเริ่มต้น 4 MB) และ spill ลง temp file มีการใช้ index query รันแบบขนาน

Submit

Mark as complete