如何使用Oracle SQL选择第一个连续的行组 [英] How to select the first continuous group of rows using Oracle SQL

问题描述

我有以下数据:

Date             GroupID     Value 
1/01/2000    1                 44 
2/01/2000    1                 55 
3/01/2000    1                 66 
4/01/2000    2                 77 
5/01/2000    2                 88 
6/01/2000    1                 99 
7/01/2000    1                 22 

我正在寻找一个查询，该查询能够按日期排序时选择具有连续GroupID的第一组记录.即在此示例中，我将得到:

I am looking for a query capable of selecting the first group of records with a continous GroupID when I order by Date. i.e. in this example I would get:

1/01/2000    1                 44 
2/01/2000    1                 55 
3/01/2000    1                 66 

随着组ID在下一行中的更改，我将不会获得任何后续数据.

As the group ID changed in the next row, I wouldn't get any subsequent data.

我们将不胜感激

谢谢

Elie

推荐答案

一种方法:

SQL> WITH DATA AS (
  2  SELECT '1/01/2000' mydate, 1 GroupID, 44 Value FROM DUAL
  3  UNION ALL SELECT '2/01/2000', 1, 55 FROM DUAL
  4  UNION ALL SELECT '3/01/2000', 1, 66 FROM DUAL
  5  UNION ALL SELECT '4/01/2000', 2, 77 FROM DUAL
  6  UNION ALL SELECT '5/01/2000', 2, 88 FROM DUAL
  7  UNION ALL SELECT '6/01/2000', 1, 99 FROM DUAL
  8  UNION ALL SELECT '7/01/2000', 1, 22 FROM DUAL
  9  )
 10  SELECT mydate, groupid, VALUE
 11    FROM (SELECT mydate, groupid, VALUE,
 12                 SUM(gap) over(ORDER BY mydate) contiguous_group
 13             FROM (SELECT mydate, groupid, VALUE,
 14                           CASE
 15                              WHEN lag(groupid)
 16                                   over(ORDER BY mydate) != groupid
 17                              THEN
 18                               1
 19                              ELSE
 20                               0
 21                           END gap
 22                      FROM DATA))
 23   WHERE contiguous_group = 0;

MYDATE       GROUPID      VALUE
--------- ---------- ----------
1/01/2000          1         44
2/01/2000          1         55
3/01/2000          1         66

这篇关于如何使用Oracle SQL选择第一个连续的行组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！