sql - 比较多个日期范围

Question

我使用的是 iReport 3.0.0，PostgreSQL 9.1。对于报告，我需要将发票的日期范围与过滤器中的日期范围进行比较，如果过滤器范围覆盖、部分覆盖等，则打印每个发票代码。为了复杂化事情，每个发票代码可以有多个日期范围。

表格发票

ID  Code    StartDate   EndDate
1   111     1.5.2012    31.5.2012
2   111     1.7.2012    20.7.2012
3   111     25.7.2012   31.7.2012
4   222     1.4.2012    15.4.2012
5   222     18.4.2012   30.4.2012

例子

过滤器:1.5.2012。 - 5.6.2012.
我需要得到的结果是:

code 111 - partialy covered 
code 222 - invoice missing

过滤器:1.5.2012。 - 2012 年 5 月 31 日。

code 111 - fully covered
code 222 - invoice missing

过滤器:1.6.2012。 - 2012 年 6 月 30 日。

code 111 -  invoice missing
code 222 -  invoice missing

Answer 1

在评论中澄清后。

据我了解，您的任务:

检查所有提供的个人日期范围(过滤器)它们是否包含在您的代码集的组合日期范围内表(发票)。

它可以用普通的 SQL 来完成，但这不是一项简单的任务。步骤可以是:

1. 提供日期范围作为过滤器。

2. 按代码合并 invoice 表中的日期范围。每个代码可以产生一个或多个范围。

3. 查找过滤器和组合发票之间的重叠

4. 分类:完全覆盖/部分覆盖。可能导致一次完全覆盖、一次或两次部分覆盖或无覆盖。减少到最大覆盖水平。

5. 以合理的排序顺序为(过滤器、代码)的每个组合及其结果覆盖显示一行

临时过滤器范围

WITH filter(filter_id, startdate, enddate) AS (
    VALUES
      (1, '2012-05-01'::date, '2012-06-05'::date) -- list filters here.
     ,(2, '2012-05-01', '2012-05-31')
     ,(3, '2012-06-01', '2012-06-30')
    )
SELECT * FROM filter;

或者将它们放在一个(临时)表中并改用该表。

按代码组合重叠/相邻的日期范围

WITH a AS (
    SELECT code, startdate, enddate
          ,max(enddate) OVER (PARTITION BY code ORDER BY startdate) AS max_end
-- Calculate the cumulative maximum end of the ranges sorted by start
    FROM   invoice
    ), b AS (
    SELECT *
          ,CASE WHEN lag(max_end) OVER (PARTITION BY code
                                        ORDER BY startdate) + 2 > startdate
-- Compare to the cumulative maximum end of the last row.
-- Only if there is a gap, start a new group. Therefore the + 2.
           THEN 0 ELSE 1 END AS step
    FROM   a
    ), c AS (
    SELECT code, startdate, enddate, max_end
          ,sum(step) OVER (PARTITION BY code ORDER BY startdate) AS grp
-- Members of the same date range end up in the same grp
-- If there is a gap, the grp number is incremented one step
    FROM   b
    )
SELECT code, grp
      ,min(startdate) AS startdate
      ,max(enddate) AS enddate
FROM   c
GROUP  BY 1, 2
ORDER  BY 1, 2

替代的最终 SELECT(可能更快也可能不快，您必须测试):

SELECT DISTINCT code, grp
          ,first_value(startdate) OVER w AS startdate
          ,last_value(enddate) OVER w AS enddate
FROM   c
WINDOW W AS (PARTITION BY code, grp ORDER BY startdate
             RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) 
ORDER  BY 1, 2;

合并为一个查询

WITH 
    -- supply one or more filter values
    filter(filter_id, startdate, enddate) AS (
    VALUES
      (1, '2012-05-01'::date, '2012-06-05'::date) -- cast values in first row
     ,(2, '2012-05-01', '2012-05-31')
     ,(3, '2012-06-01', '2012-06-30')
    )
    -- combine date ranges per code
    ,a AS (
    SELECT code, startdate, enddate
          ,max(enddate) OVER (PARTITION BY code ORDER BY startdate) AS max_end
    FROM   invoice
    ), b AS (
    SELECT *
          ,CASE WHEN (lag(max_end) OVER (PARTITION BY code ORDER BY startdate)
                      + 2) > startdate THEN 0 ELSE 1 END AS step
    FROM   a
    ), c AS (
    SELECT code, startdate, enddate, max_end
          ,sum(step) OVER (PARTITION BY code ORDER BY startdate) AS grp
    FROM   b
    ), i AS ( -- substitutes original invoice table
    SELECT code, grp
          ,min(startdate) AS startdate
          ,max(enddate) AS enddate
    FROM   c
    GROUP  BY 1, 2
    )
    -- match filters
    , x AS (
    SELECT f.filter_id, i.code
            ,bool_or(f.startdate >= i.startdate
              AND f.enddate   <= i.enddate) AS full_cover
    FROM   filter f
    JOIN   i ON i.enddate >= f.startdate
            AND i.startdate <= f.enddate -- only overlapping
    GROUP  BY 1,2
    )
SELECT f.*, i.code
      ,CASE x.full_cover
        WHEN TRUE  THEN 'fully covered'
        WHEN FALSE THEN 'partially covered'
        ELSE            'invoice missing'
       END AS covered
FROM   (SELECT DISTINCT code FROM i) i
CROSS  JOIN filter f -- all combinations of filter and code
LEFT   JOIN x USING (filter_id, code)    -- join in overlapping
ORDER  BY filter_id, code;

在 PostgreSQL 9.1 上测试并为我工作。