sql - 按合适的时间加入表

Question

在 postgresql 数据库中，我有 2 个这样的表:

表项:

item_id    item     save_date (date dd/mm/yy)
----------------------------------------------
1          car       01/12/11
2          wheel     10/12/11
3          screen    11/12/11
4          table     15/12/11

表格周期:

period_id  period_name    start (date dd/mm/yy)
------------------------------------------------
1          period1      05/12/11
2          period2      09/12/11
3          period3      12/12/11

所以我想得到一个连接表，将最合适的时间段(如果匹配)添加到表项中。如果项目的保存日期晚于期间的开始日期，则期间将匹配。

例如，对于“汽车”，日期是 01/12/2011，所以我转到表期间，在那里我可以看到所有期间都在该日期之后开始，所以我必须将其设置为空。如果我使用“screen”，日期是 11/12/11，所以在期间我应该使用 period2，因为它是最晚匹配的。

因此连接表将导致:

item_id    item     save_date   period_name
----------------------------------------------
1          car       01/12/11      null
2          wheel     10/12/11      period2
3          screen    11/12/11      period2
4          table     15/12/11      period3

执行此连接的最佳方法是什么？

谢谢

Answer 1

SET search_path='tmp';

DROP TABLE items CASCADE;
CREATE TABLE items
    ( item_id INTEGER NOT NULL PRIMARY KEY
    , item VARCHAR
    , save_date date NOT NULL
    );
INSERT INTO items(item_id,item,save_date) VALUES
 ( 1, 'car', '2011-12-01' )
,( 2, 'wheel', '2011-12-10' )
,( 3, 'screen', '2011-12-11' )
,( 4, 'table', '2011-12-15' )
    ;

DROP TABLE periods CASCADE;
CREATE TABLE periods
    ( period_id INTEGER NOT NULL PRIMARY KEY
    , period_name VARCHAR
    , start_date date NOT NULL
    );
INSERT INTO periods(period_id,period_name,start_date) VALUES
 ( 1, 'period1', '2011-12-05' )
,( 2, 'period2', '2011-12-09' )
,( 3, 'period3', '2011-12-12' )
    ;
-- self-join to find the next interval
WITH pe AS (
    SELECT p0.period_id,p0.period_name,p0.start_date
        , p1.start_date AS end_date
    FROM periods p0
    -- must be a left join; because the most recent interval is still open
    -- (has no successor)
    LEFT JOIN periods p1 ON p1.start_date > p0.start_date
    WHERE NOT EXISTS (
        SELECT * FROM periods px
        WHERE px.start_date > p0.start_date
        AND px.start_date < p1.start_date
        )
    )
SELECT it.item_id
    , it.item
    , it.save_date
    , pe.period_id
    , pe.period_name
    , pe.start_date
    , pe.end_date
FROM items it
LEFT JOIN pe
       ON it.save_date >= pe.start_date
      AND ( it.save_date < pe.end_date OR pe.end_date IS NULL)
    ;

结果:

 item_id |  item  | save_date  | period_id | period_name | start_date |  end_date
---------+--------+------------+-----------+-------------+------------+------------
       1 | car    | 2011-12-01 |           |             |            |
       2 | wheel  | 2011-12-10 |         2 | period2     | 2011-12-09 | 2011-12-12
       3 | screen | 2011-12-11 |         2 | period2     | 2011-12-09 | 2011-12-12
       4 | table  | 2011-12-15 |         3 | period3     | 2011-12-12 |
(4 rows)