橫掃SQL面試——時間序列分組與合并（會話劃分）問題

橫掃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;

在這里插入圖片描述

本文來自互聯網用戶投稿，該文觀點僅代表作者本人，不代表本站立場。本站僅提供信息存儲空間服務，不擁有所有權，不承擔相關法律責任。
如若轉載，請注明出處：http://www.pswp.cn/web/74119.shtml
繁體地址，請注明出處：http://hk.pswp.cn/web/74119.shtml
英文地址，請注明出處：http://en.pswp.cn/web/74119.shtml

如若內容造成侵權/違法違規/事實不符，請聯系多彩編程網進行投訴反饋email:809451989@qq.com，一經查實，立即刪除！

相關文章

PCB鉆孔之多邊形孔分析

PCB鉆孔之多邊形孔分析

問題分析 在鉆孔過程中&#xff0c;鉆頭的運動可以分為兩部分&#xff1a; 公轉&#xff1a;鉆頭的軸線繞理想軸線&#xff08;鉆孔中心線&#xff09;做圓周運動。自轉&#xff1a;鉆頭繞自身軸線做旋轉運動。 由于公轉和自轉的疊加&#xff0c;鉆尖的運動軌跡會形成復雜的…
閱讀更多...
Android源碼之App啟動

Android源碼之App啟動

目錄 App啟動概述 App啟動過程 App啟動過程圖 源碼概述 跨進程啟動 進程內啟動 下面以應用桌面Launcher啟動App的MainActivity來舉例&#xff1a; App啟動概述 首先&#xff0c;MainActivity是由Launcher組件來啟動的&#xff0c;而Launcher又是通過Activity管理服務Act…
閱讀更多...
指紋瀏覽器技術解析：如何實現多賬號安全運營與隱私保護

指紋瀏覽器技術解析：如何實現多賬號安全運營與隱私保護

瀏覽器指紋的挑戰與需求 在數字化運營場景中&#xff0c;瀏覽器指紋技術被廣泛用于追蹤用戶行為。通過采集設備硬件參數&#xff08;如屏幕分辨率、操作系統&#xff09;、軟件配置&#xff08;如字體、插件&#xff09;及網絡特征&#xff08;如IP地址、時區&#xff09;&…
閱讀更多...
生活電子常識——cmd不能使用anaconda的python環境，導致輸入python打開應用商店

生活電子常識——cmd不能使用anaconda的python環境，導致輸入python打開應用商店

前言 電腦已經安裝了anaconda,從自帶的Anaconda Prompt (Anaconda3)中是可以識別python環境的&#xff0c;然而切換到cmd時&#xff0c;突然發現cmd中無法識別anaconda的python環境&#xff0c;竟然打開了應用商店讓我安裝Python&#xff0c;這當然是不對的。 解決 這是因為…
閱讀更多...
搭建前端環境和后端環境

搭建前端環境和后端環境

搭建前端環境 ①、安裝vscode&#xff0c;并安裝相應的插件工具 ②、安裝node.js&#xff0c;可以選擇當前版本&#xff0c;或者其他版本 ③、創建工作區 創建一個空文件夾&#xff0c;然后通過vscode工具打開&#xff0c;保存為后綴名為.code-workspace ④、從gitee…
閱讀更多...
Java基礎知識總結(1.8)——Java 注解(持續更新)

Java基礎知識總結(1.8)——Java 注解(持續更新)

更新時間&#xff1a;2025-03-31 Web后端專欄&#xff1a;CSDN專欄——理論-Web后端技術博客總目錄&#xff1a;計算機技術系列博客——目錄頁 8.1 注解的概念 8.1.1 定義與作用 Java注解&#xff08;Annotation&#xff09;是Java語言自JDK1.5版本引入的核心特性&#xff0…
閱讀更多...
線程概念與控制（下）

線程概念與控制（下）

線程概念與控制&#xff08;中&#xff09;https://blog.csdn.net/Small_entreprene/article/details/146539064?sharetypeblogdetail&sharerId146539064&sharereferPC&sharesourceSmall_entreprene&sharefrommp_from_link對于之前學習的內容&#xff0c;我們…
閱讀更多...
SQL注入之盲注技術詳解

SQL注入之盲注技術詳解

SQL注入之盲注技術詳解 一、盲注基本概念盲注特點&#xff1a; 二、盲注主要類型1. 布爾盲注判斷依據&#xff1a; 2. 時間盲注判斷依據&#xff1a; 三、布爾盲注詳細技術1. 識別布爾盲注2. 數據提取技術(1) 判斷數據庫類型(2) 獲取數據庫名長度(3) 逐字符獲取數據庫名(4) 獲取…
閱讀更多...
OpenCV 圖形API(3)高層次設計概覽

OpenCV 圖形API(3)高層次設計概覽

操作系統&#xff1a;ubuntu22.04 OpenCV版本&#xff1a;OpenCV4.9 IDE:Visual Studio Code 編程語言&#xff1a;C11 描述 G-API 是一個異構框架&#xff0c;提供了統一的 API 來使用多個支持的后端編程圖像處理流水線。 關鍵的設計理念是在指定使用哪些內核和設備時保持流…
閱讀更多...
阿里云Tair KVCache：打造以緩存為中心的大模型Token超級工廠

阿里云Tair KVCache：打造以緩存為中心的大模型Token超級工廠

一、Tair KVCache 簡介 Tair KVCache 是阿里云瑤池旗下云數據庫 Tair 面向大語言模型推理場景推出的 KVCache 緩存加速服務。 隨著互聯網技術的演進與流量規模的激增&#xff0c;緩存技術逐漸成為系統架構的核心組件。該階段催生了 Redis 等開源緩存數據庫&#xff0c;阿里巴巴…
閱讀更多...
Open GL ES -＞GLSurfaceView正交投影與透視投影方法中近遠平面取值參考

Open GL ES -＞GLSurfaceView正交投影與透視投影方法中近遠平面取值參考

坐標系 OpenGL ES使用右手坐標系&#xff0c;相機默認朝向負z方向 相機位置|vz軸<----- 0 -----> -near -----> -far -----不可見 可見區域 不可見裁剪規則 只有z值在[-near, -far]范圍內的物體可見&#xff0c; 當z > -near&#xff08;在近平面前&#…
閱讀更多...
iOS自定義collection view的page size(width/height)分頁效果

iOS自定義collection view的page size(width/height)分頁效果

前言 想必大家工作中或多或少會遇到下圖樣式的UI需求吧 像這種cell長度不固定&#xff0c;并且還能實現的分頁效果UI還是很常見的 實現 我們這里實現主要采用collection view&#xff0c;實現的方式是自定義一個UICollectionViewFlowLayout的子類&#xff0c;在這個類里對…
閱讀更多...
Java高頻面試之并發編程-01

Java高頻面試之并發編程-01

hello啊&#xff0c;各位觀眾姥爺們&#xff01;&#xff01;&#xff01;本baby今天來報道了&#xff01;哈哈哈哈哈嗝&#x1f436; 面試官&#xff1a;并行跟并發有什么區別&#xff1f; 并發 vs 并行&#xff1a;核心區別與場景 1. 定義對比 維度并發&#xff08;Concu…
閱讀更多...
從零開始學Rust：所有權（Ownership）機制精要

從零開始學Rust：所有權（Ownership）機制精要

文章目錄 第四章&#xff1a;Ownership 所有權核心概念關鍵機制引用與借用&#xff08;Reference & Borrowing&#xff09;懸垂引用問題錯誤示例分析解決方案引用安全規則 切片&#xff08;Slice&#xff09;內存安全保證 第四章&#xff1a;Ownership 所有權 Ownership i…
閱讀更多...
一旦懂得，有趣得緊1：詞根tempt-(嘗試)的兩種解法

一旦懂得，有趣得緊1：詞根tempt-(嘗試)的兩種解法

詞根tempt-嘗試 tempt vt.引誘&#xff1b;誘惑&#xff1b;慫恿&#xff1b;利誘&#xff1b;勸誘&#xff1b;鼓動 temptation n.引誘&#xff1b;誘惑 // tempt v.引誘 -ation 名詞后綴 attempt v.&n.嘗試&#xff0c;試圖 // at- 加強 tempt 嘗試contempt n.蔑視&am…
閱讀更多...
召喚數學精靈

召喚數學精靈

1.召喚數學精靈 - 藍橋云課 問題描述 數學家們發現了兩種用于召喚強大的數學精靈的儀式&#xff0c;這兩種儀式分別被稱為累加法儀式 A(n) 和累乘法儀式 B(n)。 累加法儀式 A(n) 是將從1到 n 的所有數字進行累加求和&#xff0c;即&#xff1a; A(n)12?n 累乘法儀式 B(n) …
閱讀更多...
C語言實現查表8位SAE J1850 CRC

C語言實現查表8位SAE J1850 CRC

背景&#xff1a; 在做霍爾采集電流的時候&#xff0c;CSSV1500N 系列電流傳感器通過can數據輸出的報文需要做crc校驗&#xff0c;嵌入式常用查表的方式&#xff0c;所以就問了下deepseek怎么算這個CRC. 以下是使用 查表法&#xff08;Lookup Table&#xff09; 在C語言中高效…
閱讀更多...
【UE5.3.2】初學1：適合初學者的入門路線圖和建議

【UE5.3.2】初學1：適合初學者的入門路線圖和建議

3D人物的動作制作 大神分析：3D人物的動作制作通常可以分為以下幾個步驟： 角色綁定（Rigging）：將3D人物模型綁定到一個骨骼結構上，使得模型能夠進行動畫控制。 動畫制作（Animation）：通過控制骨骼結構，制作出人物的各種動作，例如走路、跳躍、打斗等。 動畫編輯（Ani…
閱讀更多...
mapreduce的工作原理

mapreduce的工作原理

MapReduce 是 Hadoop 中實現分布式并行計算的核心框架&#xff0c;其工作原理基于“分而治之”的思想&#xff0c;將大規模數據處理任務分解為 Map&#xff08;映射&#xff09; 和 Reduce&#xff08;歸約&#xff09; 兩個階段。 一、MapReduce 核心流程 1. Input 階段 - 輸…
閱讀更多...
換季推廣不好做？DeepBI用一鍵托管的方式，讓廣告投放跑得快、準、穩

換季推廣不好做？DeepBI用一鍵托管的方式，讓廣告投放跑得快、準、穩

每年換季&#xff0c;尤其是春夏、秋冬交替的節點&#xff0c;都是電商平臺上各類季節性商品扎堆上新的高峰期。無論是服飾鞋包、家居戶外&#xff0c;還是母嬰用品、美妝護膚&#xff0c;許多商品都有著強烈的“時間窗口效應”——一旦錯過了熱賣期&#xff0c;流量下滑迅速&a…
閱讀更多...
最新文章

Copyright @ 2022~2023