お客様先からの要件
※下記で使用するデータは全てテストデータです。
「時系列順に格納されているデータから、1社員1列ごと見れる【横持ち勤務時間テーブル】を作成してほしい」
下記2テーブルを使い1社員1列ごとのデータを抽出します。
【社員テーブル】
【縦持ち勤務時間テーブル】
この時、格納するテーブルでは社員番号がprimary key に設定されるため
単純なselect文では、下記エラーが発生します。
duplicate key value violates unique constraint "*******"
社員番号がprimary keyであるため1社員ごとに１列抽出しなければなりません。
下記が抽出イメージです。
上記イメージを抽出するのに3行程で試してみました。
phase1
with temp1 as
(
select
"社員番号"
,"名前"
,"労働時間"
,"日付"
,ROW_NUMBER() OVER (PARTITION BY "社員番号"
ORDER BY "日付" ASC) as "カウント"
FROM "勤務時間テーブル"
)
上記Queryで社員番号ごとに日付を古い順に番号を振っていきます。
上記with句で表示されるテーブルは下記になります。
ROW_NUMBER() OVER (PARTITION BY "社員番号"
ORDER BY "日付" DESC) AS "カウント"
次の行程で縦持ちだったデータを横に持ってきます。
phase2
temp2 as
(
select
"社員番号"
,max(case "カウント" when 1 then "労働時間" else null end )as "10/01"
,max(case "カウント" when 2 then "労働時間" else null end )as "10/02"--横持ちデータへ置換
,max(case "カウント" when 3 then "労働時間" else null end )as "10/03"--横持ちデータへ置換
from temp1
group by "社員番号"
)
簡単に説明しますと、ポイントと記載のある上記Queryでカウント列を作成しました。
そのカウント列が
1の場合の労働時間の列名を10/01
2の場合の労働時間の列名を10/02
3の場合の労働時間の列名を10/03
と列名を付けられ、group byで社員番号ごとに並べられます。
phase3
そして最後の行程で
上記with句で作成したテーブル【temp2】と【社員テーブル】を社員番号をキーに結合し完成です。
Queryをまとめると↓
with temp1 as
(
select
"社員番号"
,"名前"
,"労働時間"
,"日付"
,ROW_NUMBER() OVER (PARTITION BY "社員番号"
ORDER BY "日付" DESC) as "カウント"
FROM "勤務時間テーブル"
),
temp2 as
(
select
"社員番号"
,max(case "カウント" when 1 then "労働時間" else null end )as "10/01"
,max(case "カウント" when 2 then "労働時間" else null end )as "10/02"--横持ちデータへ置換
,max(case "カウント" when 3 then "労働時間" else null end )as "10/03"--横持ちデータへ置換
from temp1
group by "社員番号"
),
select
A."社員番号"
,B."名前"
,"10/01"
,"10/02"
,"10/03"
from temp2 as A
left join "社員テーブル" as b
on A."社員番号" = B."社員番号"
上記Queryで最初にイメージしていたデータ抽出ができました。
最後に
今回は縦持ちのデータを横持ちに変更するSQLについて記載しました。
SQL開発の長い方であればより簡単に、横持ちに変換することが可能かもしれませんが
今回は少し丁寧に、そして確実にできる方法について解説してみました。
私自身もQiita記事にはいつも助けられています。この記事で誰か一人でも疑問解消につながれば幸いです。
お付き合いただきありがとうございました。
株式会社ジールでは、初期費用が不要で運用・保守の手間もかからず、ノーコード・ローコードですぐに手元データを分析可能なオールインワン型データ活用プラットフォーム「ZEUSCloud」を提供しております。
ご興味がある方は是非下記のリンクをご覧ください：
https://www.zdh.co.jp/products-services/cloud-data/zeuscloud/