本章要寫一個非常簡單的應用程式，比較「傳統應用程式碼」與「SQL」解決常見問題的差異。目標是實際面對「SQL 作為程式碼庫的一部分」該如何管理，並看清何時該用應用程式碼、何時該用 SQL。
Readme 先行開發#
在寫任何程式或測試之前，作者習慣先寫 README——那個向使用者說明「為什麼要在乎這個應用」以及大致用法的小檔案：
- cdstore 是架在 Chinook 資料庫上的極簡包裝。
- Chinook 資料模型代表一間數位媒體商店，包含藝人、專輯、曲目、發票與顧客等資料表。
- cdstore 提供資料庫上的實用清單與報表，也能產生一些活動。
載入資料集#
作者選擇用 pgloader 從 SQLite 檔載入 Chinook（現在也找得到 PostgreSQL 備份檔，但 pgloader 更順手），額外的好處是載入摘要會列出每張資料表與載入的列數——這是我們與資料集的第一次照面：
$ createdb chinook
$ pgloader https://github.com/lerocha/chinook-database/raw/master \
/ChinookDatabase/DataSources \
/Chinook_Sqlite_AutoIncrementPKs.sqlite \
pgsql:///chinook延伸輸出：pgloader 載入摘要（節錄）
table name errors rows bytes total time
----------------------- --------- --------- --------- --------------
artist 0 275 6.8 kB 0.026s
album 0 347 10.5 kB 0.090s
employee 0 8 1.4 kB 0.034s
invoice 0 412 31.0 kB 0.059s
mediatype 0 5 0.1 kB 0.083s
playlisttrack 0 8715 57.3 kB 0.179s
customer 0 59 6.7 kB 0.010s
genre 0 25 0.3 kB 0.019s
invoiceline 0 2240 43.6 kB 0.090s
playlist 0 18 0.3 kB 0.056s
track 0 3503 236.6 kB 0.192s
----------------------- --------- --------- --------- --------------
Total import time ✓ 15607 394.5 kB 0.893s載入後要修正一個 SQLite 那邊定義不良的主鍵：track 資料表只有 UNIQUE 索引 idx_51519_ipk_track，沒有真正的主鍵，因此執行：
alter table track add primary key using index idx_51519_ipk_track;PostgreSQL 實作了 group by 推導（group by inference），後續部分查詢必須有這個主鍵才能執行。載入資料集後請立刻修好主鍵。
Chinook 資料庫#
Chinook 包含音樂收藏的基本元素：album、artist、track、genre、mediatype；播放清單則由 playlist 加上關聯表 playlisttrack 組成（一首曲目可屬於多個清單、一個清單含多首曲目）；顧客消費模型由 staff、customer、invoice、invoiceline 組成，共 11 張資料表。
用一條簡單查詢開始探索資料集——各曲風的曲目數：
select genre.name, count(*) as count
from genre
left join track using(genreid)
group by genre.name
order by count desc; name │ count
════════════════════╪═══════
Rock │ 1297
Latin │ 579
Metal │ 374
Alternative & Punk │ 332
Jazz │ 130
...
(25 rows)音樂目錄#
應用程式用 Python 撰寫（方便在書中瀏覽程式碼），並使用 anosql 函式庫：SQL 乾淨地放在 .sql 檔案裡，Python 端輕鬆載入。artist.sql 長這樣：
-- name: top-artists-by-album
-- Get the list of the N artists with the most albums
select artist.name, count(*) as albums
from artist
left join album using(artistid)
group by artist.name
order by albums desc
limit :n;把
.sql檔放進原始碼樹的好處：可以用 git 做版本控制、必要時寫註解，還能在應用程式目錄與互動式 psql shell 之間直接複製貼上。
anosql 的具名變數（如 limit :n）在 psql 裡也能直接受益：
> \set n 3
> \i artist.sql
name │ albums
══════════════╪════════
Iron Maiden │ 21
Led Zeppelin │ 14
Deep Purple │ 11
(3 rows)也可以從命令列設定變數，方便整合進 bash 腳本：
psql --variable "n=10" -f artist.sql chinook依藝人列出專輯#
上一章的查詢也能直接收編，album.sql 如下：
-- name: list-albums-by-artist
-- List the album titles and duration of a given artist
select album.title as album,
sum(milliseconds) * interval '1 ms' as duration
from album
join artist using(artistid)
left join track using(albumid)
where artist.name = :name
group by album
order by album;依曲風的 Top-N 藝人#
再實作一個經典的 Top-N 問題：每個曲風取出「在播放清單中出現次數最多」的前 N 首曲目。SQL 中實作 Top-N 最好的方式是 lateral join——可以先把理論簡化成「lateral join 讓你在 SQL 裡寫出明確的迴圈」。這是應用程式的 genre-topn.sql：
-- name: genre-top-n
-- Get the N top tracks by genre
select genre.name as genre,
case when length(ss.name) > 15
then substring(ss.name from 1 for 15) || '…'
else ss.name
end as track,
artist.name as artist
from genre
left join lateral
/*
* the lateral left join implements a nested loop over
* the genres and allows to fetch our Top-N tracks per
* genre, applying the order by desc limit n clause.
*
* here we choose to weight the tracks by how many
* times they appear in a playlist, so we join against
* the playlisttrack table and count appearances.
*/
(
select track.name, track.albumid, count(playlistid)
from track
left join playlisttrack using (trackid)
where track.genreid = genre.genreid
group by track.trackid
order by count desc
limit :n
)
/*
* the join happens in the subquery's where clause, so
* we don't need to add another one at the outer join
* level, hence the "on true" spelling.
*/
ss(name, albumid, count) on true
join album using(albumid)
join artist using(artistid)
order by genre.name, ss.count desc;查詢的運作方式：
- 外層迴圈走訪所有曲風；對每個曲風，內層的相關子查詢（correlated subquery）以
order by count desc limit :n取出出現次數最高的 n 首曲目。 where track.genreid = genre.genreid就是內外層迴圈之間的相關條件。- 內層（命名為
ss的 lateral 子查詢）完成後，再 joinalbum與artist取得藝人名稱。
寫這種「中等複雜度 SQL」的主因是效率。同樣的事若在應用程式端做：
- 取曲風清單（一次
select name from genre） - 對每個曲風各執行一次 Top-N 曲目查詢（
ss子查詢 × 曲風數） - 對每首入選曲目（n × 曲風數次）再查藝人名稱
大量資料在應用與資料庫之間來回、大量無謂的處理。讓資料庫直接算出恰好需要的結果集，Python 端就只剩使用者介面：解析命令列選項、印出查詢結果。
另一個常見反駁是「同樣結果 SQL 有別的寫法，不必用 lateral 子查詢」。確實可以，但那些寫法都比 lateral 低效。之後的章節會教你讀 explain 計畫（explain plan），那才是判斷最有效率寫法的方法。
延伸程式碼：cdstore.py 完整應用程式
#! /usr/bin/env python3
# -*- coding: utf-8 -*-
import anosql
import psycopg2
import argparse
import sys
PGCONNSTRING = "user=cdstore dbname=appdev application_name=cdstore"
class chinook(object):
"""Our database model and queries"""
def __init__(self):
self.pgconn = psycopg2.connect(PGCONNSTRING)
self.queries = None
for sql in ['sql/genre-tracks.sql',
'sql/genre-topn.sql',
'sql/artist.sql',
'sql/album-by-artist.sql',
'sql/album-tracks.sql']:
queries = anosql.load_queries('postgres', sql)
if self.queries:
for qname in queries.available_queries:
self.queries.add_query(qname, getattr(queries, qname))
else:
self.queries = queries
def genre_list(self):
return self.queries.tracks_by_genre(self.pgconn)
def genre_top_n(self, n):
return self.queries.genre_top_n(self.pgconn, n=n)
def artist_by_albums(self, n):
return self.queries.top_artists_by_album(self.pgconn, n=n)
def album_details(self, albumid):
return self.queries.list_tracks_by_albumid(self.pgconn, id=albumid)
def album_by_artist(self, artist):
return self.queries.list_albums_by_artist(self.pgconn, name=artist)
class printer(object):
"print out query result data"
def __init__(self, columns, specs, prelude=True):
"""COLUMNS is a tuple of column titles,
Specs an tuple of python format strings
"""
self.columns = columns
self.specs = specs
self.fstr = " | ".join(str(i) for i in specs)
if prelude:
print(self.title())
print(self.sep())
def title(self):
return self.fstr % self.columns
def sep(self):
s = ""
for c in self.title():
s += "+" if c == "|" else "-"
return s
def fmt(self, data):
return self.fstr % data
class cdstore(object):
"""Our cdstore command line application. """
def __init__(self, argv):
self.db = chinook()
parser = argparse.ArgumentParser(
description='cdstore utility for a chinook database',
usage='cdstore <command> [<args>]')
subparsers = parser.add_subparsers(help='sub-command help')
genres = subparsers.add_parser('genres', help='list genres')
genres.add_argument('--topn', type=int)
genres.set_defaults(method=self.genres)
artists = subparsers.add_parser('artists', help='list artists')
artists.add_argument('--topn', type=int, default=5)
artists.set_defaults(method=self.artists)
albums = subparsers.add_parser('albums', help='list albums')
albums.add_argument('--id', type=int, default=None)
albums.add_argument('--artist', default=None)
albums.set_defaults(method=self.albums)
args = parser.parse_args(argv)
args.method(args)
def genres(self, args):
"List genres and number of tracks per genre"
if args.topn:
p = printer(("Genre", "Track", "Artist"),
("%20s", "%20s", "%20s"))
for (genre, track, artist) in self.db.genre_top_n(args.topn):
artist = artist if len(artist) < 20 else "%s…" % artist[0:18]
print(p.fmt((genre, track, artist)))
else:
p = printer(("Genre", "Count"), ("%20s", "%s"))
for row in self.db.genre_list():
print(p.fmt(row))
def artists(self, args):
"List genres and number of tracks per genre"
p = printer(("Artist", "Albums"), ("%20s", "%5s"))
for row in self.db.artist_by_albums(args.topn):
print(p.fmt(row))
def albums(self, args):
# we decide to skip parts of the information here
if args.id:
p = printer(("Title", "Duration", "Pct"),
("%25s", "%15s", "%6s"))
for (title, ms, s, e, pct) in self.db.album_details(args.id):
title = title if len(title) < 25 else "%s…" % title[0:23]
print(p.fmt((title, ms, pct)))
elif args.artist:
p = printer(("Album", "Duration"), ("%25s", "%s"))
for row in self.db.album_by_artist(args.artist):
print(p.fmt(row))
if __name__ == '__main__':
cdstore(sys.argv[1:])執行 Top-N 查詢，取出每個曲風最常被收進播放清單的一首曲目：
$ ./cdstore.py genres --topn 1 | head
Genre | Track | Artist
---------------------+----------------------+---------------------
Alternative | Hunger Strike | Temple of the Dog
Alternative & Punk | Infeliz Natal | Raimundos
Blues | Knockin On Heav… | Eric Clapton
Bossa Nova | Onde Anda Você | Toquinho & Vinícius
Classical | Fantasia On Gre… | Academy of St. Mar…
Comedy | The Negotiation | The Office
Drama | Homecoming | Heroes
Easy Listening | I've Got You Un… | Frank Sinatra換成 --topn 3 就是每曲風前三名。若之後想改用別的權重方式選曲，也很容易：在 psql 裡把玩查詢，改好後替換 .sql 檔即可——下一章要介紹的，正是這種以 REPL 工具互動式撰寫 SQL 的方式。