逗号的博客

场景描述

我们有一个系统在不停接收其他系统发来的消息，我们的任务任务是查询连续出现两次及以上的消息。下面是一些例子

1. 假设依次收到的消息是 A 则结果为空；
2. 假设依次收到的消息是 AB 则结果为空；
3. 假设依次收到的消息是 AA 则结果为 AA；
4. 假设依次收到的消息是 AABB 则结果为 AABB；
5. 假设依次收到的消息是 ABB 则结果为 BB；
6. 假设依次收到的消息是 AAB 则结果为 AA；
7. 假设依次收到的消息是 AABCC 则结果为 AACC；
8. 假设依次收到的消息是 AABCDD 则结果为 AADD；
9. 假设依次收到的消息是 AABAACC 则结果为 AAAACC；
10. 假设依次收到的消息是 AABAABCC 则结果为 AAAACC；
11. 假设依次收到的消息是 AABAACDD 则结果为 AAAADD；
12. 假设依次收到的消息是 AABCCBDD 则结果为 AACCDD；

如上图所示，有紫色、红色、黄色三个矩形区域，其中紫色区域完全在东半球，黄色区域完全在西半球，而红色区域一半在东半球一半在西半球，即它跨越了 180°(-180°) 经线。我们用左上/右下两个点来表示一个矩形区域，因此紫色区域表示为 (147.66, 36.31) / (159.71, 30.88)，红色区域表示为 (173.53, 36.49) / (-173.32, 31.17)，黄色区域表示为 (-160.49, 36.31) / (-147.24, 30.98)。在每一个区域中分布着一些点，现在需要一种把这些点查询出来的方法。

为了方便定位地球上的点，人为定义了经线和纬线，它们组成了一个经纬网。经度的范围为 [-180°, +180°]，本初子午线的位置为 0°，向东走为东经，经度范围为 [0°, +180°]，向西走为西经，经度范围为 [-180°, 0°]，-180° 和 180° 对应同一根经线，也叫国际日期变更线。纬度的范围为 [-90°, +90°]，赤道的位置为 0°，向北走为北纬，纬度范围为 [0°, +90°]，向南走为南纬，纬度范围为 [-90°, 0°]，-90° 和 90° 的位置对应南极点和北极点。

我们的问题是对于给定两个经度或纬度，如何计算它们之间的差值？

为了说明问题我创建了一张员工表 employees 并初始化了一些数据

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

CREATE TABLE employees
(
    id         INTEGER, -- ID
    name       VARCHAR, -- 姓名
    age        INTEGER, -- 年龄
    department VARCHAR, -- 部门
    status     VARCHAR, -- 状态
    PRIMARY KEY (id)
);

INSERT INTO employees(id, name, age, department, status) VALUES (1, 'Bob', 18, 'HR', 'Active');
INSERT INTO employees(id, name, age, department, status) VALUES (2, 'Eve', 19, null, 'Inactive');
INSERT INTO employees(id, name, age, department, status) VALUES (3, 'Zoe', null, 'IT', null);

最近读到一篇非常好的文章 PostgreSQL 中 timestamp 与 timestamptz 的区别，也可以访问 https://japinli.github.io/2023/03/postgresql-timestamp/，读完这篇文章后学习到了两点

1. PostgreSQL 存在两个纪元，Unix 纪元（1970-01-01 00:00:00）和 PostgreSQL 纪元（2000-01-01 00:00:00），在底层存储系统存储的是时间相对于 PostgreSQL 纪元的数值。EXTRACT(EPOCH FROM source) 是基于 Unix 纪元的结果。
2. 顺藤摸瓜可以阅读 src/backend/utils/adt/timestamp.c 文件中的 timestamp_in 和 timestamp_out 函数，从源码上理解时间字符串的输入和输出逻辑。

因此本文的逻辑是基于显示的结果而言，从显示的结果推论可能的原因，只是为了方便理解。推荐阅读它而不是本文。

问题

我们假设区间的表示为 [start, end]，对于两个区间 A 和 B 如何判断它们是否重叠？

背景知识

全球共划分为 24 个时区，以本初子午线为基准，从 7.5°W 向东至 7.5°E ，划分为一个时区，叫中时区或零时区。在中时区以东，依次划分为东一区至东十二区；在中时区以西，依次划分为西一区至西十二区。东十二区和西十二区各跨经度 7.5°，合为一个时区。每个时区跨越经度 15°，相邻区域的时间相差 1 小时（这样 24 个时区刚好是 24 小时，地球自转一周）。

问题

在我们的业务中有一个生产者程序不停地向 message 表写入记录，简化的 message 表的结构如下所示

1
2
3
4
5
6
7

CREATE TABLE message
(
    id          SERIAL,      -- ID
    content     VARCHAR,     -- 消息内容
    create_time TIMESTAMPTZ, -- 创建时间
    PRIMARY KEY (id)
);

另外有一个消费者程序不停地读取 message 表的数据，这个消费者程序依赖一个称为“水位线”的时间戳，所有创建时间小于等于水位线的消息都已经处理，我们使用 consumer 表来记录当前的水位线

1
2
3
4
5
6

CREATE TABLE consumer
(
    id        SERIAL,      -- ID
    watermark TIMESTAMPTZ, -- 水位线
    PRIMARY KEY (id)
);

我们会有一个定时任务每隔一段时间调度一次消费者程序，消费者程序的主要逻辑是

1. 检查当前水位线

   1 2 3 4 5

   test=# SELECT watermark FROM consumer; watermark ------------------------ 2024-04-30 15:16:17+08 (1 row)

2. 使用当前水位线加载待处理消息

   1 2 3 4 5 6

   test=# SELECT content, create_time FROM message WHERE create_time > '2024-04-30 15:16:17+08' AND create_time <= NOW() ORDER BY create_time; content | create_time ---------+------------------------------- aaa | 2024-04-30 16:17:18.936795+08 bbb | 2024-04-30 17:18:19.956867+08 (2 rows)

3. 逐条处理消息，并用当前消息的创建时间更新水位线，最后一条消息处理完成时

   1 2 3 4 5 6 7

   test=# UPDATE consumer SET watermark = '2024-04-30 17:18:19.956867+08'; UPDATE 1 test=# SELECT watermark FROM consumer; watermark ------------------------------- 2024-04-30 17:18:19.956867+08 (1 row)

就当前的 SQL 版本模拟的逻辑而言在定时任务第 2 轮调度时将没有待处理消息。但是当我们使用 Java 语言实现上面的逻辑时我们发现在定时任务第 2 轮调度时内容为 bbb 的消息会再一次被处理。在 Java 版本的实现里我们使用了 java.util.Date 类型来表示 create_time 和 watermark。

问题

假设有一个 PDI（Kettle）Job，运行这个 Job 需要耗时 30 秒钟。现在每 10 秒钟调度运行一次这个 Job。我们的问题是这个 Job 的运行情况是怎样的？具体地说是上一个调度的 Job 还没有执行完就到了下一个调度的 Job 的执行时间，那么下一个调度的 Job 会执行吗？也就是定时任务如果执行的时间很长的话，那么下一次还能按时执行嘛？

• Case 1：无论上一次 Job 是否执行完成，下一次 Job 按照 10 秒钟的间隔准时开始；
• Case 2：上一次 Job 未执行完成，下一次 Job 不会开始执行；一旦上一次 Job 执行完成，下一次 Job 立即开始执行，中间没有间隔；
• Case 3：上一次 Job 未执行完成，下一次 Job 不会开始执行；上一次 Job 执行完成后等待 10 秒钟的间隔再开启下一次 Job 的执行；

Kettle 的定时任务是哪一种情况呢？

在 PostgreSQL 中执行如下的 SQL 语句

1

SELECT 'AND c1 = ''' || 5 || '''';

将输出 AND c1 = '5'。我们的疑问是 5 两边的单引号 ' 是怎么得到的？