見出し画像
Photo by 4540_73

【PostgreSQL】新入社員向け研修問題のご紹介

コグラフ株式会社 データアナリティクス事業部

はじめに

こんにちは。コグラフのデータアナリスト事業部です。

今回弊社の研修内容を記事にしようと思った理由は、

最近、
「コグラフの研修ってどんなことやってるの？」
って色々な人から聞かれているなぁ、と思ったからです。

実は弊社のSQL研修は、実務で扱うようなデータ（ダミーデータ）を用いて、実際の業務で行うような集計をする、といったかなり実践に近い内容になっているんです！

これって弊社のアピールポイントになるのでは？？と思ったのですが、
それを日常会話の中で伝えるのは非常に難しい・・・

ということで、今回は本記事を見てくださっている皆様に、
コグラフの研修を体験していただきたい！
と思っています。

本記事でお伝えしたいこと
・研修で一体どんなことを学習しているのか
・研修のレベル感はどれくらいなのか

今回ご紹介する研修問題について

今回ご紹介する研修問題は・・・

「電子書籍本アプリ購入ログ集計問題」です！
こちらは、実際にあった電子書籍本アプリのデータマート作成業務を基に、弊社が作成した問題です。

レベル感としては、以下の操作ができれば問題なく解くことができると思います。

・テーブル作成(またはWITH句)が使用できる
・テーブルの結合ができる
・ウィンドウ関数を使える
・欠損値(NULL)の補完ができる

環境

今回の環境は以下の通りです。

・PostgreSQL　10.13
・A5:SQL Mk-2　2.15.0

準備

データのダウンロード

使用するダミーデータは、こちらから無料でダウンロード可能です！
ぜひ一緒にチャレンジしてみましょう！

テーブルの作成
まずはデータを入れるテーブルを作成します。

--スキーマbook_appを作成
CREATE SCHEMA book_app; 

--テーブルbaitai_master作成
CREATE TABLE book_app.baitai_master( 
 baitai_id VARCHAR, 
 email_address VARCHAR, 
 first_access_time TIMESTAMP
); 

--テーブルbook_buy_log作成
CREATE TABLE book_app.book_buy_log( 
 buy_timestamp TIMESTAMP, 
 login_email_address VARCHAR, 
 book_name VARCHAR, 
 price_yen INT, 
 baitai_id VARCHAR
);

そして、ダウンロードしたtsvファイルのデータをテーブルに入れます。

--テーブルbaitai_masterにファイルbaitai_master.tsvのインポート
COPY book_app.baitai_master 
FROM
 'C:\Program Files\Pg\Import\book_app\baitai_master.tsv' --要PASS変更
 ENCODING 'utf8' CSV HEADER DELIMITER e '\t'; 
 
--テーブルbook_buy_logにファイルbook_buy_log.tsvのインポート
COPY book_app.book_buy_log 
FROM
 'C:\Program Files\Pg\Import\book_app\book_buy_log.tsv' --要PASS変更
 ENCODING 'utf8' CSV HEADER DELIMITER e '\t';

最後に、問題なくデータが取り込みできているか必ず確認しましょう！

--作成したデータの内容確認
SELECT
 * 
FROM
 book_app.baitai_master; --全8件

SELECT
 * 
FROM
 book_app.book_buy_log; --全21件

正常に取り込みができていれば、以下のようなテーブルが作成できているはずです。

①TABLE: baitai_master　サンプルユーザーの媒体IDマスター

画像6

baitai_id: サンプルユーザーの媒体機種ごとのID
email_address: ユーザーのemailアドレス
first_access_time: 初回アプリのインストール日と時間

以下データに関する説明です。

例）email_address: email_A＠email1.com の場合
baitai_id：A_1 は、媒体機種のA_1で、
first_access_timeが 2091-01-01 01:01:00 となっていますが、
これは2091-01-01 01:01:00に、アプリをインストールしたことを表しています。
また2行目の、baitai_id：A_2 は、媒体機種のA_2で、
first_access_timeが 2092-02-02 02:02:00 となっていますが、
これは2092-02-02 02:02:00に、アプリをインストールしたことを表しています。
つまり、機種変更後の新しい媒体機種で、アプリを再インストールしていると思われます。
baitai_idが B の場合はデータが1件のみなので、機種変更をしていないことを意味しています。

②TABLE: book_buy_log　本の購入ログデータ

画像6

buy_timestamp: 購入日時
login_email_address: ログインした時のemailアドレス
book_name: 購入本の名前
price_yen: 価格(円)
baitai_id: 媒体ID

以下データに関する説明です。

例）email_address: email_A＠email1.comの場合
buy_timestampが2091-01-01 01:03:00 となっていますが、
これは2091-01-01 01:03:00に、email_A＠email1.com のemailアドレスでログインしており、 omoroi_hon_1 を 3,000 円で購入したことを表しています。emailアドレスでログインしているので、baitai_id は NULL となっています。
　また、baitai_id：D は、buy_timestampが 2092-02-02 02:02:02 で、login_email_addressがNULLとなっていますが、
これは2092-02-02 02:02:02に、 tumaranai_hon_1 を 9,999 円で購入したことを表しています。emailでログインしていないので、 login_email_addressはNULL となっています。

ここまでで問題が無ければ、次からいよいよ集計問題となります！

問題

Q: 媒体別(baitai_id)の総購入金額を集計してください。

集計条件は以下の通りです。

※1つのemailに対して複数のbaitai_idが存在している場合は、次のbaitai_idの初回アクセス日までを集計単位とします。
例)email_A＠email1.com の場合
媒体ID：A_1は、 2091-01-01 01:01:00～2092-02-02 02:01:59 までとします。
媒体ID：A_2は、 2092-02-02 02:02:00～2093-03-03 03:02:59 までとします。
媒体ID：A_3は、 2093-03-03 03:03:00～2099-12-31 23:59:59 までとします。
(A_3は、適当にアクセス日の末日を 2099-12-31 23:59:59 としました。)
↓
login_email_address が email_A＠email1.com かつ
buy_timestampが 2091-01-01 01:03:00 のとき、baitai_idはNULLとなっていますが、実際はA_1 で購入したものとして集計します。

また、OUTPUT形式は以下の通りにしてください。

出力例

それでは実際に解いてみてください。

自力で解けた方は、この後の解説・解答を確認してみてください！

解説

ここからは、実際に問題を解きながら解説をしていこうと思います。
もし自力で解けなかった人も、最後まで読んでいただき、参考にしていただけると嬉しいです。

手順1: baitai_masterテーブルに、first_access_timeを1行下にずらしたデータの列(first_access_time_end)を追加

/* LEAD関数でTIMESTAMPの終点時刻を追加 */  
 SELECT
   baitai_id, 
   email_address, 
   first_access_time, 
   COALESCE ( 
     LEAD (first_access_time) OVER ( 
       PARTITION BY
         email_address 
       ORDER BY 
         baitai_id, 
         first_access_time 
     ) , 
     '2099/12/31 23:59:59'
   ) AS first_access_time_end 
 FROM
   book_app.baitai_master 
 ORDER BY
   baitai_id;

上記を実行すると、こんな感じのデータが抽出できます。

本アプリ購入集計_1

ここでは、LEAD関数を用いてfirst_access_time_endという列を追加しました。email_addressが同じで、baitai_idが複数あるデータに対し、次のbaitai_idでアクセスするまでの日時列を追加することができます。その結果「同じbaitai_idを使用していた期間」がそれぞれわかるようになります。

そして、最後に使用したbaitai_idや、元々baitai_idが1つしかないデータは、first_access_time_endがNULLになってしまうため、COALESCE関数で未来の日付になるようにしました。


手順2、手順1で作成したデータと、book_buy_logテーブルを結合する

手順1で作成したデータを中間テーブルにしてもいいのですが、今回はWITH句に入れて、book_buy_logテーブルと内部結合します。

WITH master_add_endtime AS ( 
 SELECT
   baitai_id, 
   email_address, 
   first_access_time, 
   COALESCE ( 
     LEAD (first_access_time) OVER ( 
       PARTITION BY
         email_address 
       ORDER BY
         baitai_id, 
         first_access_time
     ) , 
     '2099/12/31 23:59:59'
   ) AS first_access_time_end 
 FROM
   book_app.baitai_master 
) 
/* 上のマスタと購入ログを結合 */
 SELECT
   t1.baitai_id, 
   t1.email_address, 
   t1.first_access_time, 
   t1.first_access_time_end, 
   t2.buy_timestamp, 
   t2.book_name, 
   t2.price_yen 
 FROM
   master_add_endtime AS t1 
   INNER JOIN book_app.book_buy_log AS t2 
     ON (t1.email_address = t2.login_email_address) AND --email_addressが一致
     (t2.buy_timestamp >= t1.first_access_time) AND 
     (t2.buy_timestamp < t1.first_access_time_end) 
     --最初のアクセス日時 <= 購入日時 < 次の媒体でのアクセス日時
 ORDER BY
   baitai_id;

上記を実行すると、以下のようになります。

本アプリ購入集計_2

このときに注意しないといけないのは、日時の取り扱いです。

例えば、baitai_id：A_1のfirst_time_end(2092/02/02 2:02:00)と、A_2のfirst_access_time(2092/02/02 2:02:00)が重複しているので、もしBETWEENを使った結合をしてしまうと、その時刻に購入ログが存在した場合にデータが重複してしまいます(A_1でも、A_2でも購入したことになってしまう)。
そのため今回は、不等号を使って後ろの日時を含まないように処理する必要があります。
実際の業務においても、特に時間を取り扱う場合には、細心の注意を払う必要があります。


手順3、emailがあるデータと、emailが無いデータをUNION ALLで結合する

ここまで来れば、あとはbaitai_idごとに購入金額を集計するだけ・・・
と言いたいところなのですが、問題があります。

先程までのテーブル結合キーにはemail_addressを使ってきました。そのため、email_addressが存在しないbaitai_id（DとE）が抜けてしまっているのです！

そこで、baitai_idごとの購入金額を集計した後、UNION ALLを使ってデータを結合して完了です！

/* LEAD関数でTIMESTAMPの終点時刻を追加 */
WITH master_add_endtime AS ( 
 SELECT
   baitai_id, 
   email_address, 
   first_access_time, 
   COALESCE ( 
     LEAD (first_access_time) OVER ( 
       PARTITION BY
         email_address 
       ORDER BY
         baitai_id, 
         first_access_time
     ) , 
     '2099/12/31 23:59:59'
   ) AS first_access_time_end 
 FROM
   book_app.baitai_master 
) ,
/* 上で作成したマスタと購入ログを結合 */
join_master_log AS ( 
 SELECT
   t1.baitai_id, 
   t1.email_address, 
   t1.first_access_time, 
   t1.first_access_time_end, 
   t2.buy_timestamp, 
   t2.book_name, 
   t2.price_yen 
 FROM
   master_add_endtime AS t1 
   INNER JOIN book_app.book_buy_log AS t2 
     ON (t1.email_address = t2.login_email_address) AND
     (t2.buy_timestamp >= t1.first_access_time) AND
     (t2.buy_timestamp < t1.first_access_time_end)
) 
/* baitai_idごとの購入金額を集計(mail登録がないidのデータをUNION ALLで結合) */
SELECT
 baitai_id, 
 SUM (price_yen)       AS book_sumprice_yen 
FROM
 join_master_log 
GROUP BY
 baitai_id 
UNION ALL 
SELECT
 baitai_id, 
 SUM (price_yen)       AS book_sumprice_yen 
FROM
 book_app.book_buy_log 
WHERE
 baitai_id IS NOT NULL 
GROUP BY
 baitai_id 
ORDER BY　　　--実業務では、ORDER BYは処理が重たいので、最終出力の部分のみで使用します。
 baitai_id;

解答

お疲れ様です！こちらが解答です。

画像6

最後に

いかがでしたでしょうか。

コグラフではこうした実践形式の研修問題を数多く作成しており、日々改良しながら研修カリキュラムを組んでいます。実際の研修では、研修担当が研修者の理解度に合わせて研修を進めていきます。
そのため、業務未経験から入社した方でも、SQLやpythonのスキルを身に着けて案件に入ることができています！

また、研修以外にも、社員自由参加のもくもく会を定期開催しており、雑談や相談をしたり、成長意欲の高いメンバー達と一緒に勉強したり、といったことができる環境があります。

さらに、弊社のTwitterアカウントでは、何気ない社内の様子からデータ分析関係のことまで、定期的に幅広い情報を発信しているので、もしご興味を持ってくださった方がいましたら、ぜひ覗いてみてください！

それでは長くなりましたが、ここまで読んでくださった皆様、本当にありがとうございました！







この記事が気に入ったらサポートをしてみませんか？