-->
当前位置:首页 > DayDayUp > 正文内容

sql

Luz12个月前 (07-17)DayDayUp1162

统计30天重复报警总数

  1. WITH FirstAlarm AS (
  2. SELECT
  3. bjdh,
  4. MIN(bjsj) AS first_bjsj
  5. FROM
  6. ZJIPST110.VI_jjd_jjd_cx where gxdwdm in ('330522630000', '330522171300') and bjdh is not null
  7. GROUP BY
  8. bjdh
  9. ),
  10. FilteredAlarms AS (
  11. SELECT
  12. a.bjdh,
  13. a.bjsj,
  14. f.first_bjsj,
  15. CASE
  16. WHEN a.bjsj >= f.first_bjsj + INTERVAL '8' MINUTE
  17. AND a.bjsj <= f.first_bjsj + INTERVAL '60' MINUTE
  18. THEN 1
  19. ELSE 0
  20. END AS is_within_8_to_60
  21. FROM
  22. ZJIPST110.VI_jjd_jjd_cx a
  23. JOIN FirstAlarm f ON a.bjdh = f.bjdh
  24. )
  25. SELECT
  26. COUNT(*) AS value
  27. FROM
  28. FilteredAlarms
  29. WHERE
  30. is_within_8_to_60 = 1;

统计30天重复报警清单

  1. WITH FirstAlarm AS (
  2. SELECT
  3. bjdh,
  4. MIN(bjsj) AS first_bjsj
  5. FROM
  6. ZJIPST110.VI_jjd_jjd_cx where gxdwdm in ('330522630000', '330522171300') and bjdh is not null
  7. GROUP BY
  8. bjdh
  9. ),
  10. FilteredAlarms AS (
  11. SELECT
  12. a.bjdh,
  13. a.bjsj,
  14. f.first_bjsj,
  15. CASE
  16. WHEN a.bjsj >= f.first_bjsj + INTERVAL '8' MINUTE
  17. AND a.bjsj <= f.first_bjsj + INTERVAL '60' MINUTE
  18. THEN 1
  19. ELSE 0
  20. END AS is_within_8_to_60
  21. FROM
  22. ZJIPST110.VI_jjd_jjd_cx a
  23. JOIN FirstAlarm f ON a.bjdh = f.bjdh
  24. )
  25. SELECT
  26. *
  27. FROM
  28. FilteredAlarms
  29. WHERE
  30. is_within_8_to_60 = 1;

统计今日重复报警总数

  1. WITH FirstAlarm AS (
  2. SELECT
  3. bjdh,
  4. MIN(bjsj) AS first_bjsj
  5. FROM
  6. ZJIPST110.VI_jjd_jjd_cx where gxdwdm in ('330522630000', '330522171300') and bjdh is not null and bjsj>trunc(sysdate)
  7. GROUP BY
  8. bjdh
  9. ),
  10. FilteredAlarms AS (
  11. SELECT
  12. a.bjdh,
  13. a.bjsj,
  14. f.first_bjsj,
  15. CASE
  16. WHEN a.bjsj >= f.first_bjsj + INTERVAL '8' MINUTE
  17. AND a.bjsj <= f.first_bjsj + INTERVAL '60' MINUTE
  18. THEN 1
  19. ELSE 0
  20. END AS is_within_8_to_60
  21. FROM
  22. ZJIPST110.VI_jjd_jjd_cx a
  23. JOIN FirstAlarm f ON a.bjdh = f.bjdh
  24. )
  25. SELECT
  26. COUNT(*) AS value
  27. FROM
  28. FilteredAlarms
  29. WHERE
  30. is_within_8_to_60 = 1;
  31. ```sql
  32. ## 统计今日重复报警清单
  33. ```sql
  34. WITH FirstAlarm AS (
  35. SELECT
  36. bjdh,
  37. MIN(bjsj) AS first_bjsj
  38. FROM
  39. ZJIPST110.VI_jjd_jjd_cx where gxdwdm in ('330522630000', '330522171300') and bjdh is not null and bjsj>trunc(sysdate)
  40. GROUP BY
  41. bjdh
  42. ),
  43. FilteredAlarms AS (
  44. SELECT
  45. a.bjdh,
  46. a.bjsj,
  47. f.first_bjsj,
  48. CASE
  49. WHEN a.bjsj >= f.first_bjsj + INTERVAL '8' MINUTE
  50. AND a.bjsj <= f.first_bjsj + INTERVAL '60' MINUTE
  51. THEN 1
  52. ELSE 0
  53. END AS is_within_8_to_60
  54. FROM
  55. ZJIPST110.VI_jjd_jjd_cx a
  56. JOIN FirstAlarm f ON a.bjdh = f.bjdh
  57. )
  58. SELECT
  59. *
  60. FROM
  61. FilteredAlarms
  62. WHERE
  63. is_within_8_to_60 = 1;

八分钟出警率30天

  1. 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')),
  2. all_cjd as (SELECT count(*) as allcjd from ZJIPST110.VI_CJD_CJD_CX where sjdwdm in ('330522630000', '330522171300'))
  3. SELECT eight_min.eight_min/ALL_CJD.allcjd*100 as value from eight_min,all_cjd;

八分钟出警率当日

  1. 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)),
  2. all_cjd as (SELECT count(*) as allcjd from ZJIPST110.VI_CJD_CJD_CX where sjdwdm in ('330522630000', '330522171300') and bjsj>trunc(sysdate))
  3. SELECT eight_min.eight_min/ALL_CJD.allcjd*100 as value from eight_min,all_cjd;

八分钟出警数30天

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

八分钟出警数当日

  1. 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)

未结清单当日

  1. 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天

  1. 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)

未结数当日

  1. 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天

  1. 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天报警趋势

  1. 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天报警趋势(含当日)

  1. 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

发表评论

访客

◎欢迎参与讨论,请在这里发表您的看法和观点。