如何使用SQL任务计算房间电量消耗的实践案例

-- 对原始数据加工，需要将j-1条数据拼接到j条数据上
create view ods_append_row as 
select 
  `$device_name`, 
    `$event_time`, 
    `$iot_id`,
    `Fwh`,
    -- over 窗口
    row_number() over(PARTITION BY `$iot_id` ORDER BY `$event_time` DESC ) as row_no,
    nth_value(`Fwh`, 1) over (PARTITION BY `$iot_id` ORDER BY `$event_time` DESC ) as latest_value,
    nth_value(`Fwh`, 2) over (PARTITION BY `$iot_id` ORDER BY `$event_time` DESC ) as previous_value
from your_table_name;
-- 对最近两次结果进行取差值
select 
    `$device_name`,
    `$event_time`,
    latest_value - previous_value as delta_Fwh
from ods_append_row where row_no <= 2 and previous_value is not null;