データマイニングのために SQL でカウントを含む列を動的に生成する方法

SQL を使用して列を動的に生成する

この記事では、データ マイニングの分野における一般的な問題、つまり動的データに基づいて列を動的に作成する問題について説明します。この課題は、データを使いやすい形式で表示する必要がある場合、特に動的に生成された各列で値のカウントが必要な場合に発生します。

問題の説明

Customers、CustomerRewards、Rewards の 3 つのテーブルがあります。目標は、各顧客の名前と各特典タイプ (ブロンズ、シルバー、ゴールドなど) での特典の数を示す新しいテーブルを生成することです。ただし、報酬のタイプは動的であるため、時間の経過とともに新しいタイプが追加または削除される可能性があります。

解決策: PIVOT 関数を使用します

静的ピボット:

報酬タイプの数が事前にわかっている場合は、ハードコーディングされた PIVOT 関数を使用できます。例:

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

動的ピボット:

報酬タイプの数が異なる場合は、動的 SQL を使用して PIVOT を実行できます。

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

合計列が含まれます

合計列を含めるには、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>

動的ロールアップ:

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

以上がデータマイニングのために SQL でカウントを含む列を動的に生成する方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。