在 SQL 中按分区分组或遍历分区 [英] Grouping by or iterating through partitions in SQL

问题描述

关于 SQL 分区的两部分问题.

Two part question regarding partitioning in SQL.

在 T-SQL 中，当您使用 PARTITION BY 时，除了 row_number() 之类的东西之外，有没有办法为每个分区分配一个唯一编号?

In T-SQL when you use PARTITION BY is there a way to assign a unique number to each partition, in addition to something like row_number()?

例如row_number() 会产生，

E.g. row_number() would yield,

Action          Timestamp           RowNum
A               '2013-1-10'         1
A               '2013-1-11'         2
B               '2013-1-12'         1
B               '2013-1-13'         2

另外，唯一标识每个分区可以产生，

Whereas, in addition, uniquely identifying each partition could yield,

Action          Timestamp           RowNum          PartitionNum
A               '2013-1-10'         1               1
A               '2013-1-11'         2               1
B               '2013-1-12'         1               2
B               '2013-1-13'         2               2

然后可以按分区号进行分组.

Then one could GROUP BY partition number.

我的问题的第二部分是，你如何打破每个分区并遍历它，例如，

Second part of my question is, how can you break out each partition and iterate through it, e.g.,

for each partition p
   for each row r in p 
       do F(r) 

T-SQL 中有什么方法吗?

Any way in T-SQL?

推荐答案

你可以使用 dense_rank():

select  *
,       row_number() over (partition by Action order by Timestamp) as RowNum
,       dense_rank() over (order by Action) as PartitionNum
from    YourTable

SQL Fiddle 示例.

T-SQL 不擅长迭代，但如果你真的需要，请查看 光标.

T-SQL is not good at iterating, but if you really have to, check out cursors.

这篇关于在 SQL 中按分区分组或遍历分区的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！