当前位置： 文江博客话题详情

Oracle SQL - 如何返回公共日期期间并“划分”日期当期间之间有间隙时

发布于 2025-01-10 01:15:06 字数 3402 浏览 1 评论 0原文

我试图从下面的数据返回公共日期周期（每个 id），但我找不到一种方法来处理日期周期在公共周期之间有间隙的情况。有人可以帮忙吗？

|id|code_id|code|date_from|date_to|
|--|--|--|--|--|
|10|100|    1000    |02/02/2022             |03/02/2022 23:57:00|
|10|100|    1000    |07/02/2022 01:00:00    |08/02/2022         |
|10|100|    2000    |02/02/2022             |02/02/2022 23:00:00|
|10|100|    2000    |07/02/2022 03:00:00    |08/02/2022         |
|10|200|    2000    |02/02/2022 02:14:00    |04/02/2022 21:37:00|
|20|100|    1000    |01/02/2022 05:00:00    |03/02/2022         |
|30|100|    2000    |02/02/2022             |02/02/2022 23:00:00|
|30|200|    2000    |02/02/2022 02:14:00    |04/02/2022         |
|40|100|    2000    |07/02/2022 03:00:00    |08/02/2022 23:10:00|
|50|200|    2000    |04/02/2022             |04/02/2022 21:37:00|
|50|200|    3000    |04/02/2022 02:12:00    |05/02/2022 23:31:00|

下面的简单查询工作正常，但仅适用于具有一个公共句点（没有间隙）的 id。

我希望 id = 10 返回两行（因为日期之间有间隙），其时间段为：

I) 02/02/2022 00:00:00 <-> 04/02/2022 21:37:00

II) 07/02/2022 01:00:00 <-> 08/02/2022 00:00:00

 SELECT id
       ,MIN(date_from) date_from
       ,MAX(date_to)   date_to
  FROM my_gtt 
  GROUP BY id
  ORDER BY id

当前结果（但 id = 10 不正确）：

|id|date_from|date_to|
|--|--|--|
|10|    02/02/2022          |08/02/2022         |
|20|    01/02/2022 05:00:00 |03/02/2022         |
|30|    02/02/2022          |04/02/2022         |
|40|    07/02/2022 03:00:00 |08/02/2022 23:10:00|
|50|    04/02/2022          |05/02/2022 23:31:00|

数据和表创建：

CREATE GLOBAL TEMPORARY TABLE my_gtt
(
  id         NUMBER(10),
  code_id    NUMBER(10),
  code       NUMBER(10),
  date_from  DATE,
  date_to    DATE
)
ON COMMIT PRESERVE ROWS;

INSERT INTO my_gtt VALUES (10, 100, 1000, TO_DATE('02-02-2022', 'dd-mm-yyyy'), TO_DATE('03-02-2022 23:57:00', 'dd-mm-yyyy hh24:mi:ss'));
INSERT INTO my_gtt VALUES (10, 100, 1000, TO_DATE('07-02-2022 01:00:00', 'dd-mm-yyyy hh24:mi:ss'), TO_DATE('08-02-2022', 'dd-mm-yyyy'));
INSERT INTO my_gtt VALUES (10, 100, 2000, TO_DATE('02-02-2022', 'dd-mm-yyyy'), TO_DATE('02-02-2022 23:00:00', 'dd-mm-yyyy hh24:mi:ss'));
INSERT INTO my_gtt VALUES (10, 100, 2000, TO_DATE('07-02-2022 03:00:00', 'dd-mm-yyyy hh24:mi:ss'), TO_DATE('08-02-2022', 'dd-mm-yyyy'));
INSERT INTO my_gtt VALUES (10, 200, 2000, TO_DATE('02-02-2022 02:14:00', 'dd-mm-yyyy hh24:mi:ss'), TO_DATE('04-02-2022 21:37:00', 'dd-mm-yyyy hh24:mi:ss'));
INSERT INTO my_gtt VALUES (20, 100, 1000, TO_DATE('01-02-2022 05:00:00', 'dd-mm-yyyy hh24:mi:ss'), TO_DATE('03-02-2022', 'dd-mm-yyyy'));
INSERT INTO my_gtt VALUES (30, 100, 2000, TO_DATE('02-02-2022', 'dd-mm-yyyy'), TO_DATE('02-02-2022 23:00:00', 'dd-mm-yyyy hh24:mi:ss'));
INSERT INTO my_gtt VALUES (30, 200, 2000, TO_DATE('02-02-2022 02:14:00', 'dd-mm-yyyy hh24:mi:ss'), TO_DATE('04-02-2022', 'dd-mm-yyyy'));
INSERT INTO my_gtt VALUES (40, 100, 2000, TO_DATE('07-02-2022 03:00:00', 'dd-mm-yyyy hh24:mi:ss'), TO_DATE('08-02-2022 23:10:00', 'dd-mm-yyyy hh24:mi:ss'));
INSERT INTO my_gtt VALUES (50, 200, 2000, TO_DATE('04-02-2022', 'dd-mm-yyyy'), TO_DATE('04-02-2022 21:37:00', 'dd-mm-yyyy hh24:mi:ss'));
INSERT INTO my_gtt VALUES (50, 200, 3000, TO_DATE('04-02-2022 02:12:00', 'dd-mm-yyyy hh24:mi:ss'), TO_DATE('05-02-2022 23:31:00', 'dd-mm-yyyy hh24:mi:ss'));
原文

I'm trying to return common date periods (per id) from below data, but I cannot find a way to handle case when date periods have a gap between common periods. Can anyone help?

|id|code_id|code|date_from|date_to|
|--|--|--|--|--|
|10|100|    1000    |02/02/2022             |03/02/2022 23:57:00|
|10|100|    1000    |07/02/2022 01:00:00    |08/02/2022         |
|10|100|    2000    |02/02/2022             |02/02/2022 23:00:00|
|10|100|    2000    |07/02/2022 03:00:00    |08/02/2022         |
|10|200|    2000    |02/02/2022 02:14:00    |04/02/2022 21:37:00|
|20|100|    1000    |01/02/2022 05:00:00    |03/02/2022         |
|30|100|    2000    |02/02/2022             |02/02/2022 23:00:00|
|30|200|    2000    |02/02/2022 02:14:00    |04/02/2022         |
|40|100|    2000    |07/02/2022 03:00:00    |08/02/2022 23:10:00|
|50|200|    2000    |04/02/2022             |04/02/2022 21:37:00|
|50|200|    3000    |04/02/2022 02:12:00    |05/02/2022 23:31:00|

Below simple query works fine, but only for ids which have one common period (with no gaps).

I would expect for id = 10 to return two rows (as there is a gap between dates) for periods which are:

I) 02/02/2022 00:00:00 <-> 04/02/2022 21:37:00

II) 07/02/2022 01:00:00 <-> 08/02/2022 00:00:00

 SELECT id
       ,MIN(date_from) date_from
       ,MAX(date_to)   date_to
  FROM my_gtt 
  GROUP BY id
  ORDER BY id

Current results (but id = 10 is incorrect):

|id|date_from|date_to|
|--|--|--|
|10|    02/02/2022          |08/02/2022         |
|20|    01/02/2022 05:00:00 |03/02/2022         |
|30|    02/02/2022          |04/02/2022         |
|40|    07/02/2022 03:00:00 |08/02/2022 23:10:00|
|50|    04/02/2022          |05/02/2022 23:31:00|

Data and table creation:

CREATE GLOBAL TEMPORARY TABLE my_gtt
(
  id         NUMBER(10),
  code_id    NUMBER(10),
  code       NUMBER(10),
  date_from  DATE,
  date_to    DATE
)
ON COMMIT PRESERVE ROWS;

INSERT INTO my_gtt VALUES (10, 100, 1000, TO_DATE('02-02-2022', 'dd-mm-yyyy'), TO_DATE('03-02-2022 23:57:00', 'dd-mm-yyyy hh24:mi:ss'));
INSERT INTO my_gtt VALUES (10, 100, 1000, TO_DATE('07-02-2022 01:00:00', 'dd-mm-yyyy hh24:mi:ss'), TO_DATE('08-02-2022', 'dd-mm-yyyy'));
INSERT INTO my_gtt VALUES (10, 100, 2000, TO_DATE('02-02-2022', 'dd-mm-yyyy'), TO_DATE('02-02-2022 23:00:00', 'dd-mm-yyyy hh24:mi:ss'));
INSERT INTO my_gtt VALUES (10, 100, 2000, TO_DATE('07-02-2022 03:00:00', 'dd-mm-yyyy hh24:mi:ss'), TO_DATE('08-02-2022', 'dd-mm-yyyy'));
INSERT INTO my_gtt VALUES (10, 200, 2000, TO_DATE('02-02-2022 02:14:00', 'dd-mm-yyyy hh24:mi:ss'), TO_DATE('04-02-2022 21:37:00', 'dd-mm-yyyy hh24:mi:ss'));
INSERT INTO my_gtt VALUES (20, 100, 1000, TO_DATE('01-02-2022 05:00:00', 'dd-mm-yyyy hh24:mi:ss'), TO_DATE('03-02-2022', 'dd-mm-yyyy'));
INSERT INTO my_gtt VALUES (30, 100, 2000, TO_DATE('02-02-2022', 'dd-mm-yyyy'), TO_DATE('02-02-2022 23:00:00', 'dd-mm-yyyy hh24:mi:ss'));
INSERT INTO my_gtt VALUES (30, 200, 2000, TO_DATE('02-02-2022 02:14:00', 'dd-mm-yyyy hh24:mi:ss'), TO_DATE('04-02-2022', 'dd-mm-yyyy'));
INSERT INTO my_gtt VALUES (40, 100, 2000, TO_DATE('07-02-2022 03:00:00', 'dd-mm-yyyy hh24:mi:ss'), TO_DATE('08-02-2022 23:10:00', 'dd-mm-yyyy hh24:mi:ss'));
INSERT INTO my_gtt VALUES (50, 200, 2000, TO_DATE('04-02-2022', 'dd-mm-yyyy'), TO_DATE('04-02-2022 21:37:00', 'dd-mm-yyyy hh24:mi:ss'));
INSERT INTO my_gtt VALUES (50, 200, 3000, TO_DATE('04-02-2022 02:12:00', 'dd-mm-yyyy hh24:mi:ss'), TO_DATE('05-02-2022 23:31:00', 'dd-mm-yyyy hh24:mi:ss'));

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（2）

夜雨飘雪 2025-01-17 01:15:06

从 Oracle 12 开始，MATCH_RECOGNIZE 是最简单的解决方案：

SELECT *
FROM   my_gtt
MATCH_RECOGNIZE (
  PARTITION BY id
  ORDER     BY date_from, date_to
  MEASURES
    MIN(date_from) AS start_date,
    MAX(date_to)   AS end_date
  PATTERN (overlap* last_row)
  DEFINE
    overlap AS MAX(date_to) >= NEXT(date_from)
);

但是，如果您使用的是早期版本，则可以使用以下方式找到输出：

SELECT id,
       MIN(dt) AS date_from,
       MAX(dt) AS date_to
FROM   (
  SELECT id,
         dt,
         SUM(value) OVER (PARTITION BY id ORDER BY dt, ROWNUM) AS match_no
  FROM   (
    SELECT id,
           dt,
           type * SUM(type) OVER (PARTITION BY id ORDER BY dt, ROWNUM) AS value
    FROM   my_gtt
    UNPIVOT (dt FOR type IN (date_from AS 1, date_to AS -1))
  )
  WHERE  value IN (1,0)
)
GROUP BY id, match_no

对于示例数据：

INSERT INTO my_gtt VALUES (10, 100, 1000, TO_DATE('02-02-2022', 'dd-mm-yyyy'), TO_DATE('03-02-2022 23:57:00', 'dd-mm-yyyy hh24:mi:ss'));
INSERT INTO my_gtt VALUES (10, 100, 1000, TO_DATE('07-02-2022 01:00:00', 'dd-mm-yyyy hh24:mi:ss'), TO_DATE('08-02-2022', 'dd-mm-yyyy'));
INSERT INTO my_gtt VALUES (10, 100, 2000, TO_DATE('02-02-2022', 'dd-mm-yyyy'), TO_DATE('02-02-2022 23:00:00', 'dd-mm-yyyy hh24:mi:ss'));
INSERT INTO my_gtt VALUES (10, 100, 2000, TO_DATE('07-02-2022 03:00:00', 'dd-mm-yyyy hh24:mi:ss'), TO_DATE('08-02-2022', 'dd-mm-yyyy'));
INSERT INTO my_gtt VALUES (10, 200, 2000, TO_DATE('02-02-2022 02:14:00', 'dd-mm-yyyy hh24:mi:ss'), TO_DATE('04-02-2022 21:37:00', 'dd-mm-yyyy hh24:mi:ss'));
INSERT INTO my_gtt VALUES (20, 100, 1000, TO_DATE('01-02-2022 05:00:00', 'dd-mm-yyyy hh24:mi:ss'), TO_DATE('03-02-2022', 'dd-mm-yyyy'));
INSERT INTO my_gtt VALUES (30, 100, 2000, TO_DATE('02-02-2022', 'dd-mm-yyyy'), TO_DATE('02-02-2022 23:00:00', 'dd-mm-yyyy hh24:mi:ss'));
INSERT INTO my_gtt VALUES (30, 200, 2000, TO_DATE('02-02-2022 02:14:00', 'dd-mm-yyyy hh24:mi:ss'), TO_DATE('04-02-2022', 'dd-mm-yyyy'));
INSERT INTO my_gtt VALUES (40, 100, 2000, TO_DATE('07-02-2022 03:00:00', 'dd-mm-yyyy hh24:mi:ss'), TO_DATE('08-02-2022 23:10:00', 'dd-mm-yyyy hh24:mi:ss'));
INSERT INTO my_gtt VALUES (50, 200, 2000, TO_DATE('04-02-2022', 'dd-mm-yyyy'), TO_DATE('04-02-2022 21:37:00', 'dd-mm-yyyy hh24:mi:ss'));
INSERT INTO my_gtt VALUES (50, 200, 3000, TO_DATE('04-02-2022 02:12:00', 'dd-mm-yyyy hh24:mi:ss'), TO_DATE('05-02-2022 23:31:00', 'dd-mm-yyyy hh24:mi:ss'));
INSERT INTO my_gtt VALUES (60, 200, 3000, DATE '2022-01-01', DATE '2022-01-10');
INSERT INTO my_gtt VALUES (60, 200, 3000, DATE '2022-01-02', DATE '2022-01-04');
INSERT INTO my_gtt VALUES (60, 200, 3000, DATE '2022-01-06', DATE '2022-01-11');
INSERT INTO my_gtt VALUES (60, 200, 3000, DATE '2022-01-13', DATE '2022-01-16');
INSERT INTO my_gtt VALUES (60, 200, 3000, DATE '2022-01-14', DATE '2022-01-15');

两个输出：

<表类=“s-表”>
<标题>

ID
START_DATE
END_DATE


<正文>

10
2022-02-02 00:00:00
2022-02-04 21:37:00

10
2022-02-07 01:00:00
2022-02-08 00:00:00

20
2022-02-01 05:00:00
2022-02-03 00:00:00

30
2022-02-02 00:00:00
2022-02-04 00:00:00

40
2022-02-07 03:00:00
2022-02-08 23:10:00

50
2022-02-04 00:00:00
2022-02-05 23:31:00

60
2022-01-01 00:00:00
2022-01-11 00:00:00

60
2022-01-13 00:00:00
2022-01-16 00:00:00

db<>fiddle 此处

From Oracle 12, MATCH_RECOGNIZE is the simplest solution:

SELECT *
FROM   my_gtt
MATCH_RECOGNIZE (
  PARTITION BY id
  ORDER     BY date_from, date_to
  MEASURES
    MIN(date_from) AS start_date,
    MAX(date_to)   AS end_date
  PATTERN (overlap* last_row)
  DEFINE
    overlap AS MAX(date_to) >= NEXT(date_from)
);

However, if you are on an earlier version you can find the output using:

SELECT id,
       MIN(dt) AS date_from,
       MAX(dt) AS date_to
FROM   (
  SELECT id,
         dt,
         SUM(value) OVER (PARTITION BY id ORDER BY dt, ROWNUM) AS match_no
  FROM   (
    SELECT id,
           dt,
           type * SUM(type) OVER (PARTITION BY id ORDER BY dt, ROWNUM) AS value
    FROM   my_gtt
    UNPIVOT (dt FOR type IN (date_from AS 1, date_to AS -1))
  )
  WHERE  value IN (1,0)
)
GROUP BY id, match_no

Which, for the sample data:

INSERT INTO my_gtt VALUES (10, 100, 1000, TO_DATE('02-02-2022', 'dd-mm-yyyy'), TO_DATE('03-02-2022 23:57:00', 'dd-mm-yyyy hh24:mi:ss'));
INSERT INTO my_gtt VALUES (10, 100, 1000, TO_DATE('07-02-2022 01:00:00', 'dd-mm-yyyy hh24:mi:ss'), TO_DATE('08-02-2022', 'dd-mm-yyyy'));
INSERT INTO my_gtt VALUES (10, 100, 2000, TO_DATE('02-02-2022', 'dd-mm-yyyy'), TO_DATE('02-02-2022 23:00:00', 'dd-mm-yyyy hh24:mi:ss'));
INSERT INTO my_gtt VALUES (10, 100, 2000, TO_DATE('07-02-2022 03:00:00', 'dd-mm-yyyy hh24:mi:ss'), TO_DATE('08-02-2022', 'dd-mm-yyyy'));
INSERT INTO my_gtt VALUES (10, 200, 2000, TO_DATE('02-02-2022 02:14:00', 'dd-mm-yyyy hh24:mi:ss'), TO_DATE('04-02-2022 21:37:00', 'dd-mm-yyyy hh24:mi:ss'));
INSERT INTO my_gtt VALUES (20, 100, 1000, TO_DATE('01-02-2022 05:00:00', 'dd-mm-yyyy hh24:mi:ss'), TO_DATE('03-02-2022', 'dd-mm-yyyy'));
INSERT INTO my_gtt VALUES (30, 100, 2000, TO_DATE('02-02-2022', 'dd-mm-yyyy'), TO_DATE('02-02-2022 23:00:00', 'dd-mm-yyyy hh24:mi:ss'));
INSERT INTO my_gtt VALUES (30, 200, 2000, TO_DATE('02-02-2022 02:14:00', 'dd-mm-yyyy hh24:mi:ss'), TO_DATE('04-02-2022', 'dd-mm-yyyy'));
INSERT INTO my_gtt VALUES (40, 100, 2000, TO_DATE('07-02-2022 03:00:00', 'dd-mm-yyyy hh24:mi:ss'), TO_DATE('08-02-2022 23:10:00', 'dd-mm-yyyy hh24:mi:ss'));
INSERT INTO my_gtt VALUES (50, 200, 2000, TO_DATE('04-02-2022', 'dd-mm-yyyy'), TO_DATE('04-02-2022 21:37:00', 'dd-mm-yyyy hh24:mi:ss'));
INSERT INTO my_gtt VALUES (50, 200, 3000, TO_DATE('04-02-2022 02:12:00', 'dd-mm-yyyy hh24:mi:ss'), TO_DATE('05-02-2022 23:31:00', 'dd-mm-yyyy hh24:mi:ss'));
INSERT INTO my_gtt VALUES (60, 200, 3000, DATE '2022-01-01', DATE '2022-01-10');
INSERT INTO my_gtt VALUES (60, 200, 3000, DATE '2022-01-02', DATE '2022-01-04');
INSERT INTO my_gtt VALUES (60, 200, 3000, DATE '2022-01-06', DATE '2022-01-11');
INSERT INTO my_gtt VALUES (60, 200, 3000, DATE '2022-01-13', DATE '2022-01-16');
INSERT INTO my_gtt VALUES (60, 200, 3000, DATE '2022-01-14', DATE '2022-01-15');

Both output:

IDSTART_DATEEND_DATE
102022-02-02 00:00:002022-02-04 21:37:00
102022-02-07 01:00:002022-02-08 00:00:00
202022-02-01 05:00:002022-02-03 00:00:00
302022-02-02 00:00:002022-02-04 00:00:00
402022-02-07 03:00:002022-02-08 23:10:00
502022-02-04 00:00:002022-02-05 23:31:00
602022-01-01 00:00:002022-01-11 00:00:00
602022-01-13 00:00:002022-01-16 00:00:00

db<>fiddle here

回复 原文
猫卆 2025-01-17 01:15:06

SQL 模式匹配可以提供帮助：

select * from my_gtt match_recognize (
  partition by id
  order by date_from, date_to
  measures 
    min ( date_from ) start_date,
    max ( date_to ) end_date
  pattern ( overlap* gap )
  define 
    overlap as next ( date_from ) <= max ( date_to )
);

        ID START_DATE           END_DATE            
---------- -------------------- --------------------
        10 02-FEB-2022 00:00:00 04-FEB-2022 21:37:00
        10 07-FEB-2022 01:00:00 08-FEB-2022 00:00:00
        20 01-FEB-2022 05:00:00 03-FEB-2022 00:00:00
        30 02-FEB-2022 00:00:00 04-FEB-2022 00:00:00
        40 07-FEB-2022 03:00:00 08-FEB-2022 23:10:00
        50 04-FEB-2022 00:00:00 05-FEB-2022 23:31:00

我在 模式匹配用例

SQL pattern matching can help:

select * from my_gtt match_recognize (
  partition by id
  order by date_from, date_to
  measures 
    min ( date_from ) start_date,
    max ( date_to ) end_date
  pattern ( overlap* gap )
  define 
    overlap as next ( date_from ) <= max ( date_to )
);

        ID START_DATE           END_DATE            
---------- -------------------- --------------------
        10 02-FEB-2022 00:00:00 04-FEB-2022 21:37:00
        10 07-FEB-2022 01:00:00 08-FEB-2022 00:00:00
        20 01-FEB-2022 05:00:00 03-FEB-2022 00:00:00
        30 02-FEB-2022 00:00:00 04-FEB-2022 00:00:00
        40 07-FEB-2022 03:00:00 08-FEB-2022 23:10:00
        50 04-FEB-2022 00:00:00 05-FEB-2022 23:31:00

I discuss how this works in more detail in pattern matching use cases

回复 原文
~没有更多了~

关于作者

层林尽染

暂无简介

文章
评论
26 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

雪花的坚持

文章 0 评论 0

温柔一刀

文章 0 评论 0

扛起拖把扫天下

文章 0 评论 0

北方的韩爷

文章 0 评论 0

绝對不後悔。

文章 0 评论 0

青衫负雪

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文