如何在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中文网其他相关文章！