sql - SQL计算连续重复记录

Question

我有一个数据分析问题，我可以用一些 T-SQL 或一些脚本轻松解决，但我想知道是否有一个聪明的 SQL 解决方案。问题是它与 SQL 的行独立假设有点困惑。

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

ID      USERID  VARIABLE        VALUE   SUBMITTED3115    2287    votech05    2   2009-02-02 15:34:003116    2287    comcol05    1   2009-02-02 15:34:003117    2287    fouryr05    1   2009-02-02 15:35:003118    2287    none05          2   2009-02-02 15:35:003119    2287    ocol1_05    2   2009-02-02 15:44:003120    2287    disnone         2   2009-02-02 15:45:003121    2287    dissense    2   2009-02-02 15:49:003122    2287    dismobil    3   2009-02-02 15:51:003123    2287    dislearn    3   2009-02-02 15:51:003124    2287    disment         3   2009-02-02 15:52:003125    2287    disother    2   2009-02-02 15:55:003126    2287    disrefus    7   2009-02-02 15:58:00

我希望能够确定最大一组相同值的值和计数(当数据按 ID 主键排序时)。所以，对于上面的例子，因为我有四个 value=2 依次出现，而只有三个 value=3，所以我想报告:

USERID     VALUE      COUNT2287       2          4

对于给定的用户。

同样，这可以使用其他工具相当快速地完成，但是由于数据集非常大(大约 7500 万条记录)并且经常变化，所以能够通过查询解决这个问题会很好。我正在使用 SQL Server 2005。

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)