100本ノックの補習メモ-テクニック編-

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

jis.hatenablog.com

jis.hatenablog.com今回で最後となる。データ加工のテクニックについてメモ書きしておく。

クロス集計

クロス集計とは質問項目を２つ以上かけ合わせて集計する手法のことだそうである。SQLの手法としては指定されたカラムを基準として他のカラムの値をかけ合わせて複数の行を複数のカラムに集約することになる。

例えば以下のようなテーブル(gender_era_people)があったとする。

年代(era) 性別(gender_cd) 人数(people)
10,男(0),5
10,女(1),1
10,不明(2),2
・・・

これを縦に年代、横に性別のクロス集計すると以下のようになる。

年代(era) 男人数(male) 女人数(female) 不明人数(unkown)
10, 5, 1, 2

さて、これをどうやってSQLでおこなうかである。eraでグループ化すれば良さそうだがmaleなどをどう表示すればいいのか。
ポイントとしては集約関数をうまく使うことだ。
以下のようになる。

select 
 era,
 MAX(CASE gender_cd WHEN '0' THEN people ELSE 0 END) AS male ,
 MAX(CASE gender_cd WHEN '1' THEN people ELSE 0 END) AS female,
 MAX(CASE gender_cd WHEN '9' THEN people ELSE 0 END) AS unknown
FROM gender_era_people
GROUP BY era
ORDER BY era;

カラムとして表示する対象以外のものを0としてしまいそれらをMAXで取り出すことにより目的の値を表示することができる。

集約関数は集計対象を単にカラム名だけでなく上記のような形式にできるのだ。

行分割

クロス集計の例では性別毎の人数を横に並べていた（性別で横持ちさせると言うらしい）。
また元のテーブルでは性別をカラムに持っていたので人数のカラムは統合されていた（性別で縦持ちさせると言うらしい）。

横持ち形式のデータだと同じ意味のデータが横に並んでいくことになるのでいささか冗長である。これを縦持ち形式のデータ、つまり行を複数の行に分割したい。

そんな場合はUNION ALLを使えばよい。

具体的には分割したい単位の条件でSQLを作成しそれらをUNION ALLで統合すればよい。分割の単位は横持ちデータのカラム数となる。

クロス集計の例で作成したものをテーブル化してpeople_summaryとしたすると以下のようになる。

男:00,女:01,不明:99とする。 
SELECT era, '00' as gender_cd , male AS people FROM people_summary
UNION ALL
SELECT era, '01' as gender_cd, female AS people FROM people_summary
UNION ALL
SELECT era, '99' as gender_cd, unknown AS people FROM people_summary

各SQLの結果が完全に独立になるようにするのがポイントだ。

集計結果の定数化とその利用

集計した結果と元のデータを組み合わせて新たなデータを出力したいようなケースがある。例えば元のデータが平均値からどれだけ乖離しているかなどのケースである。

このような場合、定数化したい集計結果をWITHで定義してそれを元のデータと直積(Cross JOIN)すればいい。定数化した集計結果は列名として表現することになるのでWITHで定義するViewには定数化したい数分のカラムを定義することになる。

具体的には以下のような形となる。

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
 customer_id, 
 sum_amount,  
 (sum_amount - AVG_AMOUNT) / STD_AMOUNT as normal_amount
FROM sales_amount, stats_amount;

これは前回も登場したS-059の例である。データを標準化する際に平均値と標準偏差が必要であるためWITHで定義している。定数化という意味合いで今回は大文字で定義している。

集計結果に限らずデータを特定の変数として用いたい場合は上記のようにWITHと直積を使えばよい。

WITHで定義したViewは1行のみ値を返すようにするというのがポイントだ。

ランダム抽出

取得元のデータからランダムに一部のデータのみを取得したい。

これは乱数発生関数を条件に用いることで取得できる。oracleの場合のみ多少異なる。

postgresqlの場合: SELECT ... FROM ... WHERE RANDOM() <= 抽出率(0～1)
mysqlの場合/dt>: SELECT ... FROM ... WHERE RAND() <= 抽出率(0～1)
oracleの場合: SELECT ... FROM テーブル名 SAMPLE(抽出率:0.000001以上 100未満で指定)

乱数発生関数は0.0～1.0の範囲の乱数値を発生させる。抽出率は例えば1%の場合はpostgresqlとmysqlの場合は0.01に指定する。oracleの場合は1を指定する。

なお、postgresql,mysqlの場合は副問合せでも利用できるがoracleの場合はテーブルのみが対象となる。実データがないViewも利用はできない。

乱数発生関数は各レコード毎に実行される。そのため乱数発生が一様なのであれば上記の条件式で必要な数のデータが抽出できるはずである。
とはいえ乱数であるため多少ばらつきは発生する。例えば1万件の1%は100件であるが上記ではピッタリ100件とはならない。

層化抽出

抽出元となるデータから特定カテゴリの比率を保ったままデータをランダムに一定数取得することである。単純なランダム取得ではデータの比率は保たれないため一工夫必要となる。

SQL-076：顧客テーブル（customer）から性別（gender_cd）の割合に基づきランダムに10%のデータを層化抽出データし、性別ごとに件数を集計せよ。

を題材に以下に述べる。

カテゴリ毎に抽出

カテゴリ毎にそれぞれ同じ比率でデータをランダムに取得しそれを統合する。それぞれ独立したデータから同じ比率でデータを取り出せばデータの比率は保たれる。

SQL-076の場合、男・女・不明のデータからそれぞれランダムに10%データを取り出せばその比率は保たれるはずである。

WITH 
SAMPLES as (
 SELECT * FROM (SELECT * FROM customer WHERE gender_cd = '0') male WHERE random() <= 0.1
 UNION ALL
 SELECT * FROM (SELECT * FROM customer WHERE gender_cd = '1') female WHERE random() <= 0.1
 UNION ALL
 SELECT * FROM (SELECT * FROM customer WHERE gender_cd = '9') unkown WHERE random() <= 0.1
)
SELECT gender_cd, count(1) FROM SAMPLES GROUP BY gender_cd ORDER BY gender_cd;

ただランダム抽出であるので多少ブレが発生する。また抽出対象となるカテゴリ数が多くなるとその分SELECT文が必要となる。

ランダムソートを利用して抽出する

抽出対象となるデータの並びがランダムとなるように抽出しその結果に対しカテゴリ毎に番号を与える。そこからカテゴリ毎に必要な数のデータを前から取得すればデータの比率を保ったままデータを取得できる。ランダムソートは以下の指定でできる。

postgresqlの場合: ORDER BY RANDOM()
mysqlの場合: ORDER BY RAND()
oracleの場合: ORDER BY dbms_random.random

これを利用した場合SQL-076の結果は以下で求められる。

WITH 
CONST as (SELECT gender_cd, count(1) as cnt FROM customer GROUP BY gender_cd),
customer_r as (SELECT * FROM CUSTOMER ORDER BY RANDOM()),
cusotmer_rownum as (SELECT *, ROW_NUMBER() OVER(PARTITION BY gender_cd) AS rn FROM customer_r)
SELECT r.gender_cd, count(1) FROM cusotmer_rownum r, CONST 
WHERE 
 r.gender_cd = CONST.gender_cd 
 AND r.rn <= CONST.cnt * 0.1 
GROUP BY r.gender_cd
ORDER By gender_cd;

一度データをランダムにソートしているためデータ総量が多いと負荷はかなり高くなる。

だが抽出方法が乱数発生関数ではないため取得件数は常に一定に保たれる。またカテゴリ数が増えても特に対応が不要なのも利点だ。

ちなみに上記の例は実際の回答とは違っている。実際の回答の方ではARRAY_AGGとUNNESTを使用してより効率的にカテゴリ毎のデータ数の集計とランダム取得の結合を行っている。

ただ内容がイマイチ理解できなかったのとpostgresql固有の機能でやっていたので上記の形にしてみた（とはいえ上記でもmysqlとoracleではそのまま使えないが……）。実行計画は上記の例の方が残念ながら悪かった。

内容重複の名寄作業

本来は1件のはずのデータが登録方法の違いなどにより複数登録されていることがある。これを統一するのが名寄せ作業だがこれをSQLのみでおこなう。今回の名寄は一方のデータを残し他方は捨てる。

具体的にはROW_NUMBER()を使用する。同一と見なせるデータをROW_NUMBER()で採番する。このとき残したい方のデータが1番となるようにするのがポイントだ。

SQL-087: 顧客テーブル（customer）では、異なる店舗での申込みなどにより同一顧客が複数登録されている。名前（customer_name）と郵便番号（postal_cd）が同じ顧客は同一顧客とみなし、1顧客1レコードとなるように名寄せした名寄顧客テーブル（customer_u）を作成せよ。ただし、同一顧客に対しては売上金額合計が最も高いものを残すものとし、売上金額合計が同一もしくは売上実績の無い顧客については顧客ID（customer_id）の番号が小さいものを残すこととする。

これは以下のようになる。

CREATE TABLE customer_u AS (
    WITH sales_amount AS(
     SELECT
      c.customer_id, c.customer_name, c.postal_cd, SUM(r.amount) as sum_amount
     FROM
      customer c
     LEFT JOIN receipt r        
     ON c.customer_id = r.customer_id
     GROUP by
      c.customer_id, c.customer_name, c.postal_cd
    ),
    sales_ranking AS(
     SELECT
      *, ROW_NUMBER() OVER(PARTITION BY customer_name, postal_cd ORDER BY sum_amount desc, customer_id ) as rank
     FROM sales_amount
    )
    SELECT c.*
    FROM
     customer c, sales_ranking r
    WHERE
      c.customer_id = r.customer_id
      AND r.rank = 1
);

同一のものとみなす条件であるcustomer_nameとpostal_cdをPARTITION BYで採番対象として指定して売上金額が高くcustomer_idが小さいものが上位となるようにORDER BYで指定している。そしてrankが1となる方を取得することで名寄せができている。

Jの衝動書き日記

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