データ分析のために SQL で列を動的に生成するにはどうすればよいですか?

SQL で列を動的に作成する

SQL での動的列生成により、動的入力に基づいた柔軟なデータ構造の構築が可能になります。次のシナリオを考えてみましょう:

• Customers テーブルには顧客の詳細 (ID と名前) が含まれています
• CustomerRewards テーブルには報酬タイプ (TypeID と説明) が含まれています
• Rewards 顧客と報酬をつなぐテーブル

目標は、特典タイプを表す列を動的に作成し、各タイプの顧客特典の数をカウントすることです。

これを行うには、SQL の PIVOT 関数を使用できます。

SELECT
  name,
  [Bronze],
  [Silver],
  [Gold],
  [Platinum],
  [AnotherOne]
FROM
  (
    SELECT
      c.name,
      cr.description,
      r.typeid
    FROM
      customers AS c
    LEFT JOIN
      rewards AS r
      ON c.id = r.customerid
    LEFT JOIN
      customerrewards AS cr
      ON r.typeid = cr.typeid
  ) AS x
PIVOT
  (
    COUNT(typeid)
    FOR description IN ([Bronze], [Silver], [Gold], [Platinum], [AnotherOne])
  ) AS p;

ここでは、PIVOT 関数が description フィールドに基づいて行を列に変換し、各報酬タイプのカウントを表示します。

列の数が不明な場合は、動的 SQL を使用できます。

DECLARE @cols AS NVARCHAR(MAX),
    @query AS NVARCHAR(MAX);

SELECT
  @cols = STUFF(
    (
      SELECT
        ',' + QUOTENAME(description)
      FROM
        customerrewards
      GROUP BY
        description,
        typeid
      ORDER BY
        typeid
      FOR XML PATH(''),
      TYPE
    ).value('.', 'NVARCHAR(MAX)'),
    1,
    1,
    ''
  );

SET
  @query = 'SELECT name, ' + @cols + ' FROM 
             (
                SELECT
                  c.name,
                  cr.description,
                  r.typeid
                FROM
                  customers AS c
                LEFT JOIN
                  rewards AS r
                  ON c.id = r.customerid
                LEFT JOIN
                  customerrewards AS cr
                  ON r.typeid = cr.typeid
            ) AS x
            PIVOT 
            (
                COUNT(typeid)
                FOR description IN (' + @cols + ')
            ) AS p ';

EXECUTE (@query);

動的クエリは、customerrewards テーブルの値に基づいて列を作成します。

「合計」列を含めるには、ROLLUP 演算子を使用できます。

SELECT
  name,
  SUM([Bronze]) AS Bronze,
  SUM([Silver]) AS Silver,
  SUM([Gold]) AS Gold,
  SUM([Platinum]) AS Platinum,
  SUM([AnotherOne]) AS AnotherOne
FROM
  (
    SELECT
      name,
      [Bronze],
      [Silver],
      [Gold],
      [Platinum],
      [AnotherOne]
    FROM
      (
        SELECT
          c.name,
          cr.description,
          r.typeid
        FROM
          customers AS c
        LEFT JOIN
          rewards AS r
          ON c.id = r.customerid
        LEFT JOIN
          customerrewards AS cr
          ON r.typeid = cr.typeid
      ) AS x
      PIVOT
      (
        COUNT(typeid)
        FOR description IN ([Bronze], [Silver], [Gold], [Platinum], [AnotherOne])
      ) AS p
  ) AS x1
GROUP BY
  name
WITH ROLLUP;

列を動的に生成することで、データ構造の変化を効率的に処理し、包括的な方法でデータを表示できます。

以上がデータ分析のために SQL で列を動的に生成するにはどうすればよいですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。