Clever Geek Handbook

MS SQLで行を集約する5つの方法

SQLクエリの行を集約する必要がある場合があります。つまり、次のようなデータセットによってです。

Groupid アイテム
1 AAA
2 IS
5 あー
2 なに
2 THE
1 これ
次のようなものを取得します。

Groupid アイテムリスト
1 AAA、これ
2 ある、何、
5 あー
たとえば、MySQLには、次のような目的でビルトインGROUP_CONCAT（）関数があります。 

  SELECT GroupId、GROUP_CONCAT（Item SEPARATOR "、"）AS ItemList
アイテムから


MS SQL Server'eにはそのような機能はないので、倒錯する必要があります。 開始する前に、テストテーブルを作成するスクリプトを作成します。 

  CREATE TABLEアイテム（GroupId INT、アイテムNVARCHAR（10））

 INSERT INTO Items（GroupId、Item）
 SELECT 1 AS GroupId、 'AAA' AS Item
   UNION ALL
 SELECT 2、「IS」
   UNION ALL
選択5、「OMG」
   UNION ALL
選択2、「何」
   UNION ALL
 SELECT 2、「THE」
   UNION ALL
 SELECT 1、「これ」


それでは始めましょう。



最も愚かな簡単な方法は、一時テーブルを作成し、その中に集計の中間結果を収集し、Itemsテーブル上でカーソルを実行することです。 このメソッドの動作は非常に遅く、そのコードは恐ろしいものです。 感心する： 

  DECLARE @Aggregated TABLE（GroupId INT、ItemList NVARCHAR（100））

 DECLARE ItemsCursor CURSOR READ_ONLY
 FOR SELECT GroupId、アイテム
    アイテムから

 DECLARE @CurrentGroupId INT
 DECLARE @CurrentItem NVARCHAR（10）
 DECLARE @CurrentItemList NVARCHAR（100）

アイテムを開くカーソル

 ItemsCursorから次をフェッチ
 INTO @ CurrentGroupId、@ CurrentItem

 @@ FETCH_STATUS = 0の場合
開始
     SET @CurrentItemList =（SELECT ItemList
                             FROM @Aggregated
                             WHERE GroupId = @CurrentGroupId）

     IF @CurrentItemList IS NULL
         INSERT INTO @Aggregated（GroupId、ItemList）
        値（@ CurrentGroupId、@ CurrentItem）
    その他
         UPDATE @Aggregated
         SET ItemList = ItemList + '、' + @CurrentItem
         WHERE GroupId = @CurrentGroupId

     ItemsCursorから次をフェッチ
     INTO @ CurrentGroupId、@ CurrentItem
終了

アイテムを閉じるカーソル
アイテムの割り当て解除カーソル

 SELECT GroupId、ItemList
 FROM @Aggregated


一時テーブルを使用しない、より美しい方法があります。 SELECTトリックvar = var + '、' + col FROM smwhereに基づいています。 はい、可能であり、機能します。 

  CREATE FUNCTION ConcatItems（@GroupId INT）
   戻り値NVARCHAR（100）
として
開始
     DECLARE @ItemList varchar（8000）
     SET @ItemList = ''

     SELECT @ItemList = @ItemList + '、' +アイテム
    アイテムから
     WHERE GroupId = @GroupId

    部分文字列を返す（@ ItemList、2、100）
終了

行く

 SELECT GroupId、dbo.ConcatItems（GroupId）ItemList
アイテムから
 GROUP BY GroupId


少し良くなりましたが、それでも松葉杖です。 集約された行の最大数が制限されていることがわかっている場合、次の方法を使用できます（このクエリは、4つを超える要素を持つグループがないという仮定に基づいています）。 

  SELECT GroupId、
       ケースItem2 WHEN '' THEN Item1
           その他のケースItem3 WHEN '' THEN Item1 + '、' + Item2
           その他のケースItem4 WHEN '' THEN Item1 + '、' + Item2 + '、' + Item3
            ELSE Item1 + '、' + Item2 + '、' + Item3 + '、' + Item4
        END END END AS ItemList
 FROM（
   SELECT GroupId、
        MAX（ケースItemNo WHEN 1 THEN Item ELSE '' END）AS Item1
        MAX（アイテム2の場合、アイテムELSE ''終了）の場合、アイテム2、
        MAX（ケースItemNo WHEN 3 THEN Item ELSE '' END）AS Item3、
        MAX（ケースItemNo WHEN 4 THEN Item ELSE '' END）AS Item4
   FROM（
     SELECT GroupId、
         アイテム、
          ROW_NUMBER（）OVER（PARTITION BY GroupId ORDER BY Item）ItemNo
    アイテムから
   ）AS OrderedItems
   GROUP BY GroupId
 ）AS AlmostAggregated


はい、たくさんのコード。 ただし、データベース内の単一の余分なオブジェクトは、1つの純粋な選択ではありません。 これは時々重要です。



ただし、単一のリクエストのフレームワーク内に留まりながら、グループサイズの制限を回避する方法があります。 グループのすべての要素をXMLフィールドに収集し 、それを文字列型に変換して、要素間のタグをコンマに置き換えます。 

  SELECT GroupId、
        REPLACE（SUBSTRING（ItemListWithTags、4、LEN（ItemListWithTags）-7）、
                '<a>'、
                '、'）AS ItemList
 FROM（
   SELECT GroupId、
        CAST（XmlItemList AS NVARCHAR（200））ItemListWithTags
   FROM（
     SELECT GroupId、
          （SELECT AS AS A
         FROMアイテムii
         WHERE ii.GroupId = GroupIds.GroupId
         FOR XML PATH（ ''））AS XmlItemList
     FROM（SELECT DISTINCT GroupId FROM Items）AS GroupIds
   ）AS subq1
 ）AS subq2


一般に、それは非常に高速ではありませんが、常に動作します。 そして、もちろん、2000以上のSQL Serverが必要です。



はい、 CLR Aggregate Functionsを介して行を集計する方法はまだありますが、これは一般的に恐ろしいことです。なぜなら、死は遅く、タスクの複雑さにとって重要ではないからです。 そのような記事に十分な需要がある場合は、後で書きます。



コメントと批判を楽しみにしています。 そしてもう1つ、私が家でやったようなコードを強調表示する方法を誰かが知っているなら、教えてください 。 私は、スクリーンショットを挿入する方法を除いて、これまでの別の方法を参照してください。


More articles:


All Articles