如何在SQL中動態生成列進行資料分析？

SQL 中動態建立欄位

在 SQL 中動態生成列允許根據動態輸入靈活地建立資料結構。考慮以下場景：

• Customers 表包含客戶詳細資料（ID 和姓名）
• CustomerRewards 表包含獎勵類型（TypeID 和 Description）
• Rewards 表格連結客戶與獎勵

目標是動態建立表示獎勵類型的列，並計算每種類型中的客戶獎勵數量。

為此，您可以使用 SQL 的 PIVOT 函數：

<code class="language-sql">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;</code>

在這裡，PIVOT 函數根據 description 欄位將行轉換為列，顯示每種獎勵類型的計數。

對於未知數量的列，您可以使用動態 SQL：

<code class="language-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);</code>

動態查詢根據 customerrewards 表中的值建立欄位。

要包含「總計」列，您可以使用 ROLLUP 運算子：

<code class="language-sql">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;</code>

透過動態生成列，您可以有效地處理不斷變化的資料結構，並以全面的方式呈現資料。

以上是如何在SQL中動態生成列進行資料分析？的詳細內容。更多資訊請關注PHP中文網其他相關文章！