主页 / dba / 问题 / 187311
Accepted
John Eisbrener
Asked: 2017-09-30 12:41:56 +0800 CST

按隐式组求和

  • 772

我确定我错过了一个明显的解决方案，但我试图总结由显式组号和隐式排序定义的组的值。我敢肯定，这不会让这更清楚，所以假设我有这个示例源堆表：

GroupID     Value
----------- -----------
1           5
1           5
1           3
2           4
2           1
1           4
2           3
2           5
2           2
1           1

我想要一个为我提供以下结果的查询：

GroupID     Values
----------- -----------
1           13
2           5
1           4
2           10
1           1

隐含的顺序是我还没有找到解决方法的挑战……但是。任何帮助，将不胜感激。

我希望我可以使用类似于以下的查询创建确定性行排序：

SELECT    * 
        , ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum  
FROM Table WITH(TABLOCK)
OPTION (MAXDOP 1)

我希望这会强制进行分配顺序扫描，然后给我一个确定性的行顺序。

可悲的是，我坚持使用数据。我这里没有其他指标，例如日期等，可以提供任何固定顺序。我希望上面概述的技巧就足够了，但我不完全确定它会。

编辑：为了结束这个问题，因为我知道有人问我为什么要问这个问题，我有一系列堆表，按月/年命名，其中包含业务要汇总的行项目金额按天（它们与我问题中的隐含群体相关）。由于有效地执行此操作看起来不可行，我们决定在月（例如表）级别进行聚合，因此这篇文章帮助我证明了对业务需求的更改是合理的。感谢大家的投入！

sql-server order-by

2 个回答

  1. Best Answer

    您提到的“隐式”组似乎是基于行顺序的。与电子表格或文本文件不同，关系表在逻辑上是一组无序的行，无论它是存储为堆还是具有聚簇索引。除非您有另一列来促进分组，否则无法编写查询来提供所需的结果。

    我希望我可以使用类似于以下的查询创建确定性行排序：

    SELECT      * 
        , ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum  
FROM Table WITH(TABLOCK)
OPTION (MAXDOP 1)

    很抱歉成为坏消息的传播者，但按文字排序并不能保证确定性排序（即使可以，你也需要一个ORDER BY子句）。如果它看起来这样做，那只是偶然。不过，任何增量列都可以使用。

    我希望这会强制进行分配顺序扫描，然后给我一个确定性的行顺序。

    分配顺序扫描并不比任何其他实现更具确定性；你只是（不安全地）依赖于不同的观察行为。

    • 7

  2. 如果你真的想使用%%physloc%%，这里有一个解决方案：

    declare @t table(GroupID int, Value int);
insert into @t values
(1,           5),
(1,           5),
(1,           3),
(2,           4),
(2,           1),
(1,           4),
(2,           3),
(2,           5),
(2,           2),
(1,           1);

create table #t(id int identity, GroupID int, Value int);

insert into #t (GroupID, Value)
select GroupID, Value
from @t
     cross apply (select sys.fn_PhysLocFormatter (%%physloc%%) as loc) a 
     cross apply (select charindex(':', loc) as first)a1
     cross apply (select charindex(':', loc, first + 1) as second)a2
     cross apply (select cast(SUBSTRING(loc, 2, first - 2) as int)as file_,
       cast(SUBSTRING(loc, first + 1, second - 4) as bigint)as page_,
       cast(SUBSTRING(loc, second + 1, len(loc) - second - 1)as int) as slot_)a4
order by a4.file_, a4.page_, a4.slot_;

with cte as
(
select id, GroupID, Value,
       row_number() over(order by id) - row_number() over(order by GroupID, id) as grp 
from #t
)

,cte1 as
(
select GroupID, grp, sum(Value) as Value, min(id) as id 
from cte
group by GroupID, grp
)

select GroupID, Value
from cte1
order by id;

    %%physloc%%是一个物理记录定位器函数，您可以在这里阅读：SQL Server 2008：新的（未记录的）物理行定位器函数

    更新：

    正如 ypercubeᵀᴹ 所建议的那样，排序依据%%physloc%%不正确，我们需要提取文件，分页一个插槽并按它们排序

    当表中有删除时会发生什么？我们不能假设之后的插入物会获得更高的物理 RID，因为它们可能会填补空白

    这个问题应该问OP，而不是我。我的解决方案是针对原始帖子中介绍的静态堆。

    如果作者知道这个堆可能会改变，他不应该将这个堆复制到带有标识列的临时表中，而是复制到永久表中。

    • 1

相关问题