时间戳

首页 > 解决方案 > 硬币翻转 - 差距和孤岛问题 MySQL

mysql - 硬币翻转 - 差距和孤岛问题 MySQL

问题描述

这就是问题：每个员工一整天都在抛硬币，记录每次抛硬币的结果。附加的文本文件显示了所有掷硬币的综合结果。找出每个员工他们最长的不间断的头脑是什么。

08:12:16,Gavin,T
08:12:31,Amnon,T
08:12:37,Abe,T
08:12:42,Nandi,T
08:12:49,Gavin,H
08:13:09,Megan,H
08:13:28,Amnon,H
08:13:34,Jessica,H
08:13:35,Henk,H
08:13:52,Claire,H
08:14:04,Gertrude,T
08:14:19,Gavin,T
08:14:32,Megan,H
08:14:35,Amnon,T
08:14:49,Philip,H
08:14:50,Gertrude,T
08:14:59,Henk,T
08:15:05,Steven,T
08:15:24,Gavin,H
08:15:27,Gertrude,T
08:15:41,Megan,H
08:15:46,Kylen,H
08:15:57,Steven,H
08:16:01,Gavin,T
08:16:11,Steven,T
08:16:28,Philip,H
08:16:45,Pierre,T
08:17:07,Amnon,T
08:17:09,Abe,T
08:17:26,Jessica,T
08:17:30,Henk,H
08:17:34,Kylen,H
08:17:54,Andrew,T
08:18:04,Jessica,H
08:18:24,Amnon,H
08:18:30,Vuli,H
08:18:35,Jessica,T
08:18:51,Reut,H
08:18:52,Kylen,T
08:18:53,Gavin,H
08:19:11,Henk,T
08:19:17,Helen,T
08:19:33,Claire,H
08:19:41,Gertrude,T
08:19:42,Illana,H
08:19:55,Amnon,H
08:20:00,Henk,H
08:20:09,Pierre,H
08:20:24,Pierre,H
08:20:36,Maryann,T
08:20:51,Megan,T
08:21:05,Amnon,T
08:21:09,Pierre,T
08:21:28,Reut,T
08:21:41,Illana,T
08:21:53,Gertrude,T
08:21:54,Gavin,H
08:22:08,Vuli,T
08:22:10,Gertrude,T
08:22:12,Henk,T
08:22:25,Amnon,T
08:22:37,Reut,T
08:22:58,Helen,H
08:23:05,Steven,T
08:23:06,Gavin,T
08:23:23,Gertrude,H
08:23:35,Gavin,T
08:23:57,Andrew,H
08:24:00,Claire,H
08:24:03,Reut,H
08:24:22,Abe,T
08:24:32,Helen,H
08:24:35,Andrew,T
08:24:50,Reut,T
08:24:52,Maryann,T
08:25:00,Gertrude,T
08:25:09,Gertrude,T
08:25:29,Steven,H
08:25:47,Gertrude,H
08:26:06,Steven,H
08:26:12,Helen,H
08:26:24,Kylen,T
08:26:44,Megan,T
08:26:56,Jessica,H
08:26:58,Andrew,H
08:27:19,Amnon,T
08:27:24,Pierre,H
08:27:43,Nandi,T
08:28:00,Helen,H
08:28:15,Gertrude,H
08:28:23,Pierre,H
08:28:31,Henk,H
08:28:42,Claire,H
08:28:43,Philip,T
08:28:44,Helen,T
08:29:03,Illana,H
08:29:05,Philip,T
08:29:07,Philip,H
08:29:08,Nandi,H
08:29:24,Nandi,T

我认识到这是一个差距和孤岛问题，但我遇到了问题我将问题减少到只有一个用户，并通过此查询创建连续的正面或反面序列

SELECT time_of_flip, flip_result,
       ROW_NUMBER() over (ORDER BY time_of_flip) AS continues_seq,
       ROW_NUMBER() over (Partition BY flip_result ORDER BY time_of_flip) AS seq_by_value
FROM coin_flipping
where flipper = 'Abe'
order by time_of_flip ASC

这是结果

08:12:37,T,1,1
08:17:09,T,2,2
08:24:22,T,3,3
08:35:12,H,4,1
08:45:27,H,5,2
08:58:59,H,6,3
08:59:16,T,7,4
09:00:06,H,8,4
09:01:36,H,9,5
09:01:49,T,10,5
09:17:09,T,11,6
09:17:15,T,12,7
09:22:26,H,13,6
09:23:12,H,14,7
09:27:41,T,15,8
09:27:59,T,16,9
09:30:41,T,17,10
09:31:07,H,18,8
09:32:26,T,19,11
09:36:19,H,20,9
09:38:54,H,21,10
09:41:25,H,22,11
09:45:22,T,23,12
09:49:43,T,24,13
09:52:09,T,25,14
09:53:03,H,26,12
09:57:44,H,27,13
10:04:39,T,28,15
10:17:11,H,29,14
10:18:15,T,30,16

但我不知道如何继续为一名工作人员获取结果，然后为所有工作人员获取结果

标签： mysqlsql

解决方案

您可以为此使用行号的差异。获取所有序列：

SELECT flipper, result, COUNT(*) as num_heads
FROM (SELECT time_of_flip, flip_result,
             ROW_NUMBER() OVER (PARTITION BY flipper ORDER BY time_of_flip) AS continues_seq,
             ROW_NUMBER() OVER (PARTITION BY flipper, flip_result ORDER BY time_of_flip) AS seq_by_value
      FROM coin_flipping
     ) cf
WHERE result = 'H'
GROUP BY flipper, (continues_seq - seq_by_value);

这里的关键思想 - 相对于您的查询 - 是partition by鳍状肢然后聚合。

然后使用额外的子查询来获得每人的最大值：

SELECT slipper, MAX(num_heads)
FROM (SELECT flipper, result, COUNT(*) as num_heads
      FROM (SELECT time_of_flip, flip_result,
                   ROW_NUMBER() OVER (PARTITION BY flipper ORDER BY time_of_flip) AS continues_seq,
                   ROW_NUMBER() OVER (PARTITION BY flipper, flip_result ORDER BY time_of_flip) AS seq_by_value
            FROM coin_flipping
           ) cf
      WHERE result = 'H'
      GROUP BY flipper, (continues_seq - seq_by_value)
     ) f
GROUP BY flipper;

推荐阅读