醫學大健康產業案例(老人健康即時監測和通知) - 阿裡雲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核心層面也使用了類似的優化方法。

《PostgreSQL9.6 核心優化之 彙總代碼優化OP複用淺析》

但是本例更加複雜，因為不同的規則，涉及的記錄範圍可能不一樣，例如在床上這個空間維度可能12小時才通知（可能有上千條記錄），而在高度這個維度，可能5分鐘就需要通知。

2、同一個感應器的資料，儘量硬地存放，減少資料掃描成本。也就是說每個感應器一張表。類似的優化方法參考

《PostgreSQL時序最佳實踐 - 證券交易系統資料庫設計 - 阿裡雲RDSPostgreSQL最佳實踐》

3、由於通知並不需要保留所有記錄，可以使用rotate的方式，將歷史資料匯出到OSS外部表格。（偶爾需要查明細時，可以查詢，需要海量剖析時，可以直接對接HybridDB for PostgreSQL進行剖析）

《PostgreSQL資料rotate用法介紹 - 按時間覆寫歷史資料》

4、動態流計算規則，減少計算量。例如當用戶進入某個狀態後，才觸發對應的規則。例如用戶進入馬桶的空間後，才進行馬桶空間內的流計算規則運算。

以這個例子為例，講一下如何通知：

一個人在馬桶的狹小空間，超過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 (

objidint,--靜態物件空間資訊，例如床、馬桶

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

或者

http://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的即時通知功能。

http://docs.pipelinedb.com/continuous-transforms.html#built-in-transform-triggers

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();

活動大盤

曲目查詢，老人在每個地點停留的時間，同一個地方多條記錄，在轉譯大盤時，只保留兩條（到達和離開的時間）。

方法如下。

《車連網案例，曲目清洗 - 阿裡雲RDSPostgreSQL最佳實踐 - 視窗函數》

優化點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可以和HybridDBfor PostgreSQL打通，實現一站時通知、剖析需求。

《PostgreSQL9.6 核心優化之 彙總代碼優化OP複用淺析》

《車連網案例，曲目清洗 - 阿裡雲RDSPostgreSQL最佳實踐 - 視窗函數》

《潘金蓮改變了歷史之 -PostgreSQL輿情活動剖析套用》

《資料入庫即時轉換 -trigger , rule》

《PostgreSQL時序最佳實踐 - 證券交易系統資料庫設計 - 阿裡雲RDSPostgreSQL最佳實踐》

《PostgreSQL資料rotate用法介紹 - 按時間覆寫歷史資料》

《資料保留時間視窗的使用》

相關產品：

1. 醫學解決方案

2. 雲資料庫RDS

3. 安全管家

4. 雲端服務器ECS