如何在postgresql中连接datetime列上的两个表，并且这些表只在一列中显示datetime的所有寄存器

Question

我有两个表：

TABLE_MACHINE_1

datetime,            sensor_1
2017-10-01 21:00:00, 1.18047
2017-10-01 21:15:00, 1.18023
2017-10-01 21:30:00, 1.18059
2017-10-01 21:45:00, 1.18067
2017-10-01 22:00:00, 1.18056
2017-10-01 22:15:00, 1.18147
2017-10-01 22:30:00, 1.18104
2017-10-01 22:45:00, 1.18111
2017-10-01 23:00:00, 1.18103
2017-10-01 23:15:00, 1.18072
2017-10-01 23:30:00, 1.18083
2017-10-01 23:45:00, 1.18079
2017-10-02 00:00:00, 1.17996
2017-10-02 00:15:00, 1.17987
2017-10-02 00:30:00, 1.17968
2017-10-02 00:45:00, 1.17893
2017-10-02 01:00:00, 1.17852
2017-10-02 01:15:00, 1.17833

TABLE_MACHINE_2atr

datetime,            sensor_2
2017-10-01 21:45:00, 112.1
2017-10-01 22:00:00, 133.2
2017-10-01 22:15:00, 143.5
2017-10-01 22:30:00, 154.9
2017-10-01 22:45:00, 166.1
2017-10-01 23:30:00, 123.1
2017-10-01 23:45:00, 134.1
2017-10-02 00:00:00, 188.3
2017-10-02 01:00:00, 192.9
2017-10-02 01:15:00, 105.3
2017-10-02 01:30:00, 172.9
2017-10-02 01:45:00, 145.3
2017-10-02 02:00:00, 174.9
2017-10-02 02:15:00, 155.3

我预料结果如何：

加入预期的

datetime,            sensor_1  sensor_2
2017-10-01 21:00:00, 1.18047,  NULL
2017-10-01 21:15:00, 1.18023,  NULL
2017-10-01 21:30:00, 1.18059,  NULL
2017-10-01 21:45:00, 1.18067,  112.1
2017-10-01 22:00:00, 1.18056,  133.2
2017-10-01 22:15:00, 1.18147,  143.5
2017-10-01 22:30:00, 1.18104,  154.9
2017-10-01 22:45:00, 1.18111,  166.1
2017-10-01 23:00:00, 1.18103,  NULL
2017-10-01 23:15:00, 1.18072,  NULL
2017-10-01 23:30:00, 1.18083,  123.1
2017-10-01 23:45:00, 1.18079,  134.1
2017-10-02 00:00:00, 1.17996,  188.3
2017-10-02 00:15:00, 1.17987,  105.3
2017-10-02 00:30:00, 1.17968,  NULL
2017-10-02 00:45:00, 1.17893,  NULL
2017-10-02 01:00:00, 1.17852,  NULL
2017-10-02 01:15:00, 1.17833,  NULL
2017-10-02 01:30:00, NULL   ,  172.9
2017-10-02 01:45:00, NULL   ,  145.3
2017-10-02 02:00:00, NULL   ,  174.9
2017-10-02 02:15:00, NULL   ,  155.3

但如果我做完整的外部加入获得：

从TABLE_MACHINE_1完全外部联接DATETIME 上的TABLE_MACHINE_2

datetime,            sensor_1, datetime,            sensor_2
2017-10-01 21:00:00, 1.18047,  NULL,                NULL  
2017-10-01 21:15:00, 1.18023,  NULL,                NULL 
2017-10-01 21:30:00, 1.18059,  NULL,                NULL 
2017-10-01 21:45:00, 1.18067,  2017-10-01 21:45:00, 112.1
2017-10-01 22:00:00, 1.18056,  2017-10-01 22:00:00, 133.2
2017-10-01 22:15:00, 1.18147,  2017-10-01 22:15:00, 143.5
2017-10-01 22:30:00, 1.18104,  2017-10-01 22:30:00, 154.9
2017-10-01 22:45:00, 1.18111,  2017-10-01 22:45:00, 166.1
2017-10-01 23:00:00, 1.18103,  NULL,                NULL 
2017-10-01 23:15:00, 1.18072,  NULL,                NULL 
2017-10-01 23:30:00, 1.18083,  2017-10-01 23:30:00, 123.1
2017-10-01 23:45:00, 1.18079,  2017-10-01 23:45:00, 134.1
2017-10-02 00:00:00, 1.17996,  2017-10-02 00:00:00, 188.3
2017-10-02 00:15:00, 1.17987,  NULL,                NULL 
2017-10-02 00:30:00, 1.17968,  NULL,                NULL 
2017-10-02 00:45:00, 1.17893,  NULL,                NULL 
2017-10-02 01:00:00, 1.17852,  2017-10-02 01:00:00, 192.9
2017-10-02 01:15:00, 1.17833,  2017-10-02 01:15:00, 105.3
NULL,                NULL,     2017-10-02 01:30:00, 172.9 
NULL,                NULL,     2017-10-02 01:45:00, 145.3
NULL,                NULL,     2017-10-02 02:00:00, 174.9 
NULL,                NULL,     2017-10-02 02:15:00, 155.3

我需要在postgresql中的一个查询中解决它，我尝试使用完整的外部联接子句，但没有成功。

我已经考虑过以下解决方案，但我无法实现它，因为当我传递变量"generate_series"时会出现错误：

1. 创建一个变量来存储每个表的最大天数。

   datetime_end := to_char(now()::date,'YYY-MM-DD');
   

2. 将变量传递给以下查询：

   SELECT generate_series(
   timestamp without time zone '2017-10-01',
   timestamp without time zone datetime_end,
   '15 minute');
   

   3. 若要执行LEFT操作，请将前面的查询连接到两个表

提前感谢。

Answer 1

在DATETIME 上从TABLEMACHINE_1完全外部加入TABLEMAChinE_2

这不是有效的SQL。

如果使用USING而不是ON进行联接，则它将自动合并列。

select 
datetime,
t1.sensor_1,
t2.sensor_2
from table_machine_1 t1
full outer join table_machine_2 t2 
USING (datetime)

Answer 2

您想要full outer join和coalesce():

select 
coalesce(t1.datetime, t2.datetime) datetime,
t1.sensor_1,
t2.sensor_2
from table_machine_1 t1
full outer join table_machine_2 t2 
on t1.datetime = t2.datetime

DB Fiddle上的演示：

| datetime            | sensor_1 | sensor_2 |
| ------------------- | -------- | -------- |
| 2017-10-01 21:00:00 | 1.18047  |          |
| 2017-10-01 21:15:00 | 1.18023  |          |
| 2017-10-01 21:30:00 | 1.18059  |          |
| 2017-10-01 21:45:00 | 1.18067  | 112.1    |
| 2017-10-01 22:00:00 | 1.18056  | 133.2    |
| 2017-10-01 22:15:00 | 1.18147  | 143.5    |
| 2017-10-01 22:30:00 | 1.18104  | 154.9    |
| 2017-10-01 22:45:00 | 1.18111  | 166.1    |
| 2017-10-01 23:00:00 | 1.18103  |          |
| 2017-10-01 23:15:00 | 1.18072  |          |
| 2017-10-01 23:30:00 | 1.18083  | 123.1    |
| 2017-10-01 23:45:00 | 1.18079  | 134.1    |
| 2017-10-02 00:00:00 | 1.17996  | 188.3    |
| 2017-10-02 00:15:00 | 1.17987  |          |
| 2017-10-02 00:30:00 | 1.17968  |          |
| 2017-10-02 00:45:00 | 1.17893  |          |
| 2017-10-02 01:00:00 | 1.17852  | 192.9    |
| 2017-10-02 01:15:00 | 1.17833  | 105.3    |
| 2017-10-02 02:00:00 |          | 174.9    |
| 2017-10-02 01:30:00 |          | 172.9    |
| 2017-10-02 01:45:00 |          | 145.3    |
| 2017-10-02 02:15:00 |          | 155.3    |