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>
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>
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>
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>
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!