在PostgreSQL的表中添加缺失日期

Question

我有一张表格，包含了2002年每天的数据，但是有一些日期的数据丢失了。具体来说，这张表格只有354条记录代替了365条记录。为了进行计算，我需要将缺失的数据添加到这张表格中，并将其置为空值（Null）。

+-----+------------+------------+
| ID  |  rainfall  | date       |
+-----+------------+------------+
| 100 |  110.2     | 2002-05-06 |
| 101 |  56.6      | 2002-05-07 |
| 102 |  65.6      | 2002-05-09 |
| 103 |  75.9      | 2002-05-10 |
+-----+------------+------------+

你会发现2002-05-08的数据缺失了。我希望最终表格如下:

+-----+------------+------------+
| ID  |  rainfall  | date       |
+-----+------------+------------+
| 100 |  110.2     | 2002-05-06 |
| 101 |  56.6      | 2002-05-07 |
| 102 |            | 2002-05-08 |
| 103 |  65.6      | 2002-05-09 |
| 104 |  75.9      | 2002-05-10 |
+-----+------------+------------+

在PostgreSQL中有办法实现这个吗？

我只需将结果作为查询结果（不一定是更新后的表格）即可。

- f.ashouri

---

ID是连续的吗？如果插入日期，您需要将它们全部上移吗？ - Clodoaldo Neto

是的，我需要将ID更改为我在这里放置的示例表格中的样式。到一整年结束时，最后一个ID应为365。 - f.ashouri

我已经阅读了您的其他帖子（回答了其中一个），我认为您做错了。您不应该依赖ID进行排序或分组，而是“日期”才是重要的。如果您想填补空缺，那么可以这样做，这样可以使查询更容易，但并非必须的。在“日期”列上创建唯一索引以确保它们不会重复。我正在回答如何填补这些空缺。 - Clodoaldo Neto

是的，你说得对。我必须基于日期而不是ID来完成它。但问题是我无法根据日期进行分组。我会尝试的。谢谢。 - f.ashouri

Answer 1

date是标准SQL中的保留字，也是PostgreSQL中的数据类型名称。PostgreSQL允许其作为标识符，但这并不是一个好主意。我使用thedate作为列名。

不要依赖于代理ID中缺少间隔的特性。那几乎总是一个坏主意。将这样的ID视为没有含义的唯一数字，即使它看起来承载了某些其他属性大多数时间。

在这种特殊情况下，正如@Clodoaldo评论的, thedate似乎是一个完美的主键，而列id只是无用的东西 - 我已经删除了它:

CREATE TEMP TABLE tbl (thedate date PRIMARY KEY, rainfall numeric);
INSERT INTO tbl(thedate, rainfall) VALUES
  ('2002-05-06', 110.2)
, ('2002-05-07', 56.6)
, ('2002-05-09', 65.6)
, ('2002-05-10', 75.9);

查询

通过查询获取完整表格：

SELECT x.thedate, t.rainfall  -- rainfall automatically NULL for missing rows
FROM (
   SELECT generate_series(min(thedate), max(thedate), '1d')::date AS thedate
   FROM   tbl
   ) x
LEFT   JOIN tbl t USING (thedate)
ORDER  BY x.thedate

与@a_horse_with_no_name发布的类似，但更简化并忽略了修剪的id。

填补表中第一个和最后一个日期之间的空白。如果存在前导/滞后空白，请相应地扩展。您可以像@Clodoaldo演示的那样使用date_trunc()，但他的查询存在语法错误，并且可以更简单。

插入缺失的行

最快且最易读的方法是使用NOT EXISTS反半连接。

INSERT INTO tbl (thedate, rainfall)
SELECT x.thedate, NULL
FROM (
   SELECT generate_series(min(thedate), max(thedate), '1d')::date AS thedate
   FROM   tbl
   ) x
WHERE NOT EXISTS (SELECT 1 FROM tbl t WHERE t.thedate = x.thedate)

- Erwin Brandstetter

Answer 2

只需执行一个外连接，连接到返回2002年所有日期的查询：

with all_dates as (
  select date '2002-01-01' + i as date_col
  from generate_series(0, extract(doy from date '2002-12-31')::int - 1) as i
)
select row_number() over (order by ad.date_col) as id, 
       t.rainfall,
       ad.date_col as date
from all_dates ad
  left join your_table t on ad.date_col = t.date
order by ad.date_col;

这不会改变您的表，仅仅会生成所需的结果。

请注意，生成的id列不会包含与您的表中的ID列相同的值，因为它只是结果集中的计数器。

您还可以使用extract(doy from ad.date_col)替换row_number()函数。

- user330315

---

我在我的表格上运行了这个程序，但是遇到了以下错误：错误：对于表“ad”的FROM子句条目无效引用 第13行：left join rwanda1 t on ad.date_col = t.date 提示：表“ad”有一个条目，但是它不能从查询的这部分引用。 - f.ashouri

Answer 3

填补空缺。这不会重新排序ID：

insert into t (rainfall, "date") values
select null, "date"
from (
    select d::date as "date"
    from (
        t
        right join
        generate_series(
            (select date_trunc('year', min("date")) from t)::timestamp,
            (select max("date") from t),
            '1 day'
        ) s(d) on t."date" = s.d::date
    where t."date" is null
    ) q
) s

- Clodoaldo Neto

---

如果表中缺少12月31日怎么办？在这种情况下，generate_series()函数将无法创建足够的值。 - user330315

@a_horse_with_no_name 是的，这是设计上的。我认为他不想或者不应该想要未来的日期。 - Clodoaldo Neto

不，他希望结果中有365行。 - user330315

@a_horse_with_no_name 请阅读我在问题上的评论，了解我为什么认为那是错误的。 - Clodoaldo Neto

Answer 4

你必须完全重新创建你的表格，因为索引需要更改。

最好的方法是使用你喜欢的数据库接口语言，循环遍历旧表格的值（忽略ID），并将其放入新表格中以获得新序列化的ID。

for day in (whole needed calendar)
    value = select rainfall from oldbrokentable where date = day
    insert into newcleanedtable date=day, rainfall=value, id=serialized

那不是真正的代码！只是概念性的，可以根据您喜欢的脚本语言进行调整。

- F. Hauri - Give Up GitHub

---

他也可以将所有ID重新编号为无意义的值(UPDATE table SET id=-id)，然后再将它们正确地重新编号(UPDATE table SET id=q.val FROM (SELECT date, row_number() OVER (ORDER BY date) AS val FROM table) q WHERE q.date=table.date)。话虽如此，我同意@Clodoaldo的观点，这是错误的。 - willglynn

...并且极其低效。如果您想编写一个新的干净表格，您可以使用单个SQL命令，在其中使用row_number()生成新的id，或在旧版本中使用序列。但整个想法都不好。依赖于代理键中不存在间隙的思路几乎不是一个好主意。 - Erwin Brandstetter