在GROUP BY之后连接一个字段 [英] Concatenate one field after GROUP BY

问题描述

这个问题在SO中已经问过很多次了，但是没有一个答案令我满意。

1. 问题1


2. 问题2


3. 问题3


4. 问题4 li>
   

我正在处理包含多个版本的 DataObjectVersions 表，其中包含大约120万个独特对象（和增加）。我需要为每个唯一对象连接来自特定字段的更改。

现在我正在使用Q3中提供的XML Path的解决方案，但在此上运行此查询表是一个总体性能灾难。 SQL Server在19mn后开始重新调用数据。知道这些数据将会被连接两次以上，你可以想象它的影响。

我正在寻找最有效的可伸缩性方式来连接相同字段的值不同的行按照其他字段分组（这当然不是关键字）。更准确地说，这是在Datawarehouse的视图中使用的。

编辑：

我试图简化描述，但是这里有一个完整的概述
我有多个表，其中包含以下列：

 
 [ID] 
 [CreatedTime] 
 [CreatedBy] 
 [删除时间] 
 [DeletedBy] 
 [ResourceId] 
 [AccountId] 
 [类型] 
 

视图用于返回所有表中所有记录的联合，它仍会返回相同的列（在我的问题由版本表））。 [ResourceId] 和 [AccountId] 是对象（组成员，系统帐户等）的唯一组合标识符。 。资源分配专门）。 [Type] 用于标识不同的级别（如文件分配时的读/写/执行）

所有其他字段对于不同的唯一对象都包含相同的值（在不同的表中）。我需要获取对象并连接 [Type] 列的值。之后处理所有行，并且（ [ResourceId] ， [AccountId] ）组合必须是唯一的（不是当存在不同类型的情况时）。

编辑2：

这个函数：

$ $ p $ code CREATE FUNCTION [dbo]。[GetUniqueType]
（
@ResourceId as uniqueidentifier，
@Account as uniqueidentifier
）
RETURNS nvarchar（100）
AS
BEGIN
返回STUFF（（select'，'+ raType.Type from vwAllAssignments raType其中raType.AccountId = @Account和raType.ResourceId = @ResourceId和raType.DeletedBy对于xml路径为空（''）），1,1，''）
END

GO

vwAllAssignments 是返回联合的视图。

最后，我选择

  SELECT [ CreatedTime] 
，[DeletedTime] 
，[DeletedBy] 
，[ResourceId] 
，[Accoun tId] 
，dbo.GetUniqueType（[ResourceId]，[AccountId]）AS [Type] 
 FROM vwAllAssignments 
 GROUP BY [ResourceId]，[AccountId]，[CreatedTime]，[DeletedTime] ，[DeletedBy] 
  

解决方案

试试这个：

  SELECT [CreatedTime] 
，[DeletedTime] 
，[DeletedBy] 
，[ResourceId] 
，[AccountId] 
，STUFF（（select'，'+ raType.Type $ b $ from vwAllAssignments raType 
 where raType.AccountId = vwAllAssignments.AccountId and 
 raType.ResourceId = vwAllAssignments.ResourceId和
 raType.DeletedBy为空
 for xml path（''）），1,1，''）AS [Type] 
 FROM vwAllAssignments 
 GROUP BY [ ResourceId]，[AccountId]，[CreatedTime]，[DeletedTime]，[DeletedBy] 
  

这应该是有帮助的。

  create index I （类型）
  

This question have been asked many times in SO but none of the answers is satisfying to my situation.

1. Question 1
2. Question 2
3. Question 3
4. Question 4

I am dealing with a DataObjectVersions table that contains multiple versions for around 1.2 million unique objects (and increasing). I need to concatenate changes from a specific field for each unique object.

Right now I am using the solution with the XML Path presented in Q3 but running such a query on this table is a total performance disaster. SQL Server started to retun Data after 19mn. Knowing that this data will be than joined twice, you can imagine the impact.

I am looking for the most efficient scalability-aware way to concatenate the values of the same fields of different rows grouped by an other field (which is not of course a key). To be more precise, this is used within a view in a Datawarehouse.

EDIT:

I tried to simplify the description but here is a complete overview I have multiple tables with the following columns

   [ID]
   [CreatedTime]
   [CreatedBy]
   [DeletedTime]
   [DeletedBy]
   [ResourceId]
   [AccountId]
   [Type]

A view is used to return the union of all records from all tables, which will still return the same columns (described in my questions by the versions table). [ResourceId] and [AccountId] are a unique composite identifier of an object (Group membership, System account, etc.. a resource assignment specifically). The [Type] is used to identify different levels (like Read/Write/Execute in the case of a file assignment)

All other fields contain the same values (in different tables) for different unique objects. I need to get the objects and concatenate the values of the [Type] column. All the row are processed afterward and the ([ResourceId],[AccountId]) combination must be unique (not the case when different types exists).

EDIT 2:

I am using this function:

CREATE FUNCTION [dbo].[GetUniqueType]
(
    @ResourceId as uniqueidentifier,
    @Account as uniqueidentifier
)
RETURNS nvarchar(100)
AS
BEGIN   
    return STUFF((select ',' + raType.Type from vwAllAssignments raType where raType.AccountId = @Account and raType.ResourceId = @ResourceId and raType.DeletedBy is null for xml path('')), 1,1,'')
END

GO

vwAllAssignments is the view returning the union of all tables rows.

Finally I am selecting

SELECT [CreatedTime]
      ,[DeletedTime]
      ,[DeletedBy]
      ,[ResourceId]
      ,[AccountId]
      ,dbo.GetUniqueType([ResourceId],[AccountId]) AS [Type]
FROM vwAllAssignments
GROUP BY [ResourceId], [AccountId], [CreatedTime], [DeletedTime], [DeletedBy]

解决方案

Try this:

SELECT [CreatedTime]
      ,[DeletedTime]
      ,[DeletedBy]
      ,[ResourceId]
      ,[AccountId]
      ,STUFF((select ',' + raType.Type 
              from vwAllAssignments raType 
              where raType.AccountId = vwAllAssignments.AccountId and 
                    raType.ResourceId = vwAllAssignments.ResourceId and 
                    raType.DeletedBy is null 
              for xml path('')), 1,1,'') AS [Type]
FROM vwAllAssignments
GROUP BY [ResourceId], [AccountId], [CreatedTime], [DeletedTime], [DeletedBy]

And an index like this should be helpful.

create index IX_vwAllAssignments on vwAllAssignments(AccountId, ResourceId, DeletedBy) include(Type)

这篇关于在GROUP BY之后连接一个字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！