如何在SQL中按多列将连续行分组

Question

我可以翻译中文，以下是您需要翻译的内容：

我有一个查询结果包含多行数据，例如：

Date        User    Time    Location    Service     Count
1/1/2018    Nick    12:00   Location A  X           1
1/1/2018    Nick    12:01   Location A  Y           1
1/1/2018    John    12:02   Location B  Z           1
1/1/2018    Harry   12:03   Location A  X           1
1/1/2018    Harry   12:04   Location A  X           1
1/1/2018    Harry   12:05   Location B  Y           1
1/1/2018    Harry   12:06   Location B  X           1
1/1/2018    Nick    12:07   Location A  X           1
1/1/2018    Nick    12:08   Location A  Y           1

这个查询返回了一个特定用户访问过的位置以及从该位置提取的次数。结果按照用户和时间升序排序。我需要对结果进行分组，使得连续出现的具有相同“用户”和“位置”的行会被聚合，并且“计数”列会求和，“服务”列中唯一值的列表将用逗号分隔，最终结果如下：

Date        User    Start Time  End Time    Location    Service Count
1/1/2018    Nick    12:00       12:01       Location A  X,Y     2
1/1/2018    John    12:02       12:02       Location B  Z       1
1/1/2018    Harry   12:03       12:04       Location A  X       2
1/1/2018    Harry   12:05       12:06       Location B  X,Y     2
1/1/2018    Nick    12:07       12:08       Location A  X,Y     2

我不确定从哪里开始。也许是延迟或分区子句？希望SQL专家能在这里提供帮助...

- Niko

---

你能同时展示你的尝试吗？ - Vamsi Prabhala

Answer 1

这是一个间隔与岛问题。解决它的一种方法是使用row_number()函数：

select Date, User, min(Time) as start_time, max(time) as end_time,
       Location,
       listagg(Service, ',') within group (order by service),     
       count(*) as cnt
from (select t.*,
             row_number() over (date order by time) as seqnum,
             row_number() over (partition by user, date, location order by time) as seqnum_2
      from t
     ) t
group by Date, User, Location, (seqnum - seqnum_2);

这个有点难以解释。我的建议是运行子查询，你会看到行号的差异如何定义你所寻找的组。

- Gordon Linoff

Answer 2

使用lag函数获取前一行的用户和位置信息，然后使用运行总和函数，在用户和位置发生变化时生成新的分组。最后根据分类的分组、用户、位置和日期进行聚合。

select Date, User, min(Time) as start_time,max(time) as end_time, Location,
listagg(Service, ',') within group (order by Service),
count(*) as cnt
from (select Date, User, Time, Location, 
      sum(case when prev_location=location and prev_user=user then 0 else 1 end) over(order by date,time) as grp
      from (select Date, User, Time, Location,
            lag(Location) over(order by date,time) as prev_location,
            lag(User) over(order by date,time) as prev_user,
            from t
           ) t
      ) t
group by Date, User, Location, grp;

- Vamsi Prabhala

---

谢谢，经过一些修改和调整，我已经成功地让它与上面的例子一起工作了。 - Niko