标签
PostgreSQL , 物联网 , feed , 网游 , 热力 , 商场驻留 , 人群分析 , 实时热力图 , 实时在线图 , 实时分段最大最小区间图 , 任意滑动窗口实时最高、最低在线数
在现实生活中，经常有聚集分析的需求。
例如：
某个商场，每个时间点，商场的每个商铺位置的人群驻留数量。（有技术手段可以感知人的驻留位置，当走进某个区域时，将写入一条记录，表示你进入了这个区域，离开时记录一条离开的记录，如果长时间不动，则定时写心跳记录）。
某个网游，每个时间点，在线人数。（上线时写一条上线记录，下线时写一条下线记录。）
某个共享单车公司，每个时间点，在线和不在线的车辆数量。（借车时写一条上线记录，还车时写一条下线记录。同时每隔一段时间询问车辆状态。）
某个物联网企业，每个分钟单位内，最小、最大在线传感器的数量。（传感器上线时写一条上线记录，下线时写一条下线记录，同时每隔一段时间询问传感器状态。）
这种属于非常典型的FEED应用，要求输出每个时间点这个世界（系统）的在线数。（如果按时间段输出，则输出每个时间段内的最大，最小在线数，实际上就是取range的边界）。
场景：
某个物联网企业，有一些传感器，传感器上线时写一条上线记录，下线时写一条下线记录，同时每隔小时询问传感器状态，也就是说1小时内没有记录的传感器视为不在线。
企业需要统计每个分钟单位内，最小、最大在线传感器的数量。
1、表结构
2、索引
写入1.101亿测试数据（我们假设这是1小时的数据写入量，全天写入26.424亿记录），1001个传感器ID。
3、数据TTL，确保表比较瘦，只包含心跳时间范围内的数据。
由于每小时接收心跳，所以1小时内，必有数据，没有数据的传感器不计状态。因此我们保留1小时内的状态即可。
一种保留方法是pipelinedb，用法如下。
《数据保留时间窗口的使用》
另一种保留方法，使用两张表，轮询使用即可。
类似的用法如下
《PostgreSQL 数据rotate用法介绍 - 按时间覆盖历史数据》
4、使用递归查询，高效查询传感器的最终状态
执行计划如下
样例
效率很高，1.101亿数据，11毫秒获取最终在线状态。
在线的设备为state=t的。
5、统计任意时间点的传感器在线数量，如果每个设备上线的时间精确到秒(crt_time精确到秒)，那么不管有多少条记录，一天最多需要统计86400个时间点的传感器在线数量。
例如统计 时间点的传感器在线数量，加一个时间限制即可。
新增这个时间限制，会带来一定的性能影响，特别是如果这个时间是过去很久以前的时间，过滤会越多，性能下降越严重。
因此，建议实时，每秒发起一次查询请求，就不要加这个时间限制了。
6、一次性生成过去每一秒的在线数。
使用窗口查询的帧查询技术。（帧表示按时间排序，截止到当前记录的区间。）
7、统计每分钟内，最高在线数、最低在线数。
每秒查询一次，将数据写入结果表。
由于每次查询仅需12毫秒，每秒调用一次没有问题。
统计某一分钟内，最高在线数、最低在线数。
当传感器ID达到10万级别时，查询性能会下降到250毫秒。
如果传感器ID特别多，例如有百万以上，那么会下降到2.5秒。就不适合每秒查询一次了。
因此传感器数量特别多时，如何优化？
有一个比较好的方法是数据按传感器ID进行哈希分布，例如每张分区表负责1万个传感器ID。在查询在线数时，并发的查询所有的分区表，从而降低RT。
使用本文提到的方法(递归查询)，我们可以实现非常细粒度的，大量被跟踪物的状态实时统计。
用于绘制被跟踪物的实时状态图，例如：
1、实时热力图