SQL 全名為結構化查詢語言（Structured Query Language），是一種宣告式程式語言：使用者宣告想要的結果，描述成一條針對已知資料庫模型與資料集執行的資料處理管線。

  • 資料庫模型必須靜態宣告，查詢執行時每一筆資料的型別都是已知的；查詢結果集本身定義了一個關聯（relation），其型別在解析查詢時即被決定或推斷。
  • 因此使用 SQL 時，開發者其實是在操作一套型別系統與某種關聯代數（relational algebra）。
  • RDBMS 與 SQL 迫使開發者以資料結構思考，同時宣告資料結構與想取得的資料集。

有人會說 SQL 逼我們成為好開發者。如林納斯・托瓦茲（Linus Torvalds）所言：「壞程式設計師擔心程式碼，好程式設計師擔心資料結構以及它們之間的關係。」

有些程式碼是用 SQL 寫的#

  • 會讀這本書，多半表示你維護的應用程式早已內嵌 SQL 查詢。SQLite 專案就自問是否為「史上部署最廣的軟體模組」——它存在於每台 Android、iPhone、Mac、Windows 10、每個主流瀏覽器、Skype、iTunes、Dropbox、PHP 與 Python、多數電視與車用多媒體系統中。觸及範圍相當的函式庫還有 zlib、libpng 參考實作與 libjpeg。
  • 值得一提：libjpeg 的開發者湯姆・連恩（Tom Lane）後來參與制定 PNG 規格，而他正是 PostgreSQL 專案長期以來最重要的貢獻者之一。
  • 每位開發者都看過某種形式的 select … from … where …，懂一點 SQL'89 的皮毛；但現行標準是 SQL'2016，包含許多進階資料處理技術。
  • 這個執行期依賴是有狀態的服務，承載所有使用者資料——沒有正式環境的資料集，我們寫的程式碼毫無價值。

SQL 是強大的宣告式程式語言。用得好，能同時縮減程式碼量與新功能的開發時間。本書希望你把「善用 SQL」視為撰寫應用程式時最大的優勢之一。

第一個使用案例#

洲際交易所（Intercontinental Exchange）提供 2017 年 NYSE 掛牌股票的每日成交量資料（Daily NYSE Group Volume）。我們可以下載這份實為 Tab 分隔 CSV 的 Excel 檔，去掉標頭後載入 PostgreSQL。

載入資料集#

原始資料的數字帶千分位逗號與美元符號，無法直接當數值處理：

2010  1/4/2010  1,425,504,460  4,628,115  $38,495,460,645
2010  1/5/2010  1,754,011,750  5,394,016  $43,932,043,406

因此先建一張臨時性的表定義，載入後再用 alter table 轉成正確的 SQL 資料型別：

begin;

create table factbook
  (
     year    int,
     date    date,
     shares  text,
     trades  text,
     dollars text
  );

\copy factbook from 'factbook.csv' with delimiter E'\t' null ''

alter table factbook
   alter shares
    type bigint
   using replace(shares, ',', '')::bigint,

   alter trades
    type bigint
   using replace(trades, ',', '')::bigint,

   alter dollars
    type bigint
   using substring(replace(dollars, ',', '') from 2)::numeric;

commit;

\copy 是 psql 專屬指令，發起主從式（client/server）串流：讀取本機檔案，透過既有的 PostgreSQL 連線把內容送進資料表。

應用程式碼與 SQL#

經典問題：列出某個月的 factbook 資料。因為日曆是頭複雜的野獸，我們自然挑 2017 年 2 月當範例：

\set start '2017-02-01'

  select date,
         to_char(shares, '99G999G999G999') as shares,
         to_char(trades, '99G999G999') as trades,
         to_char(dollars, 'L99G999G999G999') as dollars
    from factbook
   where date >= date :'start'
     and date < date :'start' + interval '1 month'
order by date;

這條書中的第一個查詢用到幾個技巧：

  • psql 支援變數：\set 設定變數，之後以 :'start' 引用。date :'start' 等同 date '2017-02-01'，稱為裝飾字面值（decorated literal），直接指明字面值的型別，讓查詢解析器不必猜。
  • 以 interval 資料型別計算月底：月初加上 1 month 得到下個月第一天，再用嚴格小於（<）排除該日。
  • to_char() 依樣板把數字轉為文字：指定位數的數字樣板、L（貨幣符號，依 locale）、G（千分位群組符，依 locale）。完整樣板見 PostgreSQL 文件的 Data Type Formatting Functions。

結果只有 19 個交易日有資料。但我們可能期望每個日曆日都有一列，沒資料的日子補零。以下是典型的 Python 實作思路：查詢結果存入以日期為鍵的 dict，再迭代該月的每一天，有資料就取用、沒有就填零。

延伸：完整 Python 程式（psycopg2 版）
#! /usr/bin/env python3

import sys
import psycopg2
import psycopg2.extras
from calendar import Calendar

CONNSTRING = "dbname=yesql application_name=factbook"


def fetch_month_data(year, month):
    "Fetch a month of data from the database"
    date = "%d-%02d-01" % (year, month)
    sql = """
  select date, shares, trades, dollars
    from factbook
   where date >= date %s
     and date < date %s + interval '1 month'
order by date;
"""
    pgconn = psycopg2.connect(CONNSTRING)
    curs = pgconn.cursor()
    curs.execute(sql, (date, date))

    res = {}
    for (date, shares, trades, dollars) in curs.fetchall():
        res[date] = (shares, trades, dollars)

    return res


def list_book_for_month(year, month):
    """List all days for given month, and for each
    day list fact book entry.
    """
    data = fetch_month_data(year, month)

    cal = Calendar()
    print("%12s | %12s | %12s | %12s" %
          ("day", "shares", "trades", "dollars"))
    print("%12s-+-%12s-+-%12s-+-%12s" %
          ("-" * 12, "-" * 12, "-" * 12, "-" * 12))

    for day in cal.itermonthdates(year, month):
        if day.month != month:
            continue
        if day in data:
            shares, trades, dollars = data[day]
        else:
            shares, trades, dollars = 0, 0, 0

        print("%12s | %12s | %12s | %12s" %
              (day, shares, trades, dollars))


if __name__ == '__main__':
    year = int(sys.argv[1])
    month = int(sys.argv[2])

    list_book_for_month(year, month)

執行 ./factbook-month.py 2017 2 的輸出仿照 psql 格式，28 個日曆日每天一列，無交易日（如 02-04、02-05 等週末）皆為 0，方便與 SQL 版本比較工作量。

談談 SQL 注入#

SQL 注入（SQL injection）是一種安全漏洞，因 xkcd 漫畫《Exploits of a Mom》裡的小巴比・資料表（little Bobby Tables）而廣為人知。

  • 注入發生在：資料庫伺服器被誤導，把查詢的動態參數當成了查詢文字的一部分。
  • PostgreSQL 在協定層提供解法：把靜態 SQL 查詢文字與動態參數分開傳送。兩者是不同的實體，注入就不可能發生。協定文件的 Message Flow 頁面說明了 extended query 支援；libpq C 驅動程式的 PQexecParams API 亦與此相關。
  • 許多 PostgreSQL 驅動程式基於 libpq；也有不連結 C runtime、以其他語言自行實作 PostgreSQL 協定的變體，例如 JDBC 驅動與 Go 的 pq 驅動。

絕對不要在應用程式端把查詢參數直接串接進查詢字串，也不要使用任何會這麼做的函式庫、ORM 或工具。那樣組查詢字串，等於毫無理由地讓應用程式暴露在嚴重安全風險之下。

請閱讀你所用驅動程式的文件，理解如何把參數與查詢文字分開傳送——這是從此不必再擔心 SQL 注入的可靠做法。

前例使用的 psycopg 驅動基於 libpq，但它是在客戶端把參數插入 SQL 字串——你必須信任 psycopg 防住所有注入嘗試。我們其實可以更安全。

PostgreSQL 協定：伺服器端預備語句#

透過伺服器端預備語句（server-side prepared statement），查詢字串與參數即可在線路上分開傳送。這是常見做法，主因是 PQexecParams 知名度不高——儘管它早在 2003 年 11 月發布的 PostgreSQL 7.4 就登場了，至今仍有許多驅動程式沒有開放這個功能。

在 SQL 層可用 PREPARE 與 EXECUTE 指令：

prepare foo as
 select date, shares, trades, dollars
   from factbook
  where date >= $1::date
    and date < $1::date + interval '1 month'
  order by date;

execute foo('2010-02-01');

同樣的機制也存在於協定層，名為 Extended Query。文件中的訊息流程：

  • Parse 訊息：前端送出查詢文字字串，可附參數佔位符的型別資訊與目標預備語句物件名稱。
  • Bind 訊息：預備語句就緒後以此綁定，提供的參數集必須符合語句所需。
  • Execute 訊息：客戶端送出第三個訊息以接收結果集。

由此可清楚看出：PostgreSQL 解析的查詢字串不含參數——參數是在後續訊息中另行傳送的。注入之所以發生，是 SQL 解析器被騙，把參數字串當成 SQL 查詢執行；當查詢字串活在應用程式碼裡、使用者提供的參數在網路上分開傳送，解析引擎就不可能混淆。

以下範例使用 asyncpg 驅動（開源，見 MagicStack/asyncpg），它自行實作 PostgreSQL 協定並使用伺服器端預備語句，因此在設計上就免疫於 SQL 注入：

import sys
import asyncio
import asyncpg
import datetime
from calendar import Calendar

CONNSTRING = "postgresql://appdev@localhost/appdev?application_name=factbook"


async def fetch_month_data(year, month):
    "Fetch a month of data from the database"
    date = datetime.date(year, month, 1)
    sql = """
  select date, shares, trades, dollars
    from factbook
   where date >= $1::date
     and date < $1::date + interval '1 month'
order by date;
"""
    pgconn = await asyncpg.connect(CONNSTRING)
    stmt = await pgconn.prepare(sql)

    res = {}
    for (date, shares, trades, dollars) in await stmt.fetch(date):
        res[date] = (shares, trades, dollars)

    await pgconn.close()

    return res

呼叫端只需改用 asyncio 執行協程：data = asyncio.run(fetch_month_data(year, month))，其餘不變。

回到探索 SQL#

上述「補齊每個日曆日」的需求，其實一條 SQL 就能解決，完全不需在應用程式碼上花力氣：

  select cast(calendar.entry as date) as date,
         coalesce(shares, 0) as shares,
         coalesce(trades, 0) as trades,
         to_char(
            coalesce(dollars, 0),
            'L99G999G999G999'
         ) as dollars
    from /*
          * Generate the target month's calendar then LEFT JOIN
          * each day against the factbook dataset, so as to have
          * every day in the result set, whether or not we have a
          * book entry for the day.
          */
         generate_series(date :'start',
                          date :'start' + interval '1 month'
                                        - interval '1 day',
                          interval '1 day'
         )
         as calendar(entry)
         left join factbook
                 on factbook.date = calendar.entry
order by date;

這條查詢用到幾個你可能初次見到的基本技巧：

  • SQL 接受 -- 單行註解與 C 式 /* … */ 註解；註解最適合記錄意圖，因為意圖很難從程式碼本身逆向推敲。
  • generate_series() 是 PostgreSQL 的集合回傳函數（set returning function），依起訖與步長產生一連串值。PostgreSQL 懂日曆，只要給月初日期就能產出整個月的每一天。
  • generate_series() 如同 BETWEEN 是含端點的，所以用 - interval '1 day' 排除下個月第一天。
  • cast(calendar.entry as date) 把產生的項目轉為 date 型別——因為函數回傳的是 timestamp 集合，而我們不需要時間部分。
  • left join 保留產生的日曆表每一列，只在兩表 date 相等時關聯 factbook 列；日曆日在 factbook 找不到時，factbook 欄位以 NULL 填入。
  • coalesce() 回傳第一個非 NULL 的引數，所以 coalesce(shares, 0) 在有資料時是實際股數、left join 補 NULL 時就是 0。

執行結果涵蓋全部 28 天（無資料日為 0），與 Python 版輸出相同。

我們用一條簡單的 SQL 取代了 60 行 Python：日後要維護的程式碼更少，執行也更有效率——Python 版是雜湊連接加巢狀迴圈，PostgreSQL 則對兩個已排序的關聯選用 Merge Left Join。本書稍後會介紹如何取得並閱讀 PostgreSQL 的執行計畫。

書附程式包（Full/Enterprise Edition）中，對應檔案為 02-intro/02-usecase/ 下的 02.sql、04.sql 與 03_factbook-month.py，可對預載的 yesql 資料庫執行。

計算每週變化#

分析部門現在要求：為每一天加上「與上週同日相比，dollars 欄位的變化百分比」。選這個「週對週百分比差異」為例，一是它是經典的分析需求（在行銷圈尤其常見），二是依作者經驗，開發者的第一反應很少是用一條 SQL 做完所有數學。計算「週」又是日曆幫不上忙的領域——但對 PostgreSQL 而言，就只是拼出 week 這個字而已：

with computed_data as
 (
   select cast(date as date)    as date,
          to_char(date, 'Dy')   as day,
          coalesce(dollars, 0)  as dollars,
          lag(dollars, 1)
             over(
               partition by extract('isodow' from date)
                   order by date
             )
          as last_week_dollars
     from /*
           * Generate the month calendar, plus a week before
           * so that we have values to compare dollars against
           * even for the first week of the month.
           */
          generate_series(date :'start' - interval '1 week',
                           date :'start' + interval '1 month'
                                         - interval '1 day',
                           interval '1 day'
          )
          as calendar(date)
          left join factbook using(date)
 )
  select date, day,
         to_char(
              coalesce(dollars, 0),
              'L99G999G999G999'
         ) as dollars,
         case when dollars is not null
                and dollars <> 0
               then round( 100.0
                          * (dollars - last_week_dollars)
                          / dollars
                        , 2)
          end
         as "WoW %"
    from computed_data
   where date >= date :'start'
order by date;
  • 這裡需要視窗函數（window function）——1992 年就進入 SQL 標準，卻至今常被 SQL 課程略過。
  • 視窗函數是 SQL 語句中最後執行的部分，晚於 join 與 where。所以要看到 2 月第一週之前的完整一週，必須把日曆範圍往過去多延伸一週，最後再把輸出限制回原本的月份。
  • 這正是使用共同資料表運算式（Common Table Expression, CTE）——查詢中 WITH 的部分——的原因：先取得含 last_week_dollars 計算欄位的擴充資料集。
  • extract('isodow' from date) 是標準 SQL 功能，依 ISO 規則計算星期幾；放進 partition by 框架子句，就讓同星期幾的列互為同儕（peer）。
  • lag() 視窗函數依 date 排序後取前一個同儕的 dollars 值，即拿來比較的上週數字。
  • 主查詢再把 computed_data 當成一般關聯取用，套上經典的差異百分比公式即可。
延伸：查詢結果（節錄）
    date    │ day │     dollars      │ WoW %
════════════╪═════╪══════════════════╪════════
 2017-02-01 │ Wed │ $ 44,660,060,305 │  -2.21
 2017-02-02 │ Thu │ $ 43,276,102,903 │   1.71
 2017-02-03 │ Fri │ $ 42,801,562,275 │  10.86
 2017-02-04 │ Sat │ $              0 │      ¤
 2017-02-05 │ Sun │ $              0 │      ¤
 2017-02-06 │ Mon │ $ 37,300,908,120 │  -9.64
 2017-02-07 │ Tue │ $ 39,754,062,721 │ -37.41
 ...
 2017-02-27 │ Mon │ $ 43,613,734,358 │      ¤
 2017-02-28 │ Tue │ $ 57,874,495,227 │  23.25
(28 rows)

本書其餘部分將花時間解釋 CTE 與視窗函數的核心概念，並提供大量範例，讓你能寫出「恰好取回應用程式所需結果集」的查詢；同時也會探討「下更複雜的查詢」與「下更多查詢、把處理留在應用程式碼」兩種做法在效能與正確性上的差異。

Backward 導論 軟體架構 Forward