主页 / dba / 问题

问题[gaps-and-islands](dba)

Martin Hope
Mr. Geologist
Asked: 2022-01-26 10:52:27 +0800 CST
  • 0

我试图弄清楚如何检索时间序列数据中的空白。下面是我的例子。

CREATE TABLE MYTABLE
    ([ID] int, [DATE] DATE)
;

INSERT INTO MYTABLE
    ([ID], [DATE])
VALUES
    (1, '2022-01-01'),
    (1, '2022-02-01'),
    (1, '2022-03-01'),
    (1, '2022-04-01'),
    (1, '2022-05-01'),
    (1, '2022-06-01'),
    (1, '2022-10-01'),
    (1, '2022-11-01'),
    (1, '2022-12-01'),
    (2, '2022-01-01'),
    (2, '2022-02-01'),
    (2, '2022-03-01'),
    (2, '2022-04-01'),
    (2, '2022-05-01'),
    (2, '2022-06-01'),
    (2, '2022-07-01'),
    (2, '2022-08-01'),
    (2, '2022-10-01'),
    (2, '2022-11-01'),
    (2, '2022-12-01')
;

日期字段遵循一个约定，其中日期始终是每月的 1 号。对于上面的示例，差距如下。

| ID |       DATE |
|----|------------|
|  1 | 2022-07-01 |
|  1 | 2022-08-01 |
|  1 | 2022-09-01 |
|  2 | 2022-09-01 |

我将如何编写查询以返回上述结果？

sql-server gaps-and-islands
Martin Hope
FranckT
Asked: 2021-04-02 00:48:45 +0800 CST
  • 0

我的表结构和一些示例数据（测试表），以及我想要的输出（结果）都可以在这里找到。

当我做

SELECT distinct colony, min(date_check), max(date_check)
from test
where protection ='Y'
group by colony

我只按菌落检测 1 个事件。

我应该使用函数还是有其他方法？

ps：如果你想到一个更好的标题来帮助用户，不要犹豫，我在挣扎。

postgresql-9.5 gaps-and-islands
Martin Hope
igorkf
Asked: 2020-09-15 08:54:00 +0800 CST
  • 0

我有一张像这样的表格，显示每个客户在不同日期的付款：

|  stream_datetime | customer_id | order_status | rn |
|:----------------:|:-----------:|:------------:|:--:|
| 04/06/20 11:19AM |      1      |   completed  |  1 |
| 05/06/20 10:54AM |      1      |   completed  |  2 |
| 06/06/20 10:59AM |      1      |   completed  |  3 |
| 08/06/20 09:27AM |      2      |    failed    |  1 |
| 09/06/20 11:02AM |      2      |    failed    |  2 |
| 01/11/20 05:59PM |      3      |   completed  |  1 |
| 02/11/20 05:59PM |      3      |   completed  |  2 |
| 03/11/20 10:01AM |      3      |   cancelled  |  3 |
| 04/11/20 09:20AM |      3      |   completed  |  4 |
| 05/11/20 10:25AM |      3      |   completed  |  5 |
| 01/13/20 03:29PM |      4      |   completed  |  1 |
| 02/13/20 03:29PM |      4      |   completed  |  2 |
| 03/13/20 03:29PM |      4      |   cancelled  |  3 |
| 04/13/20 03:29PM |      4      |   completed  |  4 |
| 05/13/20 03:29PM |      4      |   completed  |  5 |
| 06/13/20 03:29PM |      4      |   completed  |  6 |
| 07/13/20 03:29PM |      4      |   completed  |  7 |
| 08/13/20 03:29PM |      4      |   cancelled  |  8 |
| 06/20/20 03:29PM |      5      |    failed    |  1 |
| 07/20/20 03:29PM |      5      |   completed  |  2 |
| 08/20/20 03:29PM |      5      |   completed  |  3 |
| 09/20/20 03:29PM |      5      |    failed    |  4 |
| 10/20/20 03:29PM |      5      |   completed  |  5 |

我想计算一个客户canceled他的计划的天差。

这里的挑战是客户端可以取消多次，因此客户端4必须被计为churned客户端两次，但客户端3只会被计为一次churned客户端。

我只想考虑order_status = completed后面有（不一定在下个月）的客户order_status = cancelled。

我也想创建一个名为purchase_day记录付款日期的列。

Obs.：该列rn表示特定客户端的行号。

编辑： 对不起。我犯了一些错误并写了问题。

也许order_status = cancelled先到先得order_status = completed。这是由于业务中的错误，但它可能发生。如果发生这种情况，那么我们不能将其视为流失客户。

所以这是我的预期结果（现在可以了）：

|   purchase_day   | customer_id | lifetime|
|:----------------:|:-----------:|:-------:|
| 01/11/20 05:59PM |      3      |   60    |
| 01/13/20 03:29PM |      4      |   60    |
| 04/13/20 03:29PM |      4      |   122   |

如你看到的：

  • 客户1从未取消（因此他不需要出现在结果中）
  • 客户2从未取消（因此他不需要出现在结果中）
  • 客户3取消了一次（他的生命周期等于 60 天）
  • 客户4取消了两次（一次为 60 天，另一次为 122 天）
  • 客户5从未取消（因此他不需要出现在结果中）
postgresql gaps-and-islands
Martin Hope
andyy15
Asked: 2020-05-07 13:18:05 +0800 CST
  • 0

我有一个具有以下结构的表：

id,center,shelf_id,occupied,remaining
1,East,1_1_1,1,0
2,East,1_1_2,1,0
3,East,1_1_3,1,4
4,East,1_1_4,1,0
5,East,1_1_5,1,0
6,East,1_2_1,1,3
7,East,1_2_2,1,0
8,East,1_2_3,0,4
9,East,1_2_4,0,4 
10,East,1_2_5,0,4
11,East,1_3_1,0,4
12,East,1_3_2,0,4
13,East,1_3_3,0,4
14,East,1_3_4,0,4

我想找到从该位置到表格末尾的最长重复数字字符串的开头。

对于上面的示例，mysql 语句应该返回 8 的行 id，因为从第 8 行到表的末尾，占用和剩余列中的值是 0 和 4。

任何关于将其编写为 mysql select 语句的帮助将不胜感激！

mysql gaps-and-islands
Martin Hope
Pavanraotk
Asked: 2020-04-23 14:33:47 +0800 CST
  • 0

我的表结构如下：

create table daily_summary
(
    id                 uuid           default uuid_generate_v4() not null
            primary key,
    machine            bigint                                    not null
    odometer_start     numeric(10, 3)                            not null,
    odometer_end       numeric(10, 3)                            not null,
    distance           numeric(10, 3)                            not null,
    date               timestamp                                 not null,
    ...

);

daily_summary 表中的数据对于一台机器应该是连续的，即机器的一行的 odometer_end 应该是按日期排序时下一行中同一台机器的 odometer_start。

是否有使用lead() 的查询或我可以用来识别非连续行的任何其他方式？

postgresql-9.6 gaps-and-islands
Martin Hope
dotconnor
Asked: 2019-11-24 18:50:14 +0800 CST
  • 1

假设我们有这样的表设置：

+---------+--------------+---------------+
| id text | time integer | value decimal |
+---------+--------------+---------------+
|       1 |            1 |            10 |
|       1 |            2 |            10 |
|       1 |            3 |             7 |
|       1 |            4 |             8 |
|       1 |            5 |             6 |
|       1 |            6 |             5 |
|       1 |            7 |             4 |
|       1 |            8 |             3 |
|       1 |            9 |             7 |
|       1 |           10 |            11 |
|       1 |           11 |             3 |
|       1 |           12 |             8 |
|       1 |           13 |             2 |
|       1 |           14 |             4 |
|       1 |           15 |             1 |
+---------+--------------+---------------+

是否可以在id一定时间内选择相同条件下的开始时间和结束时间？假设我们想查看1值所在的ID< 10至少几秒钟。所以结果查询将返回：

+------------+----------+
| start_time | end_time |
+------------+----------+
|         11 |       15 |
|          3 |        9 |
+------------+----------+
postgresql gaps-and-islands
Martin Hope
codigofontes
Asked: 2019-04-21 04:23:59 +0800 CST
  • 5

我需要在查询中显示每行的连续赢/输，所以给定下表，查询应该返回“预期”列。我尝试了一些使用窗口函数的方法，但没有成功。

create table matches (player text, dt date,  is_winner boolean, expected integer )
insert into matches values
('A', '2019-01-01', TRUE, 0),
('A', '2019-01-03', TRUE, 1),
('A', '2019-01-04', TRUE, 2),
('A', '2019-01-09', FALSE, 0),
('A', '2019-01-10', FALSE, -1),
('A', '2019-01-15', TRUE, 0);

player  dt          is_winner   expected
A       2019-01-01  true        0
A       2019-01-03  true        1
A       2019-01-04  true        2
A       2019-01-09  false       0
A       2019-01-10  false       -1
A       2019-01-15  true        0

逻辑是：

  1. 输后赢或赢后输时重置为 0。
  2. 获胜后增加，但如果是案例 1，则不会增加。
  3. 亏损后递减，但如果是第一种情况则不会。

欢迎任何有关如何解决此问题的见解。我最后的手段是每行都调用一个循环的函数。

postgresql gaps-and-islands
Martin Hope
SaiKarthikChandra
Asked: 2019-04-19 09:00:21 +0800 CST
  • 3

我怎样才能只合并随后发生的日期。？

（即，下一个事件在前一个事件完成后立即开始）。

在这里，我们还需要考虑其他一些列来获取输出。

这是示例事件甘特图的附加屏幕截图。 在此处输入图像描述 下表包含事件（分别为 M_ID、Y_ID、T_ID、E_ID、BeginDate、EndDate）

CREATE TABLE #EventTable
(
    M_ID INT,
    Y_ID INT,
    T_ID INT,
    E_ID INT,
    BeginDate DATE,
    EndDate DATE
);

下面的插入语句用于示例数据设置。

INSERT INTO #EventTable
VALUES
(135709, 2, 7, 1, '01 Jan 2017', '31 Mar 2017'),
(135709, 2, 7, 1, '01 Apr 2017', '30 Jun 2017'),
(135709, 2, 7, 3, '01 Jan 2017', '31 Jan 2017'),
(135709, 2, 7, 3, '01 Feb 2017', '30 Apr 2017'),
(135709, 2, 7, 3, '01 May 2017', '31 May 2017'),
(135709, 2, 7, 3, '01 Jul 2017', '31 Aug 2017'),
(135709, 2, 7, 3, '01 Oct 2017', '31 Oct 2017'),
(135709, 2, 7, 3, '01 Dec 2017', '31 Dec 2017'),
(135709, 5, 8, 1, '01 Feb 2017', '30 Apr 2017'),
(135709, 5, 8, 1, '01 Apr 2017', '31 Jul 2017'),
(134560, 5, 8, 3, '01 Apr 2017', '31 Aug 2017'),
(134560, 5, 8, 3, '01 May 2017', '31 Aug 2017'),
(134560, 5, 8, 3, '01 Oct 2017', '31 Oct 2017'),
(134560, 5, 8, 3, '01 Nov 2017', '30 Nov 2017'),
(135678, 3, 6, 2, '01 Jan 2017', '31 Mar 2017'),
(135678, 3, 6, 2, '01 Apr 2017', '30 Jun 2017'),
(135678, 3, 6, 2, '01 Oct 2017', '31 Oct 2017'),
(135678, 3, 6, 2, '01 Nov 2017', '31 Dec 2017'),
(123457, 4, 2, 3, '01 May 2017', '31 Oct 2017');

上面的屏幕截图中提到了所需的输出日期（标记为红色）。 在此处输入图像描述

例子：

对于M_ID = 135709, Y_ID = 2, T_ID = 7 and E_ID = 1;

第 2 场活动在（2017 年 3 月 31 日）第 1 场活动结束后立即开始（2017 年 4 月 1 日）。

因此，在这种情况下，输出应该是第一个事件的开始日期（2017 年 1 月 1 日）和第二个事件的结束日期（2017 年 6 月 30 日）。

同样，对于M_ID = 135709, Y_ID = 2, T_ID = 7 and E_ID = 3;

第 2 场比赛于 2017 年 2 月 1 日开始，紧接着第 1 场比赛于 2017 年 1 月 31 日结束。

第 3 场赛事在（2017 年 4 月 30 日）第二场赛事结束后立即开始（2017 年 4 月 1 日）。

因此，在这种情况下，输出应该是第一个事件的开始日期（2017 年 1 月 1 日）和第三个事件的结束日期（2017 年 6 月 30 日）。

并且由于在第 4 个事件 StartDate 和第 3 个事件 EndDate 之间存在 GAP（即第 4 个事件不会在第 3 个事件之后开始），因此日期应该保持不变。

有人可以帮我吗。？

sql-server gaps-and-islands
Martin Hope
DataGwynn
Asked: 2018-06-26 13:40:09 +0800 CST
  • 0

我需要从一个日历月中找到我们数据库中的所有成员，这些成员在发送数据之间没有超过 10 天的间隔/或在月份总数中，即 2018 年 4 月的每个人。该表是这样设置的（虽然要大得多） :

+-----------+------------+
| member_id |  data_date |
+-----------+------------+
|         1 | 2018-04-10 |
|         5 | 2018-04-16 |
|         1 | 2018-04-11 |
|         2 | 2018-04-12 |
|         3 | 2018-04-13 |
|         4 | 2018-04-12 |
|         5 | 2018-04-15 |
|         3 | 2018-04-19 |
|         2 | 2018-04-17 |
|         1 | 2018-04-18 |
|         5 | 2018-04-10 |
|         2 | 2018-04-18 |
|         1 | 2018-04-08 |
|         2 | 2018-04-03 |
|         3 | 2018-04-02 |
|         4 | 2018-04-14 |
|         5 | 2018-04-15 |
|         3 | 2018-04-16 |
|         2 | 2018-04-19 |
|         1 | 2018-04-14 |
+-----------+------------+

(member_id,data_date)被定义UNIQUE。每个 data_date 代表发送数据的一天。每个 member_id 都有重复的 data_dates。我正在运行 PostgreSQL 8.2.15。它是绿梅。

这个月每个 member_id 最多有 30 个 data_dates，我无法弄清楚如何找到最大的差距，而没有为每个成员发送整个月的数据。

下面是一些测试数据的例子：

create temp table tempdata  (
  member_id integer NOT NULL,
  data_date date
);

INSERT INTO tempdata(member_id, data_date) VALUES
   (1, '2017-04-01')
 , (1, '2017-04-02')
 , (1, '2017-04-03')
 , (1, '2017-04-04')
 , (1, '2017-04-05')
 , (1, '2017-04-06')
 , (1, '2017-04-07')
 , (1, '2017-04-08')
 , (1, '2017-04-09')
 , (1, '2017-04-10')
 , (1, '2017-04-11')
 , (1, '2017-04-12')
 , (1, '2017-04-13')
 , (1, '2017-04-14')
 , (1, '2017-04-15')
 , (1, '2017-04-16')
 , (1, '2017-04-17')
 , (1, '2017-04-18')
 , (1, '2017-04-19')
 , (1, '2017-04-20')
 , (1, '2017-04-21')
 , (1, '2017-04-22')
 , (1, '2017-04-23')
 , (1, '2017-04-24')
 , (1, '2017-04-25')
 , (1, '2017-04-26')
 , (1, '2017-04-27')
 , (1, '2017-04-28')
 , (1, '2017-04-29')
 , (1, '2017-04-30')
 , (2, '2017-04-09')
 , (2, '2017-04-10')
 , (2, '2017-04-11')
 , (2, '2017-04-12')
 , (3, '2017-04-01')
 , (3, '2017-04-02')
 , (3, '2017-04-03')
 , (3, '2017-04-04')
 , (3, '2017-04-05')
 , (3, '2017-04-06')
 , (3, '2017-04-07')
 , (3, '2017-04-08')
 , (3, '2017-04-09')
 , (3, '2017-04-10')
 , (3, '2017-04-11')
 , (3, '2017-04-12')
 , (3, '2017-04-13')
 , (3, '2017-04-14')
 , (3, '2017-04-15')
 , (3, '2017-04-16')
 , (3, '2017-04-17')
 , (3, '2017-04-18')
 , (3, '2017-04-19')
 , (3, '2017-04-20')
 , (3, '2017-04-21')
 , (3, '2017-04-22')
 , (3, '2017-04-23')
 , (3, '2017-04-24')
 , (3, '2017-04-25')
 , (3, '2017-04-26')
 , (3, '2017-04-27')
 , (3, '2017-04-28')
 , (3, '2017-04-29')
 , (3, '2017-04-30')
 , (4, '2017-04-01')
 , (4, '2017-04-02')
 , (4, '2017-04-03')
 , (4, '2017-04-04')
 , (4, '2017-04-05')
 , (4, '2017-04-06')
 , (4, '2017-04-07')
 , (4, '2017-04-08')
 , (4, '2017-04-09')
 , (4, '2017-04-10')
 , (4, '2017-04-11')
 , (4, '2017-04-12')
 , (4, '2017-04-13')
 , (4, '2017-04-14')
 , (4, '2017-04-15')
 , (4, '2017-04-16')
 , (4, '2017-04-17')
 , (4, '2017-04-18')
 , (4, '2017-04-19')
 , (4, '2017-04-20')
 , (4, '2017-04-21')
 , (4, '2017-04-22')
 , (5, '2017-04-01')
 , (5, '2017-04-02')
 , (5, '2017-04-03')
 , (5, '2017-04-04')
 , (5, '2017-04-05')
 , (5, '2017-04-06')
 , (5, '2017-04-07')
 , (5, '2017-04-08')
 , (5, '2017-04-09')
 , (5, '2017-04-10')
 , (5, '2017-04-11')
 , (5, '2017-04-12')
 , (5, '2017-04-13')
 , (5, '2017-04-14')
 , (5, '2017-04-15')
 , (5, '2017-04-16')
 , (5, '2017-04-17')
 , (5, '2017-04-18')
 , (5, '2017-04-22')
 , (5, '2017-04-23')
 , (5, '2017-04-24')
 , (5, '2017-04-25')
 , (5, '2017-04-26')
 , (5, '2017-04-27')
 , (5, '2017-04-29')
 , (5, '2017-04-30')
 , (6, '2017-04-01')
 , (6, '2017-04-02')
 , (6, '2017-04-03')
 , (6, '2017-04-04')
 , (6, '2017-04-05')
 , (6, '2017-04-06')
 , (6, '2017-04-07')
 , (6, '2017-04-08')
 , (6, '2017-04-09')
 , (6, '2017-04-10')
 , (7, '2017-04-01')
 , (7, '2017-04-04')
 , (7, '2017-04-05')
 , (7, '2017-04-06')
 , (7, '2017-04-07')
 , (7, '2017-04-08')
 , (7, '2017-04-09')
 , (7, '2017-04-11')
 , (7, '2017-04-12')
 , (7, '2017-04-13')
 , (7, '2017-04-14')
 , (7, '2017-04-15')
 , (7, '2017-04-16')
 , (7, '2017-04-17')
 , (7, '2017-04-18')
 , (7, '2017-04-19')
 , (7, '2017-04-21')
 , (7, '2017-04-22')
 , (7, '2017-04-26')
 , (7, '2017-04-27')
 , (7, '2017-04-28')
 , (7, '2017-04-30')
 , (8, '2017-04-02')
 , (8, '2017-04-03')
 , (8, '2017-04-04')
 , (8, '2017-04-05')
 ;
date gaps-and-islands
Martin Hope
Roberto Ribeiro
Asked: 2018-03-10 11:17:19 +0800 CST
  • 4

我有两个表，其中两列保留分类值的选项卡，例如：

Table 1
+----+-------+-----+-----------+
| ID | Begin | End | Condition |
+----+-------+-----+-----------+
|  1 |     1 |   8 |    Normal |
|  2 |     8 |  23 |  Critical |
|  3 |    23 |  30 |    Normal |
+----+-------+-----+-----------+

Table 2
+----+-------+-----+------------+
| ID | Begin | End | Supervisor |
+----+-------+-----+------------+
|  1 |     1 |  14 |       John |
|  2 |    14 |  30 |     Janice |
+----+-------+-----+------------+

这些 Begin 和 End 列表示值有效的连续间隔。在上面的示例中，时间间隔是一个月中的几天，因此根据表 1，我从第 1 天到第 8 天处于正常状态，从第 8 天到第 23 天处于严重状态，从第 23 天到第 30 天再次正常. 我从表 2 中得知，John 从 1 号到 14 号负责监督，而 Janice 从 14 号到 30 号接任。

我想要的是合并这两个表，在同一个表中同时具有 Condition 和 Supervisor 值，每对值的间隔最小。所以这：

Merged Table
+----+-------+-----+-----------+------------+
| ID | Begin | End | Condition | Supervisor |
+----+-------+-----+-----------+------------+
|  1 |     1 |   8 |    Normal |       John |
|  2 |     8 |  14 |  Critical |       John |
|  3 |    14 |  23 |  Critical |     Janice |
|  4 |    23 |  30 |    Normal |     Janice |
+----+-------+-----+-----------+------------+

这些表可以保证的是：

  • 每个都有其各自的“开始”和“结束”字段。
  • 这些将具有相同的跨度（第一行的“开始”值和最后一行的“结束”值对于每个表都是相同的）。
  • 每个间隔都是连续的（第 n 行的“结束”值始终等于第 n+1 行的“开始”值）。
  • 对于给定行，“结束”的值将始终高于“开始”。

我在 python 中以编程方式解决了这个问题，但我试图从我的工作流程中删除该脚本并直接在数据库中执行所有操作。最终我可以在 plpgsql 中复制我的 python 函数，但我想知道是否有更多类似 SQL 的方法来实现这一点？

postgresql gaps-and-islands