425
醫療大健康行業案例(老人健康實時監測和預警) - 阿裏雲RDS PostgreSQL最佳實踐
標簽
PostgreSQL , pipelineDB , 流式計算 , 獨立事件相關性 , 輿情分析 , 實時狀態分析 , 遞歸查詢 , 時序數據
背景
人的身體和機器差不多，隨著年齡的增長，器官逐漸老化，毛病也會越來越多，注意保養是一方麵，另一方麵也需要注意實時的監測和發出預警，在問題萌芽狀態就解決掉。
以往我們檢查身體得去醫院或專業的體檢機構，很麻煩，隨著科技的進步，一些健康指標的監測變得更加方便，例如手環也是一個普及很快的監控檢測終端（目前已能夠檢測心跳、溫度、運動等各項指標），未來這種終端能實時檢測的項目會越來越多。
如果手環算民用領域的健康檢測範疇，那麼在專業領域，比如一些醫院或養老院，他們有更多的傳感器，視頻，空間檢測等手段，可以對醫院或養老院的病人或老人進行更多指標的實時監測。
例如：
1、一個人的行為軌跡，在某個範圍不動，持續多久預警。
2、一個人在床的空間，躺了12個小時以上，預警。
3、一個人高度低於40公分，持續5分鍾，預警（周圍有人，不預警）。（比如高血壓、小偷）
4、一個人在馬桶的狹小空間，超過30分鍾，預警。
以上都有傳感器支持這樣的數據采集。
架構
數據流分為5個部分：
1、數據（來自傳感器）
2、數據轉換（格式化、狀態化）
可選步驟，數據轉換的目的是讓數據更容易被識別和處理，可以使用三種方法對數據進行實時轉換。
2.1、轉換規則定義在數據庫的UDF中，通過觸發器或規則進行入庫時實時轉換。
2.2、轉換規則定義在數據庫的UDF中，通過pipeline Transforms進行轉換。
2.3、在應用層進行轉換，轉換後的數據再入庫。
3、定義規則。
定義預警規則，例如前麵提到的：
一個人在馬桶的狹小空間，超過30分鍾，預警。
為了簡化訪問接口，可以將規則定義到數據庫的UDF中，通過視圖進行展示。
4、實時規則查詢。
查詢定義的規則，並預警。
5、預警。
優化思路
1、同一份數據（一條記錄可能包含高度、位置、心率等多個維度的屬性），可能在多個規則維度上進行計算，例如在心率上有一個預警規則，在位置上又有預警規則。
減少數據掃描量是一個比較通用的優化方法，9.6內核層麵也使用了類似的優化方法。
《PostgreSQL 9.6 內核優化之 聚合代碼優化OP複用淺析》
但是本例更加複雜，因為不同的規則，涉及的記錄範圍可能不一樣，例如在床上這個空間維度可能12小時才預警（可能有上千條記錄），而在高度這個維度，可能5分鍾就需要預警。
2、同一個傳感器的數據，盡量獨立存放，減少數據掃描成本。也就是說每個傳感器一張表。類似的優化方法參考
《PostgreSQL 時序最佳實踐 - 證券交易係統數據庫設計 - 阿裏雲RDS PostgreSQL最佳實踐》
3、由於預警並不需要保留所有記錄，可以使用rotate的方式，將曆史數據導出到OSS外部表。（偶爾需要查明細時，可以查詢，需要海量分析時，可以直接對接HybridDB for PostgreSQL進行分析）
《PostgreSQL 數據rotate用法介紹 - 按時間覆蓋曆史數據》
4、動態流計算規則，減少計算量。例如當用戶進入某個狀態後，才觸發對應的規則。例如用戶進入馬桶的空間後，才進行馬桶空間內的流計算規則運算。
DEMO
以這個例子為例，講一下如何預警：
一個人在馬桶的狹小空間，超過30分鍾，預警。
DEMO就不搞這麼複雜，不考慮優化因素。
建表
create table sensor_info(
sid int, -- 傳感器ID
pos point, -- 傳感器的相對坐標
crt_time timestamp -- 上傳時間
-- 其他屬性略，為演示方便。
);
create index idx_sensor_info on sensor_info (sid,crt_time desc);
create table userinfo (
uid -- 用戶和傳感器的對應關係表，略
sid
);
create table statistic_obj_info (
objid int, -- 靜態對象空間信息，例如床、馬桶
pos_range box -- 對象的空間範圍，如果使用postgis，請使用geometry來表示一個區間。
);
生成數據
insert into sensor_info select random()*1000, point(trunc((random()*10)::numeric,2), trunc((random()*10)::numeric,2)), now()+(id||' second')::interval from generate_series(1,10000000) t(id);
postgres=# select * from sensor_info limit 10;
sid | pos | crt_time
-----+-------------+----------------------------
888 | (1.43,5.58) | 2017-07-31 17:23:04.620488
578 | (5.6,2.01) | 2017-07-31 17:23:05.620488
186 | (6.98,9.91) | 2017-07-31 17:23:06.620488
99 | (4.1,7.46) | 2017-07-31 17:23:07.620488
30 | (6.25,6.07) | 2017-07-31 17:23:08.620488
403 | (5.12,6.26) | 2017-07-31 17:23:09.620488
60 | (9.8,8) | 2017-07-31 17:23:10.620488
654 | (1.83,5.41) | 2017-07-31 17:23:11.620488
731 | (5.72,4.67) | 2017-07-31 17:23:12.620488
230 | (4.99,8.3) | 2017-07-31 17:23:13.620488
(10 rows)
postgres=# select * from sensor_info where sid=1 order by crt_time desc limit 10;
sid | pos | crt_time
-----+-------------+----------------------------
1 | (9.83,6.18) | 2017-11-24 10:40:35.620488
1 | (3.18,9.82) | 2017-11-24 10:39:30.620488
1 | (1.79,6.24) | 2017-11-24 10:35:15.620488
1 | (3.13,8.42) | 2017-11-24 10:21:35.620488
1 | (5.11,4.17) | 2017-11-24 10:09:22.620488
1 | (9.51,3.41) | 2017-11-24 10:04:00.620488
1 | (2.24,2.35) | 2017-11-24 09:50:33.620488
1 | (7.2,8.67) | 2017-11-24 09:44:18.620488
1 | (2.32,4.48) | 2017-11-24 08:45:22.620488
1 | (0.33,9.33) | 2017-11-24 08:44:50.620488
(10 rows)
定義規則
一個人在馬桶的狹小空間，超過30分鍾，預警。
每個馬桶都定義一個相對坐標區間，當感應到用戶進入到某個區間後，對老人進行對應規則的監測。
幾何操作請參考
https://www.postgresql.org/docs/10/static/functions-geometry.html
或者
https://postgis.net/documentation/
使用UDF定義規則，輸出一個JSON。
create or replace function matong_rule(
v_sid int, -- 傳感器ID
pos_range box, -- 空間區間 , 如果使用postgis，請使用geometry來標注一個空間
ts interval -- 持續時間，采用interval類型
) returns jsonb as $$
declare
v sensor_info; -- 臨時類型
e timestamp; -- 最後時間
s timestamp; -- 最前時間
begin
for v in select * from sensor_info_1 where sid=v_sid order by crt_time desc
loop
if pos_range @> v.pos then
if e is null then e := v.crt_time; end if;
s := v.crt_time;
else
exit;
end if;
end loop;
if e-s >= ts then
return jsonb_build_object('sid', v_sid, 'pos_range', pos_range, 'start_time', s, 'end_time', e, 'interval', e-s);
else
return null;
end if;
end;
$$ language plpgsql strict;
例子，
1為老人身上的傳感器ID，中間的BOX為馬桶的區間範圍，第三個參數為持續時間。
當探測到老人進入了某個需要監測的靜態對象空間（例如進入床的空間、進入馬桶的空間）時，觸發以上規則，進行以上規則的查詢。
postgres=# select matong_rule(1,'(10,10),(0,0)','1 sec');
matong_rule
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
{"sid": 1, "end_time": "2017-11-24T10:40:35.620488", "interval": "115 days 17:08:19", "pos_range": "(10,10),(0,0)", "start_time": "2017-07-31T17:32:16.620488"}
(1 row)
Time: 23.200 ms
postgres=# select matong_rule(1,'(10,10),(1,1)','1 sec');
matong_rule
--------------------------------------------------------------------------------------------------------------------------------------------------------
{"sid": 1, "end_time": "2017-11-24T10:40:35.620488", "interval": "01:55:13", "pos_range": "(10,10),(1,1)", "start_time": "2017-11-24T08:45:22.620488"}
(1 row)
Time: 11.157 ms
postgres=# select matong_rule(1,'(10,10),(2,2)','1 sec');
matong_rule
--------------------------------------------------------------------------------------------------------------------------------------------------------
{"sid": 1, "end_time": "2017-11-24T10:40:35.620488", "interval": "00:01:05", "pos_range": "(10,10),(2,2)", "start_time": "2017-11-24T10:39:30.620488"}
(1 row)
Time: 11.325 ms
postgres=# select matong_rule(1,'(10,10),(3,3)','1 sec');
matong_rule
--------------------------------------------------------------------------------------------------------------------------------------------------------
{"sid": 1, "end_time": "2017-11-24T10:40:35.620488", "interval": "00:01:05", "pos_range": "(10,10),(3,3)", "start_time": "2017-11-24T10:39:30.620488"}
(1 row)
Time: 11.019 ms
實際上傳感器上傳的可能並不是相對坐標，而是直接上傳靜態對象（馬桶、床、。。。）的唯一標識，（例如對靜態對象全部編號，並且有對應的傳感器可以感應到有老人存在這個空間內，並實時上報監控數據。）
這樣就更加簡單了，連區間判斷都不需要，也是更加優雅的做法。但是壞處就是，老人離開監控區間的話，就無法被監控了，還是得靠老人身上的傳感器。
這兩種形態的總結：
1、輕終端，重服務端。
終端的能力較弱，隻是基本的數據采集，全部送到服務端進行計算。
2、重終端，輕服務端。
終端能力較強，可以進行數據采集，並且有部分數據計算能力，同時在大樓內增設一層靜態終端（如床、馬桶），對進入這個空間的老人（傳感器）進行聯動，上報更加精準的判斷數據。
查詢規則
輸出所有用戶的狀態。
例如，查詢傳感器ID 1-100的老人，在廁所空間內活動的狀態。
postgres=# select matong_rule(id ,'(10,10),(3,3)','1 sec') from generate_series(1,10) t(id);
matong_rule
--------------------------------------------------------------------------------------------------------------------------------------------------------
{"sid": 1, "end_time": "2017-11-24T10:40:35.620488", "interval": "00:01:05", "pos_range": "(10,10),(3,3)", "start_time": "2017-11-24T10:39:30.620488"}
{"sid": 2, "end_time": "2017-11-24T10:43:06.620488", "interval": "00:10:36", "pos_range": "(10,10),(3,3)", "start_time": "2017-11-24T10:32:30.620488"}
(10 rows)
Time: 115.501 ms
預警
有記錄返回則預警。
對於PostgreSQL，還有一個很有意思的功能，異步消息，可以用於異步的預警。
《從電波表到數據庫小程序之 - 數據庫異步廣播(notify/listen)》
《從微信小程序 到 數據庫"小程序" , 鬼知道我經曆了什麼》
《[轉載]postgres+socket.io+nodejs實時地圖應用實踐》
而如果你使用的是pipelinedb，那麼也可以選擇transform的實時預警功能。
https://docs.pipelinedb.com/continuous-transforms.html
CREATE TABLE t (user text, value int);
CREATE OR REPLACE FUNCTION insert_into_t()
RETURNS trigger AS
$$
BEGIN
INSERT INTO t (user, value) VALUES (NEW.user, NEW.value);
RETURN NEW;
END;
$$
LANGUAGE plpgsql;
CREATE CONTINUOUS TRANSFORM ct AS
SELECT user::text, value::int FROM stream WHERE value > 100
THEN EXECUTE PROCEDURE insert_into_t();
活動大盤
軌跡查詢，老人在每個場所停留的時間，同一個地方多條記錄，在繪製大盤時，隻保留兩條（到達和離開的時間）。
方法如下。
《車聯網案例，軌跡清洗 - 阿裏雲RDS PostgreSQL最佳實踐 - 窗口函數》
優化點1介紹
前麵提到了一個關於IO放大的優化，將所有的傳感器的數據分開存放，可以將IO放大的問題完全消除。我們看看經過IO消除後的性能如何：
postgres=# create table sensor_info_1 (like sensor_info including all);
CREATE TABLE
Time: 1.765 ms
postgres=# insert into sensor_info_1 select * from sensor_info where sid=1;
INSERT 0 9835
Time: 39.805 ms
postgres=# \d sensor_info_1
Table "postgres.sensor_info_1"
Column | Type | Collation | Nullable | Default
----------+-----------------------------+-----------+----------+---------
sid | integer | | |
pos | point | | |
crt_time | timestamp without time zone | | |
Indexes:
"sensor_info_1_sid_crt_time_idx" btree (sid, crt_time DESC)
postgres=# create or replace function matong_rule(
postgres(# v_sid int, -- 傳感器ID
postgres(# pos_range box, -- 空間區間
postgres(# ts interval -- 持續時間，采用interval類型
postgres(# ) returns jsonb as $$
postgres$# declare
postgres$# v sensor_info; -- 臨時類型
postgres$# e timestamp; -- 最後時間
postgres$# s timestamp; -- 最前時間
postgres$# begin
postgres$# for v in select * from sensor_info_1 where sid=v_sid order by crt_time desc
postgres$# loop
postgres$# if pos_range @> v.pos then
postgres$# if e is null then e := v.crt_time; end if;
postgres$# s := v.crt_time;
postgres$# else
postgres$# exit;
postgres$# end if;
postgres$# end loop;
postgres$#
postgres$# if e-s >= ts then
postgres$# return jsonb_build_object('sid', v_sid, 'pos_range', pos_range, 'start_time', s, 'end_time', e, 'interval', e-s);
postgres$# else
postgres$# return null;
postgres$# end if;
postgres$# end;
postgres$# $$ language plpgsql strict;
CREATE FUNCTION
Time: 0.469 ms
postgres=# select matong_rule(1,'(10,10),(3,3)','1 sec');
matong_rule
--------------------------------------------------------------------------------------------------------------------------------------------------------
{"sid": 1, "end_time": "2017-11-24T10:40:35.620488", "interval": "00:01:05", "pos_range": "(10,10),(3,3)", "start_time": "2017-11-24T10:39:30.620488"}
(1 row)
Time: 0.620 ms
相比一次查詢11毫秒，提升到了0.6毫秒。
小結
養老院是醫療大健康的典型案例之一，裏麵涉及到大量的空間數據、時間數據、大量的規則。需要一個功能強大的數據庫來支撐，否則所有數據都要搬運到APP層麵處理，效率低下。
PostgreSQL很好的支撐了養老院老人健康實時檢測，健康報告、軌跡查詢等場景的應用，同時曆史的傳感器數據通過OSS可以和HybridDB for PostgreSQL打通，實現一站時預警、分析需求。
參考
《PostgreSQL 9.6 內核優化之 聚合代碼優化OP複用淺析》
《車聯網案例，軌跡清洗 - 阿裏雲RDS PostgreSQL最佳實踐 - 窗口函數》
《潘金蓮改變了曆史之 - PostgreSQL輿情事件分析應用》
《PostgreSQL 時序最佳實踐 - 證券交易係統數據庫設計 - 阿裏雲RDS PostgreSQL最佳實踐》
《PostgreSQL 數據rotate用法介紹 - 按時間覆蓋曆史數據》
最後更新：2017-08-13 22:41:35