역할
PostgreSQL cmn 스키마에 등록된 집계·정리 프로시저 21개 + 조회/로깅 함수 5개임. 통계 화면이 읽는 *_dly_y(일별) / *_hly_y(시간별) 집계 테이블을 만들어 넣는 쪽이 전부 여기임.
- 호출하는 쪽은 공개노트/개발/모듈/통계 배치 서버임.
cmn.tb_proc_m등록 내용을 읽어SELECT cmn.{프로시저명}('0', {기준시각}, 'A')형태로 부름 - 날씨 조회 함수(
sf_*_for_usr) 3개만 예외로 공개노트/개발/모듈/MAW 앱 API 서버가 MyBatis로 직접 호출함 - 외부 시스템을 직접 호출하는 함수는 없음. DB 안에서 테이블만 읽고 씀
- 파일 위치는
lh-home-2022-svr/DB/01.Function/*.sql24개. 파일명 = 함수명
공통 골격
전부 FUNCTION이고 PROCEDURE가 아님
이름만 sp_로 시작할 뿐 실제로는 CREATE OR REPLACE FUNCTION ... LANGUAGE plpgsql임. 그래서 함수 안에 COMMIT/ROLLBACK이 없음. 호출자(배치)의 트랜잭션에 묶여 돌고, 정상 리턴해야 커밋됨.
공통 시그니처
집계 프로시저 21개가 전부 같은 파라미터 3개를 받음. DEFAULT 선언은 어디에도 없음.
FUNCTION cmn.{proc_nm}(p_proc_trg varchar, p_trg_dt varchar, p_run_ty_cd varchar)
RETURNS varchar
| 파라미터 | 의미 | 배치가 넣는 값 |
|---|---|---|
p_proc_trg | 프로시저 대상 | 항상 '0'(전체) |
p_trg_dt | 실행기준시간. 월 YYYYMM / 일 YYYYMMDD / 시간·분 YYYYMMDDHHMI00(14자리) | 직전 주기 값(일배치=어제, 시간배치=1시간 전) |
p_run_ty_cd | 실행타입. A=자동, M=수동 | 항상 'A' |
- 자릿수가 모자라거나 형식이 틀리면 대부분
대상일자 오류예외로 끝남 - 수동 재수행은
p_run_ty_cd='M'. 예:SELECT cmn.sp_tb_sbd_opr_dly_y('0','20250810','M');
시작·종료 로그 패턴
21개 전부 아래 틀을 그대로 복사해 씀. 시작·종료 기록은 공통 함수 2개가 담당함.
SELECT cmn.sf_sp_st_log(...) INTO v_log_sn; -- 시작로그, tb_proc_run_h INSERT
BEGIN -- 내부 블록(=서브트랜잭션)
(1) p_trg_dt 검증
(2) DELETE FROM {타겟} WHERE trg_ymd = ... -- 기존 집계 삭제
(3) WITH ... INSERT INTO {타겟} ... -- 재집계
EXCEPTION WHEN OTHERS THEN v_rslt_cd := '2'; -- 오류코드 + 메시지 조립
END;
PERFORM cmn.sf_sp_ed_log(...); -- 종료로그, 같은 행 UPDATE
RETURN v_rslt_cd;
| 함수 | 하는 일 | 알아둘 점 |
|---|---|---|
sf_sp_st_log | cmn.tb_proc_run_h에 실행중(rsl_cd='0') 행을 INSERT하고 proc_run_sn 반환 | 예외 처리가 없음. INSERT가 실패하면 호출한 프로시저 전체가 죽음 |
sf_sp_ed_log | 같은 행에 ed_dttm·tatm(초)·rsl_cd·rsl_row_cnt·er_msg UPDATE | 자체 예외 처리가 있어 실패해도 RAISE notice만 남기고 넘어감 |
- DELETE와 INSERT가 같은 내부 블록 안에 있음. 집계 중 오류가 나면 DELETE까지 롤백됨 → 실패해도 기존 집계가 날아가지는 않음. 다만 새 값도 안 들어옴
- 그래서 **집계 프로시저는 전부 재수행 가능(멱등)**함. 같은
p_trg_dt로 다시 돌리면 해당 일자만 지우고 다시 넣음 - 단, 모든 집계가 “실행 시점 마스터” 기준임. 세대수·가입자수는 스냅샷이 아니라
tb_hsh_m/tb_usr_m의 현재 상태로 계산됨. 며칠 지나 재수행하면 당시 값과 달라짐
실행 결과 읽는 법
결과코드 체계와 tb_proc_run_h 조회 방법은 공개노트/개발/모듈/통계 배치 서버에 정리돼 있음. DB 함수 쪽에서 추가로 알아둘 것만 적음.
rsl_cd='A'는 중간 상태임.sp_table_delete·sp_wdw_delete·sp_test_khd가 업무로직 진입 직전에 세팅함er_msg는MESSAGE_TEXT _|~ DETAIL _|~ HINT를_|~로 이어붙인 형태임. 이 구분자로 잘라 읽을 것tatm(소요시간, 초)이 갑자기 커지면 원천 테이블이 커졌다는 신호임- 로그 검색 키워드는
{프로시저명} EXCEPTION,대상일자 오류
집계·정리 프로시저 목록
주기·순번은 각 SQL 파일 헤더의 INSERT INTO cmn.tb_proc_m 예시문 기준임. 운영 실제 등록값은 아래로 확인할 것.
SELECT proc_nm, pod_cd, run_scd, run_sqn, us_yn, cron_schdl_yn
FROM cmn.tb_proc_m ORDER BY pod_cd, run_scd, run_sqn;| 프로시저 | 주기 / 순번 | 타겟 테이블 | 용도 |
|---|---|---|---|
sp_tb_sbd_opr_dly_y | 일 5시 / 1 | cmn.tb_sbd_opr_dly_y | 단지별 세대수·주거약자·누적가입 |
sp_tb_bld_opr_dly_y | 일 5시 / 2 | cmn.tb_bld_opr_dly_y | 위를 동(bld_id) 단위로 쪼갠 것 |
sp_tb_app_us_dly_y | 일 5시 / 3 | cmn.tb_app_us_dly_y | 앱 사용·접속자·제어 요청/성공 |
sp_tb_app_jn_conn_dly_y | 일 5시 / 4 | cmn.tb_app_jn_conn_dly_y | 앱 당일 가입자·접속자 |
sp_tb_app_jn_conn_hly_y | 시간 매시 5분 / 1 | cmn.tb_app_jn_conn_hly_y | 위의 시간별 + 금일 누계 |
sp_tb_web_jn_conn_dly_y | 일 5시 / 5 | cmn.tb_web_jn_conn_dly_y, cmn.tb_fo_jn_conn_dly_y | 단지 관리자 + 거점(매입임대) 가입자·접속자 |
sp_tb_web_jn_conn_hly_y | 시간 매시 5분 / 2 | cmn.tb_web_jn_conn_hly_y, cmn.tb_fo_jn_conn_hly_y | 위의 시간별 + 금일 누계 |
sp_tb_iot_cont_dly_y | 일 5시 / 7 | cmn.tb_iot_cont_dly_y | 단지×장치유형별 IoT 제어 요청·성공 |
sp_tb_iot_cont_hly_y | 시간 매시 5분 / 3 | cmn.tb_iot_cont_hly_y | 위의 시간별 |
sp_tb_dr_dly_y | 일 5시 / 6 | cmn.tb_dr_dly_y | DR 계약·AutoDR 세대, 연간 참여·감축량·수익 |
sp_tb_mngexp_rfe_rgs_dly_y | 일 5시 / 8 | cmn.tb_mngexp_rfe_rgs_dly_y | 전월 관리비·임대료 등록 세대수 |
sp_tb_cogo_hsh_dly_y | 일 5시 / 9 | cmn.tb_cogo_hsh_dly_y | 전입·전출 세대수 |
sp_tb_evn_occ_dly_y | 일 5시 / 10 | cmn.tb_evn_occ_dly_y | 단지×홈넷사×이벤트코드별 발생·조치 |
sp_tb_mngr_h_dly_y | 일 5시 / 10 | tb_mngr_h(C), tb_mngr_m(D) | Q+ 입주지원 관리자 100일 경과분 이력 이관·삭제 |
sp_tb_elc_vt_rsl_l | 시간 매시 0분 / 1 | smah.tb_elc_vt_m(U), tb_elc_vt_rsl_l(D+C) | 전자투표 상태 전환 + 마감분 결과 집계 |
sp_ev_if_gdata | 일 3시 / 2 | cmn.tb_evn_occ_l | 공공데이터 수신율 50% 미만 시 SE004 발생·해제 |
sp_table_delete | 일 3시 / 1 | 동적(설정된 모든 테이블) + batch.batch_* | 보관주기 경과분·배치 메타 삭제 |
sp_wdw_delete | 일 3시 / 1 | cmn.tb_usr_h(C), cmn.tb_usr_m(D) | 탈퇴 후 6일 경과 사용자 이력 이관·삭제 |
sp_test_khd | 테스트용 | batch.z_khd_tz | 실행 프레임 동작 확인. 운영에서 돌 일 없음 |
- 집계 대상 단지 기준이 프로시저마다 조금씩 다름. 기본은
tb_smah_sbd_d.svc_st_ymd가 있는 서비스 시작 단지 +grp_ds_cd='ARA'(지역그룹) 조인임- 매입임대 UNION 있음:
sbd_opr·bld_opr·app_us·app_jn_conn·mngexp_rfe·cogo_hsh - 매입임대 UNION 없음:
iot_cont·dr - ARA 그룹 조인 없음:
evn_occ
- 매입임대 UNION 있음:
- 순번(
run_sqn)이 앞인 프로시저가 실패하면 뒤가 그 회차에 전부 스킵됨. 공개노트/개발/모듈/통계 배치 서버
프로시저별 주의할 계산 규칙
| 프로시저 | 규칙 |
|---|---|
sp_tb_app_us_dly_y | 제어 성공 판정은 cont_rsp_cd='2000' 하나뿐임. 홈넷사가 다른 성공코드를 주면 성공률이 0%로 보임 |
sp_tb_app_us_dly_y, sp_tb_app_jn_conn_* | 접속자 = alog.tb_app_api_rcv_l의 당일 rqmr_id DISTINCT. 백그라운드 API 호출도 접속으로 잡힘. 이 로그는 보관주기 삭제 대상이라 오래된 날짜로 재수행하면 접속자수가 0이 됨 |
sp_tb_iot_cont_*, sp_tb_evn_occ_dly_y | 장치유형·이벤트코드를 CROSS JOIN해 0건 조합까지 적재함. 행수가 가장 빨리 커지는 테이블들임 |
sp_tb_evn_occ_dly_y | 발생일과 조치일이 다르면 같은 행에서 actn_cnt > occ_cnt가 나올 수 있음. 정상임. 집계 대상은 occ_src_cd IN ('HNC','H_A')뿐이라 플랫폼 자체 이벤트(SE004 등)는 안 들어감 |
sp_tb_mngexp_rfe_rgs_dly_y | 전월 청구분(clm_ym) 고정임. 월초에 “등록 세대수가 0으로 떨어졌다”는 문의는 이 기준 때문일 수 있음 |
sp_tb_cogo_hsh_dly_y | 세대 단위 1건으로 셈. 한 세대에서 2명 전입해도 1임(설계 의도). tb_cogo_h.rgs_dttm 기준이라 실제 전입일이 아니라 시스템 등록일임 |
sp_tb_dr_dly_y | 참여횟수·감축량·수익이 연간 누계임. 연초에 0부터 다시 쌓임 — “실적이 사라졌다”는 문의는 정상일 수 있음 |
sp_tb_elc_vt_rsl_l | 동점이면 rsl_chc_yn='Y'를 아무에게도 안 줌(설계 의도). 매시 0분 배치라 상태 전환이 최대 1시간 늦음. 이미 CMP가 된 투표는 재수행해도 상태 복구 안 됨 |
sp_table_delete | 삭제 기준이 p_trg_dt가 아니라 **NOW()**임. 기준컬럼이 %DTTM/%YMD로 끝나지 않으면 조용히 건너뜀 |
sp_wdw_delete | upd_dttm < 기준일 − 6일. 기준일 00시 기준이라 실측 경과는 6~7일 사이임 |
sp_tb_mngr_h_dly_y | 대상은 rol_id='MVIN_MNGR'이고 svc_st_ymd + 100일 < 기준일. 스키마 미지정 동적 SQL을 써 search_path에 의존함 |
날씨 조회 함수 (MAW 직접 호출)
집계가 아니라 앱 화면이 실시간으로 부르는 읽기 전용 함수 3개임. 로그를 안 남기므로 tb_proc_run_h에 안 뜸. 원천 수집은 공개노트/개발/모듈/SCW 스케줄러 서버 몫임.
| 함수 | 출력 | 원천 |
|---|---|---|
sf_now_wthr_for_usr | 현재 날씨 1행(기온·체감온도·습도·풍속·최저/최고) | hc.tb_strm_frcst_l 최근 3시간 |
sf_frcst_wthr_for_usr | 시간대별 예보 6건 고정 | hc.tb_strm_frcst_l 미래분 |
sf_now_wthr_arqlt_for_usr | 기상개황·기상특보·미세먼지 | hc.tb_wthrcnd_frcst_l, tb_wthrwrn_l, tb_arqlt_l |
- 사용자 → 세대 → 단지를 타고 격자좌표를 찾음. 못 찾으면 서울 종로구 (60, 127) 하드코딩 좌표로 대체됨 (3개 공통). “우리 단지 날씨가 아니라 서울 날씨가 나와요” = 단지에
grdx/grdy가 없다는 뜻임 sf_now_wthr_arqlt_for_usr는 기상개황이 구동 테이블임. 최근 1일치 개황이 없으면 특보·미세먼지도 통째로 안 나옴 → “미세먼지만 안 보여요”가 아니라 “날씨 카드 전체가 비어요”로 접수됨- 개황·특보 본문을 정규식으로 잘라 씀(
(종합)뒤,<기상특보>~<예비특보>사이). 기상청 원문 포맷이 바뀌면 내용이 비거나 엉뚱하게 잘림 sf_now_wthr_for_usr는 최저·최고기온을 CROSS JOIN으로 붙임. 당일 최저·최고 예보가 한 건도 없으면 현재기온까지 통째로 0행이 됨- 행정구역코드 앞 2자리 → 기상관서코드·측정소ID가 CASE문에 하드코딩돼 있음(17개 시·도, 기본값 서울
109/111121). 새 시·도나 측정소 변경은 코드 수정 없이는 반영 안 됨 - 빈 사용자ID로 호출하면
empty user id예외임. 로그 키워드WeatherMapper.getNowWeatherDto
하드코딩된 값
| 값 | 위치 | 내용 |
|---|---|---|
| 통계 제외 단지 | 집계 프로시저 전부 | sbd_id NOT IN ('A00001','C02631','C02683','C02783') — 아츠스테이·기축노이즈가드. 2025-08-01 커밋으로 추가됨 |
| 기본 격자좌표 | sf_*_for_usr 3개 | (60, 127) = 서울 종로구 |
| 공공데이터 수신율 임계값 | sp_ev_if_gdata | 50%, 비교기간 직전 10일 |
| 배치 메타 삭제 상한 | sp_table_delete | batch_job_execution 4000건, batch_job_instance 1000건, 보관 30일 |
| 관리자 이관 기준 | sp_tb_mngr_h_dly_y | 단지 서비스 시작일 + 100일 |
| 제어 성공 응답코드 | sp_tb_app_us_dly_y, sp_tb_iot_cont_* | cont_rsp_cd='2000' |
“특정 단지가 통계에 안 보여요” 점검 순서: 제외 4개 단지 → tb_smah_sbd_d.svc_st_ymd 공백 → tb_grp_sbd_r에 ARA 그룹 매핑 없음 → 세대(tb_hsh_m) 0건(INNER JOIN이라 통째로 빠짐) → 매입임대인데 UNION 없는 프로시저임.
알려진 이슈
sp_tb_iot_cont_*의BETWEEN경계 중복 —cont_rq_dttm BETWEEN 기준시각 AND 기준시각+1단위라 정각 데이터가 앞뒤 두 구간에 중복 집계됨. 다른 프로시저는>= AND <를 씀sp_tb_web_jn_conn_dly_y적재건수 누락 — 거점 INSERT 뒤GET DIAGNOSTICS가 주석 처리돼rsl_row_cnt가 실제보다 적게 보임- 프로그램ID 중복 —
sp_ev_if_gdata와sp_tb_web_jn_conn_dly_y가 둘 다CMN-PROC-8307 run_sqn중복 — 일 5시에sp_tb_evn_occ_dly_y·sp_tb_mngr_h_dly_y가 둘 다 10, 일 3시에sp_table_delete·sp_wdw_delete가 둘 다 1. 실행 순서가 비결정적임- 주석과 등록값 불일치 —
sp_tb_iot_cont_dly_y(주석 “매시 5분마다” ↔ 등록 일 5시),sp_table_delete·sp_wdw_delete(주석 “5시”/“1시” ↔ 등록 3시).tb_proc_m값이 진실임 - 스키마 미지정 SQL —
sp_ev_if_gdata의UPDATE tb_evn_occ_l,sp_tb_mngr_h_dly_y전체,sp_table_delete의 배치 메타 DELETE.search_path가 바뀌면 깨짐 sp_table_delete의 동적 DELETE —cmn.tb_tbl_cstd_pod_m값이 그대로 SQL로 조립됨.cstd_dd를 0이나 음수로 넣으면 그 테이블이 통째로 지워짐. 이 테이블 변경은 사실상 DELETE 문 작성과 같으므로 DBA 권한으로 제한할 것sp_tb_bld_opr_dly_y동 정보 누락 세대 —bld_id가 NULL인 세대는 사용자 집계와 매칭되지 않음sp_tb_dr_dly_y의 조인키 누락 — 정산내역을hsh_id로만 LEFT JOIN하고sbd_id를 안 씀. 세대가 이사로 단지가 바뀌면 값이 섞일 여지가 있음(추정)- 삭제 프로시저는 되돌릴 수 없음 —
sp_table_delete는 복구 불가,sp_wdw_delete·sp_tb_mngr_h_dly_y는 각각cmn.tb_usr_h·tb_mngr_h에서 되살려야 함
과거 데이터 신뢰 구간
| 수정 시점 | 내용 | 영향 |
|---|---|---|
| 2025-11-25 | 시간집계 기준시각 변환 오류(YYYYMMDDHH24MISS → 앞 10자리만) | 이전 시간별 통계 전부 매시 0~5분이 빠져 있음 (app_jn_conn_hly·iot_cont_hly·web_jn_conn_hly) |
| 2025-11-20 ~ 11-25 | 거점 시간별 접속자수 자리에 가입자수가 들어감 | 해당 기간 tb_fo_jn_conn_hly_y.conr_cnt 신뢰 불가 |
| 2025-11-11 | 매입임대 단지를 통계 대상에 추가 | 이전 통계에는 매입임대가 빠져 있음 |
| 2025-08-01 | 제외 4개 단지·del_yn='Y' 세대 제외 | 이전 통계에는 포함돼 있었음 |
| 2024-10-21 | 일별 가입·접속 집계 날짜 변환 오류 수정 | 이전 일부 기간 값이 틀렸을 수 있음 |
| 2024-10-20 | 접속자 원천을 로그인 로그 → 앱 API 수신로그로 변경 | 이전 접속자수는 과소 집계됨(JWT 유효기간 중에는 로그인을 안 함) |
| 2024-03-01 | 투표결과집계 기준시각 +1시간 보정 | 이전 투표 결과 집계 오류 |
확인 필요
확인 필요
sp_tb_web_jn_conn_hly_y의 당일 접속자수(cdy_conr_cnt)가 버그인지 — 단지분 조건이< date_trunc('day', 기준시각) + '1 hour'로 되어 있어 매 시간 행마다 “당일 0~1시 접속자수”만 들어감. 같은 파일 거점분과 앱 버전은< 기준시각 + '1 hour'임. 화면에서 WEB 당일 누적 접속자수를 쓰고 있다면 값이 틀려 있음. 개발자 확인 필요sp_wdw_delete가 헬스케어 데이터를 지우지 않는 것이 맞는지 — 헤더 주석에는 “건강측정내역·복약기본·비상연락처 삭제”라고 돼 있으나 코드가hc스키마를 건드리지 않음. 별도 파기 처리가 어딘가 있는지, 누락인지 확인 필요(개인정보 파기 정책)sp_test_khd가 운영 DB에us_yn='Y'로 등록돼 있는지 — 테스트용인데batch.z_khd_tz에 계속 INSERT함. 더구나execute pg_sleep(3)구문은 PL/pgSQLEXECUTE용법상 예외가 날 것으로 보임(추정). 등록돼 있으면 매번 실패 로그가 쌓임sp_ev_if_gdata의 0으로 나누기 — 수신율이SUM(Y_CNT)*100/SUM(O_CNT)라 직전 10일 데이터가 0이면 division by zero로 실패함(추정). 정수 나눗셈이라 소수점도 버려짐sp_tb_dr_dly_y의 계약년도 로직 — 코드에-- 국민DR 계약 기간 확인 후 수정 예정미완 주석이 남아 있음. 확정 필요cmn.tb_iot_cont_hly_y/cmn.tb_evn_occ_dly_y가 보관주기 관리 대상인지 — 두 테이블이 가장 빨리 커짐.cmn.tb_tbl_cstd_pod_m에 등록돼 있는지 확인 필요SE004이벤트가 어디로 알림되는지 —sp_ev_if_gdata는tb_evn_occ_l에 적재만 하고 알림을 안 보냄. 관리자 화면 알림·SMS 연계가 있는지 확인 필요- 함수 권한(GRANT) 상태 —
sp_tb_sbd_opr_dly_y한 파일에만 GRANT 문이 있음(OWNERsmah_admin, GRANTsmah_admin/smah_svc). 나머지 23개의 실제 권한은 DB 조회 필요- 운영
cmn.tb_proc_m실제 등록값 — 이 페이지의 주기·순번은 SQL 파일 헤더 예시 기준임. 배치 인스턴스 대수, 집계 테이블을 읽는 화면 위치와 함께 공개노트/개발/모듈/통계 배치 서버의 확인 필요 항목에도 걸려 있음
관련
공개노트/개발/모듈/통계 배치 서버 공개노트/개발/모듈/SCW 스케줄러 서버 공개노트/개발/모듈/MAW 앱 API 서버 공개노트/개발/대시보드 세대수와 단지 현황 공개노트/개발/시스템 구성 공개노트/개발/모듈/APW 연계 서버 기본매뉴얼/운영/장애 대응 절차