程序员经验分享-hwhale

复杂的 SQL 查询 - 折叠日期范围

2024-03-09

我们有一个表格列出了“服务时间”的开始和结束日期。我想要一个可以搜索所有行并根据日期间隔识别服务中断的查询。

Data:
Start         End
1/1/2000   2/1/2001
2/2/2001   4/1/2001
4/1/2004   6/2/2006
6/3/2006   9/1/2010
8/1/2011   9/1/2012

Desired result:
1/1/2001 - 4/1/2001     //The first two ranges collapsed because no break in service 
4/1/2004 - 9/1/2010     // The 3rd and 4th rows collapsed because no real break in service 
8/1/2011 - 9/1/2012

这可能在应用程序逻辑或存储过程中更容易完成，只是想知道是否有任何 SQL 巫术可以让我接近。

Table definition:
CREATE TABLE CONG_MEMBER_TERM 
(
  CONG_MEMBER_TERM_ID NUMBER(10, 0) NOT NULL 
  , CONGRESS_ID NUMBER(10, 0) NOT NULL 
  , CHAMBER_CD VARCHAR2(30 BYTE) NOT NULL 
  , CONG_MEMBER_ID NUMBER(10, 0) NOT NULL 
  , STATE_CD CHAR(2 BYTE) NOT NULL 
  , CONG_MEMBER_TYPE_CD VARCHAR2(30 BYTE) NOT NULL 
  , DISTRICT NUMBER(10, 0) 
  , START_DT TIMESTAMP(6) WITH TIME ZONE 
  , END_DT TIMESTAMP(6) WITH TIME ZONE 
  , CREATE_DT TIMESTAMP(6) WITH TIME ZONE NOT NULL 
  , UPDATE_DT TIMESTAMP(6) WITH TIME ZONE NOT NULL
)

Insert into CONG_MEMBER_TERM (CONG_MEMBER_TERM_ID,CONGRESS_ID,CHAMBER_CD,CONG_MEMBER_ID,STATE_CD,CONG_MEMBER_TYPE_CD,DISTRICT,START_DT,END_DT,CREATE_DT,UPDATE_DT) values (2945,104,'H',494,'OK','REP',2,to_timestamp_tz('04-JAN-95 01.00.00.000000000 AM -05:00','DD-MON-RR HH.MI.SS.FF AM TZR'),to_timestamp_tz('04-OCT-96 01.00.00.000000000 AM -05:00','DD-MON-RR HH.MI.SS.FF AM TZR'),to_timestamp_tz('02-MAY-12 09.45.47.000000000 AM -05:00','DD-MON-RR HH.MI.SS.FF AM TZR'),to_timestamp_tz('02-MAY-12 09.45.48.000000000 AM -05:00','DD-MON-RR HH.MI.SS.FF AM TZR'));
Insert into CONG_MEMBER_TERM (CONG_MEMBER_TERM_ID,CONGRESS_ID,CHAMBER_CD,CONG_MEMBER_ID,STATE_CD,CONG_MEMBER_TYPE_CD,DISTRICT,START_DT,END_DT,CREATE_DT,UPDATE_DT) values (2946,105,'H',494,'OK','REP',2,to_timestamp_tz('07-JAN-97 01.00.00.000000000 AM -05:00','DD-MON-RR HH.MI.SS.FF AM TZR'),to_timestamp_tz('19-DEC-98 01.00.00.000000000 AM -05:00','DD-MON-RR HH.MI.SS.FF AM TZR'),to_timestamp_tz('02-MAY-12 09.45.47.000000000 AM -05:00','DD-MON-RR HH.MI.SS.FF AM TZR'),to_timestamp_tz('02-MAY-12 09.45.49.000000000 AM -05:00','DD-MON-RR HH.MI.SS.FF AM TZR'));
Insert into CONG_MEMBER_TERM (CONG_MEMBER_TERM_ID,CONGRESS_ID,CHAMBER_CD,CONG_MEMBER_ID,STATE_CD,CONG_MEMBER_TYPE_CD,DISTRICT,START_DT,END_DT,CREATE_DT,UPDATE_DT) values (2947,106,'H',494,'OK','REP',2,to_timestamp_tz('06-JAN-99 01.00.00.000000000 AM -05:00','DD-MON-RR HH.MI.SS.FF AM TZR'),to_timestamp_tz('15-DEC-00 01.00.00.000000000 AM -05:00','DD-MON-RR HH.MI.SS.FF AM TZR'),to_timestamp_tz('02-MAY-12 09.45.47.000000000 AM -05:00','DD-MON-RR HH.MI.SS.FF AM TZR'),to_timestamp_tz('02-MAY-12 09.45.49.000000000 AM -05:00','DD-MON-RR HH.MI.SS.FF AM TZR'));
Insert into CONG_MEMBER_TERM (CONG_MEMBER_TERM_ID,CONGRESS_ID,CHAMBER_CD,CONG_MEMBER_ID,STATE_CD,CONG_MEMBER_TYPE_CD,DISTRICT,START_DT,END_DT,CREATE_DT,UPDATE_DT) values (2948,109,'S',494,'OK','SEN',null,to_timestamp_tz('04-JAN-05 01.00.00.000000000 AM -05:00','DD-MON-RR HH.MI.SS.FF AM TZR'),to_timestamp_tz('09-DEC-06 01.00.00.000000000 AM -05:00','DD-MON-RR HH.MI.SS.FF AM TZR'),to_timestamp_tz('02-MAY-12 09.45.48.000000000 AM -05:00','DD-MON-RR HH.MI.SS.FF AM TZR'),to_timestamp_tz('02-MAY-12 09.45.49.000000000 AM -05:00','DD-MON-RR HH.MI.SS.FF AM TZR'));
Insert into CONG_MEMBER_TERM (CONG_MEMBER_TERM_ID,CONGRESS_ID,CHAMBER_CD,CONG_MEMBER_ID,STATE_CD,CONG_MEMBER_TYPE_CD,DISTRICT,START_DT,END_DT,CREATE_DT,UPDATE_DT) values (2949,110,'S',494,'OK','SEN',null,to_timestamp_tz('04-JAN-07 01.00.00.000000000 AM -05:00','DD-MON-RR HH.MI.SS.FF AM TZR'),to_timestamp_tz('02-JAN-09 01.00.00.000000000 AM -05:00','DD-MON-RR HH.MI.SS.FF AM TZR'),to_timestamp_tz('02-MAY-12 09.45.48.000000000 AM -05:00','DD-MON-RR HH.MI.SS.FF AM TZR'),to_timestamp_tz('02-MAY-12 09.45.49.000000000 AM -05:00','DD-MON-RR HH.MI.SS.FF AM TZR'));
Insert into CONG_MEMBER_TERM (CONG_MEMBER_TERM_ID,CONGRESS_ID,CHAMBER_CD,CONG_MEMBER_ID,STATE_CD,CONG_MEMBER_TYPE_CD,DISTRICT,START_DT,END_DT,CREATE_DT,UPDATE_DT) values (2951,111,'S',494,'OK','SEN',null,to_timestamp_tz('06-JAN-09 01.00.00.000000000 AM -05:00','DD-MON-RR HH.MI.SS.FF AM TZR'),to_timestamp_tz('22-DEC-10 01.00.00.000000000 AM -05:00','DD-MON-RR HH.MI.SS.FF AM TZR'),to_timestamp_tz('02-MAY-12 09.45.48.000000000 AM -05:00','DD-MON-RR HH.MI.SS.FF AM TZR'),to_timestamp_tz('02-MAY-12 09.45.49.000000000 AM -05:00','DD-MON-RR HH.MI.SS.FF AM TZR'));
Insert into CONG_MEMBER_TERM (CONG_MEMBER_TERM_ID,CONGRESS_ID,CHAMBER_CD,CONG_MEMBER_ID,STATE_CD,CONG_MEMBER_TYPE_CD,DISTRICT,START_DT,END_DT,CREATE_DT,UPDATE_DT) values (2950,112,'S',494,'OK','SEN',null,to_timestamp_tz('05-JAN-11 01.00.00.000000000 AM -05:00','DD-MON-RR HH.MI.SS.FF AM TZR'),null,to_timestamp_tz('02-MAY-12 09.45.48.000000000 AM -05:00','DD-MON-RR HH.MI.SS.FF AM TZR'),to_timestamp_tz('02-MAY-12 09.45.49.000000000 AM -05:00','DD-MON-RR HH.MI.SS.FF AM TZR'));

如果上一次服务与下一次服务之间的间隔大于 24 个月，则被视为服务“间隔”。

_mike


以下是使用分析函数在 SQL 中折叠时间范围的标准方法和示例。

您的桌子：

SQL> create table mytable (startdate,enddate)
  2  as
  3  select date '2000-01-01', date '2001-02-01' from dual union all
  4  select date '2001-02-02', date '2001-04-01' from dual union all
  5  select date '2004-04-01', date '2006-06-02' from dual union all
  6  select date '2006-06-03', date '2010-09-01' from dual union all
  7  select date '2011-08-01', date '2012-09-01' from dual
  8  /

Table created.

查询：

SQL> select min(startdate) startdate
  2       , max(enddate)   enddate
  3    from ( select startdate
  4                , enddate
  5                , max(rn) over (order by startdate) maxrn
  6             from ( select startdate
  7                         , enddate
  8                         , case lag(enddate) over (order by startdate)
  9                           when startdate-1 then
 10                             null
 11                           else
 12                             rownum
 13                           end rn
 14                      from mytable
 15                  )
 16         )
 17   group by maxrn
 18   order by startdate
 19  /

STARTDATE           ENDDATE
------------------- -------------------
01-01-2000 00:00:00 01-04-2001 00:00:00
01-04-2004 00:00:00 01-09-2010 00:00:00
01-08-2011 00:00:00 01-09-2012 00:00:00

3 rows selected.

它分三个阶段进行：

  1. 仅将唯一的 rownum 分配给作为组开头的那些记录
  2. 为不是组开头的记录提供与组开头相同的编号（使用带有滑动窗口的分析函数 MAX）
  3. 按组号聚合

这个查询的真正优点在于只需要一个 TABLE ACCESS FULL：

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'))
  2  /

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------
SQL_ID  8v1suw8j53tqz, child number 0
-------------------------------------
select min(startdate) startdate      , max(enddate)   enddate   from ( select startdate               , enddate
             , max(rn) over (order by startdate) maxrn            from ( select startdate
, enddate                        , case lag(enddate) over (order by startdate)                          when
startdate-1 then                            null                          else                            rownum
                         end rn                     from mytable                 )        )  group by maxrn
order by startdate

Plan hash value: 2933657513

-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------
|   1 |  SORT ORDER BY           |         |      1 |      5 |      3 |00:00:00.01 |       3 |  2048 |  2048 | 2048  (0)|
|   2 |   HASH GROUP BY          |         |      1 |      5 |      3 |00:00:00.01 |       3 |       |       |          |
|   3 |    VIEW                  |         |      1 |      5 |      5 |00:00:00.01 |       3 |       |       |          |
|   4 |     WINDOW BUFFER        |         |      1 |      5 |      5 |00:00:00.01 |       3 |  2048 |  2048 | 2048  (0)|
|   5 |      VIEW                |         |      1 |      5 |      5 |00:00:00.01 |       3 |       |       |          |
|   6 |       WINDOW SORT        |         |      1 |      5 |      5 |00:00:00.01 |       3 |  2048 |  2048 | 2048  (0)|
|   7 |        COUNT             |         |      1 |        |      5 |00:00:00.01 |       3 |       |       |          |
|   8 |         TABLE ACCESS FULL| MYTABLE |      1 |      5 |      5 |00:00:00.01 |       3 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------


24 rows selected.

Regards,
Rob.

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)

sql

Oracle

复杂的 SQL 查询 - 折叠日期范围 的相关文章

随机推荐

  • Angular6 - 读取文本/纯文本的响应正文

    我正在执行注册操作 当用户成功注册时 我会在后端返回他的 ID 例如 105 当注册失败 用户已存在 时 我返回 USER EXISTS 我已经在 Postman 上检查了请求 响应正文是正确的 在这两种情况下 我都会返回 纯文本 文本 但

  • Django 双向ManyToMany - 如何防止在第二个模型上创建表？

    我有两个模型 每个模型都有一个共享的 ManyToMany 使用 db table 字段 但是如何防止syncdb 尝试为第二个模型创建共享表呢 class Model1 models Model othermodels ManyToMan

  • 您可以使用 Spark SQL/Hive/Presto 直接从 Parquet/S3 复制到 Redshift 吗？

    我们有大量的服务器数据存储在S3 很快将在Parquet格式 数据需要进行一些转换 因此它不能直接从 S3 复制 我将使用Spark访问数据 但我想知道是否可以跳过一个步骤并运行查询来提取 转换数据 然后复制它 而不是使用 Spark 操作

  • 如何将一个 xhtml 文档中的 div 部分提取到另一个 xhtml 文档中

    我正在尝试使用 xslt 将一个 xhtml 文档中的 div 部分提取到另一个 xhtml 文档中 然而 我没有成功 相反 xslt 转换产生了有线输出 假设要转换以下xhtml文档 some blabla div div class t

  • 无损分解与依赖关系保留

    其中任何一个都暗示另一个吗 我的逻辑是 如果保留所有依赖关系 则不会丢失信息 同样 如果分解是无损的 则一定不会违反功能依赖关系 因此本质上 依赖关系保存是确保分解无损的一种方法 我很难接受 否认它 那么这两者是否可以相互保证 或者是否存在

  • 如何从 URL 中排除单词或字符串 - 正则表达式

    我使用以下正则表达式来匹配 PHP 中的所有类型的 URL 效果非常好 reg exUrl b w www s lt gt w d punct s s 但现在 我想排除 Youtube youtu be 和 Vimeo URL 经过研究后我

  • 如何在实体框架中获取 SQL Server 序列的下一个值？

    我想使用 SQL Serversequence objects http msdn microsoft com en IN library ff878091 aspx在实体框架中显示编号规则 然后将其保存到数据库中 在当前场景中 我正在通过

  • 使用 Hotmail smtp 在 PHP 中发送邮件

    我正在尝试使用 Hotmail Smtp 以 PHP 发送邮件 但我收到如下错误 2014 03 13 06 59 01 CLIENT gt SERVER EHLO site com 2014 03 13 06 59 01 CLIENT g

  • 将数学表达式与正则表达式匹配？

    例如 这些是有效的数学表达式 a b c a b 1 50 apple 0 5 boy 1 这些是无效的数学表达式 a b 1 5 0 two consecutive signs two consecutive operators inva

  • 如何使元素对点击透明但仍然可见？

    我有兴趣在 iframe 之类的东西上放置一个嵌入框阴影 虽然将 div 覆盖在 iframe 上的策略可以提供预期的视觉显示 但 div 随后会阻止 iframe 本身上的点击 Sample http jsfiddle net YqXPg

  • GroupBy 列标题前缀上的列

    我有一个数据框 其列名以一组前缀列表开头 我想获取数据框中按以相同前缀开头的列分组的值的总和 df pd DataFrame 1 2 3 4 1 2 3 4 1 2 3 4 1 2 3 4 columns abc abd wxy wxz p

  • 无窗托盘图标应用程序

    好吧 我是 WPF 的新手 但我必须使用 wpf 开发标题中的内容 但不依赖 MVVM 我已经遵循了这个 仅具有托盘图标的 WPF 应用程序 https stackoverflow com questions 1472633 wpf app

  • 消除subst来证明平等

    我试图将 mod n 计数器表示为间隔的一部分 0 n 1 分为两部分 data Counter Set where cut i j Counter suc i j 使用它 定义两个关键操作很简单 为简洁起见 省略了一些证明 1 n Cou

  • 实现 AVAssetDownloadURLSession 下载 HLS 流时出错

    我正在尝试为流应用程序实现离线模式 目标是能够在用户的设备上下载 HLS 流 以便即使用户离线时也可以观看流 我最近偶然发现本教程 https developer apple com library content documentatio

  • Angular2-捕获错误并将其显示在视图中

    我有一个执行 get 请求并抛出错误的服务 服务 ts return this http get apiUrl map response Response gt const items response json return items

  • PNG、GIF、JPEG、SVG 有哪些不同的用例？ [关闭]

    Closed 这个问题需要多问focused help closed questions 目前不接受答案 在构建网站或界面等时何时应使用某些图像文件类型 他们的优点和缺点是什么 我知道PNG和GIF是无损的 而JPEG是有损的 但 PNG

  • 如何制作我的程序的流程图

    我有一个用java编写的非常大的服务器程序 它是由另一位不再在公司工作的开发人员编写的 非常复杂 它由23个包组成 我使用的是eclipse 我想要的是一个程序 它将获取我的程序的源文件并为函数创建一种流程图字符 从具有 main 的类开始

  • 特定计算机上的 C# 控制台应用程序出现奇怪问题

    我有最简单的 C 控制台应用程序 仅执行 WriteLine 和 ReadLine 由于某种原因 它不能在其构建的机器上以独立模式运行 我在另一个盒子上尝试了构建的可执行文件 它工作正常 尝试在这台机器上运行它会导致它挂在那里 它在 VS

  • 如何计算请求到响应的生命周期？

    我目前正在向请求对象添加一个 cookie 并从响应中的当前时间中减去它 但我假设有更好的方法来做到这一点 此外 在实际用例中 重复使用相同的 Cookie 密钥可能行不通 有任何想法吗 RequestFilters Add request

  • 复杂的 SQL 查询 - 折叠日期范围

    我们有一个表格列出了 服务时间 的开始和结束日期 我想要一个可以搜索所有行并根据日期间隔识别服务中断的查询 Data Start End 1 1 2000 2 1 2001 2 2 2001 4 1 2001 4 1 2004 6 2 20

热门标签