SQL：ID x 在日期 y 的状态是什么（有更有效的方法吗？）

Question

我有两个表：一个包含 ID 列表，另一个包含有关这些 ID 的一些时间相关信息。

示例

ID_TABLE
ID
 1
 2
 3

INFO_TABLE
ID         START           END   STATE
 1    2000-01-01    2000-01-31       A
 1    2000-02-01    2000-03-31       B
 3    2000-02-01    2000-02-31       A
 3    2000-03-01    2000-05-31       C

（请注意，第二个表中不存在 ID=2）

现在我想将此信息转换为 平衡面板数据，这意味着我想知道在特定日期每个ID 存在什么STATE。

我的 SQL 命令如下所示：

SELECT
  id_table.id,

  t1.state AS Jan,
  t2.state AS Feb,
  t3.state AS March

  FROM 
    id_table
    LEFT JOIN info_table AS t1
      ON id_table.id = t1.id
      AND '2000-01-15' BETWEEN t1.start AND t1.end
    LEFT JOIN info_table AS t2
      ON id_table.id = t2.id
      AND '2000-02-15' BETWEEN t2.start AND t2.end
    LEFT JOIN info_table AS t3
      ON id_table.id = t3.id
      AND '2000-03-15' BETWEEN t3.start AND t3.end
;

有更有效的方法吗？

Answer 1

我不完全确定我明白你的问题，但如果你链接的维基百科页面是可以参考的，那么你真正寻找的内容更接近于这个：

select t.id,
      '2000-01-01'::date + (m.mon || ' months')::interval as month,
      t.state
from generate_series(0, 11) as m (mon)
left join info_table t
on '2000-01-15'::date + (m.mon || ' months')::interval
   between t.start and t.end

如果你当前的查询是你真正需要的，那么你通过使用几何类型可以加快速度：

PostgreSQL 匹配间隔开始时间和结束时间与时间戳之间