通过切换行和行的差异进行分组(group by toggling Rows and difference in rows)

我的数据如下：

Date time Switch Water level 2015/08/07 09:00:00 1 7 2015/08/07 09:05:00 1 7 2015/08/07 09:10:00 1 6 2015/08/07 09:15:00 1 5 2015/08/07 09:20:00 1 5 2015/08/07 12:00:00 0 5 2015/08/07 15:00:00 0 5 2015/08/07 18:00:00 1 5 2015/08/07 18:05:00 1 4 2015/08/07 18:10:00 1 3 2015/08/07 18:15:00 1 2 2015/08/07 21:00:00 0 2 2015/08/08 00:00:00 0 2

我想在'switch'栏中按不同的1和0组分组数据，我也希望找到组内水位的差异。结果应该是

switch decrease in water level 1 2 0 0 1 3 0 0

我的第一优先级是按Switch列分组。第二要务是找出差异。如果不可能忽略第二优先级，但请帮我分组切换列中不同的1和0组。如果有人能帮助我这样做会很棒。

session Start Time End Time decrease_in_level 1 2015/08/07 09:00:00 2015/08/07 09:20:00 2 2 2015/08/07 18:00:00 2015/08/07 18:15:00 3

I have data as follows:

I want to group data by different sets of 1 and 0 in 'switch' column also I want to find difference of water level within group. the result should be

switch decrease in water level 1 2 0 0 1 3 0 0

My 1st Priority is to group by Switch column. 2nd priority is to find the difference. If it is not possible than ignore the 2nd priority but please help me way to group by different sets of 1 and 0 in switch column. It would be awsome if some could help me do this.

session Start Time End Time decrease_in_level 1 2015/08/07 09:00:00 2015/08/07 09:20:00 2 2 2015/08/07 18:00:00 2015/08/07 18:15:00 3

最满意答案

这是一个间隙和岛屿问题，解决它的一种方法是使用一些row_number技巧识别感兴趣的岛屿，一旦完成，我们可以应用聚合函数（min和max）来获取每个分区中的第一个和最后一个值。

这应该工作：

with c as ( select * , grp = row_number() over (order by datetime) - row_number() over (partition by switch order by datetime) from water ) select session = row_number() over(order by min(datetime)), [start time] = min(datetime), [end time] = max(datetime), decrease_in_level = max(waterlevel) - min(waterlevel) from c where switch = 1 group by grp order by min(datetime)

在查询中，我调用了表水，输出，给定您的示例数据，是：

| Session | Start Time | End Time | decrease_in_level | |---------|--------------------------|--------------------------|-------------------| | 1 | August, 07 2015 09:00:00 | August, 07 2015 09:20:00 | 2 | | 2 | August, 07 2015 18:00:00 | August, 07 2015 18:15:00 | 3 |

示例SQL小提琴

This is a gaps-and-island problem and one way to solve it is to identify the islands of interest using some row_number trickery and once that's done we can apply aggregate functions (min and max) to get the first and last values in each partition.

This should work:

In the query I called you table water and the output, given your sample data, is:

Sample SQL Fiddle

更多推荐