「10年戦えるデータ分析入門 SQLを武器にデータ活用時代を生き抜く」を読んだ

投稿時刻2023年3月20日 07:50

「10年戦えるデータ分析入門 SQLを武器にデータ活用時代を生き抜く」を 2,023 年 03 月 20 日に読んだ。

目次

メモ

1. エンジニアとプランナーの共通言語として優れている p8

SQL を使うべき3つめの理由は、「エンジニアとプランナーの共通言語として「優秀だから」です。
ここでは「プランナー」を「エンジニア以外の人」というくらいの意図で使っています。
例えば企画や商品開発をする人や、分析を専門に行うデータアナリストなどを想定しています。

この「みんなで共通のイメージを持てない」問題の原因と対策は多岐にわたります。
しかし根本的に解決するには、お互いに相手の領域を勉強し、理解するしかありません。
つまり、エンジニアもビジネスの構造や KPI やマーティングを理解すべきだし、
プランナーも IT システムの仕組みをある程度知っているべきだということです。

ではITシステムを知ってもらううえで最も学習効果が高い分野は何でしょうか。
それは断然 SQL です。

なぜならRDBMSはほとんどあらゆる業務 IT システムの中核であり、すべてのデータがそこにあるからです。
プランナーに RDBMS と SQL を学んでもらうことはすなわち、企業のデータを理解することとイコールです。
その学習効果ははかりしれません。

また、より現実的でベタベタな理由として、「SQLがわかればプランナーが自分でデータを取ってくることができる」という理由もあります。
さて、こういうことを言うと確実に「プランナーにはSQLなんてとても書けない」という反論(愚痴?)が想定されます。
しかしそれはプランナーをバカにしすぎた意見だと思います。

ITに関しては、プランナーをよく言えば「弱者」、悪く言えば「バカ扱い」する風潮があるように感じています。
「これはIT専門知識が必要で難しいからプランナーに直接扱わせるのは無理だからこちらでやろう、
せめて××のインターフェイスをかぶせよう」という方向になりがちです。
例えば「SQLはプログラミング言語だからプランナーに書かせるなんてとんでもない、
いたれりつくせりの分析アプリを用意してあげないと無理だ」という具合です。

この意見が正しいときも確かにあります。わたしもすべてのプランナーが SQL を書けるとは思いません。
しかし、理解できるプランナーも必ず一定数存在します。
そして、そういう数少ない人々は確実に「デキる」ので、ビジネス上のキーマンです。
「デキる」人間に IT システムを理解してもらうことは、必ず大きな意味を持つはずです。

基幹系の中心となる OLTP とはどんな処理か p11

ではいわゆる基幹系の、業務システムやウェブアプリケーションはどのような特徴を持つシステムなのでしょうか。
これらは、単純化して一言で言うと OLTP (online transaction processing) システムと言ってよいでしょう。
OLTP という処理の特徴は、「オンライン」で「トランザクション」を大量に処理する必要があることです。

分析向けの RDBMS p13

さきほど典型的な RDBMS として Oracle や MySQL などを挙げましたが、
実はこれらはいずれも元はOLTP を想定して設計された RDBMS です。
分析に使えないわけではないのですが、特に向いているわけでもありません。
特に名指しするなら MySQL は絶望的です。
ウェブエンジニアが分析 SQL に疎いのは MySQL のせいではないかと疑いたくなるほどです。

PostgreSQL を学ぶ利点 p20

本書では分析 SQL を解説するためのRDBMSとして、たくさんある RDBM の中から PostgreSQL という RDBMS を選びました。

PostgreSQL を選択した理由は次の3つがあります。
1. 誰でも無料で入手できる
2. シェアの高い Oracle や SQL Server と SQL が比較的似ている
3. 多機能で、データ活用のために必要な高度な機能が揃っている

まず、 PostgreSQL は無料なので、誰でもすぐに試すことができます。
また、会社で分析用に導入してみようと思ったときも初期コストが低いので「とりあえず」導入することができます。機能制限もありません。

無料で入手できる RDBMS というだけなら MySQL が代表格ですが、
MySQL は Oracle や SQL Server に比べると用語や関数が異なり、本書で身に付けた技術を応用しづらい可能性があります。

そして最後に、本書ではSQLで可能な限りあらゆる分析処理を行うことを狙っているため、
後半ではウィンドウ関数やテーブル関数のような高度な機能を多用することになります。
そのような機能を備えており、かつ巨大なデータベースにも応用が効く RDBMS … となると、 PostgreSQL が第一候補となります。
分析用データベースは PostgreSQL と親和性の高いものが多いからです。

第 8 章以降の話は MySQL ではウィンドウ関数がない、という大きな差が出てきますが、逆に言うとその程度で済みます。

3.1 select 文によるデータの抽出 p44

また、テーブル定義とサンプルデータは本書のサポートサイトから入手できます。
お手元の PostgreSQL サーバーに入れて使えるようになっているので、試してみてください。
データの投入方法もサイトに書いてあります。

注1 本書のサポートサイト: http://i.loveruby.net/stdsq//

SQL での空白の扱い （コーディングスタイルについて） p46

1. 空白はいくつあってもよいし、改行しても構いません。
2. 文の最後にはセミコロン (;) が必要です。
3. テーブル名やカラム名、それに 「select」 などのキーワードでは、大文字小文字の違いは無視されます。

limit 節で表示行数を絞る p48

テーブルは大きいものでは数億行になることもありますし、普通の企業でも100万行くらいのテーブルはふつうにできてしまいます。
そういうテーブルを単純に全行表示してしまうと、100万行を PostgreSQL サーバーからとってくることになってしまい、
いつまでたっても結果が返ってこない…なんてことになりかねません。
「とりあえずデータを見たい」ときは、結果行数の上限を指定する limit 節 (limit clause) を使うと便利です。

limit 節で結果の行数を指定するには、次のように書きます。
select * from access_log_wide limit 10;

これで、最大でも10行までしか結果が返ってこなくなります。
最初にテーブルのデータを見るときは何行あるかわかりませんから、とりあえず limit節を付けて実行しましょう。

巨大なデータを扱っていることを意識する p49

実際のシステムで select 文を使うときは、巨大なデータを発生させないようにくれぐれも注意しましょう。
Excel のワークシートをいじるのと同じような感覚で無造作にそのへんのテーブルを 「select *」 してはいけません。
もしそのテーブルが100万行あったらたいへんなことになります。
まずは何よりも先に、「select count(*)」で行数を調べてください。

そして、データを見るときは安全のため常にlimit節を付けておきましょう。
例えば「limit 10000」を付けておけば、うっかり巨大なテーブルを「select」しても最大1万行で止まるので安心です。

特に、第7章で説明する「組み合わせを生成するジョイン」の機能を使うときは注意が必要です。
うかつなジョインをすると行数が爆発的に増える可能性があり、最悪 PostgreSQL サーバーや手元のパソコンを停止させかねません。
そのような場合も、注意深く limit 節を付けておけば、未然に防ぐことが可能です。
SQLに自信がつくまでは、すべての文に limit 10000 を付けるくらいの気持ちでいるのがよいでしょう。

スキーマの検索パス p50

PostgreSQL ではデータベースを作成した時点で public というスキーマが用意されており、検索パスにも設定されています。
ですから、 public スキーマにある access_log_wide テーブルは単にテーブル名を書くだけでアクセスできるわけです。

where 節でデータを絞り込む p51

select * from access_log_wide where request_time >= timestamp '2014-12-01 00:00:00';

いろいろな比較演算子 p52

表 3.4 SQL の比較演算子 
演算子 意味
left = right left と right が等しい
left <> right left と right が異なる
left < right left よりも right が大きい
left <= right left は right 以下
left > right left のほうが right より大きい
left => right left は right 以上
wal between left and right wal は left 以上 right 以下

特に他のプログラミング言語に慣れたかたのために注記すると、SQLでは等価の演算子が「==」ではありません。
また実は「<>」の代わりに「=」を使えるRDBMSも多いのですが、「<>」のほうが標準です。

条件式を組み合わせる p53

では今度は例えば、数年分のログが入ったaccess_log_wideテーブルから、2014年12月1日の行だけを抜き出したいとしましょう。

このような場合は、「2014年12月1日0時以降」かつ「2014年12月2日0時より前」の行を抜き出す、と考えます。
それぞれの条件はさきほどの比較演算子を使って書けるので、後はその2つを組み合わせればよいわけですね。
そのようselect文は次のように書きます。 

select from where access_log_wide where
request_time >= timestamp '2014-12-01 00:00:00' and
request_time < timestamp '2014-12-02 00:00:00';

ランダムサンプリング p57

最後の絞り込み方法はちょっと毛色が違います。
条件を詳細に決めるのではなく、「どの行でもいいからランダムに 0.1% くらいの量がほしい」という場合はどうすればよいでしょうか。
つまりランダムサンプリングです。

ランダムサンプリングをしたい場合は、次のように where 節で 「random()」という記述を使います。
「random」ではなくて、最後に空の丸カッコを付けて「random()」と書く必要があることに注意してください。

select * from access_log_wide where random() < 0.001;

OLTP の where 節と分析の where 節 p58

例えばこの節で紹介した where 節の場合だと、 OLTP システムでは9割がたのSQLで1行から数行だけを抜き出すような条件を記述するでしょう。
しかもその数行を高速に抜き出すためにインデックス (index) のような高速化の機能を使うことがほぼ必須です。
インデックスが機能しないselect文はそれだけで RDBMS 全体の性能を低下させかねないからです。

一方、分析の場合はほとんどの場合に集計が前提となるため、 where 節では数百万行、数千万行までしか絞り込まないことも頻繁にあります。
これだけ大量に行が発生する場合はインデックスではさほど効果がありませんし、毎回違う select 文を実行することになるのでインデックスはまず作りません。

4.1 集約関数による単純集計 p66

「分析」 と言うといかにも統計やパターン認識のように複雑な計算を行うようなイメージがありますが、
現実には合計や平均のように単純な集計で9割はカタが付くと言ってよいでしょう。
また、 複雑な分析をする場合でも、事前に様々な集計をしてデータの性質をつかむことは必須です。
全処理に占める集計の割合は分析 SQL と OLTP の SQL とを分ける分水嶺と言ってもよいでしょう。

値がない null p68

ところで、この search_hit カラムは検索のヒット数を格納するカラムなのですが、
検索以外を表すログ行ではいったいどういう値が入っているのでしょうか。

もちろん、何が入っているか、入っているべきかは現場によって異なります。
しかし、本書のサンブルテーブルにおいては、検索以外のログ行の search_hit カラムは実は null という値になっています。

null は、「値がない」ことを示す特別な表現です。
null は数値の0でも長さゼロの文字列でもありません。
そもそも「値」ではありません。「値がない」という印なのです。

あるカラムが null であるかどうか調べるには、「is null」という特別な演算子を使って調べる必要があります。
つまり、「カラム名 is null」と書く必要があります。
「カラム名 = null」や「カラム名 <> null」では調べられません。
こう書けてしまう場合もあるのですが、たぶん意図とは結果が異なります。

null の特徴として、 null と何らかの計算をすると null になる、という点が挙げられます。
例えば 3 + null は null です。 null - 7 も null です。
したがって「カラム名 = null」もカラムに関係なく null ということになります。
where 節などの条 件式に「カラム名 = null」が書いてあった場合は、その条件は常に不成立扱いとなります。

一方、集約関数では null は基本的に無視されます。例えばさきほどのように「avg(search_hit)」を計算する場合、
search_hit が null の場合は完全に無視され、残りの (nullでない) 行だけの平均が計算されます。

このように、 null は SQL の様々な場面において特殊な扱いを受けます。
あまりに特殊なので、「 null はどうなるかわかりにくく有害だから一切使うな」という人もいるほどです。

しかし、こと分析システムについて言うと、 null はほぼ避けられません。
分析システムにはそこらじゅうからデータが集められてくるので、そのどれかに null が入っていれば必然的に取り扱わざるをえないからです。
扱いが厄介なときもありますが、実例を通して少しずつ慣れていくしかありません。

中央値と最頻値は ? p69

平均を計算する関数があるなら中央値 (median) と最頻値 (mode) を計算する関数もあってよさそうなものですが、
残念ながら標準の集約関数としては用意されていません。

方法だけ簡単に説明すると、中央値は第8章で説明する「ウィンドウ関数」の 1 つである percent_rank 関数 (percent_rank function) を使います。
最頻値は PostgreSQL 特有の mode 関数 (mode function) か count ウィンドウ関数 (count window function) を使って計算する必要があります。

それほど難しくはないので、第8章まで読み終えてから計算してみてください。

where 節と集約関数を組み合わせる p71

count(distinct col) の形の count 関数は、ログからユニークユーザー数を計算するときに使うことができます。

select count(distinct customer_id) from access_log_wide where
request_time >= timestamp '2014-01-01 00:00:00' and
request_time < timestamp '2014-02-01 00:00:00';

group by 節の働き p72

group by 節 (group by clause) は、 行を条件によっていくつかのグループに分割し、それぞれのグループ内で集約計算を行うことができる節です。
例えばアクセスログの行をユーザーIDごとにグループへ分割し、それぞれの数を数えれば、ユーザーごとのアクセス数がわかるでしょう。

select customer_id, count(*) from access_log_wide group by customer_id 

以上が group by 節の働きです。
group by 節を使うときの注意点として、 select 節には group by 節に書いたカラムか、もしくは集約関数のどちらかしか書くことができません。
このルールは、集約関数を使うときの制約 (「select 節には集約関数しか書けない」)を少し変形したものです。
group by 節に書いたカラムの値はグループごとに1行だけにまとめられますから、集約関数と同じようなものだと考えるとよいでしょう。

having 節で集約結果をさらに絞り込む p74

前掲の例では、 group by 節を使って月ごとのアクセス数を出すことができました。
今度は、月ごとのアクセス数が10万を切っている月だけを調べたくなったとしましょう。

条件にマッチした行を抜き出すと言えば where 節なのですが、
しかし、where 節は group by 節よりも前に実行されるという点が問題です。

今回は月間アクセス数を集計してから行を絞り込みたいので、 group by 節が終わった後に絞り込む必要があります。

そのような場合に役立つのが having 節です。
having 節を使うと、 where 節 と同じく、行を条件にマッチする行だけに絞り込むことができます。
ただし、 group by 節と集約関数を処理した後に行われる点だけが where 節と異なります。

select request_month, count(*) from access_log_wide 
group by request_month 
having count (*) < 100000;

select 文の実行順序 p75

1. 取得カラムを指定する 「select 節」
2. 対象テーブルを指定する 「from節」
3. 絞り込みの条件を指定する 「where 節」
4. グループ化の条件を指定する 「group by 節」
5. グループ化した後の絞り込み条件を指定する 「having節」
6. 並び換えの条件を指定する 「order by 節」
7. 取得する行数を制限する 「limit 節」

select文を書くときは、必ずこのとおりの順序で記述しなければなりません。
しかし実際の処理は次の順序で行われます。

1. from 節
2. where 節
3. group by 節
4. select 節
5. having 節
6. order by 節
7. limit 節

select文は見ためどおりに上から実行されるわけではないので、最初は混乱しがちです。
ですが、節が実行される順番は決まっていますし、よくよく見ると select 節以外は書いてある順番と同じですから、
しばらく書いていれば苦もなく読み書きできるようになるでしょう。

union 演算子で「合計」 行を追加する p81

そこで、全体合計を別途計算して結果を合成する方法について説明します。 

union all 演算子 (union all operator) を使うと、この select 文の結果をさきほどの結果の行と合体させることができます。
つまり、2つの select 文の結果を 縦につなげることができるのです。

装飾と計算は分離する p83

SQL を使って集計することに少し慣れてくると、いろいろ装飾を付けたくなってくると思います。
例えば数値は3桁ずつでカンマを入れたいとか、「0.13」ではなく「13%」と表示したいとか、単位を付けておきたいとか、そういうことです。

しかし、そこはぐっとこらえて、SQLレベルではできる限り生の数値や文字列に留めておくことをお勧めします。
なぜなら、そのほうが再利用しやすいからです。

最終的に人が見るときにいろいろと装飾が必要なのは確かなのですが、それを SQL レベルでやってしまうと再利用を阻害してしまいます。
装飾を付けるのはアウトプットを作るときと決めて、SQLでは生のデータを出すことに集中しましょう。

もっとも、何が「生」で何が「装飾」かというのも、それはそれで意外と難しい問題です。
そのような感覚は逆に、SQLに習熟することで身に付いてくるものかもしれません。

SQL の除算は整数除算 p88

なお、整数どうしの除算(割り算)は大方の人が想像しているのと違う動作をするため、注意が必要です。
PostgreSQL の 「/」 は、整数どうしの割り算をするときには割った余りを切り捨てて整数部分だけを返します。

これを整数除算 (integer division) または略して整除と言います。
例えば「17/5」の結果は、ではなく「3」です。「6/4」は1です。「3/5」は0です。

SQL にも小数はあるのですが、 コンピューターは整除のほうがかなり高速に
処理できるため、プログラミング言語では伝統的に整除がよく採用されています。
それでは、小数の演算がしたいときはどうすればいいでしょうか。
そのような場合は、左と右のどちらかの数値を小数(小数型の値)にしてやれば小数演算になります。
例えば 「17/5」 ではなく 「17.0 / 5」 とすれば、結果は3.4になります。
もちろん 「17/5.0」 としてもいいですし、「17.0/5.0」でも構いません。

型のキャスト p88

ただ、左右の数値のどちらかが 「17」 のように決まった値ならそれでよいのですが、
カラムに入っている整数に対して演算をして小数を得たい場合もあるでしょう。
そのような場合は、型のキャスト (type cast) をする必要があります。

キャストは、値を別の型に変換する操作です。
例えば integer 型 (整数)を real 型 (浮動小数点数) に変換することができます。

算数や数学で整数と小数を区別して考えるということはあまりなかったと思いますが、SQLの世界では整数と小数は完全に別物です。
ですから、整数を小数として扱ったり、小数を整数として見たいときには、必ず型キャストをはさむ必要があります。

値の型を別の型にキャストするには、次のように cast 式を使います。 
cast (col as real)

この式は、 式 col の値を real 型にキャスト (変換) しています。
例えばcolの 「5」なら、 cast(col as real) は 「5.0」 です。

また、PostgreSQL では次のようなキャスト式も用意されています。 
col::real

この式は、 cast(col as real) と完全に同じ意味です。
cast 式は書式がちょっと面倒なので、簡単に書けるように追加で用意されているのです。
ただし、 PostgreSQL 以外のデータベースでは使えない場合があるので、この書きかたをしてダメだったときは cast 式を使ってください。

最後に、キャスト式を他の式の中で使うには次のように書きます。 
cast(col as real) / 5 または (col: real) / 5 

この式はいずれも、「式 colの値を real 型に変換してから5で割る」という意味です。
「:」は式のどの部分にかかるかの規則が少々ややこしいので、原則としてカッコでくくって使うことをお勧めします。

文字列処理の関数 p92

select 節に現れた 「char_length」 は関数の呼び出しです。
前章でお話しした集約関数とは異なり、1行の中の値1つだけで計算を行います。
しかしすべての行に同じように作用するという点では「+」や「*」のような演算子と同じです。
このような関数を集約関数と区別してスカラー関数 (scalar function) と呼びます。

group by order by を短く書く p95

PostgreSQL の group by 節と order by 節は、カラムの名前の代わりに順序を表す番号を指定できます。 
例えば次の2つの select文は同じ意味です。 

select request_month, count(*) from access_log_wide group by request_month order by request_month 
select request_month, count(*) from access_log_wide group by 1 order by 1

xx時間前の時刻と××時間後の時刻 p98

日付の操作があるなら、当然、時刻の操作もあります。
例えばアクセス数のような実績の前月比や前年比を出したいときには1ヶ月前の時刻を計算したいでしょう。
また、ログが世界中から集められている場合は時刻のタイムゾーンが

協定世界時 (UTC, coordinated universal time) (タイムゾーン+0、昔のGMT)に揃えられている場合があります。
その場合は日本との時差が9時間あるので、日本時間に合わせるために数時間先を得たいというケースもありえます。

そのような場合は、 interval 型 (interval type) という特殊な値を使って計算します。
interval 型は時刻と時刻の差を表すデータ型で、「9時間」とか「365日」、「3日と2時間23分」 のような任意の時間差を表現できます。
また、 timestamp 型の値に足したり引いたりすることで、ある時刻の「9時間先」「1ヶ月前」などを計算することができます。

例えば時刻 request_time の9時間先を計算するには、次のように書きます。
select request_time interval '09:00′ from access_log;

マニュアルを読もう! p105

そのときのために、自分が使う RDBMS のマニュアルは必ず入手しておきましょう。
最近はほとんどの RDBMS ベンダーがウェブサイト上にマニュアルを公開していますから、
検索エンジンで 「PostgreSQL マニュアル」のように検索すればたいていすぐに見付かります。
自分の使っている RDBMS のマニュアルは すべて探してブックマークに入れておくことをお勧めします。

テーブルの正規化 p110

いい方法はあります。アクセスログを管理するテーブルと顧客を管理するテーブルを分ければよいのです。
テーブルを分けて情報の重複をなくしていくこの作業を、テーブルの正規化 (normalization) と言います。

テーブル同士のつながりを示すリレーションシップ p112

正規化後の access_log テーブルと customers テーブルを見てみると customers テーブルの1行に対して access_log テーブルの行は複数が対応することがわかりますね。
つまり言い換えると、 access_log テーブルと customers テーブルは行が多対一の関係にある、と言えます。

テーブルのリレーションシップはER 図 (entity relationship diagram) という図で表すのが一般的です。

join旬でジョインを記述する p115

イメージができたところで、実際の select 文を見てみましょう。
次のselect 文は、access_log テーブルと customers テーブルを、 customer_id をキーとしてジョインします。 
select * from access_log as a join customers as c on a.customer_id = c.customer_id 
ジョインはfrom 節に記述します。

join 句の詳細 p116

ここでいったんジョインの細かい文法の話をしておきましょう。
すでに述べたように、通常のジョイン処理は次のような文法で from 節に記述します。

select ... from テーブル名1 as 別名1 join テーブル名2 as 別名2 on ジョイン条件 

まず 「join」 は 「inner join」 と書くこともできます。これは単なる別名です。
いま話しているジョインは正確には inner join (内部結合) と呼ばれるジョインなのですが、
いくつかあるジョインの中でも特によく使う種類なので、短く書けるようにしているのです。

join 句の両側のテーブルには、それぞれ as 旬で別名を付けます。
この別名はなくてもよいのですが、付けないとあらゆるところで何回もテーブル名を書かなければならず、
読みにくく書きにくい文になってしまうので、短めの別名を付けることをお勧めします。

ジョインの実行順序 p118

ジョインは select 文の中で最も最初に実行される処理です。
from 節に join がない場合は単にテーブルから読み込むだけですが、 join 句が書いてある場合はそのタイミングでジョイン処理が実行されます。
その後はこれまで説明してきたとおり、 where 節、 group by 節、・・・・・・と続きます。

プライマリーキーが2カラム以上のテーブルをジョインする p126

これまでに見てきたジョインはどれもプライマリーキーが1カラムでした。
ライマリーキーが2カラム以上の場合は、ジョイン条件に2つのカラムの両方を入れる必要があります。
その場合は、 where 節のときと同じく2つの条件を and 句でつないで on 句に記述します。

select * from order_details as o inner join items as i on o.shop_id = i.shop_id and o.item_id = i.item_id

using 句によるジョイン p127

ほとんどのジョインはプライマリーキーを使ってテーブルを結合するので、条件を表す on 句が「a.x = b.x and a.y = b.y and ...」という形になります。
PostgreSQL では、そのような on 句は 「using (x, y, ・・・)」 と略記することができます。
例えばさきほどのジョイン条件は 「using (shop_id, item_id)」 で済むのです。

さらに大量のデータを一度に入れる p131

もっとも、これはこれで面倒だ、1,000行くらいあるから CSV などからデータを入れたい...という場合もあると思います。
そのようなときは copy 文 (copy statement)を使います。copy文は PostgreSQL 専用の SQL文です。 

copy テーブル名 from 'CSVファイルのフルパス' with format csv;

select 文の結果をテーブルに投入する insert select 文 p132

複雑な計算をするときは、 select 文の結果をそのまま保存しておきたいこともあるでしょう。
そのようなときは insert select 文が便利です。
次のように、「insert into テーブル名」の次に通常どおりに select文を書くことで、その select 文の結果がテーブルに書き込まれます。

insert into daily_sales 
select sales date, sum (sales_amount) from sales group by sales_date

select 文の結果でテーブルを作る create table as 文 p133

create table daily_sales as
select sales_date, sum(sales_amount) from sales group by sales_date

6.6 この章のまとめ p134

本章では、複数のテーブルをジョインしてデータをつなぎ合わせる方法について説明しました。

ジョインはRDBMSの根幹とも言える機能です。
RDBMSでデータをどう保持するか、整理するかという思想のすべては、ジョインがあることを前提として考えられています。
ジョインを理解することは、RDBMSを理解することと言ってもよいでしょう。

対応する行がないときの inner join 句の働き p136

ジョイン先のテーブルに対応する行がない場合に inner join 句を使うと、その行全体が結果からなくなります。

対応する行がなくても行を残す outer join 句 p138

しかし、これでは困る場合もあるでしょう。
データが存在しない場合は例えば 「不明」扱いにして集計したいこともあるはずです。

そのような場合に使える機能が外部結合 (outer join) です。
外部結合は outer join 句 (outer join clause) を使って記述します。

outer join 句は inner join 句と ほとんど同じ挙動をしますが、対応する行がないときの動きだけが異なります。
outer join 句を使った場合、対応する行がないときは代わりにすべてのカラムが null である行を出力します。

select request_time, request_path, prefecture from access_log as a left outer join customer_locations as l on a.customer_id = l.customer_id

outer join 句の種類 p139

outer join 句には、 left, right full の3種類があります。

left outer join は、「左のテーブル」、つまり select 文で先に書いてあるほうのテーブルの行を保存します。
保存する、とは、対応する行が右のテーブルにないときでも左のテーブルの行を結果に残して、右のテーブルのカラムの値は null にするということです。
さきほどの select 文も left outer join 句を使っているので、左のテーブルの行が保存されました。

一方の right outer join 句は、 left outer join 句と完全に逆の働きをします。
つまり、右のテーブルの行を保存します。

最後に full outer join 句は、両方のテーブルの行を保存します。
左と右、どちらのテーブルに対応する行がないときであっても、もう一方のテーブルのカラムをすべて null にして連結します。

この中で実際に一番よく使うのは left outer join 句です。
left と right は書く順番だけの問題なので、どちらかだけあれば済みますね。
そして一般的には、主として分析したいテーブル(さきほどで言うと access_log テーブル)から先に書き始めますから、自然と left outer join 句のほうをよく使うようになるわけです。

select 文解説 p141

ジョインに関してはさきほどほぼ同じ文を見たので大丈夫でしょう。
今回は customer_locations テーブルにデータのないアクセスも「不明」扱いで数えたいので、 left outer join 句を使います。
これで access_log テーブルの行は保存され、 customer_locations テーブルにデータがなければ prefecture カラムが null になります。

次に select節を見てください。 こちらはちょっと複雑です。 
select 
cast(request_time as date) as access_date,
coalesce (prefecture, '不明') as prefecture_name,
count(*) as pv 

1行めでは cast 演算子を使って request_time カラム (timestamp型)を date 型に変換し、時刻部分を切り捨てて日付だけにしています。
また、as 句を使って計算後のカラムに access_date という名前を付けます。

そして2行めですが、 coalesce という新しい関数を使っています。
この関数は、1つ以上の任意の数の引数を受け付けて、null でない最初の値を返す関数です。
つまり、この場合は、 prefecture カラムが null でなければそれを返し、null ならば「不明」を返します。
これで、 prefecture カラムが null だったら文字列「不明」に変換できるわけです。

計算式を用いたジョイン p143

まず最初は、 ジョインするときに on 旬の比較式で計算を行う手法を紹介します。
実は on 句には演算子や関数呼び出しを使った任意の式を記述できるので、その場で計算した値を条件としてジョインすることが可能なのです。

セルフジョイン p145

続いてはセルフジョイン (self join) を説明します。
セルフジョインとは、1つのテーブルを自分自身とジョインする方法のことです。
そんなこといつやるんだ、と言われるかもしれませんが、意外と利用する場面があるのです。
例えば1年前のデータとジョインして伸び率を出したい場合がそうです。

select 
curr.year,
curr.shop_id,
cast(curr.sales_amount as real) / prev.sales_amount as growth_rate
from 
yearly_sales as curr
left outer join yearly_sales as prev
on curr.year - 1 = prev.year
and curr.shop_id = prev.shop_id

クロスジョイン p147

ここまではジョインを「相手のテーブルから対応する行を持ってくる」と表現してきました。
それはそれで正しいのですが、実はその表現だとジョインの動作をうまく言い表せない場合があるのです。

ジョインの行う処理をより正確に説明すると、まずどんなジョインであっても、2つのテーブルの行の総当たりを作ります。

いずれにしても inner join 句の処理は、概念的に「すべての組み合わせを生成する」処理と、
「条件を満たす組み合わせだけを抽出する」処理の組み合わせとして表現できます。

この「すべての組み合わせを生成する」処理だけを、SQLでは cross join (cross join clause) で表現できます。 
次に示すのは cross join 句の例です。 

select * from access_log as a cross join customers as c

古いジョインの記法 p148

SQLにはよくあることですが、 ジョインにも新しい記法と古い記法があります。
いま説明してきたのは新しい記法です。
古い記法はこんな書きかたでした。 

select * from access_log a, customers c where a.customer_id = c.customer_id

inner join句はなく、単に from 節にテーブル名を並べて、ジョイン条件は where 節に書くわけです。

この文法は、意外にも、さきほど話した「すべてのジョインは総当たりの組み合わせ生成である」という考えかたを素直に反映しています。
つまり、 from 節に書いた 2 つのテーブルの行の組み合わせをすべて作り、その中から where 節に指定した条件の組み合わせだけを選ぶよ、ということですね。

いずれにしても、この記法は徐々に廃れていくと思われるので、
これから書く SQL では join on 句を使ってください。

組み合わせを生成するジョインでバスケット分析 p149

ECサイトで買い物をしていると、「こちらもお勧め」 とか 「この商品を買っている人はこれも買っています」というような表示が出ることがあります。
あの表示を出すロジックはいくつも考えられますが、例えば「一緒に買われやすい商品を出してあげる」方法が考えられます。
過去に行われたたくさんの注文を分析して、「このセーターとこのシャツは一緒に買われやすい」とか
「プログラミングの本を買う人はこのマンガも買うようだ」という傾向がわかれば、それをお勧めしてあげるわけです。 

このような分析は俗にバスケット分析 (basket analysis) と呼ばれています。
スーパーマーケットのカゴ(バスケット) に一緒に入れられやすい (買われやすい) のはどんな商品かを計算する分析だからです。
より広く言えばアソシエーション分析 (association analysis) の一種です。

ジョインを応用するとこのバスケット分析を行うことができるので、本書のサンプル EC サイトにも適用してみましょう。

「一緒に買われやすい商品」を計算しよう p149

本書のサンプルECサイトには表7.11 のような商品詳細テーブルがあるので、
このデータをもとに「一緒に買われることの多さ」...併売率を求めていきましょう。

併売率の計算式 p150

併売率はどのように求めればいいでしょうか。
併売率は「ある商品が買われたとき、同じ注文に特定の商品が入っている確率」ですから、
「商品 A と B を一緒に買った注文数 / 商品 A を買った注文数」で求められますね(図73)。

2つの商品を一緒に買っている注文数」のSQL p154

それでは今度は処理を SQL で書いてみましょう。
すべての組み合わせを生成する処理はジョインで書くことができます。
ジョインは、「すべての組み合わせを生成して条件を満たす組み合わせだけを残す」処理だという話をしましたね。
この機能を利用するわけです。 

商品の組み合わせごとの注文数を数える select 文を次に示します。 

select 
    l.item_id,
    r.item_id as item_id2,
    count(distinct l.order_id) as order_count
from 
    order_details as l
    inner join order_details as r
    on l.order_id = r.order_id
    and l.item_id <> r.item_id 
where 
    l.order_time
    between timestamp '2015-04-01 00:00:00'
    and timestamp '2015-04-30 23:59:59'
group by 
    l.item_id
    r.item_id

where 節はさきほどとまったく同じですね。 from 節が問題です。
まず order_details テーブルをセルフジョインして全行の組み合わせを生成します。
2つの order_details テーブルには、それぞれ as 句で l と r  (left と right のつもりです)という別名を付けました。
ジョイン条件の 「l.order_id = r.order_id 」で注文IDが同じものだけに絞り、さらに 「l.item_id <> r.item_id亅 によって同じ商品の組み合わせ (大根と大根とか)を除外します。
これで、1つの注文に よって注文された商品の組み合わせを求められます。 

後は、group by 節によって組み合わせごとにグループ化し、
select 節の 「count(distinct Lorder_id)」 で組み合わせごとの注文数を数えるだけです。

支持度とリフト値 p156

この節で求めた「併売率」は、アソシエーション分析の用語で信頼度 (confidence) と呼ばれる値です。
基本的には信頼度が高い組み合わせは一緒にお勧めしていく候補になりうるのですが、
他に支持度 (support) とリフト値 (lift) も参考として見たほうが精度が上がります。

まず支持度は、その組み合わせがどのくらいの割合で売れているかを示す値で、
通常は 0.00001 などのかなり小さな値を示します。
「(ある組み合わせの注文回数) / (全注文回数)」で計算できます。
信頼度が高くても、支持度があまりにも低いと、そもそもその組み合わせが売れていないということになります。
ある程度売れている組み合わせでないとお勧めする意味がないので、例えば信頼度が同じくらいならばより支持度の高い組み合わせを売っていったほうがよいわけです。

またリフト値は、「その商品を組み合わせることによって、何もしないときより売れるようになる割合」を示しています。
「(XとYを同時に買う確率) / (全体の中でYを買う確率)」で計算できます。
リフト値が1.0を切っている場合、商品を単体で買うよりも組み合わせて買うほうが確率が低いということなので、むしろ組み合わせないほうがよいということを示しています。

つまり全体的に見ると、支持度がそこそこあり、リフト値が1.0より大きく、
信頼度の高い組み合わせというのが最も併売をお勧めする価値があるわけです。

本書では支持度とリフト値の計算については詳細を示しませんが、信頼度を計算する select 文を応用すればどちらも簡単に出せますから、試してみてください。

バスケット分析の結果を活用するには p157

バスケット分析で 「一緒に買われやすい」商品がわかったら、今度はその商品を実際にお勧めする必要があります。
そのためには、一緒に買われやすい商品のデータをECサイトのシステムに送ってお勧めとして出してもらう必要があります。

また、買われやすい商品は時間とともに変わるかもしれませんし、
新しい商品に対してのお勧めも計算する必要がありますから、定期的に分析をやりなおさなければいけません。

そうなると、定期的にバスケット分析を行ってECサイトにデータを転送するというバッチ処理を組みたくなってきますね。
分析の結果は人間が参照して施策につなげる場合もありますが、システムによって活用される場合もあるということです。
このようなバッチ処理システムの構築についての話題は第2部で扱います。

サブクエリーで複雑な select 文を組み立てる p160

本章の主題はウィンドウ関数なのですが、その前にサブクエリーという機能について説明しておきたいと思います。
早くウィンドウ関数の話をしたいのですが、より複雑な分析をしていくうえではどうしてもサブクエリーが不可欠なのです。
また、ウィンドウ関数を使っているとサブクエリーが必要になってしまうとが多いため、この章で説明しておきます。

select 文を多段に組み合わせるサブクエリー p160

select文はテーブルのデータを処理して絞り込みや集計を行うことができました。
しかし、考えてみると select 文の結果もまた行とカラムをもっており、テーブルのような構造のデータですね。
つまり、 select 文はテーブルを処理してテーブルのような結果を返すわけです。

ここでちょっと頭を柔らかくして、想像力を働かせてください。
select文はテーブルを処理対象にするが、その結果もまたテーブルのようなものである・・・ということは、
select 文の結果をまた select 文で処理できてもよいのではないでしょうか。

そして実際、リレーショナルデータベースではサブクエリー (sub-query) という機能を使うとそれができます。
つまり、 select 文の結果を select 文で処理できるのです。

最初の select 文の from 節に、 カッコで囲まれた select 文が記述されていますね。
このカッコで囲まれているほうの select 文がサブクエリーです。
上記のように書くと、まずサブクエリーが通常通り実行され、その結果がさらに外側の select 文で処理されます。

サブクエリーの構文 p162

一般的に言うと、サブクエリーの構文は次のようになります。 
select カラムリスト from (select文) as サブクエリー名;

このように書くと、カッコに囲まれたほうの(内側の) select 文がまず実行され、
その結果が外側の select 文によって処理されます。

なお、 PostgreSQL では、サブクエリーの結果には必ず名前を付ける必要があります。
上で「サブクエリー名」と書いているのがそれです。
例えば「select * from (...) as tmp;」と書いたら 「tmp」 がサブクエリー名です。
as は省略することもできますが、本書では原則として as を付けます。

スカラーサブクエリー p166

サブクエリーにはもう1つ、地味ながら大変使える応用があります。
それはスカラーサブクエリーという使いかたです。

スカラーサブクエリー (scalar sub-query) とは、 select 節の式や where 節の式の一部として使うサブクエリーのことです。

select cast (order_count as real) / (select sum (order_count) from item_combination_order_counts) 
from item_combination_order_counts;

スカラーサブクエリーとして使うサブクエリーは、必ず1行1カラムのリレーションを返すサブクエリーでなければなりません。
1行1カラム······つまりたった1つの文字列や数値のような値を返すサブクエリーでなければ、実行前に構文エラーになります。

ちなみにスカラー値 (scalar value)とは「たった1つの文字列や数値のような値」のことを指します。
スカラー値を返すサブクエリーなので、スカラーサブクエリーと言うわけです。

「サブクエリー遅くない?」 p167

わたしが3段や4段にもなるサブクエリーを書いているとときどき言われるのが「サブクエリー書くと遅くない?」というセリフです。
声を大にして言いたいのですが、それは MySQL だけだろ!!

並列 RDBMS でも Oracle でも PostgreSQL でも、サブクエリーが特に遅いなんてことはありません。
MySQL がヘボすぎるだけです。
もっとも、MySQLはOLTP用のRDBMSですから、サブクエリーが何段にもなるようなクエリーを実行するのが悪いという意見は理解できます。
また、 最近は MySQL もだいぶ賢くなってきているので、サブクエリーを書くと遅いなどという不名誉な噂もそろそろ過去のものになるかもしれません。

いずれにしても、SQLで分析をする場合はOLTPと比べてかなり複雑な処理をSQLだけで行うことになるので、サブクエリーは必須です。
「サブクエリーは遅い」なんて固定観念は捨ててどんどん使っていきましょう。

ウィンドウ関数とは p168

ウィンドウ関数 (window function) は「分析関数」とも呼ばれる関数です。
たいへんざっくり言うと、 group by 節で作ったグループの中の行を見ながら、集約をせずに計算することができます。

まったく意味がわからないと思うので、具体的に説明しましょう。
group by 節を使うと、行をグループに分割して、そのグループごとに集約計算をすることができました。
例えば group by 節と sum 関数を組み合わせれば、店舗IDでグループを作って店舗ごとの売り上げを計算したり、
ユーザーIDと月でグループを作ってユーザーごと月ごとのアクセス数を数えることができました。

ですが、 group by 節を使って集約した場合は、グループごとに1行だけが出力されますね。
言いかたを変えると、1行になってしまうわけです。

しかし、ウィンドウ関数は違います。
ウィンドウ関数の sum を使うと、なんと、図8.2のように集約をせずにグループ内の合計を計算して、
その結果を元の行に追加することができるのです。

通常の sum 関数を使ったほうは customer_id ごとに1行にまとまってしまいますが、
ウィンドウ関数の sum を使った結果は、行数は変わらずに、合計が横に追加されていますね。
これがウィンドウ関数の能力なのです。

この機能を使うと、例えば、店舗ごとにどのカテゴリーの商品が店舗売り上げの何%を占めているかを一発で計算できます。 
別の言いかたで説明すると、通常の関数では1行の中の値しか見られないのに対し、
ウィンドウ関数はグループ内の他の行の値も見ることができるのです。
処理可能なデータの範囲が増えれば当然やれることも増えるわけで、ウィンドウ関数は高度な分析には欠かせない強力な機能を提供してくれます。

様々なウィンドウ関数 p169

ウィンドウ関数の概念それ自体はとても抽象的なので、慣れるにはとにかく具体的な例を見るのが一番です。
ここからは、次の6つの典型的な使用例を紹介していきましょう。

1. グループ内の行に順位を付ける: rank ウィンドウ関数 
2. 履歴テーブルから最新行を取得する : row_number ウィンドウ関数 
3. 対全体比を計算する: sum ウィンドウ関数 
4. 累積和を計算する: sum ウィンドウ関数 
5. デシル分析をする: ntile ウィンドウ関数 
6. 時系列データの移動平均を計算する : avg ウィンドウ関数

rank ウィンドウ関数で順位を付ける p170

この順位は rank ウィンドウ関数で付けられます。
rank ウィンドウ関数を使 て月ごとの売り上げランク (monthly_sales_rank) を付けるクエリーは次のとおりです。

select 
    sales month, 
    shop_id,
    sales_amount,
    rank() over (
        partition by sales_month 
        order by sales_amount desc 
    ) as monthly_sales_rank
from
    monthly sales;

select 節の rank 関数呼び出し以外は問題ないでしょうから、 rank だけ説明しましょう。
この行は記述が非常に長いですが、「rank () over (...)」までが1つの rank 関数呼び出しです。

over の中身は次のように分けて理解してください 

partition by sales_month
sales_month をキーにしてグループを作る (group by する)

order by sales_amount desc
作ったグループ内で、行を sales_amount の多い順にソートする (order by する)

いかがでしょうか。 ウィンドウ関数は、 関数と言いながらも内部に group by 節や order by 節を持っているかのような働きをします。
そのぶん記法も複雑ですが、基本的にはすでに知っている機能の組み合わせです。
見ために騙されずに、部分ごとに理解していきましょう。

ウィンドウ関数の結果で絞り込むには p173

第4章で select 文の節が実行される順序を話したことを覚えているでしょうか。
select 文で最初に実行されるのは from 節で、その後 select節、where節 この後に実行されるのです。
つまり、 where 節を判定するときはまだウィンドウ関数が実行されていないので、その結果がありません、存在しませんよ、というのがさきほどのエラーなのです。

では、ウィンドウ関数の結果で絞り込んだり、 group by 節のキーにしたりするにはどうしたらよいのでしょうか。
簡単です。次のようにサブクエリーを使えばよいのです。

累積和の対全体比を計算する p180

いま計算したのは単純な対全体比でしたが、店舗ごとの年間売り上げ目標に対して毎月の達成率を計算するような場合だと、
年間全体の売り上げ目標に対する累積売り上げの比率を出したいでしょう。
sum ウインドウ関数の使いかたを少し変えるだけで累積も計算できるので紹介します。
まずは比率を考えず、店舗ごとに毎月の売り上げのを出す文を次に示します。

select
    sales_month,
    shop_id,
    sales_amount,
    sum(sales_amount) over ( 
        partition by shop_id
        order by sales_month
        rows between unbounded preceding and current row
    ) as cumulative sales_amount
from 
    monthly_sales;

sum ウィンドウ関数の over 句の中身が増えましたね。
この 「rows between...」はウィンドウフレーム (window frame) を定義する句で、
ウインドウ開数の計算対象範囲をグループ全体から狭める働きがあります。
対全体比のselect文のようにウィンドウフレームの記述を省略した場合は、 partition by 句で設定したグループすべての行が対象となります。

ウィンドウフレームの記述 p181

一般には次のように記述します。 
rows between 前側の行の範囲 and 後ろ側の行の範囲

「前側の行の範囲」には次のいずれかを指定できます。
現在処理中の行自身を指定する 「current row」
「n行前まで」を指定する 「n preceding」
「前にある行全部」を指定する 「unbounded preceding」

「後ろ側の行の範囲」には次のいずれかを指定できます。
現在処理中の行自身を指定する「current row」
「n行後まで」を指定する 「n following」
「後ろにある行全部」を指定する 「unbounded following」

さきほどのクエリーに指定しているのは前側が「unbounded preceding」で後ろ側が「current row」なので、
「前にある行全部と、現在処理中の行」という意味です。

デシル分析とは p184

デシル分析 (decile analysis) とは、購買金額の順に顧客を人数ベースで 10 等分する分析です。
一般に、 「2割の顧客が売り上げの8割を占めている」というように、特定の顧客に売り上げが偏る傾向が知られています。
デシル分析は顧客をランク付けすることで優良顧客を探そうとする分析です。

デシルは英語だと decile で、その語幹のdeciはラテン語で10分の1を意味する「decimas」に由来しています。
デシリットルの「デシ」 と言えばわかるでしょうか。

(2) ビューとして定義する p185

次に、この select 文をビューにする方法です。
ビュー (view) は SQL の機能で、あたかもテーブルのように見えるけれども、
実は使われるたびに select 文を実行してその結果を見せてくれるという機能です。

例えばさきほどのselect文をもとにビューを作るには、次のような SQL を実行します。
create view yearly_orders as select ... (select文は上記と同じ);

これで yearly_orders というビューが作成され、
select 文で「from yearly_orders」と書くだけでさきほどの select 文を実行し、結果を得られるようになります。

(3) テーブルに書き込む p186

最後に、結果を格納するテーブルを作る方法があります。
PostgreSQLでは、次のように create table ~ as 文を使って、さきほどの select 文の結果をテーブルとして保存しておくことができます。
他のRDBMS では create table 文と insert select 文を使ってください。

create table yearly_orders as select ... (select文は上記と同じ) 

この方法でテーブルを作ると、 select 文の結果が実際に保存されるため、
毎回 orders を処理する必要がなく、高速に動作します。
その一方で orders テーブル が更新されたときにその変更は反映されませんし、RDBMSのディスク容量も消費し続けます。
select 文の結果に対して何度も何度も試行錯誤したい場合はテーブルを作るとよいでしょう。

ntile ウィンドウ関数でデシル p186

さて、元データが準備できたので、本来の目的であるデシル分析に進みましょう。
PostgreSQL では ntile ウィンドウ関数を使ってデシル分析ができます。
ちなみに ntile は 「n-tile」 のように切って読みます。

いま作った yearly_orders ビューを対象にデシル分析するには、次のような select 文を書きます。

select 
    order_year,
    customer_id,
    order_amount,
    ntile (10) over (
        partition by order_year
        order by order_amount desc
    ) as decile
from
    yearly_orders;

まずntile 呼び出しの over 句の 「partition by order_year」 で、 order_year ごとにグループ化します。
そしてその中を 「order by order_amount desc」でソートします。
desc が付いているので、 order_amount の大きい順でソートされます。
そして最後に、 「ntile(10)」 で全体を10分割して各行に1~10の値を付与し、その値に decile という名前を付けます。
ntile ウィンドウ関数の引数を変えれば 3 でも 5 でも好きな数に分割できます(デシル分析ではなくなってしまいますが)。

移動平均を計算する p188

移動平均 (moving average) とは、 図 8.6 のように、直近の n 個の値の平均をその時点の値とする計算手法です。
移動平均を使うと、日々の細かい変動要因をならして、グラフを滑らかにすることができます。

加重移動平均を計算するには p190

ここで説明した移動平均は、正確には単純移動平均 (simple moving average) です。
この他に、最近のデータにより重みを持たせる加重移動平均 (weighted moving average) や
指数移動平均 (exponential moving average) という手法もあります。

SQL でこのような加重を付けた移動平均を計算したい場合、残念ながら avg ウィンドウ関数では足りません。
次の章で利用する array_agg 集約関数をウィンドウ関数として使い、直近 n ヶ月のデータを配列にまとめて計算する必要があります。

ウィンドウ関数を一発呼ぶだけの単純移動平均と比べるとだいぶ複雑なコードが必要にはなってしまいますが、もし必要になったときは試してみてください。

配列と unnest 関数を利用した横→縦変換 p203

実は、 PostgreSQL 特有の機能を使うと、横持ちから縦持ちの変換はもっと簡単に書けます。
次のように書けばよいだけです。

select * from ( 
    select 
        order_id,
        unnest(array[item_id1, item_id2, item_id3, item_id4]) as item_id
    from
        order_details_h
) tmp 
where 
item_id is not null;

unnest という関数を呼び出している行が、よくわからないことになっていますね。
この行は「配列の作成」 と 「unnest 関数呼び出し」の組み合わせなので、順番に説明しましょう。

まず 「array[item_idl, item_id2 item_id3, item_id4]」という記述は、「配列」を作成しています。
配列 (array) とは、同じ型の値をたくさん並べたデータ型のことです。
例えば「文字列の配列」 や 「整数の配列」が存在します。
単にリストと考えてもよいでしょう。
「array[a, b, c]」 という形の式は、a、b、cの3つの値をその順番で格納した配列を新しく作成します。

そして unnest 関数は通常の関数とはかなり趣が異なり、配列の内容を「行に展開する」機能があります。
「行に展開」 するとは、一言で言えば縦持ちにするということです。
もう少しだけ正確に起こることを説明するなら、配列の中身を1行に1つの値になるよう行を増やし、
unnest 呼び出し以外のカラムをそれに合わせて複製する、となります。
unnest 関数は集約関数の逆の操作だと言ってもよいでしょう。

配列を使った縦横変換 p208

unnest 関数は配列を行に展開する関数でしたが、 PostgreSQLにはその逆、複数行の値を配列に集約する関数もあるのです。
名前を array_agg と言います。
array_agg 関数を使って縦持ちテーブルを order_id 1つにつき1行に変換するには次のような select 文を使います。

select 
    order_id,
    ids[1],
    ids[2],
    ids[3],
    ids[4]
from (   
    select
        order_id,
        array_agg (item_id) as ids
    from
        order_details_v
    group by 
        order_id 
) tmp

テーブル関数によるカラム展開 p211

PostgreSQLには、JSON 文字列を解析・分解してリレーションとして返すテーブル関数 json_to_record が用意されています。
この関数を使って JSONをカラムに展開するには、次のような select 文を使います。 

select
    l.request_time,
    l.request path,
    l.customer_id,
    v.view_seconds, 
    v.scroll_ratio, 
    v.click_button
from 
    access_log_dyn as l
    cross join json_to_record(cast(l.json_params as json)) 
        as v ( 
            view_seconds integer,
            scroll_ratio real,
            click_button text
        );

9.5 この章のまとめ p215

そして忘れてはならないのがunnest関数です。
この関数は特に××関数と 種類を言わなかったのですが、それもそのはず、
実は unnest は上記の4つのどの関数でもない、「unnest 関数」 というカテゴリーの唯一の関数なのです。
つまり本書では5種類の関数が登場していたわけですね。

論理パーティション p233

普通のSQLの本で速度と言うとたいていインデックス (index) が登場するのですが、
こと大規模な集計に関してはインデックスはほとんど効き目がありません。

大量の行から数行を抜き出すような処理にはインデックスが効果的ですが、
大量の行から連続した広い範囲をガバッと抜き出すような処理には効かないからです。

速度を向上するための仕組みも当然両者では異なってきます。 
・ねません 
分析 SQL と OLTP の SQL ではこれだけクエリーの傾向が違うわけですから、

PostgreSQL で大規模集計に効き目のある機能と言えばほぼ1つしかありません。
それは論理パーティション (logical partition) です。

論理パーティションとは、テーブルのデータを特定の範囲だけ固めてディスクに置いておく機能のことです。
論理パーティションの非常にありがちな使いかたは、 orders テーブルのように時系列の巨大なテーブルを日ごとや月ごとに固めて置いておく方法です。
そうすると、クエリーに 「2014年4月」のような日付の範 が入っている場合には、
その月のデータだけをディスクから読み込むだけで集計ができるようになるわけです。

大規模な集計の速度はディスクからの読み込みで実行速度がほぼ決まるので、
論理パーティションによって読み込み量を削減できれば、大幅な速度向上を見込めます。
数千万行にもなるような巨大なトランザクションテーブルには、必ず論理パーティションを設定すべきです。

ちなみに、このときの「日ごと」とか 「月ごと」 のデータのかたまりがパーティション (partition) です。
テーブルを論理的に複数のパーティションに分割するので、論理パーティションと呼びます。

統計を取ろう p234

これは分析 SQL に限った話ではないのですが、 PostgreSQL では、
テーブルの統計 (statistics) を取ることでデータベースがよりよい実行計画 (execution plan) を立てられるようになります。

ここで言う統計というのは、わたしたちが分析のために算出する統計のことではなく、
PostgreSQL サーバーが自分のために取得する専用の統計情報のことです。
具体的には、 analyze 文 (analyze statement) を実行することで取得することができます。
「analyze テーブル名」と書くだけです。
この統計をとって不利益になることはまずないので、新しいテーブルを作ってデータを入れたらお約束として統計を取ってしまってください。
また大量のデータを追加したときなど、 データ更新時にも統計を取り直すべきです。

また実行計画というのは、PostgreSQL サーバーがクエリーを実際にどのような順番で処理するかという計画のことです。
この実行計画は explain 文 (explain statement) を使うことで見られます。
select 文の実行計画を見るには、「explain 任意の select 文;」 を実行してください。

クエリーが複雑で巨大だと、実行計画のよしあしは実行速度に影響します。
予想したより分析クエリーが妙に遅いなと思ったら、実行計画をチェックしてみるとよいでしょう。

パルクロードとバルクエクスポート p260

ところで、 Extract や Load の操作は具体的にどうするのでしょうか。
ここは性能に影響してくるため、RDBMSごとにかなり違うところなのですが、
おおよそ次の 3 つのどれかに当てはまります。

1. select 文や insert 文のような標準の SQL を使う
2. Copy 文のような RDBMS 固有の SQL 文を使う
3. 専用ツールを使う

PostgreSQL の場合は1と2の方法を使うことができます。
2についてごく簡単に説明しましょう。
例えば access log テーブルの全行をファイルに出力させたいときは、次のような copy文を実行します。
copy access_log to '/tmp/access_log.txt'

これで access_log テーブルの全行がPostgreSQLサーバーのあるコンピューターの/tmp/access_log.txtファイルに出力されます。
PostgreSQL クライアントのコンピューター(手元のパソコン)ではなくサーバー側なので、注意してください。

あれっ? ストアドプロシージャは !? p298

一般的に SQL でバッチというとストアドプロシージャが付きものです。
ストアドプロシージャ(stored procedure) とは、複数の select 文の実行や条件分較やループのようなロジックをひとまとめにしてRDBMS側に保存したものです。
関数のように出すことで、決まった処理を実行できます。

本書ではストアドプロシージャは意図的に使いませんでした。
Hadoopや並列 ROSMSではサポートしていない場合があること、
実行時に入力・出力テーブルを差し替えるのが難しいなど柔軟性に欠けること、
select 文よりもはるかにデータベースによる差が激しいコードのバージョン管理がしにくいなどの点がネックです。

データ更新をアトミックにする p305

ここまでの話は、バッチ中はテーブルが参照されないという大前提で話しています。
しかしもし、24時間参照される可能性があるテーブルをバッチで更新した場合...言い換えると処理をアトミックにしたい場合はどうすればよいでしょうか。

例えばPostgreSQL と Redshift では、
alter table 文を次のようにトランザクション内で実行すればテーブルの入れ換えがアトミックになり、いつアクセスされてもエラーになりません。

create table daily_sales_NEW (...); 
insert into daily_sales_NEW select ... (略) ...;

begin transaction; 
ここが重要! 
alter table daily_sales rename to daily_sales_OLD; 
alter table daily_sales_NEW rename to daily_sales; 
commit:

ビューを使ってアトミックなテーブル入れ換え p306

ビューの定義変更がアトミックにできるデータベースならば、 「select *」 を実行するだけのビューを間にはさむことでテーブル入れ換えをアトミックにできる場合もあります。
その場合は、ビューの定義を 「select * from xxx_OLD」 から 「select * from xxx_NEW」 に変えることでテーブル入れ換えを実現します。

第14章 本書を読み使えた後に p314

Andrew Cumming, Gordon Russell [SQL HACKS] オライリージャパン、 2007
大量のSQLテクニック集が掲載されています。
これは思い付かない ... という技も多く参考になります。
ほとんどのレシピは代表的なRDBMS (PostgreSQL MySQL, Oracle, SQL Server) のすべてで動作するように書かれているので、
自分が使っている RDBMSで動かないということも少ないでしょう。