计算一个岛屿及其起点和终点

发布于 2025-01-14 02:17:13 字数 504 浏览 0 评论 0原文

我有一个如下所示的表格：

Table

我想要的是根据 Day_Ranking 列计算数据岛，所以输出看起来与此类似：

Person           Start Date           End Date           Count
 45286            2021-08-26           2021-09-07         8
 13914            2021-09-22           2021-10-01         7
 32914            2021-01-10           2021-01-10         1
 32914            2021-01-12           2021-01-13         2

关于如何执行此操作的任何建议？我正在使用 Oracle SQL 执行此操作

原文

I have a table that looks like this:

Table

What I would like is to count the islands of data based of the Day_Ranking column, so the output would look like something similar to this:

Person           Start Date           End Date           Count
 45286            2021-08-26           2021-09-07         8
 13914            2021-09-22           2021-10-01         7
 32914            2021-01-10           2021-01-10         1
 32914            2021-01-12           2021-01-13         2

Any suggestions on how I can do this?
I am doing this is with Oracle SQL

分享到QQ

分享到微博

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

发布评论

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

谁许谁一生繁华 2025-01-21 02:17:13

从 Oracle 12 开始，您可以使用 MATCH_RECOGNIZE 执行逐行比较：

SELECT *
FROM   table_name
MATCH_RECOGNIZE(
  PARTITION BY person
  ORDER BY day_ranking
  MEASURES
    FIRST(date_value) AS start_date,
    LAST(date_value) AS end_date,
    COUNT(*) AS count
  ONE ROW PER MATCH
  PATTERN (successive_ranks* last_rank)
  DEFINE successive_ranks AS day_ranking + 1 = NEXT(day_ranking)
)

对于示例数据：

CREATE TABLE table_name (person, date_value, day_ranking) AS
SELECT 1, DATE '2022-01-01',  6 FROM DUAL UNION ALL
SELECT 1, DATE '2022-01-02',  7 FROM DUAL UNION ALL
SELECT 1, DATE '2022-01-04',  8 FROM DUAL UNION ALL
SELECT 1, DATE '2022-01-05',  9 FROM DUAL UNION ALL
SELECT 1, DATE '2022-01-07', 10 FROM DUAL UNION ALL
SELECT 1, DATE '2022-01-09', 11 FROM DUAL UNION ALL
SELECT 2, DATE '2022-01-01', 15 FROM DUAL UNION ALL
SELECT 2, DATE '2022-01-02', 16 FROM DUAL UNION ALL
SELECT 2, DATE '2022-01-03', 17 FROM DUAL UNION ALL
SELECT 2, DATE '2022-01-04', 18 FROM DUAL UNION ALL
SELECT 2, DATE '2022-01-05', 19 FROM DUAL UNION ALL
SELECT 3, DATE '2022-01-01', 25 FROM DUAL UNION ALL
SELECT 3, DATE '2022-01-03', 27 FROM DUAL;

输出：

<表类=“s-表”>
<标题>
人
START_DATE
END_DATE
COUNT

<正文>
1
22 年 1 月 1 日
22 年 1 月 9 日
6
2
22 年 1 月 1 日
22 年 1 月 5 日
5
3
22 年 1 月 1 日
22 年 1 月 1 日
1
3
22 年 1 月 3 日
22 年 1 月 3 日
1

db>>小提琴此处

From Oracle 12, you can use MATCH_RECOGNIZE to perform a row-by-row comparison:

SELECT *
FROM   table_name
MATCH_RECOGNIZE(
  PARTITION BY person
  ORDER BY day_ranking
  MEASURES
    FIRST(date_value) AS start_date,
    LAST(date_value) AS end_date,
    COUNT(*) AS count
  ONE ROW PER MATCH
  PATTERN (successive_ranks* last_rank)
  DEFINE successive_ranks AS day_ranking + 1 = NEXT(day_ranking)
)

Which, for the sample data:

CREATE TABLE table_name (person, date_value, day_ranking) AS
SELECT 1, DATE '2022-01-01',  6 FROM DUAL UNION ALL
SELECT 1, DATE '2022-01-02',  7 FROM DUAL UNION ALL
SELECT 1, DATE '2022-01-04',  8 FROM DUAL UNION ALL
SELECT 1, DATE '2022-01-05',  9 FROM DUAL UNION ALL
SELECT 1, DATE '2022-01-07', 10 FROM DUAL UNION ALL
SELECT 1, DATE '2022-01-09', 11 FROM DUAL UNION ALL
SELECT 2, DATE '2022-01-01', 15 FROM DUAL UNION ALL
SELECT 2, DATE '2022-01-02', 16 FROM DUAL UNION ALL
SELECT 2, DATE '2022-01-03', 17 FROM DUAL UNION ALL
SELECT 2, DATE '2022-01-04', 18 FROM DUAL UNION ALL
SELECT 2, DATE '2022-01-05', 19 FROM DUAL UNION ALL
SELECT 3, DATE '2022-01-01', 25 FROM DUAL UNION ALL
SELECT 3, DATE '2022-01-03', 27 FROM DUAL;

Outputs: