Join查询

Uqbar=# CREATE TABLE cpu_info (id int, arch text, cores int, speed float);

Uqbar=# INSERT INTO cpu_info VALUES (1, 'aarch64', 8, 2000);
Uqbar=# INSERT INTO cpu_info VALUES (2, 'x86_64', 8, 2200);
Uqbar=# INSERT INTO cpu_info VALUES (3, 'aarch64', 16, 2000);
Uqbar=# INSERT INTO cpu_info VALUES (4, 'x86_64', 16, 2200);

Uqbar=# DROP TIMESERIES TABLE IF EXISTS cpu;
Uqbar=# CREATE TIMESERIES TABLE cpu(time timestamp tstime, tags_id int tstag, usage float);

Uqbar=# INSERT INTO cpu VALUES ('2023-05-01 00:00:00', 1, 82);
Uqbar=# INSERT INTO cpu VALUES ('2023-05-01 12:00:00', 1, 83);
Uqbar=# INSERT INTO cpu VALUES ('2023-05-01 23:59:59', 1, 84);
Uqbar=# INSERT INTO cpu VALUES ('2023-05-02 00:00:00', 1, 92);
Uqbar=# INSERT INTO cpu VALUES ('2023-05-02 12:00:00', 1, 93);
Uqbar=# INSERT INTO cpu VALUES ('2023-05-02 23:59:59', 1, 94);
Uqbar=# INSERT INTO cpu VALUES ('2023-05-02 00:00:00', 2, 84);
Uqbar=# INSERT INTO cpu VALUES ('2023-05-02 12:00:00', 2, 85);
Uqbar=# INSERT INTO cpu VALUES ('2023-05-02 23:59:59', 2, 86);
Uqbar=# INSERT INTO cpu VALUES ('2023-05-02 00:00:00', 3, 93);
Uqbar=# INSERT INTO cpu VALUES ('2023-05-02 00:00:00', 3, 92);
Uqbar=# INSERT INTO cpu VALUES ('2023-05-02 00:00:00', 3, 91);
Uqbar=# INSERT INTO cpu VALUES ('2023-05-02 00:00:00', 5, 91);

Uqbar=# SELECT cpu.time, cpu_info.arch, cpu_info.id, cpu_info.cores, cpu_info.speed FROM cpu LEFT JOIN cpu_info ON cpu.tags_id = cpu_info.id ORDER BY cpu.tags_id,cpu.time ASC;
        time         |  arch   | id | cores | speed
---------------------+---------+----+-------+-------
 2023-05-01 00:00:00 | aarch64 |  1 |     8 |  2000
 2023-05-01 12:00:00 | aarch64 |  1 |     8 |  2000
 2023-05-01 23:59:59 | aarch64 |  1 |     8 |  2000
 2023-05-02 00:00:00 | aarch64 |  1 |     8 |  2000
 2023-05-02 12:00:00 | aarch64 |  1 |     8 |  2000
 2023-05-02 23:59:59 | aarch64 |  1 |     8 |  2000
 2023-05-02 00:00:00 | x86_64  |  2 |     8 |  2200
 2023-05-02 12:00:00 | x86_64  |  2 |     8 |  2200
 2023-05-02 23:59:59 | x86_64  |  2 |     8 |  2200
 2023-05-02 00:00:00 | aarch64 |  3 |    16 |  2000
 2023-05-02 00:00:00 | aarch64 |  3 |    16 |  2000
 2023-05-02 00:00:00 | aarch64 |  3 |    16 |  2000
 2023-05-02 00:00:00 |         |    |       |
(13 rows)

Uqbar=# SELECT time_bucket('1 day',time) as bucket, cpu_info.id, avg(usage) FROM cpu LEFT JOIN cpu_info ON cpu.tags_id = cpu_info.id GROUP BY bucket,cpu_info.id ORDER BY cpu_info.id,bucket ASC;
       bucket        | id | avg
---------------------+----+-----
 2023-05-01 00:00:00 |  1 |  83
 2023-05-02 00:00:00 |  1 |  93
 2023-05-02 00:00:00 |  2 |  85
 2023-05-02 00:00:00 |  3 |  92
 2023-05-02 00:00:00 |    |  91
(5 rows)