将表结果合并到列中(数据透视表/交叉表?) [英] Merge table results into columns (pivot/crosstab?)

问题描述

我有大约 30 个表，可以从外部系统获取流式"数据.我正在尝试弄清楚如何检索表中特定时间点的最后已知值并以方便的方式呈现该值.

I have ~30 tables which gets "streamed" data from an external system. I'm trying to figure out how to retrieve the tables last known values for a specific point in time and present that in a convenient way.

让我们描述一下表格布局:

Let's describe the table layout:

DROP TABLE IF EXISTS table1;
DROP TABLE IF EXISTS table2;
CREATE TABLE IF NOT EXISTS table1 (
    id1             INT NOT NULL,
    id2             TEXT NOT NULL,
    update_time     TIMESTAMP(6) NOT NULL,
    val             NUMERIC NULL,
    PRIMARY KEY (id1, id2, update_time)
)
;

CREATE TABLE IF NOT EXISTS table2 (
    id1             INT NOT NULL,
    id2             TEXT NOT NULL,
    update_time     TIMESTAMP(6) NOT NULL,
    val             INT NULL,
    PRIMARY KEY (id1, id2, update_time)
)
;

--...tableN(


INSERT INTO table1(id1, id2, update_time, val) VALUES (1, 'ident 1', '2004-10-19 09:00:00', 1.23);
INSERT INTO table1(id1, id2, update_time, val) VALUES (1, 'ident 1', '2004-10-19 10:05:00', 1.25);

INSERT INTO table2(id1, id2, update_time, val) VALUES (1, 'ident 1', '2004-10-19 10:03:00', 23);
INSERT INTO table2(id1, id2, update_time, val) VALUES (1, 'ident 1', '2004-10-19 10:03:30', null);

现在对我来说呈现数据的最佳方式是这样的:

Now the optimal way for me to have the data presented would be something like:

SELECT *
FROM lastknownvalues
WHERE id1 = 1
AND id2 = 'ident 1'
AND time = '2004-10-19 10:04:00'

哪个将返回单行:

time                     id1    id2     table1    table2
'2004-10-19 10:04:00'    1      ident1  1.23      null

在 10:04:00，table1 中的最后一个已知值是 1.23(后来更新到 1.25)table2 在 10:03:30 更新为 null，因此结果中应该为 null.请注意，表中 val 的数据类型不同.

At 10:04:00 the last known value in table1 was 1.23 (the update to 1.25 was made later) table2 got updated to null at 10:03:30, so should be null in the result. Note that the data types for val is different in the tables.

我一直在努力使用数据透视表/交叉表，因为它看起来与我正在寻找的功能非常接近，但我无法弄清楚如何去做，你能弄清楚吗?:)

I've been struggeling with pivot/crosstab since that looks close enough to the functionality I'm looking for but I am unable to figure out how to do it, can you figure it out? :)

如果我想检索一个时间间隔内的所有值，以获取 2 个时间戳之间的所有组合信息，该怎么办?例如，如果选择 start_timestamp 2004-10-19 09:00:00 和 end_timestamp 2004-10-19 10:04:00 得到如下结果:

What if I would like to retrieve all values within an interval, to get all combined information between 2 timestamps? For example if selecting with start_timestamp 2004-10-19 09:00:00 and end_timestamp 2004-10-19 10:04:00 get a result like:

time                     id1    id2     table1    table2
'2004-10-19 09:00:00'    1      ident1  1.23      null
'2004-10-19 10:03:00'    1      ident1  1.23      23
'2004-10-19 10:03:30'    1      ident1  1.23      null

可能..?(请注意，我在 ~30 个表中获得了数据以与上述所需的输出相结合)

Possible..? (Note that I got data in ~30 tables to combine with the above desired output)

推荐答案

要找到最近的时间戳，首先使用比较运算符来排除未来的时间戳.接下来，通过从所需的时间戳中减去可能的时间戳候选，按最小间隔排序.要获得最近的结果，限制为 1.

To find the most recent timestamp first use the comparison operator to rule out future timestamps. Next, order by smallest interval by subtracting possible timestamp candidates from your desired timestamp. To get just the most recent result, limit 1.

要让 table2 val 列与 table1 列一起显示，您只需在子查询中为 table2 运行相同的代码.

To get the table2 val column to display with the table1 columns, you just need to run the same code for table2 within a subquery.

SELECT update_time AS time, id1, id2, val, (
  SELECT val from table2 
  WHERE '2004-10-19 10:04:00' >= update_time
  ORDER BY '2004-10-19 10:04:00' - update_time
  LIMIT 1
)
FROM table1 WHERE '2004-10-19 10:04:00' >= update_time
ORDER BY '2004-10-19 10:04:00' - update_time
LIMIT 1;

这篇关于将表结果合并到列中(数据透视表/交叉表?)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！