100本ノックの補習メモ-機能編-

前回、データサイエンス100本ノック（構造化データ加工編）の実施で得たことをメモとしてまとめた。

jis.hatenablog.com今回はその続きである。知らなかった機能、知っていても新たに知った機能についてメモ書きしておく。

副問合せの前定義

SQLで副問合せを使うとき、通常はFROMの後に問い合わせ対象をSELECT文で指定しそれに対して仮想View名を付けて使用する。
WITHを使って事前に副問合せを仮想Viewの形で定義することで何を・どこから取得するかということが明確化しやすくなる。
WITH 仮想View名 AS (SELECT ...)の形で使用する。

S-034を例にしてみる。
まずはWITHを使わない場合は以下になる。

SELECT AVG(sum_amount) FROM (
 SELECT 
  customer_id, SUM(amount) AS sum_amount
 FROM receipt
 WHERE 
  customer_id not like 'Z%'
 GROUP BY customer_id
) customer_amount ;

WITH句を使用すると以下になる。

WITH customer_amount AS (
    SELECT 
     customer_id, SUM(amount) AS sum_amount
    FROM receipt
    WHERE 
     customer_id not like 'Z%'
    GROUP BY customer_id
)
SELECT AVG(sum_amount) FROM customer_amount;

WITH句を使ったほうがS-034で求められている全顧客の平均という主題をより明確化できている。
……イマイチ実感がわかないだろうか？
S-059を例にしてみる。ちなみにこれは合計した売上金額を平均0、標準偏差1に標準化したものだ。
まずはWITH句を使わない場合は以下になる。

SELECT
 sales_amount.customer_id, 
 sales_amount.sum_amount,  
 (sales_amount.sum_amount - stats_amount.avg_amount) / stats_amount.std_amount as normal_amount
FROM (
 SELECT
  customer_id, SUM(amount) as sum_amount
 FROM receipt
 WHERE
  customer_id NOT LIKE 'Z%'
 GROUP BY customer_id
) sales_amount, (
 SELECT
  AVG(sum_amount) as avg_amount,  stddev_samp(sum_amount) as std_amount
 FROM (
 SELECT
  customer_id, SUM(amount) as sum_amount
 FROM receipt
 WHERE
  customer_id NOT LIKE 'Z%'
 GROUP BY customer_id
 ) sales_amount2
) stats_amount
;

参照先のViewに被るカラム名はないためsales_amount.customer_idなどの指定は本来不要だが明確化するために記述している。
また上記のsales_amountとsales_amount2の問い合わせ内容は全く同一であるがstats_amountの問い合わせにおいてsales_amountを使用することはできない（エラーにはならないが結果が想定外となる）。
WITHの場合WITHで定義した仮想ViewはWITH内の定義で参照可能なので以下のようになる。

WITH sales_amount AS(
 SELECT 
  customer_id, SUM(amount) as sum_amount
 FROM receipt
 WHERE
  customer_id NOT LIKE 'Z%'
 GROUP BY customer_id
),
stats_amount AS (
 SELECT
  AVG(sum_amount) as avg_amount, stddev_samp(sum_amount) as std_amount
 FROM sales_amount
)
SELECT
 sales_amount.customer_id, 
 sales_amount.sum_amount,  
 (sales_amount.sum_amount - stats_amount.avg_amount) / stats_amount.std_amount as normal_amount
FROM sales_amount, stats_amount
;

このようにWITH句の使用は仮想Viewを複数定義するときに真価を発揮する。
WITH句はpostgresql,oracle,mysql(8.0以上)で利用可能だ。
また実行計画だがS-034のような単純な利用の方法ではWITH句を使っても使わなくても変わらない。
S-059のように副問合せの中で別の副問合せの結果を利用する場合、WITH句を利用すると一時表に対するアクセスとなるので実行計画は変化する。
変化が良いか悪いかはデータの状況によるがS-059に関してはWITH句を用いたほうが実行計画のcost値は良かった。

取得数制限

通常SELECTの問い合わせは指定した条件に一致するすべてのものを返す。だが取得対象のデータ数が膨大だと困ったことになる。結果が100万件とかだったりすると表示処理でシステムが死ぬ(OR-Mapperを使ったシステムでありがち)。
これを避けるためにデータベースには取得数を指定する方法が存在する。

postgresqlの場合: SELECT … [LIMIT { number | ALL}] [OFFSET number];
mysqlの場合: SELECT … [LIMIT [offset(number),] number ;

offsetを指定した場合はそのレコード数分結果から読み飛ばす。
上記のS-059では10件のみ求められていた。なので末尾にlimit 10をつければ求める結果となる。

oracleの場合なのだがlimitは残念ながら使用できなかった。そのためROWNUM <= 10 などをwhere句で指定する必要があった。order byでソートした結果に対して件数制限する場合はインラインビューを使うも必要あった。
さて、あったと過去形で書いたのには理由がある。自分の認識では長らくそうだったのだが最近(oracle12c以降なので最近でもないが……)できるようになったらしいのだ。

oracleの場合: SELECT … [OFFSET <行数> ROWS] FETCH {FIRST|NEXT} [<行数>|<パーセント> PERCENT] ROWS {ONLY|WITH TIES} ;

SELET * FROM receipt FETCH FIRST 10 ROWS ONLY; などという形で使う。
ちなみにこのFETCHはpostgreqlでも利用可能だ。mysqlでは利用できないようだ。

余談だがoffsetを利用して前の行を読み飛ばすのは効率が悪い。offset数が多くなるとなおさらである。この場合、検索条件として前の最後の行の情報を入れるといいらしい。
興味がある場合は以下のサイトを参照されたし。

OFFSETは前の行を読み飛ばすのにはよくない方法

GROUP句の気づき

GROUP句はBYで指定したカラムで取得対象をグループ化するものである。このことは当然知っていた。
GROUP句を利用した場合SELECTで使用できるのはBYで指定したカラムか集合関数のみである。これも知っていた。
だが正確ではなかったようだ。具体的にはSELECTで使用できるカラムのところだ。
基本的にはこれで正しいが、主キー・ユニークインデックスを貼ったカラムをGROUP BYに指定した場合はBYで指定していない他のカラムもSELECT内で利用可能となるようだ。
このことに気がついたのはSQL-91の回答で以下のような記述があったからだ。

SELECT 
 c.*, CASE WHEN SUM(COALESCE(r.amount,0)) > 0 THEN 1 ELSE 0 END AS sales_flg
FROM customer c
LEFT JOIN receipt r ON c.customer_id = r.customer_id
GROUP BY c.customer_id

GROUP BYで指定したのはcustomer_idなのだからSELECTで利用できるのはcustomer_idだけのはずである。でもc.*としているので他のカラムを利用していることになる。

なぜ利用できるのか。

customerとreceiptの多重度は1対多である。これを外部結合するとreceipt内に存在するcustomerの数+receipt内に存在しないcustomerの数のレコードを取得することになる。

この外部結合したレコードの内容は同一のcustomer_idであるならばcustomerのカラムの部分はすべて同一となる。そしてこの外部結合したレコードをcustomerの主キーであるcustomer_idでグループ化してもcustomerの内容に関してはすべて重複しているので差異はない。このためSELECTで問題なく使用できると思われる。

要するにGROUP BYで指定しても結果はすべて重複しているようなカラムに関してはSELECTで使用しても問題ないわけである。
言い換えるとレコードが必ず唯一となることが保証されているカラムをGROUP BYで指定した場合はSELECTでそのカラム以外を利用しても問題ない。

この唯一となることが保証されているカラムというのがポイントだ。具体的には主キーやユニークインデックスが貼られているカラムのことである。

これ以外のカラムでGROUP BYを指定した場合はこのカラム以外はSELECTで指定することはできない。なぜならデータベースが事前に結果が唯一となることがわからないからである。

Jの衝動書き日記

さらりーまんSEの日記でございます。

100本ノックの補習メモ-機能編-

副問合せの前定義

取得数制限

GROUP句の気づき