如何获取带有条件的日期间隔

Question

如何从满足特定条件的行中获取连续的日期间隔？

我有一个包含 2 种user_position的员工状态表。 如果每user_id下一个较高的date_position具有相同的user_id、第二天的值和user_position没有变化，则间隔是连续的。用户在一天内不能有不同的用户位置。

感觉它需要几种情况，窗口函数和tsrange，但不能完全得到正确的结果。

如果您能帮助我，我将不胜感激。

小提琴：

http://sqlfiddle.com/#!17/ba641/1/0

结果应如下所示：

<th style="text-align： center;">user_idstyle="文本对齐：居中;">1style="文本对齐：居中;">1style="文本对齐：居中;">1style="文本对齐：居中;">2style="文本对齐：居中;">2style="文本对齐：居中;">2style="文本对齐：居中;">2

user_position	position_start	position_end
1	01.01.2019	02.01.2019
2	03.01.2019	04.01.2019
1	05.01.2019	06.01.2019
1	01.01.2019	2019.03.01
2	04.01.2019	05.01.2019
2	08.01.2019	08.01.2019
2	10.01.2019	2019.10.01

SELECT user_id, user_position
, min(date_position) AS position_start
, max(date_position) AS position_end
FROM  (
SELECT user_id, user_position,date_position
, count(*) FILTER (WHERE (date_position = last_date + 1
AND user_position = last_pos) IS NOT TRUE)
OVER (PARTITION BY user_id ORDER BY date_position) AS interval
FROM (
SELECT user_id, user_position, date_position
, lag(date_position) OVER w AS last_date
, lag(user_position) OVER w AS last_pos
FROM   users_position
WINDOW w AS (PARTITION BY user_id ORDER BY date_position)
) sub1
) sub2
GROUP  BY user_id, user_position, interval
ORDER  BY user_id, interval;

db<>小提琴在这里

基本上，这通过计算连续性中的中断次数来形成间隔。每当每个user_id的"下一个"行不是预期的时，就会开始一个新的间隔。

WINDOW子句允许指定一次窗口框架并重复使用;对性能没有影响。

last_date + 1工作，而last_date是类型date.看：

• 有没有办法对 DATE 类型的值进行日期算术，而结果不是 TIMESTAMP 类型？

相关：

• 根据行序列获取开始和结束日期时间
• 选择最长的连续序列

关于聚合FILTER：

• 使用其他(非重复)筛选器聚合列