Heim > Datenbank > MySQL-Tutorial > Wie generiert man dynamisch Spalten mit Anzahl in SQL für Data Mining?

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

Patricia Arquette
Freigeben: 2025-01-09 15:27:43
Original
840 Leute haben es durchsucht

How to Dynamically Generate Columns with Count in SQL for 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:

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

Dynamischer PIVOT:

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

<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 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)</code>
Nach dem Login kopieren

Enthält die Gesamtspalte

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

STATISCHES ROLLUP:

<code class="language-sql">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</code>
Nach dem Login kopieren

Dynamisches ROLLUP:

<code class="language-sql">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)</code>
Nach dem Login kopieren

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!

Quelle:php.cn
Erklärung dieser Website
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn
Neueste Artikel des Autors
Beliebte Tutorials
Mehr>
Neueste Downloads
Mehr>
Web-Effekte
Quellcode der Website
Website-Materialien
Frontend-Vorlage