数据开发在图书馆场景的应用实践案例-阿里云开发者社区

数据开发在图书馆场景的应用实践案例

2023-05-11 11998

版权

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

简介： 本文介绍如何使用物联网平台SQL工作的实践案例

1. 需求描述

图书馆书籍上贴有"电子标签"(RFID)，IoT平台设备对应"电子标签"。书籍/电子标签/IoT设备这些概念的对应关系，参考下表理解。

图书馆概念	IoT平台概念	作用
图书	产品	通过产品物模型来描述图书借阅的信息。具体有：读者标示、借阅行为、归还行为。
书籍	电子标签(RFID)设备	用设备标示一本书。例如图书馆中有5本《长安十二时辰》，编号假设为a00, a01, a02, a03, a04。那么在IoT平台会对应5个设备。设备名称分别为"a00", "a01", "a02", "a03", "a04"。

1.1 统计读者借阅时长

当读者从图书馆借走书籍扫描电子标签时，"电子标签"对应的设备会上报借阅属性值。当读者归还书籍扫描电子标签时，"电子标签"对应的设备会上报归还属性值。需要利用数据开发计算读者借阅时长。

1.2 产品模型

产品利用$device_name 对应电子标签码, Status=0标示读者借阅书籍, Status=1标示读者归还书籍, UserID读者标示。GID 书柜编码。参考下图理解。

1.3 设备数据概览

参考下图理解。

2. 实现方案

由于书籍借阅与书籍归还对应两条数据，无法直接使用减法计算。先对原始数据进行"扩宽处理"，将读者标示、图书标示、借阅时间、归还时间字段放到一张表。

使用大数据中的数据分层思想来实现，期望宽表表头如下：

UserID

$device_name

LendOutTime

RestoreTime

2.1 借阅时间/归还时间定义

定义借阅时间/归还时间有两种方法，分别表述如下。

方法一

select 
  UserID, 
  $device_name as book_name, 
  IF(`Status`=0, $event_time, null) as LendoutTime, -- status = 0 标示借出 
  IF(`status`=1, $event_time, null) AS RestoreTime  -- status = 1 标示归还
from ${pk.a1ZSNGbXFPf};

实际查询结果如下图所示。

方法二

书籍一定先借出、再归还。如果按照书籍编号分组，再按照时间升序(先上传的在第一条)，那么奇数行就是借出、偶数行就是归还。

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}

实际查询效果如下所示。

2.2 包含借阅时间和借出时间的宽表

将借阅时间和借出时间拼成宽表，实际是解决数据分组的问题。把同一个用户对一本书籍借阅和归还的数据放到一个分组中。

这里有一个异常情况就是，如果用户还未归还书籍，那么分组中只有一条数据，需要补当前时间作为时间戳。

2.1小节中方法二已经实现了分组概念。因此延续2.1小节中方法二的思路。编写SQL如下：

select 
  user_name,
  book_name, 
  Status,
  nth_value (`target_time`, 1) over (partition by group_tag order by row_no asc) as LendoutTime,
  nth_value (`target_time`, 2) over (partition by group_tag order by row_no asc) as RestoreTime
from (
  select 
    row_no,
    UserID as user_name,
    book_name,
    Status,
    concat(book_name, IF (row_no % 2 = 1, row_no, row_no - 1)) as group_tag,
    target_time
  from (
    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}
  ) s_v
) ods_v

执行结果如下所示。

2.3 计算借阅时长

select user_name, book_name, (RestoreTime - LendoutTime) as delta_time
from (
  select 
    user_name,
    book_name, 
    Status,
    nth_value (`target_time`, 1) over (partition by group_tag order by row_no asc) as LendoutTime,
    nth_value (`target_time`, 2) over (partition by group_tag order by row_no asc) as RestoreTime
  from (
    select 
      row_no,
      UserID as user_name,
      book_name,
      Status,
      concat(book_name, IF (row_no % 2 = 1, row_no, row_no - 1)) as group_tag,
      target_time
    from (
      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}
    ) s_v
  ) ods_v 
) dwd_v where Status = '1'; -- 简化处理分组内只有一个元素的1情况，直接丢弃