差距和岛屿 - 使用 Postgresl 获取某个日期范围内的失业日期列表

Question

我有一个名为职位的表格，在此表格中，我有以下内容，日期包括(yyyy-mm-dd(，以下是就业日期的简化视图

id, person_id, start_date, end_date  , title
1 , 1        , 2001-12-01, 2002-01-31, 'admin'
2 , 1        , 2002-02-11, 2002-03-31, 'admin'
3 , 1        , 2002-02-15, 2002-05-31, 'sales'
4 , 1        , 2002-06-15, 2002-12-31, 'ops'

我希望能够计算就业差距，假设某些日期重叠，为id=1的人产生以下输出

person_id, start_date, end_date  , last_position_id, gap_in_days
1        , 2002-02-01, 2002-02-10, 1               , 10
1        , 2002-06-01, 2002-06-14, 3               , 14

我已经查看了许多解决方案，UNIONS，物化视图，带有生成的日历日期范围的表格等。我真的不确定这样做的最佳方法是什么。是否有一个查询可以完成此操作？

Answer 1

逐步演示：db<>fiddle

您只需要lead()窗口功能。这样，您就可以获得当前行的值(在本例中为start_date(。

SELECT
    person_id,
    end_date + 1 AS start_date,
    lead - 1 AS end_date,
    id AS last_position_id,
    lead - (end_date + 1) AS gap_in_days
FROM (
    SELECT 
        *,
        lead(start_date) OVER (PARTITION BY person_id ORDER BY start_date)
    FROM
        positions
) s
WHERE lead - (end_date + 1) > 0

获得下一个start_date后，您可以将其与当前end_date进行比较。如果它们不同，你就有差距。可以在WHERE子句中筛选这些正值。

(如果 2 个位置重叠，则差异为负。所以可以忽略它。

Answer 2

• 首先，您需要查找哪些日期重叠 确定两个日期范围是否重叠
• 然后将这些范围合并为一个范围并保留最后一个 ID
• 最后计算一个end_date和下一个start_date - 1之间的天数范围

SQL 演示

with find_overlap as (
  SELECT t1."id" as t1_id, t1."person_id", t1."start_date", t1."end_date",
         t2."id" as t2_id, t2."start_date" as t2_start_date, t2."end_date" as t2_end_date
  FROM Table1 t1
  LEFT JOIN Table1 t2
    ON t1."person_id" = t2."person_id"
   AND t1."start_date" <= t2."end_date"
   AND t1."end_date"   >= t2."start_date"
   AND t1.id < t2.id
), merge_overlap as (
  SELECT 
         person_id,
         start_date,
         COALESCE(t2_end_date, end_date) as  end_date,
         COALESCE(t2_id, t1_id) as last_position_id
  FROM find_overlap
  WHERE t1_id NOT IN (SELECT t2_id FROM find_overlap WHERE t2_ID IS NOT NULL)
), cte as (
  SELECT *, 
         LEAD(start_date) OVER (partition by person_id order by start_date) next_start
  FROM merge_overlap 
) 
SELECT *, 
       DATE_PART('day', 
                  (next_start::timestamp - INTERVAL '1 DAY') - end_date::timestamp
                ) as days 
FROM cte
WHERE next_start IS NOT NULL 

输出

| person_id | start_date |   end_date | last_position_id | next_start | days |
|-----------|------------|------------|------------------|------------|------|
|         1 | 2001-12-01 | 2002-01-31 |                1 | 2002-02-11 |   10 |
|         1 | 2002-02-11 | 2002-05-31 |                3 | 2002-06-15 |   14 |