postgresql使用交叉表透视

Question

我在postgresql-11中使用crosstab()时遇到问题。

这是我的桌子，

CREATE TABLE monitor(tz timestamptz, level int, table_name text, status text);

该表监视其他表上的事件。它包含

table_name (table on which the event occurred)
timestamp(time at which the event occurred)
level (level of the event)
status of the event (start/end of the event) 

这是它的样本数据。

tz                | level | status |  table_name  
----------------------------------+-------+--------+--------------
2019-10-24 16:18:34.89435+05:30  |     2 | start  | test_table_2
2019-10-24 16:18:58.922523+05:30 |     2 | end    | test_table_2
2019-11-01 10:31:08.948459+05:30 |     3 | start  | test_table_3
2019-11-01 10:41:22.863529+05:30 |     3 | end    | test_table_3
2019-11-01 10:51:44.009129+05:30 |     3 | start  | test_table_3
2019-11-01 12:35:23.280294+05:30 |     3 | end    | test_table_3

给定一个时间戳，我想列出当时所有的当前事件。可以使用标准

start_time >= 'given_timestamp' and end_time <= 'given_timestamp'

因此，我尝试使用crosstab()在列table_name、status和timestamp上透视表。我的问题是，

with q1 (table_name, start_time,end_time) as
(select * from crosstab
('select table_name, status, tz from monitor ')
as finalresult (table_name text, start_time timestamptz, end_time timestamptz)), 
q2 (level,start_time,end_time) as 
(select * from crosstab('select level, status, tz from monitor ') 
as finalresult (level int, start_time timestamptz, end_time timestamptz)) 
select q1.table_name,q2.level,q1.start_time,q1.end_time 
from q1,q2 
where q1.start_time=q2.start_time;

查询的输出为

table_name  | level |            start_time            |             end_time             
--------------+-------+----------------------------------+----------------------------------
test_table_2 |     2 | 2019-10-24 16:18:34.89435+05:30  | 2019-10-24 16:18:58.922523+05:30
test_table_3 |     3 | 2019-11-01 10:31:08.948459+05:30 | 2019-11-01 10:41:22.863529+05:30

但我的预期输出是

table_name  | level |            start_time            |             end_time             
--------------+-------+----------------------------------+----------------------------------
test_table_2 |     2 | 2019-10-24 16:18:34.89435+05:30  | 2019-10-24 16:18:58.922523+05:30
test_table_3 |     3 | 2019-11-01 10:31:08.948459+05:30 | 2019-11-01 10:41:22.863529+05:30
test_table_3 |     3 | 2019-11-01 10:51:44.009129+05:30 | 2019-11-01 12:35:23.280294+05:30

如何实现预期产出？除了交叉表，还有什么更好的方法吗？

Answer 1

我会为此使用自联接。要将同一级别和表中的行放在一起，可以使用窗口函数为它们分配编号，以便对它们进行区分。

with numbered as (
select tz, level, table_name, status, 
row_number() over (partition by table_name, status order by tz) as rn
from monitor
)
select st.table_name, st.level, st.tz as start_time, et.tz as end_time
from numbered as st
join numbered as et on st.table_name = et.table_name
and et.status = 'end'
and et.level = st.level
and et.rn = st.rn
where st.status = 'start'
order by st.table_name, st.level;

这假设永远不会有具有status = 'end'的行和比具有status = 'start'的相应行更早的时间戳

在线示例：https://rextester.com/QYJK57764