SQL中高效的(线性时间)嵌套查询

Question

从此表中：

事件

event_score>//tr>6[/td>[/tr>//tr>

id	event_date
12	2020-04-10
13	2020-04-11
13	2020-04			8
13	2020-04-13
12	2020-04-15
14	2020-04-16
14	2020-04-17
14	2020-04-18	11
14	2020-04-19
14	2020年至2020年
14	2020-04-22
12	2020-04-25
14	2020-04-30

如果您运行的是MariaDB 10.2.2或更高版本，您可以将其作为缺口和孤岛问题来解决。这个想法是计算在前一行和后一行中出现的非null值的数量。然后，我们可以使用条件聚合在两个方向上过滤第一个非零值：

select id,
max(case when grp_asc  = 1 then event_score end) as first_score,
max(case when grp_desc = 1 then event_score end) as last_score
from (
select e.*,
count(event_score) over(partition by id order by event_score     ) as grp_asc,
count(event_score) over(partition by id order by event_score desc) as grp_desc
from events e
) e
group by id
order by id

我无法评估此算法的时间复杂性，但我怀疑它应该比原始查询运行得更快，因为原始查询需要每个不同的id执行两个子查询。

DB Fiddle上的演示：

id|firstrongcore|lastrongcore-：|---------：|--------：12 |null|null13|6|814|11|11

如果(id, event_date, event_sore)上有一个索引，那么这应该很快：

SELECT id,
(SELECT event_score
FROM events AS subquery
WHERE final_table.id = subquery.id AND event_score IS NOT NULL
ORDER BY event_date
LIMIT 1
) AS `first score`,
(SELECT event_score
FROM events AS subquery
WHERE final_table.id=subquery.id AND event_score IS NOT NULL
ORDER BY event_date DESC
LIMIT 1
) AS `last score`
FROM (SELECT DISTINCT e.id
FROM sensors.events e
) as final_table;

请注意，这会将SELECT DISTINCT移动到子查询。这是为了确保MariaDB实际上不使用"；独特的"；SELECT DISTINCT的算法——其他列可能会导致这种情况发生。

然而，这是O(n log n(，因为子查询需要为每个id对少量数据进行排序，并使用索引来找到正确的位置。

我想不出在SQL中实现O(n(的方法。我确信以下结构都是O(n log n(：

• 对每一行使用索引
• 对数据的任何部分进行排序
• 使用任何带有order by的窗口函数——尽管如果有合适的索引，这可能是真的

但是，SQL查询仍然很快，尤其是索引查询。