데이터 마이닝을 위해 SQL에서 개수가 포함된 열을 동적으로 생성하는 방법은 무엇입니까?

SQL을 사용하여 동적으로 열 생성

이 기사에서는 데이터 마이닝 분야의 일반적인 문제, 즉 동적 데이터를 기반으로 열을 동적으로 생성하는 문제에 대해 설명합니다. 이러한 문제는 데이터를 사용자에게 친숙한 형식으로 표시해야 할 때, 특히 동적으로 생성된 각 열에 값 개수가 필요할 때 발생합니다.

문제 설명

Customers, CustomerRewards, Rewards라는 세 개의 테이블이 있습니다. 목표는 각 고객의 이름과 각 보상 유형(예: 브론즈, 실버, 골드 등)에서 보유한 보상 수를 표시하는 새 테이블을 생성하는 것입니다. 그러나 보상 유형은 동적이므로 시간이 지남에 따라 새로운 유형이 추가되거나 제거될 수 있습니다.

해결책: PIVOT 기능을 사용하세요

정적 피벗:

리워드 유형의 개수를 미리 알고 있다면 하드코딩된 PIVOT 기능을 사용할 수 있습니다. 예:

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;

동적 피벗:

보상 유형의 수가 다를 수 있는 경우 동적 SQL을 사용하여 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)

전체 열 포함

전체 열을 포함하려면 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

동적 롤업:

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)

위 내용은 데이터 마이닝을 위해 SQL에서 개수가 포함된 열을 동적으로 생성하는 방법은 무엇입니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!