🦊 Catch the Fish
Posts Tags Categories About 🦊
🦊 Catch the Fish

Contents

数据库滑动窗口 一题流

nil zhuang included in SE
   2092 words   5 minutes 

一道题用明白 SQL 滑动窗口

题目描述

写一个 SQL 查询语句，检索连续 3 天都做了核酸而且是阴性的用户

例如对于表格 T

1
2
3
4
5
6
7
8
9

| uid |   tdate  | result |
| --- | -------- | ------ |
| 001 | 2023/1/1 |  neg   |
| 002 | 2023/1/1 |  neg   |
| 001 | 2023/1/2 |  neg   |
| 002 | 2023/1/2 |  neg   |
| 001 | 2023/1/3 |  neg   |
| 001 | 2023/1/4 |  neg   |
| 002 | 2023/1/4 |  neg   |

我们期望查询到用户 001 连续 3 天做了核酸，而 002 用户只连续做了两天，漏掉了 2023/1/3 的日期，不满足我们的要求。所以我们的查询结果是

1
2
3

| uid |
| --- |
| 001 |

解法 1: 多表连接

我们可以使用三个表格的 self join 方法，每个表格代表同一用户在不同日期的核酸测试结果，我们可以使用 t.result = 'neg' 来筛选出阴性记录，并通过 DISTINCT 关键字来去除重复。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

SELECT 
    DISTINCT t1.uid
FROM 
    T t1
    JOIN T t2 ON t1.uid = t2.uid
    JOIN T t3 ON t1.uid = t3.uid
WHERE 
    t1.result = 'neg' 
    AND t2.result = 'neg' 
    AND t3.result = 'neg'
    AND t1.tdate = DATE_SUB(t2.tdate, INTERVAL 1 DAY)
    AND t2.tdate = DATE_SUB(t3.tdate, INTERVAL 1 DAY);

当然，上面的 SQL 还有可以优化的空间，因为这样的多表链接存在大量的日期交错问题，表格的 JOIN 操作会有如下的很多冗余无意义结果

1
2
3
4
5
6

| t1.tdate  | t2.tdate  | t3.tdate  |
| -------- | -------- | -------- |
| 2023/1/1 | 2023/1/2 | 2023/1/3 |
| 2023/1/2 | 2023/1/1 | 2023/1/3 |
| 2023/1/1 | 2023/1/1 | 2023/1/1 |
……

我们可以使用日期条件进行 JOIN 操作，可以很大程度上避免重复，让查询更加简单且直观。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

SELECT 
    DISTINCT t1.uid
FROM 
    T t1
    JOIN T t2 
        ON t1.uid = t2.uid 
        AND t1.tdate = DATE_SUB(t2.tdate, INTERVAL 1 DAY)
    JOIN T t3 
        ON t2.uid = t3.uid 
        AND t2.tdate = DATE_SUB(t3.tdate, INTERVAL 1 DAY)
WHERE 
    t1.result = 'neg' 
    AND t2.result = 'neg' 
    AND t3.result = 'neg';

在上面的 SQL 语句中，我们在 JOIN 操作的 ON 子句中加入了日期条件，即 t1.tdate = DATE_SUB(t2.tdate, INTERVAL 1 DAY 这样我们表连接出来的结果就是连续且自增的三天，相比于上面的判断语句减少了开销。

解法 2: 日期与天数的差

上面的例子缺乏扩展性，如果我们更新了要求，需要你检查连续 7 天核酸或 15 天核酸，那么就需要写非常非常多的 join 操作，检索的成本和开销都会大幅提升。有没有什么更好的办法呢？ 我们可以观察到，如果是连续做核酸，那么用户做核酸的日期和一个递增的数字之差应该是相等的。我们可以看下面的 SQL 作为例子

1
2
3
4
5
6
7
8

SELECT
    uid,
    tdate,
    row_number() over(partition by uid order by tdate) date_rank
FROM
    T
WHERE
    T.result = 'neg';

我们可以得到如下的表格

1
2
3
4
5
6
7
8
9

| uid | tdate    | date_rank |
| --- | -------- | --------- |
| 001 | 2023/1/1 | 1         |
| 001 | 2023/1/2 | 2         |
| 001 | 2023/1/3 | 3         |
| 001 | 2023/1/4 | 4         |
| 002 | 2023/1/1 | 1         |
| 002 | 2023/1/2 | 2         |
| 002 | 2023/1/4 | 3         |

之后，我们只需要获取每个核酸日期 (tdate) 中，日期与核酸日期 rank(date_rank) 之间的差值，例如对于 uid 001 的用户，他在 2023/1/3 的核酸日期中 3 号，与它的每日核酸次数排名 date_rank 3 做差值，得到 1 对应的 SQL 如下

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

SELECT
    uid,
    tdate,
    date_rank,
    (date_format(tdate, "%e") - date_rank) as diff
FROM(
    SELECT
        uid,
        tdate,
        row_number() over (
            partition by uid order by tdate
        ) date_rank
    FROM
        T
    WHERE
        T.result = 'neg'
) T1;

我们可以得到如下的结果

1
2
3
4
5
6
7
8
9

| uid | tdate    | date_rank | diff |
| --- | -------- | --------- | ---- |
| 001 | 2023/1/1 | 1         | 0    |
| 001 | 2023/1/2 | 2         | 0    |
| 001 | 2023/1/3 | 3         | 0    |
| 001 | 2023/1/4 | 4         | 0    |
| 002 | 2023/1/1 | 1         | 0    |
| 002 | 2023/1/2 | 2         | 0    |
| 002 | 2023/1/4 | 3         | 1    |

从上表我们可以发现，连续核酸日期的 diff 结果上相等的，例如可以是一系列的 0，一系列的 1 或 2 等。那么我们就可以非常简单的获取用户的连续核酸天数，即只需要对这个表格按照 uid 和 diff 进行 count 聚合就可以。 对应的 SQL 如下：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

SELECT
    uid,
    diff,
    count(tdate) as cont_days
FROM(
    SELECT
        uid,
        tdate,
        date_rank,
        (date_format(tdate, "%e") - date_rank) as diff
    FROM(
        SELECT
            uid,
            tdate,
            row_number() over (
                partition by uid order by tdate
            ) date_rank
        FROM
            T
        WHERE
            T.result = 'neg'
    ) T1
) T2
GROUP BY
    uid,
    diff;

我们可以查到形如下面的表格

1
2
3
4
5

| uid | diff | cont_days |
| --- | ---- | --------- |
| 001 | 0    | 4         |
| 002 | 0    | 2         |
| 002 | 1    | 1         |

这个表格表示，在这个时间段，用户 001 连续做了 4 天核酸，而用户 002 首先连续做了 2 天核酸，然后漏做了 1 天，之后又做了 1 次核酸。 那么最后，我们只需要按照用户的连续核酸天数进行筛选，就可以得到我们的需求目标，SQL 如下：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

SELECT
    DISTINCT uid
FROM(
    SELECT
        uid,
        diff,
        count(tdate) as cont_days
    FROM(
        SELECT
            uid,
            tdate,
            date_rank,
            (date_format(tdate, "%e") - date_rank) as diff
        FROM(
            SELECT
                uid,
                tdate,
                row_number() over (
                    partition by uid order by tdate
                ) date_rank
            FROM
                T
            WHERE
                T.result = 'neg'
        ) T1
    ) T2
    GROUP BY
        uid,
        diff
)
WHERE
    cont_days >= 3;

通过这样的方式，我们就获得了连续 3 天做了核酸的用户，而且也可以非常灵活的扩展到连续 N 天做了核酸的用户。

扩展：连续出现 N 次的数字

上面通过日期和序列做差的方法是利用了日期的自增特性，那么对于更加广泛的场景，只要存在序列化信息，我们都能用上述的方法实现连续出现内容的统计。 在这个问题场景中，我们需要使用 SQL 来检索连续出现 N 次的数字，例如连续出现 3 次，如下表 NUM 所示：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

|  id  | num |
| ---  | --- |
|  1   | 1   |
|  2   | 1   |
|  3   | 1   |
|  4   | 2   |
|  5   | 2   |
|  6   | 1   |
|  7   | 7   |
|  8   | 9   |
|  9   | 9   |
|  10  | 9   |

我们期望检索出连续出现 3 次的数字，结果为

1
2
3
4

| num |
| --- |
|  1  |
|  9  |

可以仿照上述的方法，构造一个分组自增的 num_rank，与 id 做差，进行统计

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

WITH V AS (
    SELECT
        num,
        id - num_rank as diff
    FROM(
        SELECT 
            num,
            row_number() over (
                partition by num order by id
            ) as num_rank,
        FROM
            NUM    
    ) T
)

SELECT
    DISTINCT num
FROM
    V
GROUP BY
    num,
    diff
HAVING
    count(*) >= 3;

在这里，我们把自增的信息从日期变为了 id，本质上还是将一个连续出现的数字组构造一系列组内自增信息，即 num_rank，与这里的全局 id 进行做差，找到不变的序列量。

Conclusion

本文中我们探讨了如何从数据库中检索一个连续出现 N 次的内容。在这里，我们由浅入深，从最直观的多表连接入手，然后使用窗口函数扩展到连续 N 天打卡，最后将这个连续自增信息从日期扩展到了 id 等一系列代表序列的内容，实现了检索连续出现 N 次的数字。

Updated on 2023-12-05
 软件工程
Back | Home