sql

统计30天重复报警总数

WITH FirstAlarm AS (
    SELECT
        bjdh,
        MIN(bjsj) AS first_bjsj
    FROM
        ZJIPST110.VI_jjd_jjd_cx where gxdwdm in ('330522630000', '330522171300')  and bjdh is not null 
    GROUP BY
        bjdh
),
FilteredAlarms AS (
    SELECT
        a.bjdh,
        a.bjsj,
        f.first_bjsj,
        CASE
            WHEN a.bjsj >= f.first_bjsj + INTERVAL '8' MINUTE
                 AND a.bjsj <= f.first_bjsj + INTERVAL '60' MINUTE
            THEN 1
            ELSE 0
        END AS is_within_8_to_60
    FROM
        ZJIPST110.VI_jjd_jjd_cx a
        JOIN FirstAlarm f ON a.bjdh = f.bjdh
)
SELECT
    COUNT(*) AS value
FROM
    FilteredAlarms
WHERE
    is_within_8_to_60 = 1;

统计30天重复报警清单

WITH FirstAlarm AS (
    SELECT
        bjdh,
        MIN(bjsj) AS first_bjsj
    FROM
        ZJIPST110.VI_jjd_jjd_cx where gxdwdm in ('330522630000', '330522171300')  and bjdh is not null 
    GROUP BY
        bjdh
),
FilteredAlarms AS (
    SELECT
        a.bjdh,
        a.bjsj,
        f.first_bjsj,
        CASE
            WHEN a.bjsj >= f.first_bjsj + INTERVAL '8' MINUTE
                 AND a.bjsj <= f.first_bjsj + INTERVAL '60' MINUTE
            THEN 1
            ELSE 0
        END AS is_within_8_to_60
    FROM
        ZJIPST110.VI_jjd_jjd_cx a
        JOIN FirstAlarm f ON a.bjdh = f.bjdh
)
SELECT
    *
FROM
    FilteredAlarms
WHERE
    is_within_8_to_60 = 1;

统计今日重复报警总数

WITH FirstAlarm AS (
    SELECT
        bjdh,
        MIN(bjsj) AS first_bjsj
    FROM
        ZJIPST110.VI_jjd_jjd_cx where gxdwdm in ('330522630000', '330522171300')  and bjdh is not null  and bjsj>trunc(sysdate)
    GROUP BY
        bjdh
),
FilteredAlarms AS (
    SELECT
        a.bjdh,
        a.bjsj,
        f.first_bjsj,
        CASE
            WHEN a.bjsj >= f.first_bjsj + INTERVAL '8' MINUTE
                 AND a.bjsj <= f.first_bjsj + INTERVAL '60' MINUTE
            THEN 1
            ELSE 0
        END AS is_within_8_to_60
    FROM
        ZJIPST110.VI_jjd_jjd_cx a
        JOIN FirstAlarm f ON a.bjdh = f.bjdh
)
SELECT
    COUNT(*) AS value
FROM
    FilteredAlarms
WHERE
    is_within_8_to_60 = 1;

```sql

## 统计今日重复报警清单
```sql
WITH FirstAlarm AS (
    SELECT
        bjdh,
        MIN(bjsj) AS first_bjsj
    FROM
        ZJIPST110.VI_jjd_jjd_cx where gxdwdm in ('330522630000', '330522171300')  and bjdh is not null and bjsj>trunc(sysdate)
    GROUP BY
        bjdh
),
FilteredAlarms AS (
    SELECT
        a.bjdh,
        a.bjsj,
        f.first_bjsj,
        CASE
            WHEN a.bjsj >= f.first_bjsj + INTERVAL '8' MINUTE
                 AND a.bjsj <= f.first_bjsj + INTERVAL '60' MINUTE
            THEN 1
            ELSE 0
        END AS is_within_8_to_60
    FROM
        ZJIPST110.VI_jjd_jjd_cx a
        JOIN FirstAlarm f ON a.bjdh = f.bjdh
)
SELECT
    *
FROM
    FilteredAlarms
WHERE
    is_within_8_to_60 = 1;

八分钟出警率30天

with eight_min as(SELECT count(*) as eight_min from ZJIPST110.VI_CJD_CJD_CX where (DDXCSJ-PDJSSJ)*24*60<8 and sjdwdm in ('330522630000', '330522171300')),
     all_cjd as (SELECT count(*) as allcjd from ZJIPST110.VI_CJD_CJD_CX where sjdwdm in ('330522630000', '330522171300'))
SELECT eight_min.eight_min/ALL_CJD.allcjd*100 as value from eight_min,all_cjd;

八分钟出警率当日

with eight_min as(SELECT count(*) as eight_min from ZJIPST110.VI_CJD_CJD_CX where (DDXCSJ-PDJSSJ)*24*60<8 and sjdwdm in ('330522630000', '330522171300') and bjsj>trunc(sysdate)),
     all_cjd as (SELECT count(*) as allcjd from ZJIPST110.VI_CJD_CJD_CX where sjdwdm in ('330522630000', '330522171300') and bjsj>trunc(sysdate))
SELECT eight_min.eight_min/ALL_CJD.allcjd*100 as value from eight_min,all_cjd;

八分钟出警数30天

SELECT count(*) as eight_min from ZJIPST110.VI_CJD_CJD_CX where (DDXCSJ-PDJSSJ)*24*60<8 and sjdwdm in ('330522630000', '330522171300')

八分钟出警数当日

SELECT count(*) as eight_min from ZJIPST110.VI_CJD_CJD_CX where (DDXCSJ-PDJSSJ)*24*60<8 and sjdwdm in ('330522630000', '330522171300') and bjsj>trunc(sysdate)

未结清单当日

select * from ZJIPST110.VI_JJD_JJD_CX jjd where gxdwdm in ('330522630000', '330522171300') and bjsj>trunc(sysdate) and not EXISTS(SELECT 1 from ZJIPST110.VI_FKD_FKD_CX fkd where fkd.jjdbh=JJD.JJDBH)

未结清单30天

select * from ZJIPST110.VI_JJD_JJD_CX jjd where gxdwdm in ('330522630000', '330522171300') and not EXISTS(SELECT 1 from ZJIPST110.VI_FKD_FKD_CX fkd where fkd.jjdbh=JJD.JJDBH)

未结数当日

select count(*) as value from ZJIPST110.VI_JJD_JJD_CX jjd where gxdwdm in ('330522630000', '330522171300') and bjsj>trunc(sysdate) and not EXISTS(SELECT 1 from ZJIPST110.VI_FKD_FKD_CX fkd where fkd.jjdbh=JJD.JJDBH)

未结数30天

select count(*) as value from ZJIPST110.VI_JJD_JJD_CX jjd where gxdwdm in ('330522630000', '330522171300') and not EXISTS(SELECT 1 from ZJIPST110.VI_FKD_FKD_CX fkd where fkd.jjdbh=JJD.JJDBH)

30天报警趋势

SELECT to_char(trunc(bjsj),'YYYY-MM-DD') as alarm_date,count(*) as value from ZJIPST110.VI_JJD_JJD_CX where gxdwdm in ('330522630000', '330522171300') GROUP BY trunc(bjsj) order by alarm_date

7天报警趋势（含当日）

SELECT to_char(trunc(bjsj),'YYYY-MM-DD') as alarm_date,count(*) as value from ZJIPST110.VI_JJD_JJD_CX where gxdwdm in ('330522630000', '330522171300') and bjsj>(trunc(sysdate)-6) GROUP BY trunc(bjsj) order by alarm_date