「部屋にいるのは何人？」オリジナルのSQLパズル第一弾
こんにちは！コグラフデータ事業部のモロズミです。
今回はデータ分析で使われるSQL(構造化問い合わせ言語）の自作のパズルを紹介します。
問題
ABC株式会社の部屋は、タイムカードのデータから、何時何分に誰が入退室したのかがわかります。
次の表は、2023年7月7日の8時30分から9時30分にかけての入退室のデータです。
csv形式のデータはこちらです。
各項目はそれぞれ下の意味になります。
timestamp：入退室の時刻
member：入退室したメンバーの名前
member_id：入退室したメンバーのID
entry_flg：入室を1、退室を0とする標識
8時30分にJohnさんが入室したときは、部屋に誰もいませんでした。
それでは、各メンバーが出入りしたことで、部屋の中に合計何人いるかを、各時刻ごとに表示するSQLはどのようになるでしょうか？
回答
こちらではPostgreSQLをA5:SQL Mk-2で動かす環境での回答を紹介します。
まず、解く準備として、スキーマを作成します。
CREATE SCHEMA puzzle_1;次に、テーブルを作成します。
CREATE TABLE puzzle_1_table
(
timestamp TIMESTAMP,
member VARCHAR,
member_id VARCHAR,
entry_flg INTEGER
)
;続けて、上のcsvファイルを格納したパスを参照してデータをコピーします。
COPY puzzle_1_table FROM '~sql_puzzle_1.csv' ENCODING 'utf8' CSV HEADER DELIMITER E',';'~sql_puzzle_1.csv'のところは格納したファイルのパスを指定してください。
最後に、回答を出力するSQL文は次のようになります。
WITH member_increase_or_decrease AS (
SELECT
*,
CASE
WHEN entry_flg = 0
THEN -1
ELSE 1
END AS change_num
FROM
puzzle_1_table)
SELECT
*,
SUM(change_num) over(order by timestamp) AS member_num
FROM
member_increase_or_decrease;出力結果はこちらです。
回答の発想は、下のような流れで生まれました。
entry_flgの退室を意味する0は、合計人数を出すときに扱いにくいので、CASE句で「entry_flgが1のときは1を返し、0のときは-1を返す」というchange_numという値に変換しておく。
各時刻でのその時点までのchange_numの累積和をSUM(change_num) over(order by timestamp)で求める。
change_numを求める処理と累積和を求める処理をまとめると難しいのでWITH句で二つに分ける。
累積和を求めることは、事業の売上集計などでも使われる処理になります。
今回はご紹介しませんでしたが、各メンバーIDをカウントしたとき、入室と退室が同じ回数（つまり部屋にいないとき）は偶数になり、入室が退室よりも多いとき（つまり部屋にいるとき）は奇数になることを利用して、奇数のカウント数となるユニークIDの数を数えるなどの別解も考えられそうです。
さらに、今回の問題では最初に部屋に誰もいない条件でしたが、最初に誰かがいる状態から集計するなどのより一般的な状況でのSQL文を考えることで応用力も磨けます。
他にも解き方はいろいろ考えられると思いますので、ぜひいろいろと試して楽しんで解いていただけると嬉しいです。
Twitterもやってます！
コグラフデータ事業部ではTwitterでも情報を発信しています。
データ分析に興味がある、データアナリストになりたい人など、ぜひフォローお願いします！
📢Wantedly新掲載！
— アラリコ@コグラフ株式会社 | データ事業部 (@CographData) July 14, 2023
「データに興味がある」
「データに携わる仕事がしたい」
そこのあなた！
私たちと一緒にデータ分析しませんか？
IT業界未経験の方も大歓迎です☺️#エンジニア転職 #データ分析 #駆け出しエンジニアと繋がりたいhttps://t.co/S9o7VSjGRt