时间戳

首页 > 解决方案 > 在值随组更改之前找到的行数

sql - 在值随组更改之前找到的行数

问题描述

我有一张像这样的桌子

CREATE TABLE Levels
    ([userid] int, [counter1] int, [counter2] int, [date] datetime)
;

是counter2一个增量值。这date只是创建该行的日期时间。这counter1是一个可以采用不同整数值的字段。以及userid用户的id。

这是数据的一个例子。您可以在sqlfiddle中找到两个用户的更大示例

| userid | counter1 | counter2 |                 date |
|--------|----------|----------|----------------------|
|    123 |        6 |       42 | 2010-07-31T00:12:28Z |
|    123 |        6 |       43 | 2010-11-20T00:11:15Z |
|    123 |        6 |       44 | 2011-03-12T00:15:07Z |
|    123 |        5 |       45 | 2011-07-02T01:11:09Z |
|    123 |        5 |       46 | 2011-10-22T00:24:18Z |
|    123 |        5 |       47 | 2012-02-10T23:51:54Z |
|    123 |        5 |       48 | 2012-06-01T23:43:26Z |
|    123 |        5 |       49 | 2012-09-21T23:43:59Z |
|    123 |        4 |       50 | 2013-01-11T23:52:43Z |
|    123 |        4 |       51 | 2013-05-03T23:49:25Z |
|    123 |        4 |       52 | 2013-08-23T23:48:24Z |
|    123 |        3 |       53 | 2013-12-14T00:01:20Z |
|    123 |        3 |       54 | 2014-04-04T23:45:45Z |
|    123 |        4 |       55 | 2014-07-25T23:44:34Z |
|    123 |        5 |       56 | 2014-11-14T23:46:11Z |

我试图做的是计算在它改变之前有多少次counter1具有相同的值。为什么我在 stackoverflow 中找到的其他问题都不起作用？

sqlfiddle 中完整示例的所需结果是

| userid | counter1 | count |
|--------|----------|-------|
|     123|         6|      3|
|     123|         5|      5|
|     123|         4|      3|
|     123|         3|      2|
|     123|         4|      1|
|     123|         5|      1|
|     123|         6|      2|
|     123|         5|      5|
|     123|         4|      2|
|     123|         5|      1|
|     123|         4|      5|
|     123|         5|      5|
|     345|         6|      2|
|     345|         6|      9|

标签： sqlsql-servertsqlsql-server-2008

解决方案

这是一种差距和孤岛问题。幸运的是，您可以使用行号的差异：

select userid, counter1, count(*)
from (select t.*,
             row_number() over (partition by userid order by counter2) as seqnum,
             row_number() over (partition by userid, counter1 order by counter2) as seqnum_2
      from t
     ) t
group by userid, counter1, (seqnum - seqnum_2)
order by userid, min(counter2);

注意：这里假设排序基于counter2. 如果它确实基于，date那么您可以改用该列。

为什么这行得通有点难以解释。但是，如果您查看子查询的结果，您将看到当相邻行上的值相同时，两个row_number()值之间的差异是如何保持不变的。counter1

推荐阅读