使用来自2个不同表的数据进行交叉表 [英] Crosstab using data from 2 different tables

问题描述

我有2个表Measurement_timestamps和sensor_double_precision，其形式如下:

I have 2 tables measurement_timestamps and sensor_double_precision of the following form:

id    start_time    stop_time
 1    2020-02-22    2020-02-24
 2    2020-02-25    2020-02-27 

和

id    sensor_name    value_cal    timestamp
 1    start_freq            15    2020-02-23
 2    stop_freq             18    2020-02-23
 3    start_freq            15    2020-02-26
 4    stop_freq             18    2020-02-26

我想要一个查看测量时间戳的VIEW，并针对每个start_time-stop_time对，旋转(转置?)sensor_name列，以使start_freq和stop_freq成为具有各自value_cal作为行的自己的列.

I want a VIEW that looks at measurement timestamps and for each start_time - stop_time pair, pivots (transposes?) the sensor_name column such that start_freq and stop_freq become there own columns with the respective value_cal as the rows.

所以我基本上希望VIEW看起来像这样:

So I basically want the VIEW to looks like this:

id    start_freq    stop_freq    timestamp
 1            15           18    2020-02-23
 2            15           18    2020-02-26

请注意，与VIEW中与id 1相关联的时间戳是如何在measurement_timestamps表中的id 1的start_time和stop_time之间.

Notice how the timestamp associated with id 1 in the VIEW is in between the start_time and stop_time for id 1 in the measurement_timestamps table.

执行此操作的合理方法是什么?我不想为每个单独的sensor_name创建一个VIEW，因为我有比这更多的传感器，而且它似乎并不十分健壮.以下是我被推荐的一种方法，但是它似乎不起作用，因为我可能做错了什么.

What is a reasonable way of doing this? I do not want to create a VIEW for each individual sensor_name because I have many more sensors than this and it just doesn't seem very robust. The following is an approach that I have been recommended but it does not seem to work because I am probably doing something wrong.

SELECT * 
FROM crosstab('with current_data as (
        select distinct on (mt.id)
                mt.id, sdp.sensor_name, sdp.value_cal
            from measurement_timestamps mt, sensor_double_precision sdp
            order by mt.id desc
    ),
    ids as (
        select distinct id from current_data
    ),
    sensor_names as (
        select distinct sensor_name from current_data
    )
    select ids.id, sensor_names.sensor_name, current_data.value_cal
    from ids cross join sensor_names
    left join current_data on (ids.id=current_data.id and sensor_names.sensor_name=current_data.sensor_name)
    order by ids.id,sensor_names.sensor_name') final_data (id integer, start_freq double precision,
                                                        stop_freq double precision, timestamp timestamp)

侧注-start_freq和stop_freq似乎不像传感器名称，但我正在使用遵循标准化格式的表，因此我们将其称为sensor_name.还有其他我不为此任务担心的传感器.

Sidenote - start_freq and stop_freq don't seem like sensor names but I am working with a table that follows a standardized form because of which we are calling it sensor_name. There are also other sensors that I am not worried about for this task.

编辑-下面建议的查询结果:

EDIT - result from the query that was suggested below:

id.  start_freq       stop_freq.             timestamp
18      15             null         "2020-07-09 20:03:38.937195+00"
19     null             18.         "2020-07-09 20:03:39.051836+00"
20     null            null          "2020-07-09 20:03:39.171837+00"
21     null            null         "2020-07-09 20:03:39.287994+00"
22     null            null         "2020-07-09 20:03:39.287994+00"
23     15              null         "2020-07-09 20:03:39.287994+00"
24     null            18           "2020-07-09 20:03:39.287994+00"

EDIT2-我在问题中附加的数据是示例数据，使问题更易于讨论.结构和所有内容都与真实数据集相似.

EDIT2 - The data I have attached in the problem is sample data to make the problem easier to talk about. The structure and everything are similar to the real data set.

推荐答案

有两种方法可以实现它.(有条件的只有一组开始和停止事件，其时间范围为 measurement_timestamps )

There are 2 ways to achieve it. (conditionally only one set of start and stop event with in time range of measurement_timestamps )

1. 使用常规聚合和 filter

select 
m.id, 
min(s.value_cal) filter (where sensor_name='start_freq'),
min(s.value_cal) filter (where sensor_name='stop_freq'),
min(s.timestamp) filter (where sensor_name='stop_freq')
from measurement_timestamps m 
inner join sensor_double_precision s on s.timestamp between m.start_time and m.stop_time
group by m.id

注意:您尚未指定所需的日期，所以我已选择了 stop_freq时间戳.您可以更改所需的内容.

Note: you have not specified which date you want so i have taken stop_freq timestamp. You can change what you want.

1. 使用 Crosstab (您的方式)

1. Using Crosstab (Your Way)

select * from crosstab('select 
m.id, 
s.timestamp,
s.sensor_name,
s.value_cal
from measurement_timestamps m 
inner join sensor_double_precision s on s.timestamp between m.start_time and m.stop_time',
'select ''start_freq'' union select ''stop_freq'' ') as (id int, timestamp date, start_freq varchar, stop_freq varchar)

注意:仅当 start_freq时间戳和 stop_freq时间戳相同

演示

这篇关于使用来自2个不同表的数据进行交叉表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！