sql-server - SQL Server STRING_AGG 函数排序未按预期工作
问题描述
我已经打破了这个问题,试图在动态生成的数据集上重现它,但它终于解决了!
这是代码
;with tbl as
(
select Id, ClCode, Manager, ChangeDate
from (values
(1, '000005', 'Cierra Vega', '2017-10-05'),
(2, '000005', 'Alden Cantrell', '2017-11-29'),
(3, '000005', 'Alden Cantrell', '2017-11-30'),
(4, '000005', 'Kierra Gentry', '2018-09-05'),
(5, '000005', 'Kierra Gentry', '2018-09-12'),
(6, '000005', 'Pierre Cox', '2018-11-06'),
(7, '000005', 'Thomas Crane', '2019-09-11'),
(8, '000005', 'Thomas Crane', '2019-10-01'),
(9, '000005', 'Miranda Shaffer', '2020-04-27'),
(10,'000360', 'Bradyn Kramer', '2017-10-06')
) as t(Id, ClCode, Manager, ChangeDate)
)
, grouped as
(
select c.ClCode
, count(distinct c.Manager) [ManagerChangesCount]
, STRING_AGG(c.[Manager], ',') within group (order by c.MinChangeDate) [Managers]
, STRING_AGG(c.MinChangeDate, ',') within group (order by c.MinChangeDate) [ChangeDates]
from (
select x.ClCode
, x.[Manager]
, min(x.ChangeDate) [MinChangeDate]
from tbl x
group by x.ClCode, x.[Manager]
) c
group by c.ClCode
)
select *
from grouped
我的示例数据集包含有关 (ChangeDate) 某些客户 (ClCode) 何时更改其经理 (Manager) 的数据。它是真实 DWH 客户端维度表(SCD 类型 2)的一部分,因此这些“重复项”仅包含其他列中某处的更改。
我想要实现的目标:我需要一个客户代码列表,其中包含他们的经理被更改的次数,以及这些经理姓名的逗号分隔列表,按照他们的更改顺序从左到右排序
ClCode ManagerChangesCount Managers ChangeDates
000005 6 Cierra Vega,Alden Cantrell,Kierra Gentry,Pierre Cox,Thomas Crane,Miranda Shaffer 2017-10-05,2017-11-29,2018-09-05,2018-11-06,2019-09-11,2020-04-27
000360 1 Bradyn Kramer 2017-10-06
但实际上我收到的结果没有或有一些奇怪的排序
ClCode ManagerChangesCount Managers ChangeDates
000005 6 Alden Cantrell,Cierra Vega,Kierra Gentry,Miranda Shaffer,Pierre Cox,Thomas Crane 2017-11-29,2017-10-05,2018-09-05,2020-04-27,2018-11-06,2019-09-11
000360 1 Bradyn Kramer 2017-10-06
此查询返回良好的排序:
- 如果我删除
, count(distinct c.Manager) [ManagerChangesCount]
- 如果我删除字符串 id=10(第二个客户端)
- 如果我添加一个过滤条件
where ClCode = '000005'
- 如果我删除数据集中的重复行并从子查询中删除 group by
但对我来说都一样,它看起来像一个错误......我的意思是我的查询未排序的结果
伙计们,如果你能解决这个问题,请帮助我理解为什么排序不起作用
解决方案
首先,我同意你得到的行为不应该发生,但是,Stack Overflow 不是用于报告应用程序的错误。对于 SQL Server,应该在他们的Azure 反馈门户中完成。
至于解决问题,删除冗余DISTINCT
会COUNT
导致问题消失。要实现DISTINCT
(在 aSELECT DISTINCT
或 a中COUNT(DISTINCT {expression})
)SQL Server 需要首先对结果进行排序,因为它可以轻松删除具有相同排序位置的任何值。因此STRING_AGG
,即使它们有明确的ORDER BY
子句,也会在您的表达式中表达排序。
我说你的原因DISTINCT
是多余的,因为在查询中,Manager
对于给定的值,不会有重复的值ClCode
。这是因为您已经在子查询Manager
和ClCode
子查询中进行了分组。如果您单独运行该查询,您会看到它Manager
没有任何重复项:
WITH tbl AS
(SELECT Id,
ClCode,
Manager,
ChangeDate
FROM (VALUES (1, '000005', 'Cierra Vega', '2017-10-05'),
(2, '000005', 'Alden Cantrell', '2017-11-29'),
(3, '000005', 'Alden Cantrell', '2017-11-30'),
(4, '000005', 'Kierra Gentry', '2018-09-05'),
(5, '000005', 'Kierra Gentry', '2018-09-12'),
(6, '000005', 'Pierre Cox', '2018-11-06'),
(7, '000005', 'Thomas Crane', '2019-09-11'),
(8, '000005', 'Thomas Crane', '2019-10-01'),
(9, '000005', 'Miranda Shaffer', '2020-04-27'),
(10, '000360', 'Bradyn Kramer', '2017-10-06')) t (Id, ClCode, Manager, ChangeDate) )
SELECT x.ClCode,
x.[Manager],
MIN(x.ChangeDate) AS [MinChangeDate]
FROM tbl x
GROUP BY x.ClCode,
x.[Manager];
因此,DISTINCT
inCOUNT
只是增加了实例的开销,因为它不是必需的(SQL Server 已经对数据进行了排序,GROUP BY
那么为什么还要要求它再次排序呢?)。如果您在已经聚合的查询中使用 a DISTINCT
,那么您很可能不需要它。
推荐阅读
- git - 如何仅针对某个目录从“他们的”分支中接受 git 合并冲突?
- algorithm - 哪些技术可以有效地找到任意数据点的周期性?
- python - 如何使用 Scrapy 退回多件商品?
- javascript - ChartJS 不显示时间序列条形图的数据
- android - WebView 未加载 Url,因为 Canvas:试图绘制太大(...)位图
- sql - 如何更新表中的数据以将结果存储在另一个表中
- python-3.x - 在交叉验证期间更改测试和训练的大小
- r - R:从数据框中子集一列,保留列名
- javascript - 为什么 bundle.js 中没有我的函数?
- algorithm - 有人可以向我解释 Hoare 的分区方案吗?