比较多个日期范围 [英] Compare multiple date ranges
问题描述
我正在使用iReport 3.0.0，PostgreSQL 9.1。对于报告，我需要将发票中的日期范围与过滤器中的日期范围进行比较，如果过滤器范围涵盖，部分涵盖等，则每个发票代码打印一次，等等。事情，每个发票代码可以有多个日期范围。
I am using iReport 3.0.0, PostgreSQL 9.1. For a report I need to compare date ranges from invoices with date ranges in filters and print for every invoice code if a filter range is covered, partially covered, etc. To complicate things, there can be multiple date ranges per invoice code.
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。
我需要得到的结果是：
Examples
Filter: 1.5.2012. - 5.6.2012.
Result that I need to get is:
code 111 - partialy covered
code 222 - invoice missing
过滤器：1.5.2012。 - 2012年5月31日。
Filter: 1.5.2012. - 31.5.2012.
code 111 - fully covered
code 222 - invoice missing
过滤：1.6.2012。 - 30.6.2012。
Filter: 1.6.2012. - 30.6.2012.
code 111 - invoice missing
code 222 - invoice missing
推荐答案
在评论中澄清。
检查所有提供的个人日期范围（过滤器）表中是否包含组合日期范围的代码集（ invoice ）。
Check for all supplied individual date ranges (filter) whether they are are covered by the combined date ranges of sets of codes in your table (invoice).
可以使用纯SQL完成，但不是一项简单的任务。步骤可以是：
It can be done with plain SQL, but it is not a trivial task. The steps could be:
-
将日期范围作为过滤器。
Supply date ranges as filters.
将每个代码的 invoice 表中的日期范围组合在一起。
每个代码可以生成一个或多个范围。
Combine date ranges in invoice table per code.
Can result in one or more ranges per code.
查找过滤器和合并发票之间的重叠
Look for overlaps between filters and combined invoices
分类：完全覆盖/部分覆盖。
可以产生一次全覆盖，一次或两次部分覆盖或无覆盖。
降低到最高覆盖水平。
Classify: fully covered / partially covered. Can result in one full coverage, one or two partial coverages or no coverage. Reduce to maximum level of coverage.
显示每行（过滤器，代码）组合的一行以及最终的覆盖范围订单
Display one row for every combination of (filter, code) with the resulting coverage, in a sensible sort order
特设过滤器范围
Ad hoc filter ranges
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;
或将它们放入（临时）表中并使用表格代替。
Or put them in a (temporary) table and use the table instead.
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（可能更快或不更快，你必须测试）：
Alternative final SELECT (may be faster or not, you'll have to test):
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;
合并到一个查询
Combine to one query
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上测试并适用于我。
Tested and works for me on PostgreSQL 9.1.
这篇关于比较多个日期范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
