时间戳

首页 > 解决方案 > 从表中确定“最长的 X 条纹”（SQL Server 2018）

sql - 从表中确定“最长的 X 条纹”（SQL Server 2018）

问题描述

我有一个表格，其中数据的格式为

+---------------------+-------------------------------------+--------+
|     Start Time      |                Name                 | Result |
+---------------------+-------------------------------------+--------+
| 19/09/2018 00:02:06 | Kevin Jones                         | WIN    |
| 19/09/2018 00:21:44 | Claire Miley                        | LOSE   |
| 19/09/2018 01:07:54 | Claire Miley                        | FISH   |
| 19/09/2018 01:16:40 | Kevin Jones                         | WIN    |
| 19/09/2018 01:14:01 | Kevin Jones                         | RED    |
| 19/09/2018 01:11:12 | Claire Miley                        | LOSE   |
| 19/09/2018 01:28:37 | Claire Miley                        | LOSE   |
| 19/09/2018 01:09:42 | Kevin Jones                         | LOSE   |
| 19/09/2018 01:05:50 | Kevin Jones                         | WIN    |
| 19/09/2018 01:29:52 | Martyn Jones                        | BLUE   |
| 19/09/2018 01:01:01 | Claire Miley                        | RED    |
| 19/09/2018 01:00:21 | Claire Miley                        | WIN    |
| 19/09/2018 00:57:01 | Kevin Jones                         | SOCK   |
| 19/09/2018 01:39:52 | Margaret Weir                       | WIN    |
| 19/09/2018 00:54:06 | Margaret Weir                       | CALL   |
| 19/09/2018 00:53:16 | Martyn Jones                        | LOSE   |
| 19/09/2018 00:50:47 | Kevin Jones                         | FREE   |
| 19/09/2018 00:48:40 | Martyn Jones                        | WIN    |
| 19/09/2018 00:30:14 | Claire Miley                        | WIN    |
| 19/09/2018 00:25:09 | Kevin Jones                         | LOSE   |
| 19/09/2018 02:07:20 | Margaret Weir                       | FISH   |
| 19/09/2018 02:07:30 | Martyn Jones                        | WIN    |
| 19/09/2018 02:09:03 | Kevin Jones                         | RED    |
| 19/09/2018 00:24:11 | Claire Miley                        | LOSE   |
| 19/09/2018 00:17:42 | Kevin Jones                         | LOSE   |
| 19/09/2018 02:15:21 | Kevin Jones                         | LOSE   |
| 19/09/2018 02:19:10 | Martyn Jones                        | WIN    |
| 19/09/2018 00:15:49 | Martyn Jones                        | BLUE   |
| 19/09/2018 00:14:25 | Kevin Jones                         | RED    |
| 19/09/2018 00:11:03 | Kevin Jones                         | WIN    |
| 19/09/2018 00:10:10 | Claire Miley                        | SOCK   |
| 19/09/2018 00:09:56 | Martyn Jones                        | WIN    |
| 19/09/2018 00:05:24 | Martyn Jones                        | CALL   |
| 19/09/2018 00:03:01 | Margaret Weir                       | LOSE   |
| 19/09/2018 00:01:17 | Martyn Jones                        | FREE   |
| 19/09/2018 01:18:46 | Kevin Jones                         | WIN    |
| 19/09/2018 01:23:21 | Margaret Weir                       | WIN    |
| 19/09/2018 01:28:51 | Kevin Jones                         | BLUE   |
| 19/09/2018 01:29:34 | Kevin Jones                         | RED    |
| 19/09/2018 01:07:54 | Claire Miley                        | WIN    |
+---------------------+-------------------------------------+--------+

还有更多对问题不重要的列。我想弄清楚谁目前拥有最长的连胜成绩，而其中没有一个是 WIN 结果，以及该连胜持续多长时间。这是我可以在 SQL 中做的事情，还是最好在将原始数据提取到另一个程序后做？

在这个示例数据中，我想要的结果是

Kevin Jones

5 results in a row without a WIN result

感谢马丁史密斯

标签： sqlsql-server

解决方案

应该这样做（演示）

WITH T
     AS (SELECT *,
                ROW_NUMBER()
                  OVER (
                    PARTITION BY [Name]
                    ORDER BY [Start Time]) - ROW_NUMBER()
                                               OVER (
                                                 PARTITION BY [Name], Win
                                                 ORDER BY [Start Time]) AS Grp
         FROM   Table1
                CROSS APPLY (SELECT IIF([Result] = 'WIN', 1, 0))CA(Win))
SELECT TOP 1 [Name],
             count(*),
             MIN([Start Time]) AS StreakStart,
             MAX([Start Time]) AS StreakEnd
FROM   T
WHERE  Win = 0
GROUP  BY [Name],
          Grp
ORDER  BY count(*) DESC

推荐阅读