用 ORDER BY 排序#

ORDER BY 廣為人知，因為 SQL 不保證任何查詢結果集的順序——除非你使用 order by 子句。最簡單的形式是依資料模型中的一或多個欄位排序，某些情況下 PostgreSQL 甚至能循著既有索引直接以正確順序回傳資料：

  select year, url
    from seasons
order by year desc
   limit 3;

結果本身不算有趣，有趣的是它的執行計畫——PostgreSQL 反向走訪主鍵索引來回傳最近的三筆：

explain (costs off)
  select year, url
    from seasons
order by year desc
   limit 3;
                          QUERY PLAN
══════════════════════════════════════════════════════════════
 Limit
   -> Index Scan Backward using idx_57708_primary on seasons
(2 rows)

order by 也可以引用查詢別名與計算值；在 PostgreSQL 中甚至接受複雜運算式與子查詢。例如用 CASE 條件式控制賽果排序：先依名次、再依圈數，最後依狀態排序，並加一條特殊規則——Power Unit 故障優先排前（規則本身完全是任意的，但你可能正替車隊調查某個故障硬體）：

select drivers.code, drivers.surname,
       position,
       laps,
       status
  from results
       join drivers using(driverid)
       join status using(statusid)
 where raceid = 972
order by position nulls last,
         laps desc,
         case when status = 'Power Unit'
               then 1
               else 2
          end;
raceid = 972 的排序結果
 code │ surname    │ position │ laps │   status
══════╪════════════╪══════════╪══════╪════════════
 BOT  │ Bottas     │        1 │   52 │ Finished
 VET  │ Vettel     │        2 │   52 │ Finished
 RAI  │ Räikkönen  │        3 │   52 │ Finished
 HAM  │ Hamilton   │        4 │   52 │ Finished
 ...
 WEH  │ Wehrlein   │       16 │   50 │ +2 Laps
 RIC  │ Ricciardo  │        ¤ │    5 │ Brakes
 ALO  │ Alonso     │        ¤ │    0 │ Power Unit
 PAL  │ Palmer     │        ¤ │    0 │ Collision
 GRO  │ Grosjean   │        ¤ │    0 │ Collision
(20 rows)

看著這份結果，幾乎就像親眼看了那場比賽。

kNN 排序與 GiST 索引#

order by 的另一個用途是實作 k 最近鄰（k nearest neighbours, kNN） 搜尋。找出離巴黎（經度 2.349014、緯度 48.864716）最近的十個賽道，就是 k = 10 的 kNN 搜尋：

  select name, location, country
    from circuits
order by point(lng,lat) <-> point(2.349014, 48.864716)
   limit 10;

結果除了法國各地的賽道，還包括比利時與英國的幾條（Rouen-Les-Essarts、Reims-Gueux、Magny-Cours、Le Mans、Nivelles-Baulers、Dijon-Prenois、Charade、Brands Hatch、Zolder、Spa-Francorchamps）。

point 資料型別是 PostgreSQL 很實用的擴充。上面的 point 是從原始欄位動態計算的；更道地的做法是在 circuits 表加一個 point 型別欄位並建 GiST 索引：

begin;

alter table f1db.circuits add column position point;
update f1db.circuits set position = point(lng,lat);
create index on f1db.circuits using gist(position);

commit;

改用新欄位查詢後結果集當然相同——索引在任何情況下都不允許改變查詢結果；如果改變了，那叫 bug，或者是資料損毀。看看有 GiST 索引後的執行計畫：

explain (costs off, buffers, analyze)
  select name, location, country
    from circuits
order by position <-> point(2.349014, 48.864716)
   limit 10;
                             QUERY PLAN
══════════════════════════════════════════════════════════════════
 Limit (actual time=0.039..0.061 rows=10 loops=1)
   Buffers: shared hit=7
   -> Index Scan using circuits_position_idx on circuits
         (actual time=0.038..0.058 rows=10 loops=1)
          Order By: ("position" <-> '(2.349014,48.864716)'::point)
          Buffers: shared hit=7
 Planning time: 0.129 ms
 Execution time: 0.105 ms
(7 rows)

PostgreSQL 樂於使用 GiST 索引，甚至整個 kNN 搜尋都在索引內完成。相較之下，動態運算式 point(lng,lat) 的版本只能 Seq Scan 加 top-N heapsort，執行時間 0.344 ms 對上索引版的 0.105 ms。

距離運算子 <-> 預設只對幾何資料型別定義；pg_trgm 等擴充能擴大適用範圍，讓 like 甚至正規表達式運算子 ~ 的查詢也能受益於 kNN 索引查找。本書稍後會再談 PostgreSQL 的正規表達式。

Top-N 排序：LIMIT#

想列出每個年代贏最多場比賽的前三名車手，可以結合 PostgreSQL 的日期函式與 lateral join。以下是經典的 top-N 實作——經典在於透過 lateral 子查詢完成；不那麼經典的是我們 join 的對象是計算出來的資料：年代（decade）不在資料模型裡，得從 races.date 欄位萃取。

with decades as
(
    select extract('year' from date_trunc('decade', date)) as decade
      from races
  group by decade
)
select decade,
       rank() over(partition by decade
                   order by wins desc)
       as rank,
       forename, surname, wins

  from decades
       left join lateral
       (
          select code, forename, surname, count(*) as wins
            from drivers

                    join results
                      on results.driverid = drivers.driverid
                     and results.position = 1

                    join races using(raceid)

            where     extract('year' from date_trunc('decade', races.date))
                    = decades.decade

         group by decades.decade, drivers.driverid
         order by wins desc
            limit 3
       )
       as winners on true

order by decade asc, wins desc;

查詢的結構：

  • 先在 WITH 引入的共同資料表運算式（Common Table Expression, CTE）中萃取年代清單，之後在 from 子句中把它當成資料來源使用——from 子句處理的是關聯（relation），而關聯完全可以是動態計算出的資料集。
  • lateral join 允許子查詢「對一個資料集逐列執行迴圈」：對每個年代，lateral 子查詢找出該年代的前三名贏家。
  • 子查詢只關心勝利成績，所以把 results.position = 1 推進 join 條件；「只算當前年代的勝場」則實作在子查詢的 where 子句，這正是 lateral 子查詢的運作方式。
  • 也因為 join 條件已經以 where 形式注入子查詢，外層的 join 子句寫成 on true。子查詢內部再以 order by wins desc 加 limit 3 取出前三名。
各年代前三名車手結果
 decade │ rank │ forename  │ surname    │ wins
════════╪══════╪═══════════╪════════════╪══════
   1950 │    1 │ Juan      │ Fangio     │   24
   1950 │    2 │ Alberto   │ Ascari     │   13
   1950 │    3 │ Stirling  │ Moss       │   12
   1960 │    1 │ Jim       │ Clark      │   25
   1960 │    2 │ Graham    │ Hill       │   14
   1960 │    3 │ Jack      │ Brabham    │   11
   1970 │    1 │ Niki      │ Lauda      │   17
   1970 │    2 │ Jackie    │ Stewart    │   16
   1970 │    3 │ Emerson   │ Fittipaldi │   14
   1980 │    1 │ Alain     │ Prost      │   39
   1980 │    2 │ Nelson    │ Piquet     │   20
   1980 │    2 │ Ayrton    │ Senna      │   20
   1990 │    1 │ Michael   │ Schumacher │   35
   1990 │    2 │ Damon     │ Hill       │   22
   1990 │    3 │ Ayrton    │ Senna      │   21
   2000 │    1 │ Michael   │ Schumacher │   56
   2000 │    2 │ Fernando  │ Alonso     │   21
   2000 │    3 │ Kimi      │ Räikkönen  │   18
   2010 │    1 │ Lewis     │ Hamilton   │   45
   2010 │    2 │ Sebastian │ Vettel     │   40
   2010 │    3 │ Nico      │ Rosberg    │   23
(21 rows)

別用 OFFSET：分頁的正確實作#

SQL 標準提供 FETCH 指令來取代 PostgreSQL 的 limit／offset 變體。無論如何，offset 子句對查詢效能非常糟糕，我們強烈建議不要用。請抽空閱讀 Markus Winand 的《Paging Through Results》——作者自認不會解釋得比他更好。另外：永遠別再用 offset 了！

書中仍給出重點：offset 會讓查詢計畫照樣讀出所有結果，然後丟棄大部分資料直到到達 offset 數。分頁翻得越深，每多取一頁就越沒效率。

正確的分頁實作是使用索引查找（index lookup）；若排序子句包含多個欄位，可以用 row() 建構式做到。

以 laptimes 表示範（包含每場比賽每位車手的每圈時間；raceid 972 有 828 列）。為節省版面，每頁取三列。第一頁如預期，這裡用的是 SQL 標準的 limit 拼法：

     select lap, drivers.code, position,
            milliseconds * interval '1ms' as laptime
       from laptimes
            join drivers using(driverid)
      where raceid = 972
   order by lap, position
fetch first 3 rows only;
 lap │ code │ position │       laptime
═════╪══════╪══════════╪═════════════════════
   1 │ BOT  │        1 │ @ 2 mins 5.192 secs
   1 │ VET  │        2 │ @ 2 mins 7.101 secs
   1 │ RAI  │        3 │ @ 2 mins 10.53 secs
(3 rows)

關鍵在於：應用程式必須記住目前顯示到 lap = 1、position = 3，下一頁查詢就以此為起點：

     select lap, drivers.code, position,
            milliseconds * interval '1ms' as laptime
       from laptimes
            join drivers using(driverid)
      where raceid = 972
        and row(lap, position) > (1, 3)
   order by lap, position
fetch first 3 rows only;

第二頁正確地從 lap 1、position 4 開始（HAM、VER、MAS）。

只要你在乎查詢時間，就請永遠不要再使用 offset！

Backward SELECT、FROM、WHERE GROUP BY、HAVING、WITH、UNION ALL Forward