SQLのPIVOT句を使ったクロス集計をマスター: 行データを列データに変換するテクニック

IT技術情報

SQLのPIVOT句は、データベース内の行データを列データに変換する際に欠かせないツールです。特に、レポート作成やデータの集計作業において、その価値は計り知れません。PIVOT句を使用する基本的なプロセスは、特定の列の値を行から列に変換し、その過程で集計関数を用いてデータを再構成することにあります。

PIVOTを使った便利な実例

月別販売集計

PIVOTを使用して月別の売上を集計します。MONTH(SaleDate)で月を抽出し、SUM(Amount)で月別売上を算出。結果は1月から12月までの売上が列として表示され、月ごとの売上トレンド分析に役立ちます。

Sales (販売データ)

SaleDateAmountCategoryIDEmployeeIDRegionIDQuantity
2023-01-102001112
2023-02-151502221
2023-03-203003133

実行結果:

SaleMonth12312
合計200150300

商品カテゴリ別売上高

商品カテゴリ別に売上高を集計する例です。JOINでCategoriesテーブルを結合し、PIVOTでCategoryNameごとの売上をSUM(Amount)で算出します。このSQLにより、各カテゴリの売上高が列に展開され、売れ筋カテゴリを把握できます。

Categories (商品カテゴリ)

CategoryIDCategoryName
1Electronics
2Clothing
3Furniture

実行結果:

CategoryNameElectronicsClothingFurniture
合計200150300

年度別・四半期別売上集計

年度ごと、四半期ごとの売上を集計する例。YEAR(SaleDate)とCEILING(MONTH(SaleDate) / 3.0)で年度と四半期を算出し、PIVOTで四半期ごとの売上をSUM(Amount)で集計します。この方法で、四半期のパフォーマンスを年度別に分析できます。

実行結果:

SaleYearQ1Q2Q3Q4
202325000300003500040000

従業員別売上目標達成率

従業員ごとの売上目標と実際の売上を比較し、達成率を計算する例。Employeesテーブルから目標を取り、Salesテーブルから実売上を集計し、PIVOTでTargetとActualを列として表示します。目標達成度を確認し、従業員のパフォーマンス評価に活用できます。

Employees (従業員)

EmployeeIDEmployeeNameSalesTarget
1John Doe5000
2Jane Smith6000

実行結果:

EmployeeNameTargetActual
John Doe5000045000
Jane Smith6000065000

地域別販売数の集計

Regionsテーブルを結合して地域別の販売数を集計する例。PIVOTでRegionNameごとにSUM(Quantity)で販売数を集計し、各地域の販売実績を列に展開します。地域別の市場パフォーマンスを把握し、地域ごとの販売戦略を立てるのに役立ちます。

Regions (地域)

RegionIDRegionName
1North
2South
3East
4West

実行結果:

RegionNameNorthSouthEastWest
合計120015001000800

基本テーブル構造

  • Sales (販売データ)
    • SaleDate: 販売日
    • Amount: 販売額
    • CategoryID: 商品カテゴリID
    • EmployeeID: 従業員ID
    • RegionID: 地域ID
    • Quantity: 数量
  • Categories (商品カテゴリ)
    • CategoryID: カテゴリID
    • CategoryName: カテゴリ名
  • Employees (従業員)
    • EmployeeID: 従業員ID
    • EmployeeName: 従業員名
    • SalesTarget: 販売目標
  • Regions (地域)
    • RegionID: 地域ID
    • RegionName: 地域名

おすすめ情報

ある程度SQLに慣れてくるとより複雑なSQLを組むようになり、分析関数(ウインドウ関数)の壁にぶち当たります。本書は私が初級から抜け出すのになったきっかけの本です。比較的新しい関数なので古いSQLの本には載ってません。昔ながらのSQLを書く人はウインドウ関数の理解に苦しむケースが多いと思います。一歩先に進むためにも苦手意識を克服したいですね。

著:ミック
¥2,750 (2024/05/24 18:04時点 | Amazon調べ)

コメント

タイトルとURLをコピーしました