您的位置：首页 > 财经 > 产业 > 江西网站开发方案_专业做网站的网站_seo技术员_站长工具排名分析

江西网站开发方案_专业做网站的网站_seo技术员_站长工具排名分析

2025/4/3 21:30:05 来源：https://blog.csdn.net/weixin_48935611/article/details/146798472  浏览:  次   关键词：江西网站开发方案_专业做网站的网站_seo技术员_站长工具排名分析
江西网站开发方案_专业做网站的网站_seo技术员_站长工具排名分析

横扫SQL面试题

📌 时间序列分组与合并问题

在这里插入图片描述

📚 横扫SQL面试——时间序列分组与合并解析

🌟 核心问题类型

时间序列分组（Sessionization）
处理具有时间维度的连续数据流，根据特定规则（如时间间隔、属性连续性）将相邻记录合并为逻辑组。

话不多说——直接上题：🎈🎈🎈🎈🎈🎈🎈

一、用户访问记录会话划分🎈🎈🎈

给定用户的访问记录access_records表，需要按照同一用户相邻两次访问记录时间间隔小于等于60秒则认为两次浏览记录属于同一会话的规则，为属于同一会话的访问记录增加一个相同的会话id字段。

表名字段名字段含义数据类型
用户访问记录表user_id用户标识VARCHAR
access_time访问时间DATETIME
action访问时执行的操作（如页面浏览等）VARCHAR
user_idaccess_timeaction
User12025-03-31 10:00:00浏览首页
User12025-03-31 10:00:30点击商品详情
User22025-03-31 10:10:00浏览列表页
User12025-03-31 10:01:00返回首页
User22025-03-31 10:10:30加入购物车

结果预期✅

user_idaccess_timeactionsession_id
User12025-03-31 10:00:00浏览首页1
User12025-03-31 10:00:30点击商品详情1
User12025-03-31 10:01:00返回首页1
User22025-03-31 10:10:00浏览列表页2
User22025-03-31 10:10:30加入购物车2

✅✅✅

原始数据 → 计算相邻时间差 → 标记新会话 → 累加分组 → 分配ID↓              ↓               ↓             ↓
prev_access_time → new_session_start → session_groups → 最终结果
步骤1：获取相邻记录时间差（CTE: prev_access_time）✅
with prev_access_time as (selectuser_id,access_time,lag(access_time) over (partition by user_id order by access_time) as prev_timefrom access_records
)

在这里插入图片描述

在这里插入图片描述

输入数据 ✅

user_idaccess_time
UserA2024-01-01 08:00:00
UserA2024-01-01 08:00:50
UserA2024-01-01 08:02:00

中间表 prev_access_time

user_idaccess_timeprev_time
UserA2024-01-01 08:00:00NULL
UserA2024-01-01 08:00:502024-01-01 08:00:00
UserA2024-01-01 08:02:002024-01-01 08:00:50
  • LAG(access_time) 获取同一用户前一条记录的访问时间
  • PARTITION BY user_id 确保按用户独立处理
  • ORDER BY access_time 按时间顺序排序
步骤2：标记新会话起点（CTE: new_session_start）✅

在这里插入图片描述

new_session_start as (selectuser_id,access_time,casewhen timestampdiff(second, prev_time, access_time) >= 60 or prev_time is nullthen 1else 0end as is_new_sessionfrom prev_access_time
)

在这里插入图片描述

中间表 new_session_start

user_idaccess_timeis_new_session
UserA2024-01-01 08:00:001 (首条记录)
UserA2024-01-01 08:00:500 (间隔50秒)
UserA2024-01-01 08:02:001 (间隔70秒)

判断逻辑

  • 首条记录：prev_time IS NULL → 标记为1
  • 间隔计算：08:00:50 - 08:00:00 = 50秒 → <60秒 → 标记0
  • 间隔计算：08:02:00 - 08:00:50 = 70秒 → >=60秒 → 标记1
步骤3：生成会话分组（CTE: session_groups）✅
session_groups as (selectuser_id,access_time,sum(is_new_session) over (partition by user_id order by access_time) as session_groupfrom new_session_start
)

在这里插入图片描述

在这里插入图片描述

中间表 session_groups

user_idaccess_timesession_group
UserA2024-01-01 08:00:001
UserA2024-01-01 08:00:501 (累加0)
UserA2024-01-01 08:02:002 (累加1)
  • SUM(is_new_session) 按用户和时间顺序累加标记
  • 同一会话组内的记录具有相同的 session_group 值
步骤4：分配会话ID（主查询）✅
selectuser_id,access_time,dense_rank() over (partition by user_id order by session_group) as session_id
from session_groups;

在这里插入图片描述

在这里插入图片描述

最终结果

user_idaccess_timesession_id
UserA2024-01-01 08:00:001
UserA2024-01-01 08:00:501
UserA2024-01-01 08:02:002
  • DENSE_RANK() 保证会话ID连续（无间隔）
  • 按 session_group 排序确保同一组ID相同

二、网站页面浏览时长统计🎈🎈🎈

给定网站页面浏览记录，需要按照用户在离开页面后30分钟内再次访问同一页面则认为是连续浏览的规则。
计算：

  1. 合并时长：将连续访问合并为一个时间段，计算从首次开始时间到最后结束时间的总跨度
  2. 实际时长：累加每次独立访问的实际时长（不合并连续访问）
  3. 增加会话id：属于同一浏览的访问记录增加一个相同的会话id字段。
表名字段名字段含义数据类型
网站页面浏览记录表user_id用户标识VARCHAR
page_url页面URLVARCHAR
view_start_time开始浏览时间DATETIME
view_end_time结束浏览时间DATETIME
user_idpage_urlview_start_timeview_end_time
User1https://example.com2025-04-01 09:00:002025-04-01 09:10:00
User1https://example.com2025-04-01 09:15:002025-04-01 09:20:00
User2https://example.net2025-04-01 10:00:002025-04-01 10:15:00
User1https://example.com2025-04-01 09:25:002025-04-01 09:30:00
User2https://example.net2025-04-01 10:30:002025-04-01 10:45:00

结果预期

user_idpage_urlview_start_timeview_end_timemerged_durationtotal_real_duration
User1https://example.com2025-04-01 09:00:002025-04-01 09:30:003020 (10+5+5)
User2https://example.net2025-04-01 10:00:002025-04-01 10:15:001515
User2https://example.net2025-04-01 10:30:002025-04-01 10:45:001515

还是跟上题同样的套路~😝😝😝

步骤核心操作关键函数/逻辑
时间排序获取前次结束时间LAG() + 窗口排序
标记会话起点判断30分钟间隔TIMESTAMPDIFF() + CASE
生成会话组累加标记值SUM() OVER()
分配会话ID生成唯一连续编号DENSE_RANK()
聚合结果计算时间跨度和累加时长MIN()/MAX()/SUM()
步骤1：排序并获取前次结束时间✅
with ordered_views as(selectuser_id,page_url,view_start_time,view_end_time,lag(view_end_time) over(partition by user_id,page_url order by view_start_time) as prev_end_timefrom table
)

在这里插入图片描述

中间表 ordered_views ✅

user_idpage_urlview_start_timeview_end_timeprev_end_time
User1https://example.com2025-04-01 09:00:002025-04-01 09:10:00NULL
User1https://example.com2025-04-01 09:15:002025-04-01 09:20:002025-04-01 09:10:00
User1https://example.com2025-04-01 09:25:002025-04-01 09:30:002025-04-01 09:20:00
User2https://example.net2025-04-01 10:00:002025-04-01 10:15:00NULL
User2https://example.net2025-04-01 10:30:002025-04-01 10:45:002025-04-01 10:15:00
步骤2：标记新会话起点✅
session_flags as(select *,casewhen prev_end_time IS NULL OR timestampdiff(minute, prev_end_time, view_start_time) > 30 then 1 else 0 end as is_new_sessionfrom ordered_views
)

在这里插入图片描述

中间表 session_flags ✅

user_idpage_urlis_new_session
User1https://example.com1
User1https://example.com0
User1https://example.com0
User2https://example.net1
User2https://example.net1
步骤3：生成会话分组ID✅
session_groups as(select*,sum(is_new_session) over(partition by user_id, page_url order by view_start_time) as session_groupfrom session_flags
)

在这里插入图片描述

中间表 session_groups ✅

user_idpage_urlsession_group
User1https://example.com1
User1https://example.com1
User1https://example.com1
User2https://example.net1
User2https://example.net2
步骤4：分配唯一会话ID✅
session_ids AS (SELECT *,dense_rank() over (partition by user_id, page_url order by session_group ) as session_idfrom session_groups
)

在这里插入图片描述

中间表 session_ids ✅

user_idpage_urlsession_id
User1https://example.com1
User1https://example.com1
User1https://example.com1
User2https://example.net1
User2https://example.net2
步骤5：聚合结果并计算双指标✅
selectuser_id,page_url,session_id,min(view_start_time) AS view_start_time,max(view_end_time) AS view_end_time,TIMESTAMPDIFF(MINUTE, min(view_start_time), max(view_end_time)) AS merged_duration,sum(TIMESTAMPDIFF(MINUTE, view_start_time, view_end_time)) AS total_real_duration
from session_ids
group by user_id, page_url, session_id;

在这里插入图片描述

最终结果 ✅

user_idpage_urlsession_idview_start_timeview_end_timemerged_durationtotal_real_duration
User1https://example.com12025-04-01 09:00:002025-04-01 09:30:003020
User2https://example.net12025-04-01 10:00:002025-04-01 10:15:001515
User2https://example.net22025-04-01 10:30:002025-04-01 10:45:001515

还是有些难度的哈~

-- 使用CTE（公共表表达式），命名为ordered_views
with ordered_views as (-- 从“网站页面浏览记录表”中选择数据select user_id,page_url,view_start_time,view_end_time,-- 使用LAG窗口函数，获取每个用户和页面URL分组下，按浏览开始时间排序后的上一条记录的浏览结束时间lag(view_end_time) over (partition by user_id, page_url order by view_start_time) as prev_end_timefrom 网站页面浏览记录表
)
-- 第二个CTE，命名为session_flags
, session_flags as (-- 选择ordered_views CTE中的所有列，并添加一个新列is_new_sessionselect *,-- 使用CASE语句判断是否为新会话case -- 如果上一条记录的结束时间为空（即当前是该用户和页面的第一条记录）-- 或者当前记录的开始时间与上一条记录的结束时间间隔大于30分钟when prev_end_time is null or timestampdiff(minute, prev_end_time, view_start_time) > 30 then 1 else 0 end as is_new_sessionfrom ordered_views
)
-- 第三个CTE，命名为session_groups
, session_groups as (-- 选择session_flags CTE中的所有列，并添加一个新列session_groupselect *,-- 使用SUM窗口函数，对每个用户和页面URL分组下，按浏览开始时间排序后的is_new_session列进行累加sum(is_new_session) over (partition by user_id, page_url order by view_start_time) as session_groupfrom session_flags
)
-- 第四个CTE，命名为session_ids
, session_ids as (-- 选择session_groups CTE中的所有列，并添加一个新列session_idselect *,-- 使用DENSE_RANK窗口函数，对每个用户和页面URL分组下，按session_group进行排名，得到会话IDdense_rank() over (partition by user_id, page_url order by session_group) as session_idfrom session_groups
)-- 主查询
select user_id,page_url,session_id,-- 对于每个用户、页面和会话ID分组，获取最小的浏览开始时间min(view_start_time) as view_start_time,-- 对于每个用户、页面和会话ID分组，获取最大的浏览结束时间max(view_end_time) as view_end_time,-- 计算合并后的会话时长（以分钟为单位）timestampdiff(minute, min(view_start_time), max(view_end_time)) as merged_duration,-- 计算每个会话内实际的浏览时长总和（以分钟为单位）sum(timestampdiff(minute, view_start_time, view_end_time)) as total_real_duration
from session_ids
-- 按照用户ID、页面URL和会话ID进行分组
group by user_id, page_url, session_id
-- 按照用户ID、页面URL和会话ID进行排序
order by user_id, page_url, session_id;

三、公交乘客乘车时间分析🎈🎈🎈

根据公交乘客乘车记录，按照 “下车后90分钟内再次上车视为同一次出行” 的规则，完成以下任务：

  1. 分配出行ID：为每个乘客的每次连续出行分配唯一ID
  2. 计算双指标：
    • 合并时长：从首次上车到最后一次下车的总时间跨度
    • 实际时长：累加各次乘车的独立时长
表名字段名字段含义数据类型
公交乘车记录表passenger_id乘客标识VARCHAR
boarding_time上车时间DATETIME
alighting_time下车时间DATETIME
passenger_idboarding_timealighting_time
P0012025-04-01 08:00:002025-04-01 08:30:00
P0012025-04-01 08:40:002025-04-01 09:00:00
P0022025-04-01 09:30:002025-04-01 10:00:00
P0012025-04-01 09:10:002025-04-01 09:30:00
P0022025-04-01 10:40:002025-04-01 11:00:00

结果预期 ✅

passenger_idtrip_idboarding_timealighting_timemerged_durationtotal_actual_duration
P00112025-04-01 08:00:002025-04-01 09:30:009070 (30+20+20)
P00212025-04-01 09:30:002025-04-01 10:00:003030
P00222025-04-01 10:40:002025-04-01 11:00:002020

换汤不换药~😂

步骤1：排序并获取前次下车时间✅

在这里插入图片描述

中间表 ordered_records ✅

passenger_idboarding_timealighting_timeprev_alighting_time
P0012025-04-01 08:00:002025-04-01 08:30:00NULL
P0012025-04-01 08:40:002025-04-01 09:00:002025-04-01 08:30:00
P0012025-04-01 09:10:002025-04-01 09:30:002025-04-01 09:00:00
P0022025-04-01 09:30:002025-04-01 10:00:00NULL
P0022025-04-01 10:40:002025-04-01 11:00:002025-04-01 10:00:00
步骤2：标记新出行起点✅

在这里插入图片描述

中间表 session_flags ✅

passenger_idis_new_session
P0011 (首条记录)
P0010 (间隔10分钟)
P0010 (间隔10分钟)
P0021
P0021 (间隔40分钟)
步骤3：生成出行分组ID✅

在这里插入图片描述

中间表 session_groups ✅

passenger_idsession_group
P0011
P0011
P0011
P0021
P0022
步骤4：分配出行ID并聚合结果✅

在这里插入图片描述

最终结果 ✅

passenger_idboarding_timealighting_timetotal_riding_durationtotal_actual_duration
P0012025-04-01 08:00:002025-04-01 09:30:009070 (30+20+20)
P0022025-04-01 09:30:002025-04-01 10:00:003030
P0022025-04-01 10:40:002025-04-01 11:00:002020
-- 使用CTE（公共表表达式），命名为ordered_records
with ordered_records as (-- 从“公交乘车记录表”中选择数据select passenger_id,boarding_time,alighting_time,-- 使用LAG窗口函数，获取每个乘客分组下，按上车时间排序后的上一条记录的下车时间lag(alighting_time) over (partition by passenger_id order by boarding_time) as prev_alighting_timefrom 公交乘车记录表
)
-- 第二个CTE，命名为session_flags
, session_flags as (-- 选择ordered_records CTE中的所有列，并添加一个新列is_new_sessionselect *,-- 使用CASE语句判断是否为新会话case -- 如果上一条记录的下车时间为空（即当前是该乘客的第一条记录）-- 或者当前记录的上车时间与上一条记录的下车时间间隔大于90分钟when prev_alighting_time is null or timestampdiff(minute, prev_alighting_time, boarding_time) > 90 then 1 else 0 end as is_new_sessionfrom ordered_records
)
-- 第三个CTE，命名为session_groups
, session_groups as (-- 选择session_flags CTE中的所有列，并添加一个新列session_groupselect *,-- 使用SUM窗口函数，对每个乘客分组下，按上车时间排序后的is_new_session列进行累加sum(is_new_session) over (partition by passenger_id order by boarding_time) as session_groupfrom session_flags
)-- 主查询
select passenger_id,-- 对于每个乘客和会话组分组，获取最小的上车时间min(boarding_time) as boarding_time,-- 对于每个乘客和会话组分组，获取最大的下车时间max(alighting_time) as alighting_time,-- 计算总的乘车时长（以分钟为单位）timestampdiff(minute, min(boarding_time), max(alighting_time)) as total_riding_duration,-- 计算每个会话组内实际的乘车时长总和（以分钟为单位）sum(timestampdiff(minute, boarding_time, alighting_time)) as total_actual_duration
from session_groups
-- 按照乘客ID和会话组进行分组
group by passenger_id, session_group
-- 按照乘客ID和会话组进行排序
order by passenger_id, session_group;

熟悉的套路😂

步骤核心操作关键函数/逻辑
数据排序按乘客和时间排序LAG() + 窗口排序
标记新出行起点判断90分钟间隔TIMESTAMPDIFF() + CASE
生成分组ID累加标记值SUM() OVER()
聚合结果计算时间跨度和实际时长MIN()/MAX()/SUM()

后面大家可以自己做一做 博主就不啰嗦啦~ 答案贴在最后啦 做完再看哈🤣

📚 时间序列分组与合并扩展题库

题目一：用户登录会话合并

题干
合并用户登录记录，若两次登录间隔 ≤15分钟 视为同一会话，计算每个会话的持续时间和登录次数。

表结构

表名字段名字段含义数据类型
user_loginuser_id用户IDVARCHAR
login_time登录时间DATETIME

示例数据

user_idlogin_time
U10012024-03-01 08:00:00
U10012024-03-01 08:05:00
U10012024-03-01 08:25:00

期望输出

user_idsession_startsession_endduration_minlogin_count
U10012024-03-01 08:00:002024-03-01 08:05:0052
U10012024-03-01 08:25:002024-03-01 08:25:0001
题目二：设备故障时段统计

题干
合并设备的连续故障时段（故障状态代码=500），若两次故障间隔 ≤10分钟 视为同一故障事件。

表结构

表名字段名字段含义数据类型
device_logdevice_id设备IDVARCHAR
log_time日志时间DATETIME
status_code状态码INT

示例数据

device_idlog_timestatus_code
D0012024-03-01 10:00:00500
D0012024-03-01 10:05:00500
D0012024-03-01 10:20:00500

期望输出

device_idstart_timeend_timeerror_duration_min
D0012024-03-01 10:00:002024-03-01 10:05:005
D0012024-03-01 10:20:002024-03-01 10:20:000
题目三：订单支付超时分析

题干
合并订单的创建和支付操作，若支付时间在创建后 30分钟内 视为有效支付，统计每个订单的实际支付处理时长。

表结构

表名字段名字段含义数据类型
ordersorder_id订单IDVARCHAR
create_time创建时间DATETIME
pay_time支付时间DATETIME

示例数据

order_idcreate_timepay_time
O10012024-03-01 12:00:002024-03-01 12:05:00
O10012024-03-01 12:10:002024-03-01 12:30:00
O10022024-03-01 13:00:00NULL

期望输出

order_idvalid_pay_duration_min
O100125 (12:05-12:30)
O10020
题目四：交通卡口连续通行分析

题干
合并车辆通过相邻卡口的记录，若通过时间间隔 ≤5分钟 且行驶方向相同，视为连续通行，计算平均速度。

表结构

表名字段名字段含义数据类型
traffic_logcar_plate车牌号VARCHAR
direction行驶方向VARCHAR
pass_time通过时间DATETIME
location卡口位置VARCHAR

示例数据

car_platedirectionpass_timelocation
沪A12345东向2024-03-01 09:00:00卡口A
沪A12345东向2024-03-01 09:03:00卡口B
沪A12345东向2024-03-01 09:10:00卡口C

期望输出

car_platestart_timeend_timeavg_speed_kmh
沪A123452024-03-01 09:00:002024-03-01 09:10:0048
答案：
题目一：用户登录会话合并
-- 使用公共表表达式（CTE），命名为 ordered_logins
with ordered_logins as (-- 从 user_login 表中选择数据select user_id,login_time,-- 使用 LAG 窗口函数，在每个 user_id 分区内，按照 login_time 排序-- 获取当前记录的上一条记录的 login_time，命名为 prev_loginlag(login_time) over (partition by user_id order by login_time) as prev_loginfrom user_login
)
-- 第二个 CTE，命名为 session_flags
, session_flags as (-- 选择 ordered_logins CTE 中的所有列，并添加一个新列 is_new_sessionselect *,-- 使用 CASE 语句判断是否为新的会话case -- 如果上一条记录的登录时间为空（即当前是该用户的第一条登录记录）-- 或者当前记录的登录时间与上一条记录的登录时间间隔大于 15 分钟when prev_login is null or timestampdiff(minute, prev_login, login_time) > 15 then 1 else 0 end as is_new_sessionfrom ordered_logins
)
-- 第三个 CTE，命名为 session_groups
, session_groups as (-- 选择 session_flags CTE 中的所有列，并添加一个新列 session_idselect *,-- 使用 SUM 窗口函数，在每个 user_id 分区内，按照 login_time 排序-- 对 is_new_session 列进行累加，得到每个会话的唯一标识 session_idsum(is_new_session) over (partition by user_id order by login_time) as session_idfrom session_flags
)
-- 主查询
select user_id,-- 对于每个用户和会话 ID 分组，获取最小的登录时间作为会话开始时间min(login_time) as session_start,-- 对于每个用户和会话 ID 分组，获取最大的登录时间作为会话结束时间max(login_time) as session_end,-- 计算会话的持续时间（以分钟为单位）timestampdiff(minute, min(login_time), max(login_time)) as duration_min,-- 统计每个会话内的登录次数count(*) as login_count
from session_groups
-- 按照用户 ID 和会话 ID 进行分组
group by user_id, session_id;
题目二：设备故障时段统计
-- 使用公共表表达式（CTE），命名为 ordered_logs
with ordered_logs as (-- 从 device_log 表中选择数据，且只选择状态码为 500 的记录select device_id,log_time,-- 使用 LAG 窗口函数，在每个 device_id 分区内，按照 log_time 排序-- 获取当前记录的上一条记录的 log_time，命名为 prev_loglag(log_time) over (partition by device_id order by log_time) as prev_logfrom device_logwhere status_code = 500
)
-- 第二个 CTE，命名为 session_flags
, session_flags as (-- 选择 ordered_logs CTE 中的所有列，并添加一个新列 is_new_sessionselect *,-- 使用 CASE 语句判断是否为新的故障时段case -- 如果上一条记录的日志时间为空（即当前是该设备的第一条故障记录）-- 或者当前记录的日志时间与上一条记录的日志时间间隔大于 10 分钟when prev_log is null or timestampdiff(minute, prev_log, log_time) > 10 then 1 else 0 end as is_new_sessionfrom ordered_logs
)
-- 第三个 CTE，命名为 session_groups
, session_groups as (-- 选择 session_flags CTE 中的所有列，并添加一个新列 session_idselect *,-- 使用 SUM 窗口函数，在每个 device_id 分区内，按照 log_time 排序-- 对 is_new_session 列进行累加，得到每个故障时段的唯一标识 session_idsum(is_new_session) over (partition by device_id order by log_time) as session_idfrom session_flags
)
-- 主查询
select device_id,-- 对于每个设备和故障时段 ID 分组，获取最小的日志时间作为故障开始时间min(log_time) as start_time,-- 对于每个设备和故障时段 ID 分组，获取最大的日志时间作为故障结束时间max(log_time) as end_time,-- 计算故障时段的持续时间（以分钟为单位）timestampdiff(minute, min(log_time), max(log_time)) as error_duration_min
from session_groups
-- 按照设备 ID 和故障时段 ID 进行分组
group by device_id, session_id;
题目三：订单支付超时分析
select order_id,-- 使用 COALESCE 函数，若第一个参数不为 NULL，则返回第一个参数的值，否则返回第二个参数的值-- 这里计算有效支付时长（以分钟为单位）coalesce(-- 计算在创建时间和支付时间间隔不超过 30 分钟的情况下，最大支付时间和最小支付时间的差值（以分钟为单位）timestampdiff(minute, min(case when timestampdiff(minute, create_time, pay_time) <= 30 then pay_time end), max(case when timestampdiff(minute, create_time, pay_time) <= 30 then pay_time end)), 0) as valid_pay_duration_min
from orders
-- 按照订单 ID 进行分组
group by order_id;
题目四：交通卡口连续通行分析
-- 使用公共表表达式（CTE），命名为 ordered_passes
with ordered_passes as (-- 从 traffic_log 表中选择数据select car_plate,direction,pass_time,-- 使用 LAG 窗口函数，在每个 car_plate 和 direction 分区内，按照 pass_time 排序-- 获取当前记录的上一条记录的 pass_time，命名为 prev_passlag(pass_time) over (partition by car_plate, direction order by pass_time) as prev_passfrom traffic_log
)
-- 第二个 CTE，命名为 session_flags
, session_flags as (-- 选择 ordered_passes CTE 中的所有列，并添加一个新列 is_new_groupselect *,-- 使用 CASE 语句判断是否为新的连续通行组case -- 如果上一条记录的通过时间为空（即当前是该车牌和方向的第一条记录）-- 或者当前记录的通过时间与上一条记录的通过时间间隔大于 5 分钟when prev_pass is null or timestampdiff(minute, prev_pass, pass_time) > 5 then 1 else 0 end as is_new_groupfrom ordered_passes
)
-- 第三个 CTE，命名为 session_groups
, session_groups as (-- 选择 session_flags CTE 中的所有列，并添加一个新列 group_idselect *,-- 使用 SUM 窗口函数，在每个 car_plate 和 direction 分区内，按照 pass_time 排序-- 对 is_new_group 列进行累加，得到每个连续通行组的唯一标识 group_idsum(is_new_group) over (partition by car_plate, direction order by pass_time) as group_idfrom session_flags
)
-- 第四个 CTE，命名为 aggregated
, aggregated as (-- 选择 car_plate、计算每个连续通行组的最小通过时间、最大通过时间和通过次数select car_plate,min(pass_time) as start_time,max(pass_time) as end_time,count(*) as num_passesfrom session_groups-- 按照 car_plate、direction 和 group_id 进行分组group by car_plate, direction, group_id
)
-- 主查询
select car_plate,start_time,end_time,-- 计算平均速度（单位：km/h），假设每次通过间隔 4 公里（(num_passes - 1) * 4）-- 用通过间隔距离除以总时间（以小时为单位），并四舍五入到整数round((num_passes - 1) * 4 / (timestampdiff(minute, start_time, end_time) / 60.0), 0) as avg_speed_kmh
from aggregated
-- 只选择通过次数大于 1 的记录
where num_passes > 1;

在这里插入图片描述

版权声明:

本网仅为发布的内容提供存储空间，不对发表、转载的内容提供任何形式的保证。凡本网注明“来源：XXX网络”的作品，均转载自其它媒体，著作权归作者所有，商业转载请联系作者获得授权，非商业转载请注明出处。

我们尊重并感谢每一位作者，均已注明文章来源和作者。如因作品内容、版权或其它问题，请及时与我们联系，联系邮箱：809451989@qq.com，投稿邮箱：809451989@qq.com

最新新闻

热搜词

Go+Gin实现安全多文件上传：带MD5校验的完整解决方案 springboot毕设 会计学习网站 程序+论文 第二篇：判断、循环 关于解决拼接like的问题 【ceph运维】ceph误删admin auth处理 建造者模式——KFC点餐系统

声明：本站所有新闻及新闻图片来源于其他网站，如有侵权，请及时联系我们！

客户服务 | 关于我们 | 版权声明

版权所有：

Copyright 2024 尧图网 All Rights Reserved.QQ:809451989