问2个或更多记录中开始日期和结束日期之间的Oracle SQL重叠
EN

Stack Overflow用户

提问于 2017-03-14 04:30:55

回答 2查看 776关注 0票数 1

数据库my_table

id     seq     start_date     end_date
1      1       01-01-2017     02-01-2017
1      2       07-01-2017     09-01-2017
1      3       11-01-2017     11-01-2017
2      1       20-01-2017     20-01-2017
3      1       01-02-2017     02-02-2017
3      2       03-02-2017     04-02-2017
3      3       08-01-2017     09-02-2017
3      4       09-01-2017     10-02-2017
3      5       10-01-2017     12-02-2017

我的要求是获取第一个日期(通常是序列1开始日期)和结束日期(通常是最后一个序列结束日期)，以及每个唯一ID在所有序列中发生的日期数。

发生日期：

id      1              2              3
        01-01-2017     20-01-2017     01-02-2017
        02-01-2017                    02-02-2017
        07-01-2017                    03-02-2017
        08-01-2017                    04-02-2017
        09-01-2017                    08-02-2017
        11-01-2017                    09-02-2017
                                      10-02-2017
                                      11-02-2017
                                      12-02-2017
total   6              1              9

下面是我想要的结果：

id     start_date     end_date       num_date
1      01-01-2017     11-01-2017     6
2      20-01-2017     20-01-2017     1
3      01-02-2017     12-02-2017     9

我试过了

SELECT   id
         , MIN(start_date)
         , MAX(end_date)
         , SUM(end_date - start_date + 1)
FROM     my_table
GROUP BY id

由于开始日期和结束日期之间没有重叠日期，因此这条SQL语句在id1和id2中工作得很好。但是对于id 3，结果num_date是11。您能建议使用SQL语句来解决这个问题吗？谢谢。

还有一个问题:数据库中的日期是datetime格式的。如何将其转换为date。我尝试使用TRUNC函数，但有时它会将日期转换为昨天。

sql

oracle

date

aggregate

overlap

回答 2

Stack Overflow用户

发布于 2017-03-14 04:59:25

您需要计算一个end_date等于下面的start_date的次数。为此，您需要使用lag()或lead()分析函数。您可以使用case表达式进行比较，但遗憾的是，您不能将case表达式包装在同一查询中的COUNT或SUM中；您需要一个子查询和一个外部查询。

就像这样；没有经过测试，因为您没有提供CREATE TABLE和INSERT语句来重新创建示例数据。

select   id, min(start_date) as start_date, max(end_date) as end_date,
         sum(end_date - start_date + 1 - flag) as num_days
from     ( select id, start_date, end_date, 
                  case when start_date = lag(end_date) 
                            over (partition by id order by end_date) then 1
                                                                     else 0 end as flag
           from   my_table
         )
group by id;

票数 1

Stack Overflow用户

发布于 2017-03-14 09:28:06

SELECT id,
       MIN( start_date ) AS start_date,
       MAX( end_date )   AS end_date,
       SUM( end_date - start_date + 1 ) AS num_days
FROM   (
  SELECT id,
         GREATEST(
           start_date,
           COALESCE(
             LAG( end_date ) OVER ( PARTITION BY id ORDER BY seq ) + 1,
             start_date
           )
         ) AS start_date,
         end_date
  FROM   your_table
)
WHERE  start_date <= end_date
GROUP BY id;