【横展開⇔縦展開】データ分析は便利に効率良く！SQLでのデータ加工のコツをわかりやすくご紹介します♪【プログラミング】

まず初めに

みなさん、こんにちは。はねすけと申します。
今回は前回に引き続きSQLに関する記事を掲載させていただきます。
テーマはずばり「SQLにおける横展開⇔縦展開」です。
データ分析の際に役立つ技術の１つですね。

SQLはデータベース操作に用いる言語であることは前回にご説明したとおりです。

データの分析方法が毎回様々であるのと同様に、それに合わせてデータの取得方法も目的に合うように毎回変化させる必要があります。
テーブルの中身を単純にそのまま毎回使うだけであれば良いのですが、なかなか現実はそう単純ではありません。

そこでよく登場するのがSQLによるRDSデータの縦展開・横展開なのです。
ベテランの方には今更かもしれませんが、初心者の方も決して知っておいて損はないテクニックですので、ぜひ習得してみてください！

データ分析について

データ分析とは

まずは今回のテーマの前提として、データ分析とは何なのかというところからひも解いていければと思います。
「データ分析」という単語に限って言えば、そのまま「データを分析すること」を意味するのですが、重要なのはその「データ」が何を指すかですね。

データ分析という際に対象となるデータには実に様々な形式のものがあります。
今回のテーマである横展開・縦展開の活用対象であるRDB（リレーショナルデータベース）上のデータや、NoSQL形式のデータ・そもそも形式ばっていなくて無数・大量に存在しているビッグデータなど多様なものがあります。
※この場で詳しい言及は避けますが、以下にそれぞれの定義リンクを貼っておきますので、詳しく知りたい方は確認してみてください。

参考

関係データベース

参考

ビッグデータ

それら多様な形式のデータをひも解いて、目的に即した情報を見つけ出す作業、そのことを「データ分析」と言います。

個人的な経験をお話しさせていただきますと、ログ情報からどのようなサービスが利用されているのか、どの時間帯にどういった規模でサービスが使用されているのかを分析したりといった作業が実務では発生しましたね。

データ分析の重要性

次にデータ分析の重要性について確認していきましょう。
データ分析は21世紀のビジネスにおいて欠かせないツールとなっています。企業や組織は日々蓄積される膨大なデータから価値を引き出し、戦略的な意思決定を行う必要があります。
データ分析を適切に活用することは、競争力を維持し、持続可能な成功を収めるための重要な手段なのです。

データクレンジングとその重要性

最後にデータクレンジングについてご紹介したいと思います。
データクレンジングとは、目的の情報を取得するためにデータを整形することを指します。クレンジングという通り、データをきれいに整理してわかりやすい形に変えるわけですね。
データの品質が低いまま分析を進めると、正確な結果を得ることが難しくなりますので、データ分析において非常に重要な工程であると言えます。

では具体的にはどのようなことをするのでしょうか。大きく分けると以下の３つになるはずです。

• データのトランスフォーメーション
• 欠損データの補正
• 異常値の検出・対処

まず１つ目は「データのトランスフォーメーション」です。こちらはそのままデータの形を変更することを意味します。
データが知りたい情報を取得するのに適した状態であることはまれなことで、大抵は目的に合わせて形を調整する必要があります。今回のテーマであるRDBの横展開・縦展開もこのデータのトランスフォーメーションにあたる作業です。

次に「欠損データの補正」ですね。
データセットには欠損値が含まれることがよくあります。欠損値をそのままにしておくと、分析の信頼性が損なわれます。
有益な分析データをつくりあげるためにも、欠損データをどう扱うかというのも重要な作業になります。
あくまで補正であり、都合のいいように変更・捏造するのとはわけが違うので気を付けたいところですね(笑)

最後に「異常値の検出・対処」です。
こちらも先の欠損データの補正と似ているのですが、分析データの信頼性に関するものです。より説得力・信頼のあるデータ分析を行うためにどのようにして異常なデータを見つけ出すのか、またどのように処置をするかという内容の作業になります。

今回の記事では「欠損データの補正」「異常値の検出・対処」については具体的には触れませんが、また別の記事で詳しく見ていければと思います。

横展開について

条件整理

一通りデータ分析の概要について再確認してきました。ここからはいよいよ本題で、データ分析のデータクレンジング作業の１つであるRDBの横展開・縦展開について詳しくみていきましょう。
前提の確認ですが、RDBにおけるデータクレンジング手法とお伝えしたように、横展開・縦展開ともに行と列からなるデータについてSQLを用いて変形していく作業です。

まずは横展開からお伝えしていきます。
今回解説のために以下のようなデータを使用したいと思います。

主キーであるID項目と区分、そして日付と金額だけのシンプルなテーブル構造ですね。

このようなデータを例えば「日付ごとに区分別の金額をまとめて出したい」となった場合どのようにすればよいでしょうか？完成表のイメージとしては以下のようになります。

思考順序を整理しましょう

区分項目のように、「もとは同一の列の内容であるものを、基準ごとに１つの行の内容として置き換えること」を横展開と言います。

SQLの記載の考え方として以下の手順になります。

①②の手順をSQLに起こしたのが以下の内容です。

SELECT 日付
     , SUM(CASE WHEN 区分 = 'A' THEN 金額 ELSE 0 END) as A
     , SUM(CASE WHEN 区分 = 'B' THEN 金額 ELSE 0 END) as B
     , SUM(CASE WHEN 区分 = 'C' THEN 金額 ELSE 0 END) as C
FROM テーブルA
GROUP BY 日付

CASE文を用いることで必要な項目だけを合算し、そうでない場合は0として計算しないようにするわけですね。
これによりA・B・Cそれぞれの金額が日付ごとに集計され、１発できれいな表として出力することができそうです！
これが横展開の基本的な流れになります。

実際に試してみましょう

理屈を確認したところで、実際にデータに対してSQLを実行してみましょう。
実際に実行するにあたり、SQL ServerをGUIベースで簡単に操作するためにSQL Server Management Studio(SSMS)というソフトウェアを使用していきます。

SQL Serverを使用している方であれば基本的に使用したことがあると思いますが、まだ使ってみたことがない方はぜひダウンロード・インストールして試してみることをお勧めします。
非常に使いやすく簡単にデータベース操作ができるため、面白い体験ができることでしょう。

以下の記事において、簡単ではありますがSSMSのダウンロード・インストールについて触れていますので参考にしてください。

2023年11月27日【python】DjangoでSQLServerへ接続する手順を図解で初心者にもわかりやすく解説【ライブラリ】

無事に実際に試してみても問題ないことが確認されました！

縦展開について

条件整理

次に縦展開です。これはつまりは横展開の時とは逆のことを意味します。

このようなデータを、

のように変更して出力します。

意外とシンプル縦展開

つまり、「１つの行の別々の列の内容を、１つの列の項目であるように置き換えること」を縦展開と言います。

縦展開は横展開よりもずっとシンプルに表現できます。
まとめたい項目ごとに別々にSELECTし、それらをUNION ALLで結合するだけです。

例えばテーブルBの行１を縦展開してみましょう。

//日付が2020/04/01の明細を区分ごとに縦展開)
SELECT 日付
             , 'A' as 区分
             , 区分Aの金額
FROM テーブルB
WHERE 日付 = '2020/04/01'

UNION ALL

SELECT 日付
             , 'B' as 区分
             , 区分Bの金額
FROM テーブルB
WHERE 日付 = '2020/04/01'

//以下同様

上記のSQL文では日付が「2020/04/01」の明細分について縦展開を行っています。
別の日付についても同様のことを繰り返していけば、全体的な縦展開も行えますね。

実際に試してみましょう

では縦展開についても実際に手を動かして確認していきます。

SELECT date
     , 'A' as 区分
     , price_A as price
FROM sample_table2
WHERE date = '2020/04/01'

UNION ALL

SELECT date
     , 'B' as 区分
     , price_B as price
FROM sample_table2
WHERE date = '2020/04/01'

UNION ALL

SELECT date
     , 'C' as 区分
     , price_C as price
FROM sample_table2
WHERE date = '2020/04/01'

UNION ALL

SELECT date
     , 'A' as 区分
     , price_A as price
FROM sample_table2
WHERE date = '2020/05/01'

UNION ALL

SELECT date
     , 'B' as 区分
     , price_B as price
FROM sample_table2
WHERE date = '2020/05/01'

UNION ALL

SELECT date
     , 'C' as 区分
     , price_C as price
FROM sample_table2
WHERE date = '2020/05/01'

UNION ALL

SELECT date
     , 'A' as 区分
     , price_A as price
FROM sample_table2
WHERE date = '2020/06/01'

UNION ALL

SELECT date
     , 'B' as 区分
     , price_B as price
FROM sample_table2
WHERE date = '2020/06/01'

UNION ALL

SELECT date
     , 'C' as 区分
     , price_C as price
FROM sample_table2
WHERE date = '2020/06/01'

ここまでで横展開・縦展開についてその手法をご紹介してきました。
縦展開⇔横展開が使いこなせるようになると、やりたいことの実現度・作業効率は間違いなく向上します。

一点ご注意いただきたいのですが、SQLで同様のことを実現する手法はほかにもあるということです。
色んな書き方で工夫して表現できる点はSQLもプログラミングでも同じですね。ぜひ自分なりの効率の良い表現方法を見つけ出してみてください。

まとめ

いかがだったでしょうか？

本日お伝えしたかった内容を簡単にまとめると以下のようになります。

ぜひ何らかの形でお役に立てれば幸いです。

それでは次の記事でまたお会いしましょう♪

関連記事

2023年11月30日【UPDATE+SELECT】組み合わせてスッキリと！便利で実用的なSQL文の書き方を図解で丁寧にご紹介します♪【プログラミング】