识别低效充电桩
随着时间推移，充电桩会变得不那么高效。一旦出现这种情况，就需要维护或更换。是否高效以充电速率为依据。为帮助识别可能需要更新的充电桩，您需要编写一个查询，利用窗口框架计算某个充电桩的平均充电速率，以及其剩余会话数量。祝您成功！
本练习是课程的一部分
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;