Bagaimana untuk Menjana Lajur secara Dinamik dalam SQL untuk Analisis Data?

Buat lajur dalam SQL secara dinamik

Penjanaan lajur dinamik dalam SQL membolehkan pembinaan struktur data yang fleksibel berdasarkan input dinamik. Pertimbangkan senario berikut:

Jadual
• Customers mengandungi butiran pelanggan (ID dan nama)
Jadual
• CustomerRewards mengandungi jenis ganjaran (TypeID dan Penerangan)
• Rewards jadual yang menghubungkan pelanggan dan ganjaran

Matlamatnya adalah untuk mencipta lajur yang mewakili jenis ganjaran secara dinamik dan mengira bilangan ganjaran pelanggan dalam setiap jenis.

Untuk melakukan ini, anda boleh menggunakan fungsi PIVOT 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;

Di sini, fungsi PIVOT menukarkan baris menjadi lajur berdasarkan medan description, menunjukkan kiraan untuk setiap jenis ganjaran.

Untuk bilangan lajur yang tidak diketahui, anda boleh menggunakan SQL dinamik:

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);

Pertanyaan dinamik membuat lajur berdasarkan nilai dalam jadual customerrewards.

Untuk memasukkan lajur "Jumlah", anda boleh menggunakan operator 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;

Dengan menjana lajur secara dinamik, anda boleh mengendalikan perubahan struktur data dengan cekap dan mempersembahkan data dengan cara yang komprehensif.

Atas ialah kandungan terperinci Bagaimana untuk Menjana Lajur secara Dinamik dalam SQL untuk Analisis Data?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!