見出し画像

「部屋にいるのは何人？」オリジナルのSQLパズル第一弾

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

こんにちは！コグラフデータ事業部のモロズミです。

今回はデータ分析で使われるSQL(構造化問い合わせ言語）の自作のパズルを紹介します。

問題

ABC株式会社の部屋は、タイムカードのデータから、何時何分に誰が入退室したのかがわかります。

次の表は、2023年7月7日の8時30分から9時30分にかけての入退室のデータです。

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;

出力結果はこちらです。

出力結果

回答の発想は、下のような流れで生まれました。

  1. entry_flgの退室を意味する0は、合計人数を出すときに扱いにくいので、CASE句で「entry_flgが1のときは1を返し、0のときは-1を返す」というchange_numという値に変換しておく。

  2. 各時刻でのその時点までのchange_numの累積和をSUM(change_num) over(order by timestamp)で求める。

  3. change_numを求める処理と累積和を求める処理をまとめると難しいのでWITH句で二つに分ける。

累積和を求めることは、事業の売上集計などでも使われる処理になります。

今回はご紹介しませんでしたが、各メンバーIDをカウントしたとき、入室と退室が同じ回数（つまり部屋にいないとき）は偶数になり、入室が退室よりも多いとき（つまり部屋にいるとき）は奇数になることを利用して、奇数のカウント数となるユニークIDの数を数えるなどの別解も考えられそうです。

さらに、今回の問題では最初に部屋に誰もいない条件でしたが、最初に誰かがいる状態から集計するなどのより一般的な状況でのSQL文を考えることで応用力も磨けます。

他にも解き方はいろいろ考えられると思いますので、ぜひいろいろと試して楽しんで解いていただけると嬉しいです。


Twitterもやってます！

コグラフデータ事業部ではTwitterでも情報を発信しています。
データ分析に興味がある、データアナリストになりたい人など、ぜひフォローお願いします！

#データ分析入門 #私の仕事 #データアナリスト #データアナリスト募集 #コグラフデータ

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