PostgreSQL如何扩展表补充缺失pl_id的指定格式记录？

需求说明 #

现有两张表：

• the_table：由复杂查询得到，结构为pl_id（整数）、date（日期）、value（整数），包含部分pl_id的日期数据
• the_source：存储所有可用的pl_id，结构为pl_id（整数）、msg（文本）

需要扩展the_table，补充the_source中未在the_table出现的pl_id对应的记录：日期沿用the_table已有的2024-05-01和2024-06-01，value设为0。

现有表结构及数据 #

CREATE TABLE the_table (
  "pl_id" INTEGER,
  "date" DATE,
  "value" INTEGER
);

INSERT INTO the_table
VALUES
  ('1', '2024-05-01', '10'),
  ('1', '2024-06-01', '11'),
  ('2', '2024-05-01', '12'),
  ('2', '2024-06-01', '13');
  

CREATE TABLE the_source (
  "pl_id" INTEGER,
  "msg" TEXT
 );
 
 INSERT INTO the_source
 VALUES
    ('1', 'aaaaa'),
    ('2', 'bbbbb'),
    ('3', 'ccccc'),
    ('4', 'ddddd');

缺失pl_id的查询语句 #

可以通过以下语句找出the_source中未出现在the_table的pl_id：

select * from the_source
where not exists (
  select
    *
  from the_table
  where the_table.pl_id = the_source.pl_id)

期望结果 #

最终得到的the_final表数据如下：

('1', '2024-05-01', '10'),
('1', '2024-06-01', '11'),
('2', '2024-05-01', '12'),
('2', '2024-06-01', '13'),
('3', '2024-05-01', '0'),
('3', '2024-06-01', '0'),
('4', '2024-05-01', '0'),
('4', '2024-06-01', '0');

实现方案 #

完全可以通过SQL实现，以下是两种常用方法：

方法1：交叉连接生成全量组合 + 左连接填充值 #

先生成所有pl_id与目标日期的组合，再关联原表填充值，缺失值设为0：

SELECT
    s.pl_id,
    d.date,
    COALESCE(t.value, 0) AS value
FROM the_source s
CROSS JOIN (SELECT DISTINCT date FROM the_table) d
LEFT JOIN the_table t ON s.pl_id = t.pl_id AND d.date = t.date
ORDER BY s.pl_id, d.date;

逻辑说明：

• CROSS JOIN生成所有pl_id和目标日期的笛卡尔积，确保每个pl_id都有所有需要的日期记录
• LEFT JOIN关联原表，匹配到的记录保留原value，未匹配到的用COALESCE将NULL转换为0

方法2：UNION ALL合并原数据与补全记录 #

保留原表数据，单独生成缺失的pl_id日期记录后合并：

-- 保留原表已有数据
SELECT pl_id, date, value FROM the_table
UNION ALL
-- 生成缺失pl_id的补全记录
SELECT
    s.pl_id,
    d.date,
    0 AS value
FROM the_source s
CROSS JOIN (SELECT DISTINCT date FROM the_table) d
WHERE NOT EXISTS (
    SELECT 1 FROM the_table t
    WHERE t.pl_id = s.pl_id AND t.date = d.date
)
ORDER BY pl_id, date;

逻辑说明：

• 第一部分直接取原表的所有已有数据
• 第二部分通过NOT EXISTS筛选出未在原表中出现的pl_id+日期组合，设置value为0后与原数据合并

两种方法都能得到符合预期的结果。

内容的提问来源于stack exchange，提问作者OcMaRUS