主页 / dba / 问题 / 289075
Accepted
FranckT
Asked: 2021-04-02 00:48:45 +0800 CST

检测同一 id 的多个有效日期期间，与无效期间交错

  • 772

我的表结构和一些示例数据（测试表），以及我想要的输出（结果）都可以在这里找到。

当我做

SELECT distinct colony, min(date_check), max(date_check)
from test
where protection ='Y'
group by colony

我只按菌落检测 1 个事件。

我应该使用函数还是有其他方法？

ps：如果你想到一个更好的标题来帮助用户，不要犹豫，我在挣扎。

postgresql-9.5 gaps-and-islands

2 个回答

  1. 没有 CTE 和窗口函数：

    SELECT colony, MIN(date_in) date_in, date_out
FROM ( SELECT t1.colony, t1.date_check date_in, MAX(t2.date_check) date_out
       FROM test t1
       JOIN test t2 ON t1.colony = t2.colony
                   AND t1.date_check < t2.date_check
                   AND t1.protection = 'Y'
                   AND t2.protection = 'Y'
                   AND NOT EXISTS ( SELECT NULL
                                    FROM test t3
                                    WHERE t1.colony = t3.colony
                                      AND t1.date_check < t3.date_check
                                      AND t3.date_check < t2.date_check
                                      AND t3.protection != 'Y' )
       GROUP BY t1.colony, t1.date_check ) subquery
GROUP BY colony, date_out
ORDER BY colony, date_in;

    使用 CTE 和窗口函数：

    WITH
cte1 AS (SELECT *, 
                CASE WHEN protection = 'N' OR LAG(protection) OVER (PARTITION BY colony ORDER BY date_check) = 'Y'
                     THEN 0
                     ELSE 1 END AS row_in,
                CASE WHEN protection = 'Y' OR LEAD(protection) OVER (PARTITION BY colony ORDER BY date_check) = 'N'
                     THEN 0
                     ELSE 1 END AS row_out
         FROM test),
cte2 AS (SELECT *, SUM(row_in) OVER (PARTITION BY colony ORDER BY date_check) group_no
         FROM cte1)
SELECT colony, MIN(date_check) date_in, MAX(date_check) date_out
FROM cte2
WHERE protection = 'Y'
GROUP BY colony, group_no
ORDER BY colony, date_in;

    https://dbfiddle.uk/?rdbms=postgres_9.5&fiddle=bf769987d7211b73e89c0564bc6902a5

    • 1
  2. Best Answer

    为了解决您的问题，我执行了以下操作（此处提供了以下所有代码的小提琴）：

    几点：

    • 此解决方案使用LAG()窗口函数。

    • 窗口函数非常强大，并且会回报您多次学习它们所付出的努力。

    • 最后，PostgreSQL 9.5 不再受支持——您最好转向受支持的版本。

    第一步：

    我们获得了菌落或保护发生变化的点。

    SELECT 
  colony, 
  protection, 
  date_check,
  CASE 
    WHEN (LAG(protection, 1) OVER (PARTITION BY colony
                                   ORDER BY date_check) != protection) THEN 1
    ELSE 0
  END AS change 
FROM test;

    结果（为简洁起见）：

    colony  protection    date_check   change
     2           N    2019-10-26        0
     2           N    2019-10-27        0
     2           Y    2019-11-01        1
     2           Y    2019-11-03        0
     7           Y    2019-10-12        0
     7           Y    2019-10-13        0   
 ...
 ...

    第2步：

    我们对变化进行汇总，获得保护 = 'Y' 的每个开始和停止日期的不同记录。

    SELECT 
  colony,
  date_check, 
  SUM(change) OVER (PARTITION BY colony ORDER BY date_check) AS sc
FROM
(
  SELECT 
    colony, 
    protection, 
    date_check,
    CASE 
      WHEN (LAG(protection, 1) OVER (PARTITION BY colony
                                     ORDER BY date_check) != protection) THEN 1
      ELSE 0
    END AS change 
  FROM test
) AS tab
WHERE protection = 'Y'  -- test what happens when we comment out this line...
ORDER BY colony, date_check;

    结果：

    colony  date_check  sc
2       2019-11-01  1
2       2019-11-03  1
7       2019-10-12  0
7       2019-10-13  0
7       2019-10-14  0
7       2019-10-15  0
7       2019-10-16  0
7       2019-10-17  0
7       2019-10-23  1
7       2019-10-24  1
7       2019-10-25  1
7       2019-10-26  1
7       2019-11-01  2
7       2019-11-04  2

    第 3 步：

    最后，我们得到check_date从“N”到“Y”（反之亦然）变化的 MIN() 和 MAX()，但只取那些 where protection= 'Y'。

    SELECT 
  colony,
  MIN(date_check) AS "Date in",
  MAX(date_check) AS "Date out"
  , sc  -- not strictly necessary here in the SELECT - illustrative!
FROM
(
  SELECT 
    colony,
    date_check, 
    SUM(change) OVER (PARTITION BY colony ORDER BY date_check) AS sc
  FROM
  (
    SELECT 
      colony, 
      protection, 
      date_check,
      CASE 
        WHEN (LAG(protection, 1) OVER (PARTITION BY colony
                                       ORDER BY date_check) != protection) THEN 1
        ELSE 0
      END AS change 
    FROM test
  ) AS tab1
  WHERE protection = 'Y'
  ORDER BY colony, date_check
) AS tab2
GROUP BY colony, sc   -- sc not necessary in the SELECT but 
ORDER BY colony, sc;  -- it is required in the   GROUP BY - test!!!

    结果：

    colony    Date in     Date out  sc
     2  2019-11-01  2019-11-03  1
     7  2019-10-12  2019-10-17  0
     7  2019-10-23  2019-10-26  1
     7  2019-11-01  2019-11-04  2

    量子点

    窗口函数解决方案似乎是性能最高的 - 请参阅此处- 多次运行小提琴并改变查询的顺序......我尝试在运行之前预热缓存，EXPLAIN (ANALYZE...)但使用您自己的表进行测试和硬件...

    • 1

相关问题