需求描述
图书馆书籍上贴有"电子标签"(RFID)，IoT平台设备对应"电子标签"。书籍/电子标签/IoT设备这些概念的对应关系，参考下表理解。
1.统计读者借阅时长
当读者从图书馆借走书籍扫描电子标签时，"电子标签"对应的设备会上报借阅属性值。当读者归还书籍扫描电子标签时，"电子标签"对应的设备会上报归还属性值。需要利用数据开发计算读者借阅时长。
2.产品模型
产品利用$device_name 对应电子标签码, Status=0标示读者借阅书籍, Status=1标示读者归还书籍, UserID读者标示。GID 书柜编码。参考下图理解。
3.设备数据概览
参考下图理解。
实现方案
由于书籍借阅与书籍归还对应两条数据，无法直接使用减法计算。先对原始数据进行"扩宽处理"，将读者标示、图书标示、借阅时间、归还时间字段放到一张表。
使用大数据中的数据分层思想来实现，期望宽表表头如下：
1.借阅时间/归还时间定义
定义借阅时间/归还时间有两种方法，分别表述如下。
方法一
实际查询结果如下图所示。
方法二
书籍一定先借出、再归还。如果按照书籍编号分组，再按照时间升序(先上传的在第一条)，那么奇数行就是借出、偶数行就是归还。
实际查询效果如下所示。
2.包含借阅时间和借出时间的宽表
将借阅时间和借出时间拼成宽表，实际是解决数据分组的问题。把同一个用户对一本书籍借阅和归还的数据放到一个分组中。
这里有一个异常情况就是，如果用户还未归还书籍，那么分组中只有一条数据，需要补当前时间作为时间戳。
1小节中方法二已经实现了分组概念。因此延续1小节中方法二的思路。编写SQL如下：
1 select
2 user_name,
3 book_name,
4 Status,
5 nth_value (`target_time`, 1) over (partition by group_tag order by row_no asc) as LendoutTime,
6 nth_value (`target_time`, 2) over (partition by group_tag order by row_no asc) as RestoreTime
7 from (
8 select
9 row_no,
10 UserID as user_name,
11 book_name,
12 Status,
13 concat(book_name, IF (row_no % 2 = 1, row_no, row_no - 1)) as group_tag,
14 target_time
15 from (
16 select UserID, $device_name as book_name, Status, row_number() over (partition by $iot_id order by $event_time asc) as row_no, $event_time as target_time from ${pk.a1ZSNGbXFPf}
17 ) s_v
18 ) ods_v
执行结果如下所示。
3.计算借阅时长
1 select user_name, book_name, (RestoreTime - LendoutTime) as delta_time
2 from (
3 select
4 user_name,
5 book_name,
6 Status,
7 nth_value (`target_time`, 1) over (partition by group_tag order by row_no asc) as LendoutTime,
8 nth_value (`target_time`, 2) over (partition by group_tag order by row_no asc) as RestoreTime
9 from (
10 select
11 row_no,
12 UserID as user_name,
13 book_name,
14 Status,
15 concat(book_name, IF (row_no % 2 = 1, row_no, row_no - 1)) as group_tag,
16 target_time
17 from (
18 select UserID, $device_name as book_name, Status, row_number() over (partition by $iot_id order by $event_time asc) as row_no, $event_time as target_time from ${pk.a1ZSNGbXFPf}
19 ) s_v
20 ) ods_v
21 ) dwd_v where Status = '1'; -- 简化处理分组内只有一个元素的1情况，直接丢弃
执行结果如下所示。
结果使用
数据API
在云云对接场景用于应用集成，则可以配置API及SDK调用获取结果数据。
数据导出
如何结果数据需要线下备份或者使用xls进一步分析，可以使用“导出”功能。
可以按照时间范围进行数据导出，目前导出格式只支持csv。
详细操作步骤可以参考《IoT设备数据的存储、解析和价值挖掘实践》，这里不赘述。
相关产品文档如您想要了解更细节的产品功能使用指南，可以访问物联网平台的官网产品文档。
分析洞察官网文档：
https://help.aliyun.com/document_detail/325547.html
产品操作实践文档：
https://developer.aliyun.com/article/1166086