pipelinedb命令积累

2022-03-01 10:56:45

1.查看所有插件

1	`select` `* from pg_available_extensions;`

2.pipelinedb1.0创建流

create foreign table dwb.first_stream

(

arrival_timestamp timestamp with time zone,

id bigint

)

server pipelinedb;

comment on foreign table dwb.first_stream is '\u521d\u6b21\u4f7f\u7528\u6d41';

alter foreign table dwb.first_stream owner to postgres;

3.流规则

--使用SUM时，请务必使用COALESCE把NULL值替换掉，否则会导致严重的数据库后端进程崩溃重启！！！

drop view if exists dwb.test_view_mq ;

create view dwb.test_view_mq with (action=materialize) as

select

to_date( style="font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.8em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; text-align: left !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: normal !important; font-style: normal !important; font-size: 12px !important; min-height: auto !important; white-space: nowrap !important; color: rgb(0, 0, 255) !important;">'datetime'

,'YYYY-MM-DD HH24:MI:SS') as f_ds,

sum

(coalesce(cast( style="font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.8em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; text-align: left !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: normal !important; font-style: normal !important; font-size: 12px !important; min-height: auto !important; white-space: nowrap !important; color: rgb(0, 0, 255) !important;">'unreal_key'

as numeric),0)) as "testName",

count(*) as "车辆总数",

count( style="font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.8em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; text-align: left !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: normal !important; font-style: normal !important; font-size: 12px !important; min-height: auto !important; white-space: nowrap !important; color: rgb(0, 0, 255) !important;">'veh_type'

) as "去重"

from dwb.streams_page_log_mq

group by

f_ds

;

--清空cv数据

select pipelinedb.truncate_continuous_view('dws.flow_vehicle_min_statistic')

分享好友

分享这个小栈给你的朋友们，一起进步吧。