224 lines
6.5 KiB
Markdown
224 lines
6.5 KiB
Markdown
|
### (토글)수동위치보고요청 패킷 분석
|
||
|
|
```SQL
|
||
|
|
|
||
|
|
-- 요청안한 자료 (기상예보)
|
||
|
|
SELECT distinct PACKET5
|
||
|
|
FROM TMP_REQUEST tr
|
||
|
|
WHERE not EXISTS (
|
||
|
|
SELECT 'x' FROM TB_PASSIVLC_REQUST
|
||
|
|
WHERE mssage_id = tr.PACKET5 )
|
||
|
|
ORDER BY PACKET5
|
||
|
|
|
||
|
|
20220202020015018
|
||
|
|
20220202020015818
|
||
|
|
20220202020016590
|
||
|
|
20220202020017318
|
||
|
|
20220202130015013
|
||
|
|
20220202130015802
|
||
|
|
20220202130016536
|
||
|
|
20220202130017272
|
||
|
|
20220202190015030
|
||
|
|
20220202190015816
|
||
|
|
20220202190016545
|
||
|
|
20220202190017300
|
||
|
|
|
||
|
|
-- 실 DB 확인
|
||
|
|
SELECT *
|
||
|
|
FROM TB_PASSIVLC_REQUST
|
||
|
|
WHERE MSSAGE_ID LIKE '2022020202001501%'
|
||
|
|
|
||
|
|
--------------------------------------------------------------
|
||
|
|
|
||
|
|
/*2.2일자 위치요청한 지료중 실패자료 분석 */
|
||
|
|
-- 삼영2일자 자료중 육상국 DB에 실패 처리된 자료 건수
|
||
|
|
-- 전체 91row, 103건
|
||
|
|
-- 완전실패 12row, 24건
|
||
|
|
SELECT *
|
||
|
|
FROM (
|
||
|
|
SELECT SAMENSS_SND_DT ,
|
||
|
|
count(*) AS cnt,
|
||
|
|
sum(CASE WHEN FAILR_MODEM_LIST IS NULL THEN 1 ELSE 0 END ) AS scnt,
|
||
|
|
sum(CASE WHEN FAILR_MODEM_LIST IS NULL THEN 0 ELSE 1 END ) AS fcnt
|
||
|
|
FROM TB_PASSIVLC_REQUST
|
||
|
|
WHERE MSSAGE_ID IN
|
||
|
|
( SELECT PACKET5
|
||
|
|
FROM TMP_REQUEST tr
|
||
|
|
WHERE PACKET1 = 30 )
|
||
|
|
GROUP BY SAMENSS_SND_DT
|
||
|
|
ORDER BY SAMENSS_SND_DT
|
||
|
|
) a
|
||
|
|
WHERE fcnt >= 1
|
||
|
|
|
||
|
|
-- 실패건중 육상국 요청자료
|
||
|
|
SELECT PROCESS
|
||
|
|
FROM TB_PASSIVLC_REQUST tpr
|
||
|
|
WHERE SAMENSS_SND_DT IN (
|
||
|
|
SELECT SAMENSS_SND_DT
|
||
|
|
FROM (
|
||
|
|
SELECT SAMENSS_SND_DT ,
|
||
|
|
count(*) AS cnt,
|
||
|
|
sum(CASE WHEN FAILR_MODEM_LIST IS NULL THEN 1 ELSE 0 END ) AS scnt,
|
||
|
|
sum(CASE WHEN FAILR_MODEM_LIST IS NULL THEN 0 ELSE 1 END ) AS fcnt
|
||
|
|
FROM TB_PASSIVLC_REQUST
|
||
|
|
WHERE MSSAGE_ID IN
|
||
|
|
( SELECT PACKET5
|
||
|
|
FROM TMP_REQUEST tr
|
||
|
|
WHERE PACKET1 = 30 )
|
||
|
|
GROUP BY SAMENSS_SND_DT
|
||
|
|
ORDER BY SAMENSS_SND_DT
|
||
|
|
) a
|
||
|
|
WHERE fcnt >= 1
|
||
|
|
)
|
||
|
|
ORDER BY SAMENSS_SND_DT, SND_DT
|
||
|
|
|
||
|
|
|
||
|
|
-- 특정일자 실패자료 + 요청LOG + 응답LOG
|
||
|
|
SELECT *
|
||
|
|
FROM (
|
||
|
|
SELECT '1' AS gbn, 'DB' AS gbnnm, a.mssage_id, a.mmsi, a.slot_no, a.modem_list, a.failr_modem_list, a.process,
|
||
|
|
to_char(a.process_dt, 'yyyy-mm-dd hh24:mi:ss') AS process_dt,
|
||
|
|
to_char(a.insert_dt, 'yyyy-mm-dd hh24:mi:ss') AS insert_dt,
|
||
|
|
to_char(a.snd_dt, 'yyyy-mm-dd hh24:mi:ss') AS snd_dt,
|
||
|
|
a.SAMENSS_SND_DT,
|
||
|
|
'' AS packet
|
||
|
|
FROM tmp_PASSIVLC_REQUST_fail a
|
||
|
|
UNION ALL
|
||
|
|
SELECT '2' AS gbn,'요청 LOG(실패모뎀)' AS gbnnm, PACKET5 , '', null, '', PACKET2 AS failr_modem_list, null, '', '', PACKET12, ''
|
||
|
|
, PACKET0
|
||
|
|
||nvl2(PACKET1, ','||PACKET1, '')
|
||
|
|
||nvl2(PACKET2, ','||PACKET2, '')
|
||
|
|
||nvl2(PACKET3, ','||PACKET3, '')
|
||
|
|
||nvl2(PACKET4, ','||PACKET4, '')
|
||
|
|
||nvl2(PACKET5, ','||PACKET5, '')
|
||
|
|
||nvl2(PACKET6, ','||PACKET6, '')
|
||
|
|
||nvl2(PACKET7, ','||PACKET7, '')
|
||
|
|
||nvl2(PACKET8, ','||PACKET8, '')
|
||
|
|
||nvl2(PACKET9, ','||PACKET9, '')
|
||
|
|
||nvl2(PACKET10,','||PACKET10, '')
|
||
|
|
||nvl2(PACKET11,','||PACKET11, '') AS packet
|
||
|
|
FROM TMP_REQUEST_log a
|
||
|
|
WHERE PACKET1 = '28'
|
||
|
|
AND EXISTS (
|
||
|
|
SELECT 'o'
|
||
|
|
FROM tmp_PASSIVLC_REQUST_fail
|
||
|
|
WHERE mssage_id = a.PACKET5
|
||
|
|
AND instr(failr_modem_list, a.PACKET2) > 0)
|
||
|
|
UNION ALL
|
||
|
|
SELECT '3' AS gbn,'응답 LOG(실패모뎀)' AS gbnnm, PACKET5 , '', null, '', PACKET2 AS failr_modem_list, 0, '', '', PACKET12, ''
|
||
|
|
, PACKET0
|
||
|
|
||nvl2(PACKET1, ','||PACKET1, '')
|
||
|
|
||nvl2(PACKET2, ','||PACKET2, '')
|
||
|
|
||nvl2(PACKET3, ','||PACKET3, '')
|
||
|
|
||nvl2(PACKET4, ','||PACKET4, '')
|
||
|
|
||nvl2(PACKET5, ','||PACKET5, '')
|
||
|
|
||nvl2(PACKET6, ','||PACKET6, '')
|
||
|
|
||nvl2(PACKET7, ','||PACKET7, '')
|
||
|
|
||nvl2(PACKET8, ','||PACKET8, '')
|
||
|
|
||nvl2(PACKET9, ','||PACKET9, '')
|
||
|
|
||nvl2(PACKET10,','||PACKET10, '')
|
||
|
|
||nvl2(PACKET11,','||PACKET11, '') AS packet
|
||
|
|
FROM TMP_REQUEST_log a
|
||
|
|
WHERE PACKET1 = '30'
|
||
|
|
AND EXISTS (
|
||
|
|
SELECT 'o'
|
||
|
|
FROM tmp_PASSIVLC_REQUST_fail
|
||
|
|
WHERE mssage_id = a.PACKET5
|
||
|
|
AND instr(failr_modem_list, a.PACKET2) > 0)
|
||
|
|
) a
|
||
|
|
ORDER BY mssage_id, decode(gbn, '1', '0', failr_modem_list), gbn
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
SELECT *
|
||
|
|
FROM TB_PASSIVLC_REQUST
|
||
|
|
WHERE SAMENSS_SND_DT = '20220130181603964'
|
||
|
|
|
||
|
|
SELECT *
|
||
|
|
FROM TB_PASSIVLC_REQUST
|
||
|
|
WHERE MSSAGE_ID = '20220130202309606'
|
||
|
|
|
||
|
|
SELECT *
|
||
|
|
FROM TMP_REQUEST tr
|
||
|
|
WHERE PACKET5 = '20220130202309606'
|
||
|
|
|
||
|
|
SELECT *
|
||
|
|
FROM TMP_REQUEST_log tr
|
||
|
|
WHERE PACKET5 = '20220130170334367'
|
||
|
|
|
||
|
|
```
|
||
|
|
|
||
|
|
### (토글)수동위치보고요청 건수 집계
|
||
|
|
```SQL
|
||
|
|
-- 총 선박 : 850
|
||
|
|
SELECT count(*) FROM TB_SHIP
|
||
|
|
|
||
|
|
|
||
|
|
-- 하루 총 요청 건수 : 3,870
|
||
|
|
SELECT count(*)
|
||
|
|
from TB_PASSIVLC_REQUST
|
||
|
|
WHERE TO_char(insert_dt, 'yyyymmdd') = '20220411'
|
||
|
|
|
||
|
|
|
||
|
|
-- 월별 요청건수 총합, 평균
|
||
|
|
SELECT substr(INSDATE, 1,6) AS yyyymm, sum(cnt) AS mcnt, round(avg(cnt)) mavg
|
||
|
|
FROM (
|
||
|
|
SELECT TO_char(insert_dt, 'yyyymmdd') AS insdate, count(*) AS cnt
|
||
|
|
FROM TB_PASSIVLC_REQUST
|
||
|
|
WHERE TO_char(insert_dt, 'yyyymmdd') like '2022%'
|
||
|
|
GROUP BY TO_char(insert_dt, 'yyyymmdd')
|
||
|
|
ORDER BY insdate DESC, cnt DESC ) a
|
||
|
|
GROUP BY substr(INSDATE, 1,6)
|
||
|
|
ORDER BY 1
|
||
|
|
|
||
|
|
|
||
|
|
-- 일자+선박별
|
||
|
|
SELECT TO_char(insert_dt, 'yyyymmdd') AS insdate, SLOT_NO
|
||
|
|
, sum(CASE WHEN requst_at = 0 THEN 1 ELSE 0 end) AS "사용자요청"
|
||
|
|
, sum(CASE WHEN requst_at IN (1, 2) THEN 1 ELSE 0 end) AS "GW요청"
|
||
|
|
, count(*) AS "요청합계"
|
||
|
|
, sum(CASE WHEN process IN (0, 1) THEN 1 ELSE 0 end) AS "미처리"
|
||
|
|
, sum(CASE WHEN process NOT IN (0, 1) THEN 1 ELSE 0 end) AS "처리"
|
||
|
|
, sum(CASE WHEN process = 0 THEN 1 ELSE 0 end) AS "처리대기"
|
||
|
|
, sum(CASE WHEN process = 1 THEN 1 ELSE 0 end) AS "처리중"
|
||
|
|
, sum(CASE WHEN process = 2 THEN 1 ELSE 0 end) AS "완료"
|
||
|
|
, sum(CASE WHEN process = 8 THEN 1 ELSE 0 end) AS "일부실패"
|
||
|
|
, sum(CASE WHEN process = 9 THEN 1 ELSE 0 end) AS "실패"
|
||
|
|
FROM TB_PASSIVLC_REQUST
|
||
|
|
WHERE TO_char(insert_dt , 'yyyymm') in ('202202', '202203')
|
||
|
|
GROUP BY TO_char(insert_dt, 'yyyymmdd'), SLOT_NO
|
||
|
|
ORDER BY insdate
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
-- 2월 요청플래그별 건수(선박)
|
||
|
|
SELECT TO_char(insert_dt, 'yyyymmdd') AS insdate, SLOT_NO
|
||
|
|
, count(*) AS "총 건수"
|
||
|
|
, sum(CASE WHEN requst_at = 0 THEN 1 ELSE 0 end) AS "사용자"
|
||
|
|
, sum(CASE WHEN requst_at IN (1, 2) THEN 1 ELSE 0 end) AS "자동합계"
|
||
|
|
, sum(CASE WHEN requst_at = 1 THEN 1 ELSE 0 end) AS "30분"
|
||
|
|
, sum(CASE WHEN requst_at = 2 THEN 1 ELSE 0 end) AS "60분"
|
||
|
|
FROM TB_PASSIVLC_REQUST
|
||
|
|
WHERE TO_char(insert_dt, 'yyyymmdd') like '202203%'
|
||
|
|
GROUP BY TO_char(insert_dt, 'yyyymmdd'), SLOT_NO
|
||
|
|
ORDER BY insdate
|
||
|
|
|
||
|
|
|
||
|
|
/* 실제로 30분 단위로 수동위치 요청하는가? -> O*/
|
||
|
|
|
||
|
|
-- 60분요청 확인
|
||
|
|
SELECT *
|
||
|
|
FROM TB_PASSIVLC_REQUST
|
||
|
|
WHERE TO_char(insert_dt, 'yyyymmdd') = '20220412'
|
||
|
|
--AND REQUST_AT = 2
|
||
|
|
AND SLOT_NO = 726
|
||
|
|
ORDER BY insert_dt, MSSAGE_ID
|
||
|
|
|
||
|
|
-- 특정선박의 자동수신위치 이력
|
||
|
|
SELECT DISTINCT REPORT_DT
|
||
|
|
FROM TB_LAST_LC_202204
|
||
|
|
WHERE SLOT_NO = 726
|
||
|
|
AND to_char(INSERT_DT, 'yyyymmdd') LIKE '20220412'
|
||
|
|
ORDER BY 1
|
||
|
|
```
|