Starrocks实时指标-物化视图SQL示例

CREATE MATERIALIZED VIEW mv_realtime_alarm_statistics

COMMENT '实时报警等级统计:高等级/中等级/低等级'

REFRESH ASYNC START ('2024-11-13 00:00:00') EVERY (INTERVAL 10 SECOND) 

PROPERTIES ("replication_num" = "1")

AS 

WITH sort_values AS (

    SELECT 1 AS Sort UNION ALL

    SELECT 2 UNION ALL

    SELECT 3

)

SELECT 

    COALESCE(alarm_info.DeviceAlarmLevelId, NULL) AS DeviceAlarmLevelId,

    sv.Sort,

    alarmlevel.Name,

    COALESCE(alarm_info.cnt, 0) AS cnt

FROM sort_values sv

LEFT JOIN (

    SELECT 

        AlarmLevelId AS DeviceAlarmLevelId, 

        AlarmLevelSort AS Sort, 

        COUNT(*) AS cnt

    FROM bigdata_bi.ods_db_evoc_deviceinfo_alarm_info

    WHERE ParkId = 1625043522838081536

    AND NOT IsWhiteList

    AND HandleStatus = 1

    GROUP BY AlarmLevelId, AlarmLevelSort

) alarm_info ON sv.Sort = alarm_info.Sort

LEFT JOIN (

    SELECT 

        ALevelId, Custom, Name, ParkId, Remark, Sort

    FROM bigdata_bi.ods_evoc9_message_ms_alarmlevel

    WHERE ParkId = 1625043522838081536

) alarmlevel ON sv.Sort = alarmlevel.Sort;

 

CREATE MATERIALIZED VIEW mv_alarm_list_show

COMMENT '告警列表展示'

REFRESH ASYNC START ('2024-11-13 00:00:00') EVERY (INTERVAL 10 SECOND) 

PROPERTIES ("replication_num" = "1")

AS 

SELECT 

alarm_info.Id, 

alarm_info.alarmlevelsort,

alarmlevel.Name as alarmlevel_name,

alarm_info.AlarmId,

alarm_info.AlarmType,

alarm_type.alarm_incident_name,

alarm_info.DeviceTypeId,

alarm_info.captureposition,

device_type.Name,

alarm_info.DeviceAlarmLevelId,

alarm_info.AlarmTime

FROM (

SELECT 

Id, 

AlarmId,

AlarmType,

AlarmLevelId AS DeviceAlarmLevelId,

CapturePosition,

DeviceTypeId,

AlarmTime,

AlarmLevelSort

FROM bigdata_bi.ods_db_evoc_deviceinfo_alarm_info

WHERE ((ParkId = 1625043522838081536) AND NOT (IsWhiteList)) 

AND HandleStatus = 1

ORDER BY AlarmLevelSort, AlarmTime DESC

LIMIT 20

) alarm_info

left join (

SELECT 

ALevelId,Custom,Name,ParkId,Remark, Sort

FROM bigdata_bi.ods_evoc9_message_ms_alarmlevel

where ParkId = 1625043522838081536

) alarmlevel on alarm_info.AlarmLevelSort = alarmlevel.Sort 

left join (

select 

DeviceTypeId,

Name

from bigdata_bi.ods_db_evoc_deviceinfo_device_type

) device_type on alarm_info.DeviceTypeId = device_type.DeviceTypeId

left join dim_alarm_incident_type as alarm_type 

on alarm_info.AlarmType = alarm_type.alarm_incident_id

 

CREATE MATERIALIZED VIEW mv_alarm_list_show

COMMENT '告警列表展示'

REFRESH ASYNC START ('2024-11-13 00:00:00') EVERY (INTERVAL 10 SECOND) 

PROPERTIES ("replication_num" = "1")

AS 

SELECT 

alarm_info.Id, 

alarm_info.alarmlevelsort,

alarmlevel.Name as alarmlevel_name,

alarm_info.AlarmId,

alarm_info.AlarmType,

alarm_type.alarm_incident_name,

alarm_info.DeviceTypeId,

alarm_info.captureposition,

device_type.Name,

alarm_info.DeviceAlarmLevelId,

alarm_info.AlarmTime

FROM (

SELECT 

Id, 

AlarmId,

AlarmType,

AlarmLevelId AS DeviceAlarmLevelId,

CapturePosition,

DeviceTypeId,

AlarmTime,

AlarmLevelSort

FROM bigdata_bi.ods_db_evoc_deviceinfo_alarm_info

WHERE ((ParkId = 1625043522838081536) AND NOT (IsWhiteList)) 

AND HandleStatus = 1

ORDER BY AlarmLevelSort, AlarmTime DESC

LIMIT 20

) alarm_info

left join (

SELECT 

ALevelId,Custom,Name,ParkId,Remark, Sort

FROM bigdata_bi.ods_evoc9_message_ms_alarmlevel

where ParkId = 1625043522838081536

) alarmlevel on alarm_info.AlarmLevelSort = alarmlevel.Sort 

left join (

select 

DeviceTypeId,

Name

from bigdata_bi.ods_db_evoc_deviceinfo_device_type

) device_type on alarm_info.DeviceTypeId = device_type.DeviceTypeId

left join dim_alarm_incident_type as alarm_type 

on alarm_info.AlarmType = alarm_type.alarm_incident_id

 

CREATE MATERIALIZED VIEW mv_today_visitor_person_statistics

COMMENT '今日来访人员统计'

REFRESH ASYNC START ('2024-11-13 00:00:00') EVERY (INTERVAL 10 SECOND) 

PROPERTIES ("replication_num" = "1")

AS 

WITH person_stats AS (

    SELECT 

        COUNT(DISTINCT CASE WHEN person_detail.GuestStatus = 2 AND person_detail.ReviewStatus = 1 THEN person_detail.PersonId END) AS await_interview_person_num, -- 待访人数

        COUNT(DISTINCT CASE WHEN person_detail.GuestStatus = 2 AND person_detail.ReviewStatus = 2 THEN person_detail.PersonId END) AS already_visited_person_num, -- 已到访

        COUNT(DISTINCT CASE WHEN person_detail.GuestStatus = 1 THEN person_detail.PersonId END) AS have_already_reserved_person_num,  -- 已预约

        COUNT(DISTINCT CASE WHEN person_detail.GuestStatus = 2 AND person_detail.ReviewStatus = 1 THEN person_detail.PersonId END) +

        COUNT(DISTINCT CASE WHEN person_detail.GuestStatus = 2 AND person_detail.ReviewStatus = 2 THEN person_detail.PersonId END) +

        COUNT(DISTINCT CASE WHEN person_detail.GuestStatus = 1 THEN person_detail.PersonId END) AS total_person_num -- 预约总数

    FROM (

        SELECT 

            PersonId,

            DateTimeFrom,

            DateTimeTo, 

            GuestStatus,

            ParkId,

            ReviewStatus, 

            Status, 

            SyncStatus

        FROM bigdata_bi.ods_db_evoc9_person_ps_person

        WHERE (

            ((Type = 2) AND (ParkId = 1625043522838081536)) 

            AND (DateTimeFrom <= NOW())

            AND (DateTimeTo >= NOW())

        )

        AND (IsDelete = 0)

    ) person_detail

)

SELECT 

    '待访人数' AS name,

    COALESCE(await_interview_person_num, 0) AS value

FROM person_stats

UNION ALL

SELECT 

    '已到访' AS name,

    COALESCE(already_visited_person_num, 0) AS value

FROM person_stats

UNION ALL

SELECT 

    '已预约' AS name,

    COALESCE(have_already_reserved_person_num, 0) AS value

FROM person_stats

UNION ALL

SELECT 

    '预约总数' AS name,

    COALESCE(total_person_num, 0) AS value

FROM person_stats;

 

CREATE MATERIALIZED VIEW mv_today_person_through_proportion

COMMENT '今日人员通行类别占比统计'

REFRESH ASYNC START ('2024-11-13 00:00:00') EVERY (INTERVAL 10 SECOND) 

PROPERTIES ("replication_num" = "1")

AS

WITH entry_modes AS (

    SELECT 1 AS EntryMode UNION ALL

    SELECT 2 UNION ALL

    SELECT 3 UNION ALL

    SELECT 99

)

SELECT 

    em.EntryMode,

    COALESCE(p.entry_mode_name, CASE 

        WHEN em.EntryMode = 1 THEN '人脸'

        WHEN em.EntryMode = 2 THEN '二维码'

        WHEN em.EntryMode = 3 THEN '刷卡'

        WHEN em.EntryMode = 99 THEN '其他'

    END) AS entry_mode_name,

    COALESCE(p.count, 0) AS count,

    COALESCE((p.count * 100.0 / total.total_count), 0.00) AS ratio_percent

FROM entry_modes em

LEFT JOIN (

    SELECT 

        EntryMode AS RecordType,

        CASE 

            WHEN EntryMode = 1 THEN '人脸'

            WHEN EntryMode = 2 THEN '二维码'

            WHEN EntryMode = 3 THEN '刷卡'

            WHEN EntryMode = 99 THEN '其他'

        END AS entry_mode_name,

        COUNT(*) AS count

    FROM bigdata_bi.ods_db_evoc9_person_ps_personvisit

    WHERE ParkId = 1625043522838081536

    AND SignDate >= TIMESTAMP(CURDATE())

    AND SignDate < TIMESTAMP(CURDATE() + INTERVAL 1 DAY)

    GROUP BY EntryMode

) p ON em.EntryMode = p.RecordType

JOIN (

    SELECT 

        COUNT(*) AS total_count

    FROM bigdata_bi.ods_db_evoc9_person_ps_personvisit

    WHERE ParkId = 1625043522838081536

    AND SignDate >= TIMESTAMP(CURDATE())

    AND SignDate < TIMESTAMP(CURDATE() + INTERVAL 1 DAY)

) total;

 

CREATE MATERIALIZED VIEW mv_24hour_traffic_statistics

COMMENT '近24小时人流量统计'

REFRESH ASYNC START ('2024-11-14 00:00:00') EVERY (INTERVAL 10 SECOND) 

PROPERTIES ("replication_num" = "1")

AS

WITH hour_segments AS (

    SELECT 

        DATE_SUB(NOW(), INTERVAL 1 DAY) + INTERVAL h HOUR AS StartDateTime,

        DATE_SUB(NOW(), INTERVAL 1 DAY) + INTERVAL (h + 1) HOUR AS EndDateTime

    FROM (

        SELECT 00 AS h UNION ALL SELECT 01 UNION ALL SELECT 02 UNION ALL SELECT 03 UNION ALL SELECT 04 UNION ALL

        SELECT 05 UNION ALL SELECT 06 UNION ALL SELECT 07 UNION ALL SELECT 08 UNION ALL SELECT 09 UNION ALL

        SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL

        SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL

        SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23

    ) AS hours

),

person_stats AS (

    SELECT

        p.Type,

        p.PersonId,

        CONVERT(p.SignDate, date) AS Date, 

        EXTRACT(hour FROM p.SignDate) AS Hour

    FROM bigdata_bi.ods_db_evoc9_person_ps_personvisit AS p

    WHERE 

        p.ParkId = 1625043522838081536

        AND p.SignDate >= DATE_SUB(NOW(), INTERVAL 1 DAY)

        AND p.SignDate < NOW()

        AND p.Type IN (1, 2)

),

default_types AS (

    SELECT 

        hs.StartDateTime,

        hs.EndDateTime,

        t.Type

    FROM hour_segments hs

    CROSS JOIN (SELECT 1 AS Type UNION ALL SELECT 2) t

)

SELECT 

    DATE(dt.StartDateTime) AS Date,

    EXTRACT(hour FROM dt.StartDateTime) AS Hour,

    COALESCE(SUM(CASE WHEN ps.Type = 1 THEN 1 ELSE 0 END), 0) AS employee_person_num,

    COALESCE(SUM(CASE WHEN ps.Type = 2 THEN 1 ELSE 0 END), 0) AS visitor_person_num,

    CASE 

        WHEN dt.Type = 1 THEN '员工'

        WHEN dt.Type = 2 THEN '访客'

    END AS name

FROM default_types dt

LEFT JOIN person_stats ps ON DATE(dt.StartDateTime) = ps.Date AND EXTRACT(hour FROM dt.StartDateTime) = ps.Hour AND dt.Type = ps.Type

GROUP BY DATE(dt.StartDateTime), EXTRACT(hour FROM dt.StartDateTime), dt.Type

ORDER BY DATE(dt.StartDateTime), EXTRACT(hour FROM dt.StartDateTime), dt.Type;

 

#卡片统计

CREATE MATERIALIZED VIEW mv_card_building_statistics

COMMENT '卡片数量统计-楼栋数量'

REFRESH ASYNC START ('2024-11-13 00:00:00') EVERY (INTERVAL 10 SECOND) 

PROPERTIES ("replication_num" = "1")

AS

SELECT 

    COALESCE(t2.cnt, 0) AS cnt,

    '楼栋数量' AS description

FROM (

    SELECT 1 AS dummy

) t1

LEFT JOIN (

    SELECT 

        COUNT(*) AS cnt

    FROM bigdata_bi.ods_evoc_platform_pf_building

    WHERE ParkId = 1625043522838081536

    AND Type = 1

    AND IsDeleted = 0

) t2 ON t1.dummy = 1;

 

CREATE MATERIALIZED VIEW mv_card_firm_statistics

COMMENT '卡片数量统计-入驻企业数'

REFRESH ASYNC START ('2024-11-13 00:00:00') EVERY (INTERVAL 10 SECOND) 

PROPERTIES ("replication_num" = "1")

AS

SELECT 

    COALESCE(t2.cnt, 0) AS cnt,

    '入驻企业' AS description

FROM (

    SELECT 1 AS dummy

) t1

LEFT JOIN (

    SELECT 

        COUNT(*) AS cnt

    FROM bigdata_bi.ods_evoc_platform_pf_company

    WHERE ParkId = 1625043522838081536

    AND IsDeleted = 0

) t2 ON t1.dummy = 1;

 

CREATE MATERIALIZED VIEW mv_card_employee_person_statistics

COMMENT '卡片数量统计-员工人数'

REFRESH ASYNC START ('2024-11-13 00:00:00') EVERY (INTERVAL 10 SECOND) 

PROPERTIES ("replication_num" = "1")

AS

SELECT 

    t1.Type AS PersonType, 

    CASE 

        WHEN t1.Type = 1 THEN '员工人数'

        WHEN t1.Type = 2 THEN '访客人数'

    END AS name,

    COALESCE(t2.cnt, 0) AS cnt

FROM (

    SELECT 1 AS Type 

) t1

LEFT JOIN (

    SELECT 

        Type, 

        COUNT(*) AS cnt

    FROM bigdata_bi.ods_db_evoc9_person_ps_person

    WHERE ParkId = 1625043522838081536

    AND Type = 1

    AND IsDelete = 0

    GROUP BY Type

) t2 ON t1.Type = t2.Type;

 

CREATE MATERIALIZED VIEW mv_card_visitor_person_statistics

COMMENT '卡片数量统计-访客人数'

REFRESH ASYNC START ('2024-11-13 00:00:00') EVERY (INTERVAL 10 SECOND) 

PROPERTIES ("replication_num" = "1")

AS

SELECT 

    t1.Type AS PersonType, 

    CASE 

        WHEN t1.Type = 1 THEN '员工人数'

        WHEN t1.Type = 2 THEN '访客人数'

    END AS name,

    COALESCE(t2.cnt, 0) AS cnt

FROM (

    SELECT 2 AS Type 

) t1

LEFT JOIN (

    SELECT 

        Type, 

        COUNT(*) AS cnt

    FROM bigdata_bi.ods_db_evoc9_person_ps_person

    WHERE ParkId = 1625043522838081536

    AND Type = 2

    AND IsDelete = 0

    GROUP BY Type

) t2 ON t1.Type = t2.Type;

################################################################################

#设备类型、设备接入

#设备信息表—吴鸿洲（不同设备种类的设备数量）

-- 设备种类数量，就是去重后的DeviceCode的设备信息数量

CREATE MATERIALIZED VIEW mv_device_info_statistics

COMMENT '设备信息表-统计设备类型数和总设备数'

REFRESH ASYNC START ('2024-11-12 00:00:00') EVERY (INTERVAL 10 MINUTE)

PROPERTIES ("replication_num" = "1")

AS

SELECT 

    COUNT(DISTINCT DeviceCode) AS device_type_count,  -- 设备类型数

    COUNT(*) AS total_devices                         -- 总设备数

FROM bigdata_bi.ods_db_evoc_deviceinfo_device_info

WHERE ParkId = 1625043522838081536

AND IsDelete = 0

###########################################################

#能效设备信息表—宁承恒

#设备种类数量、设备数量

CREATE MATERIALIZED VIEW mv_device_energy_efficiency_statistics

COMMENT '能效设备信息表-统计设备类型数和总设备数'

REFRESH ASYNC START ('2024-11-12 00:00:00') EVERY (INTERVAL 10 MINUTE)

PROPERTIES ("replication_num" = "1")

AS

SELECT 

 COUNT(DISTINCT device_platform_type) AS device_type_count,  -- 设备类型数

 COUNT(device_type_id) AS total_devices          -- 总设备数

FROM bigdata_bi.ods_yx_energy_device_info

WHERE del_flag = 0

AND park_id = 1625043522838081536

###########################################################

#定位设备信息表—佘虎

#设备类型数

-- 定位设备为固定的3种数量类型，三张表就是代表3种类型。

CREATE MATERIALIZED VIEW mv_device_orientation_statistics

COMMENT '定位设备信息表-统计设备类型数和总设备数'

REFRESH ASYNC START ('2024-11-12 00:00:00') EVERY (INTERVAL 10 MINUTE)

PROPERTIES ("replication_num" = "1")

AS

select 

3 device_type_count,

sum(orientation_device.device_total) total_devices

from (

select 

'device_beacon' name,

count(*) device_total

from bigdata_bi.ods_positioning_device_beacon 

where del_flag=0 and park_id=1625043522838081536

union all

select 

'device_label' name,

count(*) device_total

from bigdata_bi.ods_positioning_device_label 

where del_flag=0 and park_id=1625043522838081536

union all

select 

'device_station' name,

count(*) device_total

from bigdata_bi.ods_positioning_device_station 

where del_flag=0 and park_id=1625043522838081536

) orientation_device

#######################################################################

#设备机房信息表—王仕林

#设备类型、设备数量统计

CREATE MATERIALIZED VIEW mv_device_room_statistics

COMMENT '设备机房信息表-统计设备类型数和总设备数'

REFRESH ASYNC START ('2024-11-12 00:00:00') EVERY (INTERVAL 10 MINUTE)

PROPERTIES ("replication_num" = "1")

AS

SELECT 

 COUNT(DISTINCT device_type) AS device_type_count,   -- 设备类型数

 COUNT(device_type_id) AS total_devices        -- 总设备数

FROM bigdata_bi.ods_devicex_app_device_info

WHERE del_flag = 0

and park_id = 1625043522838081536

 

#统计总设备类型数和总设备数

CREATE MATERIALIZED VIEW mv_deviceTyep_statistics

COMMENT '卡片数量统计-统计设备类型总数'

REFRESH ASYNC START ('2024-11-13 00:00:00') EVERY (INTERVAL 10 MINUTE)

PROPERTIES ("replication_num" = "1")

AS

SELECT 

    COALESCE(SUM(device_type_count), 0) AS total_device_types

FROM (

    SELECT device_type_count FROM bigdata_bi.mv_device_info_statistics

    UNION ALL

    SELECT device_type_count FROM bigdata_bi.mv_device_energy_efficiency_statistics

    UNION ALL

    SELECT device_type_count FROM bigdata_bi.mv_device_orientation_statistics

    UNION ALL

    SELECT device_type_count FROM bigdata_bi.mv_device_room_statistics

) AS device_data;

 

CREATE MATERIALIZED VIEW mv_deviceConnection_statistics

COMMENT '卡片数量统计-统计设备接入总数'

REFRESH ASYNC START ('2024-11-13 00:00:00') EVERY (INTERVAL 10 MINUTE)

PROPERTIES ("replication_num" = "1")

AS

SELECT

    COALESCE(SUM(total_devices), 0) AS total_devices

FROM (

    SELECT total_devices FROM bigdata_bi.mv_device_info_statistics

    UNION ALL

    SELECT total_devices FROM bigdata_bi.mv_device_energy_efficiency_statistics

    UNION ALL

    SELECT total_devices FROM bigdata_bi.mv_device_orientation_statistics

    UNION ALL

    SELECT total_devices FROM bigdata_bi.mv_device_room_statistics

) AS device_data;