SQL Server で文字列データの PIVOT を活用：データ分析を高度化する

この解説では、SQL Server の PIVOT 機能を使って文字列データの集計・分析を高度化する手法について、分かりやすく解説します。具体的には、以下の内容を網羅します。

• 文字列データの PIVOT 操作：具体的な例と解説
• 集計関数と PIVOT の組み合わせ：高度な分析の実現
• 動的 PIVOT：柔軟なデータ分析
• PIVOT の制限事項と注意点

PIVOT 機能とは？

PIVOT 機能は、テーブル内の行データを列データに変換する強力なツールです。集計や分析を容易にし、データの可視化や理解を促進します。特に、以下のケースで有効です。

• 異なる属性に基づいてデータを比較したい
• 時系列データの推移を分析したい
• データの集計結果をコンパクトに表示したい

文字列データの PIVOT 操作:

文字列データの PIVOT 操作は、集計関数と組み合わせることで、高度な分析を可能にします。以下に、具体的な例と解説を示します。

例1：商品カテゴリ別売上金額の集計

SELECT Category,
       SUM(SalesAmount) AS TotalSales
FROM Sales
PIVOT (
    SUM(SalesAmount)
    FOR Category IN ('Clothing', 'Electronics', 'Furniture')
) AS p;

この例では、Sales テーブルから商品カテゴリ別売上金額を集計しています。PIVOT 句によって、Category 列を基に列方向にデータが展開され、各カテゴリの合計売上金額 (TotalSales) が表示されます。

例2：国別顧客数と平均購入金額の集計

SELECT Country,
       COUNT(*) AS CustomerCount,
       AVG(SalesAmount) AS AverageSales
FROM Sales
GROUP BY Country
PIVOT (
    COUNT(*)
    FOR Country IN ('Japan', 'USA', 'UK')
) AS p;

この例では、国別顧客数と平均購入金額を集計しています。GROUP BY 句と PIVOT 句を組み合わせることで、国ごとに集計結果を表示できます。

集計関数と PIVOT の組み合わせ:

PIVOT 機能は、SUM、COUNT、AVG などの様々な集計関数と組み合わせることができます。これにより、より詳細な分析が可能になります。

SELECT Category,
       [2023-01] AS 'Jan-23',
       [2023-02] AS 'Feb-23',
       [2023-03] AS 'Mar-23'
FROM Sales
PIVOT (
    SUM(SalesAmount)
    FOR OrderDate IN ([2023-01], [2023-02], [2023-03])
) AS p;

この例では、商品カテゴリ別売上金額の推移を分析しています。PIVOT 句によって、OrderDate 列を基に列方向にデータが展開され、各カテゴリの月別売上金額が表示されます。

動的 PIVOT:

動的 PIVOT は、クエリ実行時に列名を動的に指定できる機能です。事前に列名を定義する必要がなく、柔軟なデータ分析を可能にします。

例4：ユーザー入力に基づいた動的 PIVOT

DECLARE @cols VARCHAR(MAX);

SET @cols = 'Country,';

SELECT @cols += CONCAT(Country, ',');
FROM Countries;

SET @cols = LEFT(@cols, LEN(@cols) - 1);

EXEC sp_executesql @sql = N'
SELECT ' + @cols + '
FROM Sales
PIVOT (
    SUM(SalesAmount)
    FOR Country IN (' + @cols + ')
) AS p;
';

この例では、ユーザー入力に基づいて動的に PIVOT クエリを実行しています。sp_executesql 構文を使って、動的に生成されたクエリを実行します。

• PIVOT 操作は、大量のデータに対しては処理速度が低下する可能性があります。
• PIVOT 句で使用する列は、一意である必要があります。
• 集計関数は、PIVOT 句の前に記述する必要があります。

SELECT Category,
       SUM(SalesAmount) AS TotalSales
FROM Sales
PIVOT (
    SUM(SalesAmount)
    FOR Category IN ('Clothing', 'Electronics', 'Furniture')
) AS p;

SELECT Country,
       COUNT(*) AS CustomerCount,
       AVG(SalesAmount) AS AverageSales
FROM Sales
GROUP BY Country
PIVOT (
    COUNT(*)
    FOR Country IN ('Japan', 'USA', 'UK')
) AS p;

SELECT Category,
       [2023-01] AS 'Jan-23',
       [2023-02] AS 'Feb-23',
       [2023-03] AS 'Mar-23'
FROM Sales
PIVOT (
    SUM(SalesAmount)
    FOR OrderDate IN ([2023-01], [2023-02], [2023-03])
) AS p;

DECLARE @cols VARCHAR(MAX);

SET @cols = 'Country,';

SELECT @cols += CONCAT(Country, ',');
FROM Countries;

SET @cols = LEFT(@cols, LEN(@cols) - 1);

EXEC sp_executesql @sql = N'
SELECT ' + @cols + '
FROM Sales
PIVOT (
    SUM(SalesAmount)
    FOR Country IN (' + @cols + ')
) AS p;
';

CASE 式は、条件に応じて異なる値を返す式です。比較的シンプルな集計処理に適しています。

利点:

• 記述が比較的簡単
• 複雑な条件分岐にも対応可能

欠点:

• 複雑な集計処理には不向き
• 可読性が低下する可能性がある

例:

SELECT Category,
       SUM(CASE WHEN Gender = 'Male' THEN SalesAmount END) AS MaleSales,
       SUM(CASE WHEN Gender = 'Female' THEN SalesAmount END) AS FemaleSales
FROM Sales
GROUP BY Category;

GROUP BY と子クエリ:

GROUP BY 句と子クエリを組み合わせることで、より複雑な集計処理を行うことができます。

• 柔軟な集計処理が可能
• 可読性が高い

• 記述が複雑になる場合がある

SELECT Category,
       (SELECT SUM(SalesAmount)
        FROM Sales
        WHERE Gender = 'Male'
        AND Category = c.Category) AS MaleSales,
       (SELECT SUM(SalesAmount)
        FROM Sales
        WHERE Gender = 'Female'
        AND Category = c.Category) AS FemaleSales
FROM Sales AS c
GROUP BY Category;

FOR XML PATH:

FOR XML PATH を使用することで、ピボット表形式のデータを XML 形式で出力することができます。

• ピボット表形式のデータを簡単に生成
• XML データを他のツールで活用可能

SELECT Category,
       SUM(SalesAmount) AS TotalSales
FROM Sales
FOR XML PATH('');

C# や PowerShell などのスクリプト:

C# や PowerShell などのスクリプトを使用して、独自の PIVOT ロジックを実装することができます。

• 非常に柔軟な処理が可能

• 開発スキルが必要

C# の例:

using System;
using System.Collections.Generic;
using System.Linq;

public class Program
{
    public static void Main()
    {
        var salesData = new List<SalesData>()
        {
            new SalesData { Category = "Clothing", Gender = "Male", SalesAmount = 100 },
            new SalesData { Category = "Clothing", Gender = "Female", SalesAmount = 200 },
            new SalesData { Category = "Electronics", Gender = "Male", SalesAmount = 300 },
            new SalesData { Category = "Electronics", Gender = "Female", SalesAmount = 400 },
        };

        var pivotData = salesData.GroupBy(x => x.Category)
            .Select(x => new
            {
                Category = x.Key,
                MaleSales = x.Where(y => y.Gender == "Male").Sum(y => y.SalesAmount),
                FemaleSales = x.Where(y => y.Gender == "Female").Sum(y => y.SalesAmount)
            });

        foreach (var item in pivotData)
        {
            Console.WriteLine($"{item.Category} - Male Sales: {item.MaleSales}, Female Sales: {item.FemaleSales}");
        }
    }
}

public class SalesData
{
    public string Category { get; set; }
    public string Gender { get; set; }
    public int SalesAmount { get; set; }
}