Comment générer dynamiquement des colonnes avec un nombre en SQL pour l'exploration de données ?

Utiliser SQL pour générer dynamiquement des colonnes

Cet article aborde un problème courant dans le domaine du data mining : la création dynamique de colonnes basées sur des données dynamiques. Ce défi se pose lorsque les données doivent être présentées dans un format convivial, en particulier lorsqu'un décompte de valeurs est requis dans chaque colonne générée dynamiquement.

Énoncé du problème

Nous avons trois tableaux : Clients, CustomerRewards et Rewards. L'objectif est de générer un nouveau tableau indiquant le nom de chaque client et le nombre de récompenses dont il dispose dans chaque type de récompense (par exemple Bronze, Argent, Or, etc.). Cependant, les types de récompenses sont dynamiques, ce qui signifie que de nouveaux types peuvent être ajoutés ou supprimés au fil du temps.

Solution : Utiliser la fonction PIVOT

PIVOT statique :

Si le nombre de types de récompenses est connu à l'avance, nous pouvons utiliser une fonction PIVOT codée en dur. Par exemple :

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 dynamique :

Si le nombre de types de récompenses peut varier, nous pouvons utiliser du SQL dynamique pour effectuer 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)

Contient la colonne totale

Pour inclure la colonne total, nous pouvons utiliser ROLLUP :

ROLLUP STATIQUE :

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

ROLLUP dynamique :

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)

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!