找出效率不佳的充電器
隨著時間推移，充電器會變得沒那麼有效率。遇到這種情況，就需要維護或更換。是否有效率以充電速率為準。為了協助找出可能需要汰換的充電器，你需要撰寫一個查詢，使用視窗框架計算某個充電器的平均充電速率與剩餘工作階段數。加油！
本練習屬於課程
Snowflake 的 Window Functions
練習說明
- 以
charging_station_location為單位，使用從「第一列到目前列」的視窗框架，計算charging_rate的平均值。 - 依據
charging_station_location計算紀錄數量。 - 建立依
charging_start_time排序、從「目前列到最後一列」的視窗框架。
動手互動練習
試著完成這個範例程式碼，體驗一下這個練習。
SELECT
user_id,
TO_DATE(charging_start_time),
charging_station_location,
charging_rate,
-- Find the average charging rate, by charging station location
-- using a window frame between the first row and current row
___(___) OVER(
PARTITION BY ___
ORDER BY charging_start_time
ROWS BETWEEN ___ ___ AND ___ ___
) AS running_average_charging_rate,
-- Count the number of records by charging station location
___(*) OVER(
PARTITION BY charging_station_location
-- Create a window frame between the current row and the
-- last row, ordered by charging start time
ORDER BY ___
ROWS BETWEEN ___ ___ AND ___ ___
) AS remaining_charges
FROM ELECTRIC_VEHICLES.charging
ORDER BY charging_station_location, charging_start_time;