上下文
以下是我的问题的简化版本
我们有一个名为positions
的表,它存储了一个项目在几个容器中的移动.
每条记录都包含
- 容器的名称(我们称其为
container
) - 名为
date_from
和date_to
的两个DateTime属性,它们包含项目进入和离开容器的时间戳
在两个连续的记录之间可能存在"时间间隔".也就是说,物品在上午10点之前一直放在集装箱A里,然后下午4点出现在集装箱B里,中间没有任何东西.
以下是一个示例数据集
ID | container |
date_from |
date_to |
---|---|---|---|
1 | A | 2023-10-01T00:00:00 | 2023-10-01T10:00:00 |
2 | A | 2023-10-03T09:00:00 | 2023-10-03T11:00:00 |
3 | B | 2023-10-04T02:00:00 | 2023-10-04T03:00:00 |
4 | C | 2023-10-04T06:00:00 | 2023-10-04T08:00:00 |
5 | C | 2023-10-05T00:00:00 | 2023-10-06T10:00:00 |
6 | A | 2023-10-06T11:00:00 | 2023-10-06T20:00:00 |
7 | C | 2023-10-06T21:00:00 | 2023-10-07T10:00:00 |
这些要求
我需要挤压所有连续的相邻位置
- 在同一容器中(项在子序列上不会离开该容器)
- 并且彼此之间足够接近:即,对于第二位置中的哪
date_from
个位置而言,从前一位置的date_to
开始的特定时间阈值内.
对于我压缩的每个子序列,我需要获取第一个值date_from
和最后一个值date_to
,并将它们放在同一个结果行中.
例如,如果容器A中有5个连续的记录,并且根据规则它们足够接近以被压扁,那么我压扁这些位置的最后一行将具有
-
container
=A= -
date_from
从我压扁的5个位置中的第一个 - 从5个位置中的最后一个位置减go
date_to
我编写的PostgreSQL查询
WITH with_next_position AS (
SELECT
id,
container,
date_from,
date_to,
(
SELECT subquery.id
FROM positions subquery
WHERE subquery.date_from > base.date_from
ORDER BY subquery.date_from ASC
LIMIT 1
) AS next_position_id
FROM positions
),
with_time_lapse AS (
SELECT
with_next_position.date_from AS date_from,
with_next_position.date_to AS date_from,
with_next_position.container AS container,
CASE
WHEN join_table.date_from IS NOT NULL
THEN EXTRACT(EPOCH FROM (join_table.date_from - with_next_position.date_to))
ELSE
NULL
END AS time_lapse,
join_table.marina_id AS next_container
FROM
with_next_position
FULL OUTER JOIN with_next_position join_table ON join_table.id = with_next_position.next_position_id
WHERE
with_next_position.container IS NOT NULL
),
with_marked_to_squash AS (
SELECT
date_from,
date_to,
container,
CASE
WHEN next_container = container AND time_lapse <= 10000000 # This is where I put the threshold
THEN TRUE
ELSE
FALSE
END AS to_squash
FROM with_time_lapse
)
with_marked_first_to_squash AS (
SELECT
date_from,
date_to,
container,
CASE
WHEN to_squash
THEN (
SELECT CASE WHEN to_squash THEN FALSE ELSE TRUE END
FROM with_marked_to_squash subquery
WHERE subquery.date_from < with_marked_to_squash.date_from
ORDER BY subquery.date_from DESC
LIMIT 1
)
ELSE
FALSE
END AS first_to_squash
FROM with_marked_to_squash
),
with_first_to_squash AS (
SELECT
date_from,
date_to,
container,
(
SELECT subquery.date_from
FROM with_marked_first_to_squash subquery
WHERE subquery.date_from < with_marked_first_to_squash.date_from AND first_to_squash IS TRUE
ORDER BY subquery.date_from DESC
LIMIT 1
) AS first_date_in_position
FROM with_marked_first_to_squash
WHERE to_squash IS FALSE
)
SELECT
COALESCE(first_date_in_position, date_from) AS date_from,
date_to,
container
EXTRACT(EPOCH FROM (date_to - COALESCE(first_date_in_position, date_from))) AS time_spent
FROM with_first_to_squash
ORDER BY date_from
性能问题
上面的查询是正确的,它做了我期望它做的事情.然而,当提取子查询with_first_to_squash
时出现性能问题.如果我将查询切碎到with_first_to_squash
之前,则性能将呈指数级提高.
我认为性能问题的原因是,通过连续运行with_marked_first_to_squash
和with_first_to_squash
,我使数据库引擎遍历了两个嵌套循环:
- 首先,我们将那些已经标记为"to_Squash"并且是此类位置中的第一个的位置标记为"first_to_Squash"(即,前一个位置尚未标记为"to_Squash"):这是通过内联子查询(在
with_marked_first_to_squash
的定义中)完成的 - 其次,我们只 Select 不会被挤压的位置(即,每个相邻子序列中的最后一个),并对每个位置运行一个子查询,该查询"返回"到过go 标记为"First_to_Squash"的第一个位置:一旦找到该位置,我们使用它来检索
date_from
在我删除第二个子查询的那一刻,事情变得非常迅速.
我确信有一个解决方案允许从子序列的第一个位置提取date_from
,可能涉及分区,但我不熟悉分区及其语法.有谁能给我一个提示吗?