按升序将数据插入空单元格

Question

我有一个具有以下结构的Postgres表：

CREATE TABLE tb1 (
id integer,
name text,
date date,
time time without tz
);
CREATE TABLE tb2 (
id integer,
name text,
date date
);

我需要生成一个第 3 个表tb3该表将具有列time_now，增量步长为 10 分钟。如果tb1.time与tb3.time_now不同，则填写b2.name。如果tb1.time等于（或接近time_now），则将其插入到tb3 中。
例：

结核病1

1, xxxx, 2014-10-01, 08:20:00
2, yyyy, 2014-10-01, 08:40:00

结核病2

1, zzzz, 2014-10-01
2, vvvv, 2014-10-01
3, eeee, 2014-10-01

第三个表应如下所示：

1, 08:00:00,zzzz -----> from tb2
2, 08:10:00,vvvv -----> from tb2
3, 08:20:00,xxxx -----> from tb1
4, 08:30:00,eeee -----> from tb2
5, 08:40:00,yyyy -----> from tb1

如何实现这一点？

Answer 1

SELECT t.id, t.time::text, COALESCE(t.name, t2.name) AS name
FROM  (
   SELECT g.id, g.time, t1.name
        , CASE WHEN t1.name IS NULL THEN
            row_number() OVER (PARTITION BY t1.name ORDER BY g.id)
          END AS rn
   FROM  (
      SELECT g AS id, '08:00'::time + '10 min'::interval * (g-1) AS time
      FROM   generate_series (1,6) g
      ) g
   LEFT JOIN tb1 t1 USING (time)
   ) t
LEFT   JOIN tb2 t2 ON t2.id = t.rn
ORDER  BY t.id;

1. 首先用generate_series()建立一个所需时间的表。在我的示例中，从 8：00 到 8：50。

2. 加入 tb1 on time .将升序数字附加到带有row_number() （rn） 的空插槽。

3. 按升序将tb2连接到剩余的空插槽。

4. 使用 COALESCE 从tb1和tb2中选取名称。

警惕 off-by-1 错误。

SQL 小提琴。

旁白：我不会使用这些列名。 id和name不是描述性的 - 永远不要使用它们，更具体。 date和time是基本类型名称。