多个组通过保持他们的订单

Question

让我们考虑这个例子：

Clients   Routes   City   Timestamp
1         10        NY       0
1         11        NY       10
1         12        WDC      11    
1         13        NY       20
2         22        LA       15

我想要的输出是这样的：

Clients   Routes_number    City   min(Timestamp)
1         2                NY       0
1         1                WDC      11    
1         1                NY       20
2         1                LA       15

这里的想法是，我必须通过遵守他们的订单来做多个组。例如，如果我们看到客户 1 的城市，我们可以理解他从纽约 -> WDC -> 纽约(在同一天(旅行。所以这个想法就像做一个小组，计算routes_number和最小时间戳，但每次找到一个新城市时它都会停止。如果我做一个全局组，我会得到这样的东西：

Clients   Routes_number    City   min(Timestamp)
1         3                NY       0
1         1                WDC      11    
2         1                LA       15

有了这样的输出，我们丢失了我们拥有 NY-> WDC 和 AGAIN NY 的信息。我们以为他只做纽约 ->WDC以一种方式......

我甚至不知道是否可以使用SQL来做这样的请求，或者我是否必须在我的代码中做(我是Spark和Scala的新手，但Scala是我使用的语言(。

谢谢！

Answer 1

这是一个间隙和孤岛问题，可以通过行号的差异来解决：

select client, city, count(*), min(timestamp)
from (select t.*,
row_number() over (partition by client, city order by timestamp) as seqnum_1,
row_number() over (partition by client order by timestamp) as seqnum_2
from t
) t
group by client, city, (seqnum_2 - seqnum_1);

这是一个数据库<>小提琴。

要了解行号的差异如何识别具有相同城市值的相邻行可能很棘手。 如果您查看子查询的结果，您将很好地了解它的工作原理。