postgreSQL查询，将更改日志转换为间隔

Question

让我来描述一下问题：
-我有一个任务表，其中有一个assignee列、一个created列和一个resolved列(创建和解析都是时间戳(

+---------+----------+------------+------------+  
| task_id | assignee |  created   |  resolved  |  
+---------+----------+------------+------------+  
| tsk1    | him      | 2000-01-01 | 2018-01-03 |  
+---------+----------+------------+------------+

-我有一个更改日志表，其中包含task_id、from列、to列和日期列，记录每次更改受让人的

+---------+----------+------------+------------+  
| task_id | from     |  to        |  date      |  
+---------+----------+------------+------------+  
| tsk1    | me       | you        | 2017-04-06 |  
+---------+----------+------------+------------+  
| tsk1    | you      | him        | 2017-04-08 |  
+---------+----------+------------+------------+ 

我想选择一个表，显示在间隔内完成任务的所有受让人的列表

+---------+----------+------------+------------+  
| task_id | assignee |  from      |  to        |  
+---------+----------+------------+------------+  
| tsk1    | me       | 2000-01-01 | 2017-04-06 |  
+---------+----------+------------+------------+  
| tsk1    | you      | 2017-04-06 | 2017-04-08 |  
+---------+----------+------------+------------+  
| tsk1    | him      | 2017-04-08 | 2018-01-03 |  
+---------+----------+------------+------------+  

我在第一行(/最后一行(遇到了问题，from(/to(应该设置为created(/resolved(，我不知道如何用来自两个不同表的数据创建一列
我试过让它们在自己的选择中，然后用并集合并所有行，但我认为这不是一个很好的解决方案。。。

Answer 1

嗯。这比看上去更狡猾。这个想法是使用lead()来获得下一个日期，但您需要使用tasks表中的信息来"扩充"数据：

select task_id, to, date as fromdate,
coalesce(lead(date) over (partition by task_id order by date),
max(resolved) over (partition by task_id)
) as todate
from ((select task_id, to, date, null::timestamp
from log l
) union all
(select distint on (t.task_id) t.task_id, l.from, t.created, t.resolved
from task t join
log l
on t.task_id = l.task_id
order by t.task_id, l.date
)
) t;

Answer 2

demo:db<>小提琴

SELECT 
l.task_id, 
assignee_from as assignee, 
COALESCE(
lag(assign_date) OVER (ORDER BY assign_date), 
created
) as date_from,
assign_date as date_to
FROM 
log l 
JOIN
task t
ON l.task_id = t.task_id
UNION ALL
SELECT * FROM (
SELECT DISTINCT ON (l.task_id)
l.task_id, assignee_to, assign_date, resolved
FROM 
log l 
JOIN
task t
ON l.task_id = t.task_id
ORDER BY l.task_id, assign_date DESC
) s
ORDER BY task_id, date_from

UNION由两部分组成：日志中的部分和任务表中的最后一行。

第一部分使用LAG()窗口函数获取当前行的上一个日期。因为"me"没有前一行，所以会产生NULL值。因此，这是通过从task表中获取created日期来实现的。

第二部分是获取最后一行：这里我通过DISTINCT和ORDER BY assign_date DESC获取日志的最后一行。所以我知道最后一个assignee_to。其余部分与第一部分类似：从任务表中获取resolved值。

Answer 3

感谢S-Man和Gordon Linoff的回答，我能够想出这个解决方案：

SELECT t.task_id,
t.item_from AS assignee,
COALESCE(lag(t.changelog_created) OVER (
PARTITION BY t.task_id ORDER BY t.changelog_created), 
max(t.creationdate) OVER (PARTITION BY t.task_id)) AS fromdate,
t.changelog_created as todate
FROM ( SELECT ch.task_id,
ch.item_from,
ch.changelog_created,
NULL::timestamp without time zone AS creationdate
FROM changelog_generic_expanded_view ch
WHERE ch.field::text = 'assignee'::text
UNION ALL
( SELECT DISTINCT ON (t_1.id_task) t_1.id_task,
t_1.assigneekey,
t_1.resolutiondate,
t_1.creationdate
FROM task_jira t_1
ORDER BY t_1.id_task)) t;

注意：这是最终版本，所以名称有点不同，但想法保持不变。

这与Gordon Linoff基本上是相同的代码，但我以相反的方向查看变更日志
我使用UNION ALL的第二部分来生成最后一个受让人，而不是第一个受让人(这是为了处理根本没有变更日志的情况，生成最后的受让人时不涉及变更日志(