如何使用SQL任务计算房间电量消耗的实践案例-阿里云开发者社区

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

2023-05-11 608

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

简介： 文本介绍一例物联网平台数据服务SQL洞察用于智能家居的实践案例

1. 需求描述

在智能家居行业里产生了各种各样场景的数字解决方案，有解决方案商使用物联网平台的能力搭建各种数据分析应用，比如房间电量消耗统计，在公寓中部署了智能电表，每隔15min上传一次累计电量，需要根据设备上传值计算用电量。结合下表理解。

设备标示	上报属性值	上报时间	用电量
房间1电表	56.2	00:00	-
房间2电表	100.2	00:00	-
房间1电表	56.3	00:15	56.3-56.2 = 0.1度电
房间2电表	101	00:15	101-100.2 = 0.8度电
房间1电表	60	08:00	60-56.3 = 3.7度电
房间2电表	120	08:00	120-101 = 19度电

2. 实现方案

2.1 创建场景

创建电表模拟产品，电表产品只有一个只读属性Fwh，表示累计电量。参考图1。

创建两个电表设备:room1 和 room2。参考图2。

使用物联网在线调试功能定时上报属性, 为调试方便，可以将间隔设置为10s上报一次属性值。参考图3。

在数据开发页面刷新平台设备数据表，用于创建电表的分析模型。

图1 创建电表模拟产品

图2 创建模拟设备

图3 定时上报电量

2.2 计算公式

使用数据服务是具有计算能力的平台，可以在SQL工作台创建SQL脚本任务来完成计算，将计算结果用于业务系统。

设计计算公式如下：

可以使用窗口函数取出设备最后上传的2次属性值，计算差值即可。

-- 对原始数据加工，需要将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;