「何日から出社できる？」オリジナルのSQLパズル第二弾

こんにちは、コグラフ株式会社データアナリティクス事業部のモロズミです。

今回はオリジナルSQLパズル第二弾を紹介します。

問題

A株式会社では最近感染症が流行しています。

そこで、感染拡大を防止するために、一度感染の疑われた人が体調を回復した後に会社に出社しても良いかどうかの基準を作成しました。

その出社基準は次の二つの条件を両方とも満たすことです。

• 感染が確認された日から10日を経過したこと

• 前日の朝昼夜の測定体温の平均が普段の平熱のプラスマイナス0.5度の範囲内に収まること

（例えば3日に感染が確認された場合、10日が経過したとみなすのは14日以降になるので、13日の朝昼夜の測定体温の平均が平熱のプラスマイナス5度の範囲内に収まっていれば、その社員は14日に出社できるとします）

A株式会社の社員3名の7月1日から7月25日までの営業日と各社員の体温の測定結果と感染が確認された日付を記載したデータはこちらです。

health_log.csv

647 Bytes

ファイルダウンロードについて

ダウンロード

a_temp.csv

23 Bytes

ファイルダウンロードについて

ダウンロード

helath_log.csvのイメージ

a_tempのデータイメージ

helath_log.csvの各カラムの意味は以下になります。

• name：人の名前（出社、感染、体温測定の主体）

• day：7月の日にち（8月1日は32とする）

• bussiness_flg：1が営業日、0が営業日以外の日

• infection_flg：1が感染した日（初日）、0がそれ以外の日

• m_temp：その日の朝に測定した体温

• d_temp：その日の昼に測定した体温

• n_temp：その日の夜に測定した体温

a_temp.csvの各カラムの意味は以下になります。

• name：人の名前（平均体温の該当者）

• a_temp：普段の平均体温

これらのデータと規則をもとにして、各社員が感染してから最初に出社できる日を出力してください。

ただし、出社できる日が営業日でない日にあたるときは、そこから最短の営業日を出社日として回答してください。

回答

回答をコードを用いて説明していきます。

まず、スキーマを作成します。

CREATE SCHEMA infection;

次に、テーブルを作成します。

CREATE TABLE infection.health_log
(
name VARCHAR,
day INTEGER,
bussiness_flg INTEGER,
infection_flg INTEGER,
m_temp NUMERIC,
d_temp NUMERIC,
n_temp NUMERIC
)
;

CREATE TABLE infection.a_temp
(
name VARCHAR,
a_temp NUMERIC
)
;

helath_log.csvとa_temp.csvを用意したテーブルにコピーします。

それぞれのデータを格納したパス名を適宜反映させてください。

COPY infection.health_log FROM 'pass_name(health_log.csv)' encoding 'UTF8' csv header delimiter e',';

COPY infection.a_temp FROM 'pass_name(a_temp.csv)' encoding 'UTF8' csv header delimiter e',';

抽出は次のコードできます。

WITH onset AS (
SELECT
  name,
  day AS onset_day
FROM
  infection.health_log
WHERE
  infection_flg = 1),
ranking AS(
SELECT
  infection.health_log.day,
  RANK() OVER(ORDER BY day ASC) AS rank
FROM
  infection.health_log
  LEFT OUTER JOIN
    infection.a_temp AS average_temp
      ON infection.health_log.name = average_temp.name
  LEFT OUTER JOIN
    onset
      ON infection.health_log.name = onset.name
WHERE
  infection.health_log.day >= onset.onset_day + 11
  AND infection.health_log.bussiness_flg = 1
  AND ABS(average_temp.a_temp-(m_temp + d_temp + n_temp)/3) <= 0.5)
SELECT
  day
FROM
  ranking
WHERE
  rank = 1

一つ目のWITH句「onset」で感染した日付を抽出します。

二つ目のWITH句「ranking」で感染した日付(onset_day)と平均体温(a_temp)の値をhelath_log.csvに左外部結合で連結させたうえで、問題文にある体温の条件、感染日から10日経過の条件、営業日のみ出社日の候補とする条件をWHERE句でまとめて抽出し、日付の昇順になるように上から順に順位を付けます。ここで、問題文の条件を順次検討するのでなく、一つのWHERE句で一気に絞り切ってしまうことがポイントです。

最後に、二つ目のWITH句で設定した順位が1位の日付を抽出すれば、それが求めたい出社日の日付になります。

最終出力結果はこちらになります。

答えは24日でした

Twitterもやってます！

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

📢Wantedly新掲載！

「データに興味がある」
「データに携わる仕事がしたい」
そこのあなた！
私たちと一緒にデータ分析しませんか？
IT業界未経験の方も大歓迎です☺️#エンジニア転職 #データ分析 #駆け出しエンジニアと繋がりたいhttps://t.co/S9o7VSjGRt

— アラリコ@コグラフ株式会社 | データ事業部 (@CographData) July 14, 2023

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