gpt4 book ai didi

mysql - 使用 union 按查询优化组

转载 作者:行者123 更新时间:2023-12-01 21:22:56 28 4
gpt4 key购买 nike

我有一个 MySQL 表,如下所示:

enter image description here

我想找到一个像这样对我的表进行分组的查询:

enter image description here

详细信息:

a_id = map 上的分隔区域

is_flag = 1-如果传感器在区域内/0-如果传感器不在区域内

基本上,第一个表格描述了我的传感器在每个时间戳的哪个区域。

第二张表告诉我我的传感器在每个区域停留的时间。

我对每个 areas_id 和 union all 使用下面的查询,以便在单个表中输出我的传感器如何在区域之间移动的时间段以及它在每个区域中停留的时间。

select t.a_id, min(t.timestamp) starttime,max(t.timestamp) endtime, 
t.is_flag from(SELECT *,
ROW_NUMBER() OVER(ORDER BY a.timestamp) - ROW_NUMBER() OVER(PARTITION BY
a.is_flag ORDER BY a.timestamp) as GRP
FROM tablename a where areas_id=25 ) t
group by is_flag , GRP, a_id

这是我的 dbfiddle:https://www.db-fiddle.com/f/5pHiYKyx4yHoirRbGX4kP4/0

我的查询满足了我的需要,但它需要一整天的时间。

最佳答案

WITH 
cte1 AS (SELECT CAST(JSON_UNQUOTE(`timestamp`) AS DATETIME) ts,
areas_id,
is_in_or_out
FROM inouts),
cte2 AS (SELECT ts,
areas_id,
is_in_or_out,
CAST(ROW_NUMBER() OVER (PARTITION BY areas_id ORDER BY ts ASC) AS SIGNED)
-CAST(ROW_NUMBER() OVER (PARTITION BY areas_id ORDER BY is_in_or_out, ts ASC) AS SIGNED) AS grp
FROM cte1)
SELECT areas_id,
ANY_VALUE(is_in_or_out) is_in_or_out,
MIN(ts) min_ts,
MAX(ts) max_ts
FROM cte2
GROUP BY areas_id,
grp
ORDER BY areas_id, min_ts;

fiddle

PS1。源数据略有改动。

PS2。 MySQL 中需要 CAST,因为 ROW_NUMBER() 生成无符号的 bigint。可以替换为 0.0 + ...

关于mysql - 使用 union 按查询优化组,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/63638193/

28 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com