1.上半部分是创建物化视图的建表语句,下半部分是将哪些数据更新到物化视图中;
- 如果是创建普通视图只需将下半部分中的 MATERIALIZED 字段去掉即可;
CREATE TABLE south.alarm_daily_statistics (
`alarm_time` DateTime DEFAULT CAST('1970-01-01 00:00:01',
'DateTime'),
`vehicle_no` String,
`owers_id` Int16,
`owers_name` String,
`alarmNumber` Int16
) ENGINE = MergeTree() PARTITION BY toYYYYMM(alarm_time) ORDER BY (alarm_time, owers_id) SETTINGS index_granularity = 8192;
CREATE MATERIALIZED VIEW south.alarm_daily_statistics_vim TO south.alarm_daily_statistics AS SELECT
toDate( a.alarm_time ) AS alarm_time,
a.device_id as vehicle_no,
max(owers_id) as owers_id,
max(owers_name) as owers_name,
count((CASE WHEN ( `a`.`alarm_source` = 2 ) THEN 1 END )) AS `alarmNumber`
FROM
alarm_info a
GROUP BY
alarm_time,
device_id
ORDER BY
alarm_time,
device_id;