Wie generiert man dynamisch Spalten mit Anzahl in SQL für Data Mining?

Verwenden Sie SQL, um Spalten dynamisch zu generieren

In diesem Artikel geht es um ein häufiges Problem im Bereich Data Mining: das dynamische Erstellen von Spalten basierend auf dynamischen Daten. Diese Herausforderung entsteht, wenn Daten in einem benutzerfreundlichen Format dargestellt werden müssen, insbesondere wenn eine Anzahl von Werten in jeder dynamisch generierten Spalte erforderlich ist.

Problembeschreibung

Wir haben drei Tabellen: Kunden, CustomerRewards und Rewards. Das Ziel besteht darin, eine neue Tabelle zu erstellen, die den Namen jedes Kunden und die Anzahl der Prämien anzeigt, die er in jedem Prämientyp (z. B. Bronze, Silber, Gold usw.) hat. Belohnungstypen sind jedoch dynamisch, was bedeutet, dass im Laufe der Zeit neue Typen hinzugefügt oder entfernt werden können.

Lösung: Verwenden Sie die PIVOT-Funktion

Statischer PIVOT:

Wenn die Anzahl der Belohnungstypen im Voraus bekannt ist, können wir eine hartcodierte PIVOT-Funktion verwenden. Zum Beispiel:

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;

Dynamischer PIVOT:

Wenn die Anzahl der Belohnungstypen variieren kann, können wir dynamisches SQL verwenden, um PIVOT durchzuführen:

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)

Enthält die Gesamtspalte

Um die Gesamtspalte einzubeziehen, können wir ROLLUP verwenden:

STATISCHES ROLLUP:

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

Dynamisches ROLLUP:

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)

Das obige ist der detaillierte Inhalt vonWie generiert man dynamisch Spalten mit Anzahl in SQL für Data Mining?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!