使用SQL计算连续重复记录

Question

我有一个数据分析问题，可以很容易地通过T-SQL或脚本解决，但我想知道是否有巧妙的SQL解决方案。问题是它在某种程度上会影响SQL的行独立性假设。

我有一个表，其中包含与用户相关联的名称-值对，按提交顺序排序，例如：

ID USERID VARIABLE VALUE SUBMITTED 3115 2287 votech05 2 2009-02-02 15:34:00 3116 2287 comcol05 1 2009-02-02 15:34:00 3117 2287 fouryr05 1 2009-02-02 15:35:00 3118 2287 none05 2 2009-02-02 15:35:00 3119 2287 ocol1_05 2 2009-02-02 15:44:00 3120 2287 disnone 2 2009-02-02 15:45:00 3121 2287 dissense 2 2009-02-02 15:49:00 3122 2287 dismobil 3 2009-02-02 15:51:00 3123 2287 dislearn 3 2009-02-02 15:51:00 3124 2287 disment 3 2009-02-02 15:52:00 3125 2287 disother 2 2009-02-02 15:55:00 3126 2287 disrefus 7 2009-02-02 15:58:00

我想确定最大相同值组的值和计数（当数据按照ID主键排序时）。因此，对于上面的示例，因为我有四个连续出现的value = 2，而只有三个value = 3，所以我希望报告：

USERID VALUE COUNT 2287 2 4

给定用户。

再次强调，使用其他工具可以很快地完成这项工作，但由于数据集非常大（约7500万条记录）且经常更改，因此最好能够使用查询解决此问题。我正在使用SQL Server 2005。

- Jason Francis

---

好的标题编辑，Brian。谢谢。我为一位调查研究员工作，他的行话影响了我对问题的思考。 - Jason Francis

Answer 1

(评论后编辑)

您可以通过为每个连续值组分配一个“头”编号来实现。然后，选择每行的头编号，并对每个头进行聚合。

以下是一个使用CTE提高可读性的示例:

WITH
OrderedTable as (
    select value, rownr = row_number() over (order by userid, id)
    from YourTable
    where userid = 2287
),
Heads as (
    select cur.rownr, CurValue = cur.value
    ,   headnr = row_number() over (order by cur.rownr)
    from OrderedTable cur
    left join OrderedTable prev on cur.rownr = prev.rownr+1 
    where IsNull(prev.value,-1) != cur.value
),
ValuesWithHead as (
    select value
    ,   HeadNr = (select max(headnr) 
                from Heads 
                where Heads.rownr <= data.rownr)
    from OrderedTable data
)
select Value, [Count] = count(*)
from ValuesWithHead
group by HeadNr, value
order by count(*) desc

这将输出：

Value   Count
2       4
3       3
1       2
2       1
2       1
7       1

使用 "top 1" 只选择第一行。

以下是创建测试数据的查询：

create table YourTable (
    id int primary key,
    userid int,
    variable varchar(25),
    value int
)
insert into YourTable (id, userid, variable, value) values (3115, 2287, 'votech05', 2)
insert into YourTable (id, userid, variable, value) values (3116, 2287, 'comcol05', 1)
insert into YourTable (id, userid, variable, value) values (3117, 2287, 'fouryr05', 1)
insert into YourTable (id, userid, variable, value) values (3118, 2287, 'none05', 2)
insert into YourTable (id, userid, variable, value) values (3119, 2287, 'ocol1_05', 2)
insert into YourTable (id, userid, variable, value) values (3120, 2287, 'disnone', 2)
insert into YourTable (id, userid, variable, value) values (3121, 2287, 'dissense', 2)
insert into YourTable (id, userid, variable, value) values (3122, 2287, 'dismobil', 3)
insert into YourTable (id, userid, variable, value) values (3123, 2287, 'dislearn', 3)
insert into YourTable (id, userid, variable, value) values (3124, 2287, 'disment', 3)
insert into YourTable (id, userid, variable, value) values (3125, 2287, 'disother', 2)
insert into YourTable (id, userid, variable, value) values (3126, 2287, 'disrefus', 7)

- Andomar

---

不完全准确，因为我不想要每个值的总计数，只是它们如何聚集在一起，即2,1,2,2,1,1,2,2,2,2,1,1会返回value=2，count=4，而不是7。 - Jason Francis

这看起来很有前途。让我看看它。如果可能的话，我宁愿不使用游标（尽管对于这些相互依赖的问题，使用CTE可能会得到相同的性能）。谢谢。 - Jason Francis

Answer 2

这可能是最好通过游标解决的问题。试试这个方法。它应该很接近，但没有经过测试，因为您没有提供包含示例数据的CREATE TABLE和INSERT语句，这使得测试变得困难。

declare @userid int
set @userid = 2287;
declare C cursor fast_forward for
select VALUE from T
where USERID = @userid
order by ID;

declare @value int, @prevvalue int;
declare @runcount int, @runlongest int;
set @runlongest = 0;
declare @valuelongest int;
open C;
fetch next from C into @value;
while @@fetch_status = 0 begin
  if @value = @prevvalue set @runcount = @runcount + 1 else set @runcount = 1;
  if @runcount > @runlongest begin
    set @runlongest = @runcount;
    set @valuelongest = @value;
  end;
  set @prevvalue = @value;
  fetch next from C into @value;
end;
select @userid as USERID, @valuelongest as VALUE, @runlongest as [COUNT];

close C;
deallocate C;

如果有7500万行数据，速度可能不会很快，但也不会太慢。如果你的运行时间非常长，并且你有正确的索引，你可以通过在临时表中使用row_number对行进行编号，然后使用一个WHILE循环一次跳过一个运行来提高性能。如果你认为这值得一试，请让我知道（如果可能的话，请附带CREATE TABLE和INSERT语句以及示例数据）。

- Steve Kass

Answer 3

没有经过测试，我认为以下的代码可以实现：

Row_number() over (partition by userid, value order by id)

运行以上代码后，只需要选择最高的 row_number 即可。

请告诉我这个方案是否可行！

谢谢，Edi

- eschlech

---

Edi，row_number（）无法工作，因为它将像非连续值一样处理连续值。这里的问题是值的顺序，而不仅仅是有多少个值。 - Steve Kass

这是不是意味着没有顺序可以定义？抱歉，我不明白。 - eschlech