sql - 从跨越多对多的 postgres 查询中删除 CROSS JOIN LATERAL
问题描述
我有以下三个表(多对多):
地点
+====+==============+===+===+=============+
| id | coord_system | x | y | last_update |
+====+==============+===+===+=============+
| | | | | |
+----+--------------+---+---+-------------+
映射
+=============+============+
| location_id | history_id |
+=============+============+
| | |
+-------------+------------+
历史
+====+=======+======+
| id | speed | date |
+====+=======+======+
| | | |
+----+-------+------+
位置表表示特定坐标系内的物理 x、y 位置。对于每个 x、y 位置,历史表中至少存在一行。历史表中的每一行都可以指向位置表中的多行。
需要注意的重要一点是 (coord_system, x, y) 是索引的并且是唯一的。我认为这没有什么区别,但所有 id 和 coord_system 都是 UUID。在下面的代码示例中,我将使用字母使其更易于阅读。location 和 history 有额外的列,但不改变问题的范围。location 表上的 last_update 列应该与 History 表上的 date 列相匹配(我会在后面的文章中谈到这一点)。
目标是获取 (coor_system, x, y) 范围内的最新历史记录行。目前这是通过 CROSS JOIN LATERAl 完成的,例如
SELECT *
FROM location loc
CROSS JOIN LATERAL
(SELECT *
FROM history hist
LEFT JOIN mapping map ON hist.id = map.history_id
WHERE map.location_id = loc.id
ORDER BY date DESC limit(1)) AS records
WHERE loc.coord_system = '43330ccc-3f42-4f05-8ec5-18cb659bfd2d'
AND (x >= 403047
AND x <= 404047)
AND (y >= 16451337
AND y <= 16452337);
对于这个 x、y 和 coord_system 的特定范围,查询需要大约 25 秒才能运行并返回 182 351 行。
我在 SQL 方面不是很有经验,但认为这个查询的目标也可以使用常规连接来实现。如果我对三个表进行连接,使用相同的 x、y 和 coord_system“过滤器”,大约需要 2 秒并返回约 300 万行。我试图变得聪明并使用日期来修剪结果:
SELECT *
FROM history hist
RIGHT JOIN mapping map ON hist.id = map.history_id
RIGHT JOIN location loc ON loc.id = map.location_id
WHERE loc.coord_system = '43330ccc-3f42-4f05-8ec5-18cb659bfd2d'
AND (x >= 403047
AND x <= 404047)
AND (y >= 16451337
AND y <= 16452337)
AND location.last_update = hist.date
这与原始查询的结果非常接近。结果是约 3 秒内 182 485 行。不幸的是,结果需要完全相同。我猜我在查询中犯了一个逻辑错误,来到这里希望有人能指出这一点。
我的问题是:有没有一种聪明的方法可以让连接只从 history.date 列中获取具有“最新”日期的行?正如预期的那样,我试图在保持正确结果集的同时使查询尽可能快地运行。
在下表中,我展示了连接的玩具示例和我期望的结果(标记在“return_row”列中)。
+=============+==============+===+===+=============+============+============+=======+============+============+
| location.id | coord_system | x | y | location_id | history_id | history.id | speed | date | return_row |
+=============+==============+===+===+=============+============+============+=======+============+============+
| 0 | a | 1 | 1 | 0 | 0 | 0 | 3.0 | 2020/10/31 | * |
+-------------+--------------+---+---+-------------+------------+------------+-------+------------+------------+
| 0 | a | 1 | 1 | 0 | 1 | 1 | 3.1 | 2020/10/30 | |
+-------------+--------------+---+---+-------------+------------+------------+-------+------------+------------+
| 0 | a | 1 | 1 | 0 | 2 | 2 | 3.2 | 2020/10/29 | |
+-------------+--------------+---+---+-------------+------------+------------+-------+------------+------------+
| 1 | a | 1 | 2 | 1 | 3 | 3 | 3.1 | 2020/10/31 | * |
+-------------+--------------+---+---+-------------+------------+------------+-------+------------+------------+
| 1 | a | 1 | 2 | 1 | 4 | 4 | 3.0 | 2020/10/30 | |
+-------------+--------------+---+---+-------------+------------+------------+-------+------------+------------+
| 2 | a | 2 | 2 | 2 | 5 | 5 | 4 | 2020/10/31 | * |
+-------------+--------------+---+---+-------------+------------+------------+-------+------------+------------+
| 3 | b | 1 | 1 | 3 | 6 | 6 | 5 | 2020/10/1 | * |
+-------------+--------------+---+---+-------------+------------+------------+-------+------------+------------+
解决方案
搭配效果更好DISTINCT ON
吗?
SELECT DISTINCT ON (l.id) l.id, h.date, ... -- enumerate the columns here
FROM location l
LEFT JOIN mapping m ON m.location_id = l.id
LEFT JOIN history h ON h.id = m.history_id
WHERE
l.coord_system = '43330ccc-3f42-4f05-8ec5-18cb659bfd2d'
AND l.x BETWEEN 403047 AND 404047
AND l.y BETWEEN 16451337 AND 16452337
ORDER BY l.id, h.date DESC
推荐阅读
- azure - “Azure 订阅未向 CDN 提供程序注册”。我该如何注册?我在哪里注册?
- python - Z维度在moderngl中消失了吗?
- c# - 如何在 qnamaker.ai 中正确创建多个知识库,然后与 Luis.ai 连接
- reactjs - 带有 IdentityServer4 和 oidc-client-js 的 ReactJS 错误:“无响应代码”
- jquery - 通过添加类将过滤器合并为一个
- javascript - 如何引用映射数组的单个元素来呈现视图
- javascript - 如何从外部网站提取文章 URL 并将它们存储在列表/数组中?
- r - 在 R 中使用加权黄土插值时间序列
- plsql - 如何在 PLSQL 中将工作日添加到日期?
- node.js - 如何使用 Express 重新加载页面?