sql-server - SQL Server：根据文本字段中的 csv 值按用户分组的聚合计数

Question

如果没有之前/之后的示例，我不确定如何最好地用文字描述我的问题，这可能就是我在这里找不到现有解决方案的原因。

我在票证数据库的用户定义字段中有“标签”，以表示处理所述票证时发生的错误。该字段可以为空，可以有一个条目或多个条目。它们应该用逗号分隔，但我无法在输入文本时对其进行验证。下面的临时表#Categories 显示了我希望出现在该字段中的当前“标签”。

我可以使用下面的方法得到我想要的结果，但我觉得这很笨拙，而且几乎可以肯定有一个更优雅的解决方案，它不需要通过手动更改它匹配的类别来一遍又一遍地运行相同的更新命令。如果引入了新的“标签”（或者如果有人打错了标签，因此不会导致匹配），这也需要手动更新。

我想要的结果是一个用户列表，其中包含“错误”计数，即在指定日期范围内（包括 0）的所有工单的所有用户定义字段中出现这些标签，如下所示。理想的解决方案将固有地包括新用户和新类别（标签）而无需人工干预，因此我会在将用于的结果报告中看到它们。

用户	类别	错误
jdoe	木板	0
jdoe	预算	1
jdoe	冲突	0
jdoe	接触	2
jdoe	欺骗	1
jdoe	物品	0
jdoe	跳过调度	0
jdoe	SLAMiss	0
jdoe	子类型	5
jdoe	类型	0
jdoe	空白	0
史密斯	木板	0
史密斯	预算	0
史密斯	冲突	1
史密斯	接触	0
史密斯	欺骗	0
史密斯	物品	2
史密斯	跳过调度	0
史密斯	SLAMiss	0
史密斯	子类型	0
史密斯	类型	0
史密斯	空白	1

-- I want to include all users and all category combinations, so I build a table
-- with those combinations first to ensure NULL entries are still represented (as zero)

CREATE TABLE #Categories (Category varchar(30))

INSERT INTO #Categories (Category)
VALUES ('Agreement')
        ,('Board')
        ,('Budget')
        ,('Conflict')
        ,('Contact')
        ,('Dupe')
        ,('Item')
        ,('SkipDispatch')
        ,('SLAMiss')
        ,('Subtype')
        ,('Type')
        ,('Whitespace')

CREATE TABLE #Errors 
(
    User varchar(50), 
    Category varchar(30), 
    Errors Int
)

INSERT INTO #Errors (User, Category, Errors)
    SELECT DISTINCT
        A.User, D.Category, 0
    FROM
        Tickets_SLA_Workflow A
    LEFT JOIN
        Tickets C ON A.Tickets_RecID = C.Tickets_RecID
    LEFT JOIN 
        Tickets_User_Defined_Field_Value B ON C.Tickets_RecID = B.Tickets_RecID
    CROSS JOIN 
        #Categories D
    WHERE
        B.User_Defined_Field_RecID = 28
        AND A.Date_Responded BETWEEN @Start AND @End

-- Next I update that table with the actual counts of occurrences of the tag "Board"

UPDATE #Errors
SET Errors = Board.Errors
FROM #Errors A
LEFT JOIN
    (SELECT A.User, COUNT(B.User_Defined_Field_Value) Errors
     FROM Tickets_SLA_Workflow A
     LEFT JOIN Tickets C ON A.Tickets_RecID = C.Tickets_RecID
     LEFT JOIN Tickets_User_Defined_Field_Value B ON C.Tickets_RecID = B.Tickets_RecID
     WHERE B.User_Defined_Field_RecID = 28
       AND A.Date_Responded_UTC BETWEEN @Start AND @End
       AND B.User_Defined_Field_Value LIKE '%Board%'
     GROUP BY A.User) Board on A.User = Board.User
WHERE
    A.Category = 'Board'
    AND A.User = Board.User

-- Then I repeat for tag "Budget" ... and so on through all the categories

UPDATE #Errors
SET Errors = Budget.Errors
FROM #Errors A
LEFT JOIN
    (SELECT A.User, COUNT(B.User_Defined_Field_Value) Errors
     FROM Tickets_SLA_Workflow A
     LEFT JOIN Tickets C ON A.Tickets_RecID = C.Tickets_RecID
     LEFT JOIN Tickets_User_Defined_Field_Value B ON C.Tickets_RecID = B.Tickets_RecID
     WHERE B.User_Defined_Field_RecID = 28
       AND A.Date_Responded_UTC BETWEEN @Start AND @End
       AND B.User_Defined_Field_Value LIKE '%Budget%'
     GROUP BY A.User) Budget ON A.User = Budget .User
WHERE
    A.Category = 'Budget'
    AND A.User = Budget.User

标签： sql-server

Answer 1

您的设计是非规范化的，因为每个类别都应该在单独的行中。但是我们可以通过使用逗号分隔类别来转换它STRING_SPLIT。

然后，您可以简单地按用户和类别进行汇总。

SELECT
  wf.[User],
  s.value AS Category,
  COUNT(*) Errors
FROM Tickets_SLA_Workflow wf
JOIN Tickets t ON wf.Tickets_RecID = t.Tickets_RecID
JOIN Tickets_User_Defined_Field_Value fv
  ON t.Tickets_RecID = fv.Tickets_RecID
    AND fv.User_Defined_Field_RecID = 28
CROSS APPLY STRING_SPLIT (fv.User_Defined_Field_Value, ',') s
WHERE wf.Date_Responded_UTC BETWEEN @Start AND @End
GROUP BY
  wf.User,
  s.value;

如果不是所有的类别都用逗号正确分隔，但你有一个实际的类别列表，那么你可以通过使用得到几乎相同的结果LIKE。缺点是重叠的类别（例如Contact和Contacts）将返回两次。

SELECT
  wf.[User],
  c.Category,
  COUNT(*) Errors
FROM Tickets_SLA_Workflow wf
JOIN Tickets t ON wf.Tickets_RecID = t.Tickets_RecID
JOIN Tickets_User_Defined_Field_Value fv
  ON t.Tickets_RecID = fv.Tickets_RecID
    AND fv.User_Defined_Field_RecID = 28
JOIN Categories c ON fv.User_Defined_Field_Value LIKE '%' + c.Category + '%'
WHERE wf.Date_Responded_UTC BETWEEN @Start AND @End
GROUP BY
  wf.User,
  c.Category;

注意：避免使用无意义的别名，例如A和B

注意：该表上的LEFT JOIN后跟变为WHEREINNER JOIN