AI & Data Dreamers練功房
寶箱片場規則
QUERY GATES・資料庫關卡・高階・4 題

SQL 高階・API 效能與管線趨勢

最近排名法百分位、組內前 N 與平均的差、工作階段切分、與前一天比較的趨勢。

這一頁是題幹試閱;登入/註冊後每題可以在瀏覽器裡執行、對答案,第一次交對 +10 XP。

  1. 01

    回應時間第75百分位

    困難百分位數

    SLA 報表要每個端點回應時間的第 75 百分位(P75)。SQLite 沒有百分位函式,請用「最近排名法」自己算:

    把同一個端點的所有呼叫(含錯誤)依 response_ms 由小到大排好,共 n 筆,第 k 筆的 response_ms 就是 P75,k=0.75 × n 無條件進位(例如 n = 7 時 k = 6,n = 8 時 k = 6)。

    輸出欄位依序為:endpoint、n、P75、最大回應時間。依端點由小到大排序。

    資料表 api_calls(API 呼叫紀錄,每次呼叫一列):call_id 呼叫編號(整數,主鍵,越晚越大)、endpoint 端點路徑(文字)、method HTTP 方法(文字)、status_code 狀態碼(整數)、response_ms 回應時間(整數,毫秒)、called_at 呼叫時間(文字,格式 'YYYY-MM-DD HH:MM:SS')、user_id 使用者(文字)。狀態碼 400 以上算錯誤。

    資料表 pipeline_runs(資料管線執行紀錄,每個階段每跑一次一列):run_id 紀錄編號(整數,主鍵,越晚越大)、pipeline 管線名稱(文字)、run_date 執行日(文字,格式 'YYYY-MM-DD')、stage 階段(文字:'extract'、'transform'、'load')、started_at 開始時間、finished_at 結束時間(文字,格式 'YYYY-MM-DD HH:MM:SS';還在跑的為 NULL)、status 狀態(文字:'success'、'failed'、'running')、rows_processed 處理筆數(整數;失敗或還在跑的為 NULL)。同一天的同一個階段可能因為失敗重跑或手動補跑而有多列。

    本題在瀏覽器內以 SQLite 執行,只寫一段 SELECT 即可。

    作答登入後可作答
  2. 02

    各端點最慢兩筆

    困難組內前 N

    要列出每個端點最慢的 2 次呼叫(含錯誤),並看它們比該端點的平均慢多少。

    每個端點依 response_ms 由大到小取前 2 筆;response_ms 相同時,call_id 較小的排前面,每個端點剛好取 2 筆。端點平均=該端點所有呼叫的 AVG(response_ms)。

    輸出欄位依序為:endpoint、名次(1 或 2)、call_id、response_ms、端點平均(ROUND(平均, 1))、差值(ROUND(response_ms - 平均, 1),平均用未四捨五入的值)。依端點由小到大、再依名次排序。

    資料表 api_calls(API 呼叫紀錄,每次呼叫一列):call_id 呼叫編號(整數,主鍵,越晚越大)、endpoint 端點路徑(文字)、method HTTP 方法(文字)、status_code 狀態碼(整數)、response_ms 回應時間(整數,毫秒)、called_at 呼叫時間(文字,格式 'YYYY-MM-DD HH:MM:SS')、user_id 使用者(文字)。狀態碼 400 以上算錯誤。

    資料表 pipeline_runs(資料管線執行紀錄,每個階段每跑一次一列):run_id 紀錄編號(整數,主鍵,越晚越大)、pipeline 管線名稱(文字)、run_date 執行日(文字,格式 'YYYY-MM-DD')、stage 階段(文字:'extract'、'transform'、'load')、started_at 開始時間、finished_at 結束時間(文字,格式 'YYYY-MM-DD HH:MM:SS';還在跑的為 NULL)、status 狀態(文字:'success'、'failed'、'running')、rows_processed 處理筆數(整數;失敗或還在跑的為 NULL)。同一天的同一個階段可能因為失敗重跑或手動補跑而有多列。

    本題在瀏覽器內以 SQLite 執行,只寫一段 SELECT 即可。

    作答登入後可作答
  3. 03

    切分工作階段

    困難工作階段

    要把每位使用者的呼叫切成一段一段的工作階段(session),列出每一段的起訖與呼叫數。

    規則:同一位使用者的呼叫依 called_at 排序;和前一次呼叫相隔超過 30 分鐘(大於 1800 秒;剛好 30 分鐘不算超過)就開始新的一段,每位使用者的第一次呼叫是第 1 段。相隔秒數請用 strftime('%s', 這一次) - strftime('%s', 前一次) 計算。

    輸出欄位依序為:user_id、段號(每位使用者從 1 開始)、這段第一次呼叫的 called_at、這段最後一次呼叫的 called_at、這段的呼叫數。依 user_id 由小到大、再依段號排序。

    資料表 api_calls(API 呼叫紀錄,每次呼叫一列):call_id 呼叫編號(整數,主鍵,越晚越大)、endpoint 端點路徑(文字)、method HTTP 方法(文字)、status_code 狀態碼(整數)、response_ms 回應時間(整數,毫秒)、called_at 呼叫時間(文字,格式 'YYYY-MM-DD HH:MM:SS')、user_id 使用者(文字)。狀態碼 400 以上算錯誤。

    資料表 pipeline_runs(資料管線執行紀錄,每個階段每跑一次一列):run_id 紀錄編號(整數,主鍵,越晚越大)、pipeline 管線名稱(文字)、run_date 執行日(文字,格式 'YYYY-MM-DD')、stage 階段(文字:'extract'、'transform'、'load')、started_at 開始時間、finished_at 結束時間(文字,格式 'YYYY-MM-DD HH:MM:SS';還在跑的為 NULL)、status 狀態(文字:'success'、'failed'、'running')、rows_processed 處理筆數(整數;失敗或還在跑的為 NULL)。同一天的同一個階段可能因為失敗重跑或手動補跑而有多列。

    本題在瀏覽器內以 SQLite 執行,只寫一段 SELECT 即可。

    作答登入後可作答
  4. 04

    成功率趨勢

    困難趨勢比較

    要看每條管線每天的成功率和前一天比是變好還是變差。

    每個 (pipeline, run_date) 的成功率(百分比)=當天 status 為 'success' 的列數 × 100.0 ÷ 當天全部列數(含 failed 與 running),ROUND(..., 1)。和同一條管線的前一個執行日比較(用未四捨五入的值比):較高為 '上升'、較低為 '下降'、相等為 '持平';該管線的第一個執行日沒有前一天,趨勢為 '首日',前一天成功率為 NULL。

    輸出欄位依序為:pipeline、run_date、成功率、前一天成功率(ROUND(..., 1))、趨勢。依 pipeline 由小到大、再依 run_date 由小到大排序。

    資料表 api_calls(API 呼叫紀錄,每次呼叫一列):call_id 呼叫編號(整數,主鍵,越晚越大)、endpoint 端點路徑(文字)、method HTTP 方法(文字)、status_code 狀態碼(整數)、response_ms 回應時間(整數,毫秒)、called_at 呼叫時間(文字,格式 'YYYY-MM-DD HH:MM:SS')、user_id 使用者(文字)。狀態碼 400 以上算錯誤。

    資料表 pipeline_runs(資料管線執行紀錄,每個階段每跑一次一列):run_id 紀錄編號(整數,主鍵,越晚越大)、pipeline 管線名稱(文字)、run_date 執行日(文字,格式 'YYYY-MM-DD')、stage 階段(文字:'extract'、'transform'、'load')、started_at 開始時間、finished_at 結束時間(文字,格式 'YYYY-MM-DD HH:MM:SS';還在跑的為 NULL)、status 狀態(文字:'success'、'failed'、'running')、rows_processed 處理筆數(整數;失敗或還在跑的為 NULL)。同一天的同一個階段可能因為失敗重跑或手動補跑而有多列。

    本題在瀏覽器內以 SQLite 執行,只寫一段 SELECT 即可。

    作答登入後可作答