我有诸如的数据，
ID time
1 9/6/2016 00:01:00
1 9/6/2016 00:01:30
1 9/6/2016 00:02:00
1 9/6/2016 00:04:30
1 9/6/2016 00:05:30
1 9/6/2016 01:05:30
1 9/6/2016 05:05:30
1 9/6/2016 05:06:30
2 9/6/2016 01:55:00
2 9/6/2016 01:56:29
2 9/6/2016 01:57:31
2 9/6/2016 03:55:00
2 9/6/2016 04:13:00
2 9/6/2016 04:15:21
对于每个ID，我想将一个称为flag的新变量设置为1并检查时间的第一个值。从时间的第一个值开始，我要检查距第一个条目3分钟以内的条目，并将所有内容设置为1。一旦时间条目超过3分钟，我想将flag变量设置为2，然后再次设置请在该时间后的3分钟内检查输入内容，然后继续对每个ID进行操作。我想为每个ID找到3分钟的分组，以便为每个ID形成表格。
我想要的输出是
ID time flag
1 9/6/2016 00:01:00 1
1 9/6/2016 00:01:30 1
1 9/6/2016 00:02:00 1
1 9/6/2016 00:04:30 2
1 9/6/2016 00:05:30 2
1 9/6/2016 01:05:30 2
1 9/6/2016 05:05:30 2
1 9/6/2016 05:06:30 2
2 9/6/2016 01:55:00 1
2 9/6/2016 01:56:29 1
2 9/6/2016 01:57:31 1
2 9/6/2016 03:55:00 2
2 9/6/2016 04:13:00 3
2 9/6/2016 04:15:21 3
对于ID 1，标志设置为1，并一直检查3分钟条目，直到第3行为止；一旦它超过3分钟，它就设置为2，并再次开始检查3分钟条目。同样对于ID 2。
以下是我尝试过的
select ID, time, rank() over (order by time) as rank from table_name;
这是整个表的排名。我在想，我们可以为每个ID排序，然后调用第一个值，然后用剩余值减去它，然后在此处编写子查询。
有没有更好的有效方法来做到这一点？我在这里使用Hive查询。任何帮助，将不胜感激。
最佳答案
请注意，您的样本输出不正确；对于ID = 1，时间= 01:05:30是00:05:30之后的整整一个小时，但是两者的标记相同。
这是专门使用Oracle SQL的解决方案。检查“标志转换”条件；按照我写的方式，只有在经过严格超过3分钟后，才会启动新标记。如果要在刚过3分钟后开始新计数，请将第一个不等式更改为non-strict，将第二个不等式更改为严格。
解决方案*使用递归分解子查询，因此需要Oracle 11.2或更高版本。
*注意:从逻辑上讲，我不必在其中减去3 /(24 * 60)；相反，不等式应该与3 /(24 * 60)进行比较。这在Oracle 12中有效，但使用ORA-00600导致Oracle 11.2上的数据库崩溃，有人告诉我这是Oracle在11.2(在12.1中已修复)的递归查询实现中的一个已知错误的标记。我是非付费客户，所以我自己无法访问错误信息。我测试了下面在Oracle 11.2上发布的查询，该查询可以运行，而简化版本崩溃了。两种版本在12.1上都能正常工作。
with
inputs ( id, time ) as (
select 1, to_date('9/6/2016 00:01:00', 'mm/dd/yyyy hh24:mi:ss') from dual union all
select 1, to_date('9/6/2016 00:01:30', 'mm/dd/yyyy hh24:mi:ss') from dual union all
select 1, to_date('9/6/2016 00:02:00', 'mm/dd/yyyy hh24:mi:ss') from dual union all
select 1, to_date('9/6/2016 00:04:30', 'mm/dd/yyyy hh24:mi:ss') from dual union all
select 1, to_date('9/6/2016 00:05:30', 'mm/dd/yyyy hh24:mi:ss') from dual union all
select 1, to_date('9/6/2016 01:05:30', 'mm/dd/yyyy hh24:mi:ss') from dual union all
select 1, to_date('9/6/2016 05:05:30', 'mm/dd/yyyy hh24:mi:ss') from dual union all
select 1, to_date('9/6/2016 05:06:30', 'mm/dd/yyyy hh24:mi:ss') from dual union all
select 2, to_date('9/6/2016 01:55:00', 'mm/dd/yyyy hh24:mi:ss') from dual union all
select 2, to_date('9/6/2016 01:56:29', 'mm/dd/yyyy hh24:mi:ss') from dual union all
select 2, to_date('9/6/2016 01:57:31', 'mm/dd/yyyy hh24:mi:ss') from dual union all
select 2, to_date('9/6/2016 03:55:00', 'mm/dd/yyyy hh24:mi:ss') from dual union all
select 2, to_date('9/6/2016 04:13:00', 'mm/dd/yyyy hh24:mi:ss') from dual union all
select 2, to_date('9/6/2016 04:15:21', 'mm/dd/yyyy hh24:mi:ss') from dual
),
rec ( id, time, flag, time_diff ) as (
select id, time, 1,
time - min(time) over (partition by id order by time) - 3/(24*60)
from inputs
union all
select id, time, flag + 1,
time - min(time) over (partition by id order by time) - 3/(24*60)
from rec
where time_diff > 0
)
select id, time, flag
from rec
where time_diff <= 0
order by id, time
;
输出:
ID TIME FLAG
---- ------------------- ----------
1 06/09/2016 00:01:00 1
1 06/09/2016 00:01:30 1
1 06/09/2016 00:02:00 1
1 06/09/2016 00:04:30 2
1 06/09/2016 00:05:30 2
1 06/09/2016 01:05:30 3
1 06/09/2016 05:05:30 4
1 06/09/2016 05:06:30 4
2 06/09/2016 01:55:00 1
2 06/09/2016 01:56:29 1
2 06/09/2016 01:57:31 1
2 06/09/2016 03:55:00 2
2 06/09/2016 04:13:00 3
2 06/09/2016 04:15:21 3
14 rows selected