根据以前的组计算唯一条目

Trying to find unique values in each group, however with a look back at the previously grouped items. It will be group by time, so if previous time block had the unique value it should not appear in the next time block. Lookback should span all previous time blocks. So at time 2, it looks at time 0 and 1, while at time 10 it looks back at time 0 to 9.

尝试在每个组中查找唯一值,但回顾先前分组的项目。它将按时间分组,因此如果前一个时间块具有唯一值,则它不应出现在下一个时间块中。回顾应该跨越所有以前的时间块。因此,在时间2,它查看时间0和1,而在时间10,它回顾时间0到9。

I am also looking to do this dynamically, without manually offsetting each time block with a subquery, as time here is continuous and not discrete data set.

我也希望动态地执行此操作,而无需使用子查询手动抵消每个时间块,因为此处的时间是连续的而不是离散数据集。

Sample data:

2018-03-25 00:00:00.000, 123
2018-03-25 00:00:00.000, 231
2018-03-26 00:00:00.000, 234
2018-03-26 00:00:00.000, 123
2018-03-27 00:00:00.000, 123
2018-03-27 00:00:00.000, 231
2018-03-27 00:00:00.000, 234
2018-03-27 00:00:00.000, 432

Sample output:

2018-03-25 00:00:00.000, 2
2018-03-26 00:00:00.000, 1
2018-03-27 00:00:00.000, 1

1 个解决方案

#1

If I got you right, you can consider that if the value exists in any past group, it should be excluded from the results set.

如果我找到了你,你可以考虑如果该值存在于任何过去的组中,它应该从结果集中排除。

I think this kind of approach should help you:

我认为这种方法可以帮助你:

select groupped.t, count(*) from
(select distinct base.t, base.v from foo as base where v not in 
  (
  select u.v from foo as u where u.t < base.t
  )
) as groupped group by groupped.t;

Heres also a fiddle. Hope this helps. http://sqlfiddle.com/#!18/4a65e/1

继承人也是一个小提琴。希望这可以帮助。 http://sqlfiddle.com/#!18/4a65e/1

#1