差距和孤岛问题-对数据的连续运行进行分组，以获得最大和最小日期

Question

我有一个无法使用Oracle解决的鸿沟和孤岛问题。

我正在尝试获取PC_ID和USER_NAME中具有相同值的连续系列中的第一个和最后一个事件(LOGIN_TIME)。在本例中，我需要MIN(LOGIN_TIME)和MAX(LOGIN_TIME)，但仅在'jane‘登录之前的前三个登录中。然后，我希望'jane‘也是这样，以此类推。

查询

SELECT 
    PC_ID,
    USER_NAME,
    LOGIN_TIME
FROM
    LOGIN_AUDIT
WHERE PC_ID = 72
AND LOGIN_TIME BETWEEN '2020-08-10 00:00:00' AND '2020-08-18 00:00:00'
;

上述查询的输出将如下所示：

| PC_ID | USER_NAME | LOGIN_TIME          |
|-------|-----------|---------------------|
| 72    | bob       | 2020-08-10 09:00:00 |
| 72    | bob       | 2020-08-10 13:30:00 |
| 72    | bob       | 2020-08-11 09:00:00 |
| 72    | jane      | 2020-08-12 08:00:00 |
| 72    | jane      | 2020-08-13 09:00:00 |
| 72    | jane      | 2020-08-13 14:30:00 |
| 72    | bob       | 2020-08-14 08:00:00 |
| 72    | bob       | 2020-08-15 08:00:00 |
| 72    | bob       | 2020-08-16 08:00:00 |
| 72    | bob       | 2020-08-17 08:00:00 |

我正在寻找的输出是：

| PC_ID | USER_NAME | FIRST_LOGIN         | LAST_LOGIN          |
|-------|-----------|---------------------|---------------------|
| 72    | bob       | 2020-08-10 09:00:00 | 2020-08-11 09:00:00 |
| 72    | jane      | 2020-08-12 08:00:00 | 2020-08-13 14:30:00 |
| 72    | bob       | 2020-08-14 08:00:00 | 2020-08-17 08:00:00 |

我们将非常感谢您的帮助。

Answer 1

对于这种类型的间隙和岛屿问题，一种简单的方法是行数差法。考虑一下：

WITH cte AS (
    SELECT t.*, ROW_NUMBER() OVER (PARTITION BY PC_ID ORDER BY LOGIN_TIME) rn1,
                ROW_NUMBER() OVER (PARTITION BY PC_ID, USER_NAME ORDER BY LOGIN_TIME) rn2
    FROM yourTable t
)

SELECT
    PC_ID,
    USER_NAME,
    MIN(LOGIN_TIME) AS FIRST_LOGIN,
    MAX(LOGIN_TIME) AS LAST_LOGIN
FROM cte
GROUP BY
    PC_ID,
    USER_NAME,
    (rn1 - rn2)
ORDER BY
    MIN(LOGIN_TIME);

​

​

​

Demo

​