如何根据时间间隔条件从关联表更新目标表空值字段？

现有数据表 #

Table 1（初始状态） #

prev_m | next_m | prev_order | next_order | prev_loc | next_loc |      datetime       | prev_datetime |    next_datetime    | id
--------+--------+------------+------------+----------+----------+---------------------+---------------+---------------------+------
        | 6      |            |      308   |          |   222.5  | 2023-07-08 11:14:46 |               | 2023-07-08 11:15:33 | 101
        | 3      |            |      672   |          |   3.020  | 2023-07-08 16:17:56 |               | 2023-07-08 16:22:56 | 105

Table 2 #

m  | orders|   loc  | message_id |    datetimestamp    | id
------+-------+--------+------------+---------------------+--------
  0   |    11 |  0.000 |         24 | 2023-07-08 11:14:13 | 101
  6   |   308 |  0.000 |         36 | 2023-07-08 11:14:25 | 107
  0   |    70 |  0.000 |         24 | 2023-07-08 11:14:28 | 105
  5   |   672 |  7.100 |         36 | 2023-07-08 11:14:30 | 101
  0   |  3418 |  0.000 |         24 | 2023-07-08 14:23:39 | 106
  3   |  3428 |  0.000 |         36 | 2023-07-08 15:23:41 | 101
  0   |  1899 |  0.000 |         24 | 2023-07-08 16:13:22 | 101
  0   |  1894 | 75.000 |         36 | 2023-07-08 16:14:34 | 101
  0   |    12 |  0.000 |         24 | 2023-07-08 16:15:49 | 105
  6   |   121 |  0.000 |         36 | 2023-07-08 16:16:01 | 101
  8   |  1528 | 50.000 |         36 | 2023-07-08 16:16:33 | 105
  0   |   15  |  7.020 |         24 | 2023-07-08 16:18:20 | 109

更新规则 #

• 当id相同时，若Table 1的datetime大于Table 2的datetimestamp，且两者时间差在30分钟内，且message_id = 24，则更新Table 1的prev_datetime字段。
• 当id相同时，若Table 1的datetime大于Table 2的datetimestamp，且两者时间差在30分钟内，且message_id = 36，则更新Table 1的prev_m、prev_order、prev_loc字段。

期望更新后的Table 1 #

prev_m | next_m | prev_order | next_order | prev_loc | next_loc |      datetime       |     prev_datetime    |    next_datetime    | id
--------+--------+------------+------------+----------+----------+---------------------+----------------------+---------------------+------
   5    | 6      |   672      |      308   |  7.100   |   222.5  | 2023-07-08 11:14:46 |  2023-07-08 11:14:13 | 2023-07-08 11:15:33 | 101
   8    | 3      |   1528     |      672   |  50.000  |   3.020  | 2023-07-08 16:17:56 |  2023-07-08 16:15:49 | 2023-07-08 16:22:56 | 105

问题分析与解决方案 #

原SQL存在的问题 #

1. 多匹配覆盖：直接关联Table 2会让同一id下的多条符合条件记录依次更新，最终被最后一条匹配记录覆盖，但我们需要的是最接近Table 1.datetime的那条符合条件记录（即最大的datetimestamp）。
2. 分支逻辑错误：原SQL中ELSE NULL会在不符合条件时把字段设为NULL，正确逻辑应该是保留原字段值。
3. 时间条件冗余：c1.datetime > c2.datetimestamp和c1.datetime BETWEEN...重复，后者已包含前者的判断。

正确的SQL语句 #

通过子查询筛选每个id下符合条件的最新记录，避免多记录覆盖问题：

UPDATE table_1 c1
SET
    prev_datetime = (
        SELECT datetimestamp
        FROM table_2 c2
        WHERE c2.id = c1.id
          AND c2.message_id = 24
          AND c1.datetime > c2.datetimestamp
          AND c1.datetime <= c2.datetimestamp + INTERVAL '30 minutes'
        ORDER BY c2.datetimestamp DESC
        LIMIT 1
    ),
    prev_m = (
        SELECT m
        FROM table_2 c2
        WHERE c2.id = c1.id
          AND c2.message_id = 36
          AND c1.datetime > c2.datetimestamp
          AND c1.datetime <= c2.datetimestamp + INTERVAL '30 minutes'
        ORDER BY c2.datetimestamp DESC
        LIMIT 1
    ),
    prev_order = (
        SELECT orders
        FROM table_2 c2
        WHERE c2.id = c1.id
          AND c2.message_id = 36
          AND c1.datetime > c2.datetimestamp
          AND c1.datetime <= c2.datetimestamp + INTERVAL '30 minutes'
        ORDER BY c2.datetimestamp DESC
        LIMIT 1
    ),
    prev_loc = (
        SELECT loc
        FROM table_2 c2
        WHERE c2.id = c1.id
          AND c2.message_id = 36
          AND c1.datetime > c2.datetimestamp
          AND c1.datetime <= c2.datetimestamp + INTERVAL '30 minutes'
        ORDER BY c2.datetimestamp DESC
        LIMIT 1
    );

说明 #

• 每个字段的子查询都会从Table 2中筛选出符合时间和message_id条件的记录，按datetimestamp倒序取第一条（即最接近目标时间的记录），确保更新值准确。
• 如果需要保留原字段的非空值，可在赋值时加上COALESCE(c1.字段名, 子查询)，仅在原字段为空时更新。

内容的提问来源于stack exchange，提问作者RKIDEV