【PostgreSQL】新入社員向け研修問題のご紹介
はじめに
こんにちは。コグラフのデータアナリスト事業部です。
今回弊社の研修内容を記事にしようと思った理由は、
最近、
「コグラフの研修ってどんなことやってるの?」
って色々な人から聞かれているなぁ、と思ったからです。
実は弊社のSQL研修は、実務で扱うようなデータ(ダミーデータ)を用いて、実際の業務で行うような集計をする、といったかなり実践に近い内容になっているんです!
これって弊社のアピールポイントになるのでは??と思ったのですが、
それを日常会話の中で伝えるのは非常に難しい・・・
ということで、今回は本記事を見てくださっている皆様に、
コグラフの研修を体験していただきたい!
と思っています。
今回ご紹介する研修問題について
今回ご紹介する研修問題は・・・
「電子書籍本アプリ購入ログ集計問題」です!
こちらは、実際にあった電子書籍本アプリのデータマート作成業務を基に、弊社が作成した問題です。
レベル感としては、以下の操作ができれば問題なく解くことができると思います。
環境
今回の環境は以下の通りです。
準備
データのダウンロード
使用するダミーデータは、こちらから無料でダウンロード可能です!
ぜひ一緒にチャレンジしてみましょう!
テーブルの作成
まずはデータを入れるテーブルを作成します。
--スキーマ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マスター
以下データに関する説明です。
②TABLE: book_buy_log 本の購入ログデータ
以下データに関する説明です。
ここまでで問題が無ければ、次からいよいよ集計問題となります!
問題
Q: 媒体別(baitai_id)の総購入金額を集計してください。
集計条件は以下の通りです。
また、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;
上記を実行すると、こんな感じのデータが抽出できます。
ここでは、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;
上記を実行すると、以下のようになります。
このときに注意しないといけないのは、日時の取り扱いです。
例えば、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;
解答
お疲れ様です!こちらが解答です。
最後に
いかがでしたでしょうか。
コグラフではこうした実践形式の研修問題を数多く作成しており、日々改良しながら研修カリキュラムを組んでいます。実際の研修では、研修担当が研修者の理解度に合わせて研修を進めていきます。
そのため、業務未経験から入社した方でも、SQLやpythonのスキルを身に着けて案件に入ることができています!
また、研修以外にも、社員自由参加のもくもく会を定期開催しており、雑談や相談をしたり、成長意欲の高いメンバー達と一緒に勉強したり、といったことができる環境があります。
さらに、弊社のTwitterアカウントでは、何気ない社内の様子からデータ分析関係のことまで、定期的に幅広い情報を発信しているので、もしご興味を持ってくださった方がいましたら、ぜひ覗いてみてください!
それでは長くなりましたが、ここまで読んでくださった皆様、本当にありがとうございました!
この記事が気に入ったらサポートをしてみませんか?