Bagaimana untuk Menjana Lajur Secara Dinamik dengan Kiraan dalam SQL untuk Perlombongan Data?

Gunakan SQL untuk menjana lajur secara dinamik

Artikel ini membincangkan masalah biasa dalam bidang perlombongan data: mencipta lajur secara dinamik berdasarkan data dinamik. Cabaran ini timbul apabila data perlu dibentangkan dalam format yang mesra pengguna, terutamanya apabila kiraan nilai diperlukan dalam setiap lajur yang dijana secara dinamik.

Pernyataan Masalah

Kami mempunyai tiga jadual: Pelanggan, Ganjaran Pelanggan dan Ganjaran. Matlamatnya adalah untuk menjana jadual baharu yang menunjukkan nama setiap pelanggan dan bilangan ganjaran yang mereka miliki dalam setiap jenis ganjaran (mis. Gangsa, Perak, Emas, dll.). Walau bagaimanapun, jenis ganjaran adalah dinamik, bermakna jenis baharu boleh ditambah atau dialih keluar dari semasa ke semasa.

Penyelesaian: Gunakan fungsi PIVOT

PIVOT Statik:

Jika bilangan jenis ganjaran diketahui lebih awal, kita boleh menggunakan fungsi PIVOT berkod keras. Contohnya:

select name, [Bronze], [Silver], [Gold], [Platinum], [AnotherOne]
from
(
  select c.name,
    cr.description,
    r.typeid
  from customers c
  left join rewards r
    on c.id = r.customerid
  left join customerrewards cr
    on r.typeid = cr.typeid
) x
pivot
(
  count(typeid)
  for description in ([Bronze], [Silver], [Gold], [Platinum], [AnotherOne])
) p;

PIVOT Dinamik:

Jika bilangan jenis ganjaran mungkin berbeza-beza, kami boleh menggunakan SQL dinamik untuk melaksanakan PIVOT:

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 c
                left join rewards r
                  on c.id = r.customerid
                left join customerrewards cr
                  on r.typeid = cr.typeid
            ) x
            pivot
            (
                count(typeid)
                for description in (' + @cols + ')
            ) p '

execute(@query)

Mengandungi jumlah lajur

Untuk memasukkan jumlah lajur kita boleh menggunakan ROLLUP:

GULING STATIK:

select name, sum([Bronze]) Bronze, sum([Silver]) Silver,
  sum([Gold]) Gold, sum([Platinum]) Platinum, sum([AnotherOne]) AnotherOne
from
(
  select name, [Bronze], [Silver], [Gold], [Platinum], [AnotherOne]
  from
  (
    select c.name,
      cr.description,
      r.typeid
    from customers c
    left join rewards r
      on c.id = r.customerid
    left join customerrewards cr
      on r.typeid = cr.typeid
  ) x
  pivot
  (
    count(typeid)
    for description in ([Bronze], [Silver], [Gold], [Platinum], [AnotherOne])
  ) p
) x
group by name with rollup

GULING Dinamik:

DECLARE @cols AS NVARCHAR(MAX),
    @colsRollup 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,'')

select @colsRollup
      = STUFF((SELECT ', Sum(' + QUOTENAME(description) + ') as ' + QUOTENAME(description)
                    from customerrewards
                    group by description, typeid
                    order by typeid
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')


set @query
          = 'SELECT name, ' + @colsRollup + '
             FROM
             (
                SELECT name,' + @cols + ' from
                 (
                    select c.name,
                      cr.description,
                      r.typeid
                    from customers c
                    left join rewards r
                      on c.id = r.customerid
                    left join customerrewards cr
                      on r.typeid = cr.typeid
                ) x
                pivot
                (
                    count(typeid)
                    for description in (' + @cols + ')
                ) p
              ) x1
              GROUP BY name with ROLLUP'

execute(@query)

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