github ThingsBoard的问题反馈中提到类似需求

准备

创建五个设备ABCDE，三个资产ABC，关联关系如下：关联类型都是Contains，关联方向都是从。

通过几个目标来一步步实现

1. 查询资产A/B/C下面的所有设备，包括资产下的资产中的设备，且设备不能重复统计。
2. 查询资产A/B/C下面的所有设备总数
3. 查询资产A/B/C下面的所有设备离线总数和在线总数
4. 查询租户/客户下的设备离线总数和在线总数（不关联资产，同于首页展示，谁登陆展示谁的所有设备）

说明：
目标2.3：可用于仪表板显示某些资产的设备总数，在线数，离线数。
也可以结合添加首页设置-展示仪表功能和添加首页设置-指定客户首页仪表功能为租户和客户定制首页仪表板。
目标4：用于首页展示当前登录用户的所有设备在线数，离线数。
本文描述的在线离线是基于设备服务端属性的active字段，设备disconnect不会立刻触发。可参考设备在线状态-active服务端属性分析。
文章在sql层面进行分析，没有发布接口相关文字。
relation相关介绍参考ThingsBoard中的关系Relation

目标1

查询资产A/B/C下面的所有设备，但设备不能重复。例如资产A包含的设备应该是五个，资产B五个设备，资产C三个设备。
设备及资产的id如下

先查询资产C id=ff8a1860-b2e4-11eb-8988-8dc0a81563ee

WITH RECURSIVE views AS (
    SELECT from_type, from_id, to_type, to_id
    FROM relation
    WHERE from_id = 'ff8a1860-b2e4-11eb-8988-8dc0a81563ee'
    UNION
    SELECT r.from_type, r.from_id, r.to_type, r.to_id
    FROM relation r
             INNER JOIN views v ON v.to_id = r.from_id
)
SELECT distinct to_id FROM views v where v.to_type = 'DEVICE';

结果

查询资产B id=f584c9a0-b2e4-11eb-8988-8dc0a81563ee
结果

查询资产A id=ef2418e0-b2e4-11eb-8988-8dc0a81563ee
结果

都符合预期。现在是查询资产下的所有设备，那么如何统计资产下面设备的总数，离线数，在线数呢？

目标2

统计资产A/B/C下面的所有设备总数，要求和前面一样。

WITH RECURSIVE views AS (
    SELECT from_type, from_id, to_type, to_id
    FROM relation
    WHERE from_id = 'ef2418e0-b2e4-11eb-8988-8dc0a81563ee'
    UNION
    SELECT r.from_type, r.from_id, r.to_type, r.to_id
    FROM relation r
             INNER JOIN views v ON v.to_id = r.from_id
)
select count(devices.to_id) from (SELECT distinct to_id FROM views v where v.to_type = 'DEVICE') devices;

测试结果就不贴出来了

目标3

查询资产A/B/C下面的所有设备离线总数和在线总数
使用MQTTBox让设备A/B/D在线

那么实际结果应该是资产A在线3离线2，资产B在线3离线2，资产C在线1离线2.结合active字段来实现。
先看结果：
资产A资产B资产C
SQL如下：