[postgresql] working day 테이블 만들기

개요

업무 담당자에게 특정 업무 처리 요청을 위해 하루에 한번 문자나 메일 발송을 해야하는 상황이 있다고 가정해봅시다.
업무 담당자는 평일에 열심히 업무를 보고 주말에 일에 대한 생각없이 푹 쉬고자 하는데 갑자기 문자 알림이 옵니다.
"처리할 업무가 있습니다. 처리해주세요" 보기만 해도 짜증이 확 밀려옵니다.

일일배치로 문자나 이메일 발송 프로그램을 제작하는 주체도 수신자와 같은 '사람'이기 때문에 수신자의 고충을 이해하고자 휴일을 따로 관리하는 테이블을 만들어보겠습니다.

작업은 크게 두가지로 나누어집니다.

1. 평일을 제외한 주말을 휴일로 지정
2. 명절 및 공휴일을 휴일로 지정

1.테이블 생성(주말)

먼저 전체 기간이 들어있는 테이블을 생성합니다.

전체 기간은 이번 포스팅에서 2022년, 1년으로 가정하겠습니다.

SELECT GENERATE_SERIES('2022-01-01'::date, '2022-12-31'::date, '1 day');

테이블을 생성하기 위해 먼저 조회부터 해봅니다. 1월 1일부터 12월 31일까지 한번에 가져왔습니다.

generate_series 는 postgresql에서 일련의 번호를 생성해줍니다. generate_series(a, b, c) 와 같이 사용하며 a부터 b까지의 일련번호를 c간격으로 가져오게되는데, 위에서는 c에 1 day를 부여함으로써 하루 간격으로 전체 날짜를 가져오게 됩니다.

쿼리를 실행시키면,

다음과 같은 결과를 확인할 수 있습니다. 일단 이번 테이블 생성의 목적은 working "DAY" 이기 때문에 일자 아래 단위는 절삭하도록 하겠습니다.

SELECT DATE(GENERATE_SERIES('2022-01-01'::date, '2022-12-31'::date, '1 day'));

연월일까지 나오는 모습을 확인할 수 있습니다. 이제 이 쿼리를 바탕으로 우리의 목적인 WORKING DAY 테이블을 만들어보겠습니다.

WITH BASE_DATE AS (
            SELECT DATE(GENERATE_SERIES('2022-01-01'::date, '2022-12-31'::date, '1 day')) AS YMD
            )
SELECT YMD
       ,TO_CHAR(YMD, 'DAY') AS YAMND
       ,TO_CHAR(YMD, 'D') AS YAMND_NUM
       ,CASE WHEN TO_CHAR(YMD, 'D') IN ('1', '7') THEN 1 END AS HLDY
FROM   BASE_DATE
;

먼저, CTE와 같은 WITH 구문을 사용해 위에서 조회했던 쿼리를 BASE_DATE로 임시로 저장합니다.
BASE_TABLE의 연-월-일 형식으로 되어있던 결과를 YMD 컬럼으로 사용하고, YMD 컬럼을 통해 해당 요일과
요일을 나타내는 시퀀스를 추가해줍니다.(TO_CHAR(YMD, 'DAY'), TO_CHAR(YMD, 'D'))
YAMND_NUM을 확인해보면 순서대로 일,월,화,수,목,금,토를 나타내므로
주말인 1과 7에 해당하면 휴일을 나타내는 HLDY 테이블에 각각 1이라는 구분값을 넣어줍니다.

CREATE TABLE TB_HLDY
AS
WITH BASE_DATE AS (
            SELECT DATE(GENERATE_SERIES('2022-01-01'::date, '2022-12-31'::date, '1 day')) AS YMD
            )
SELECT YMD
       ,TO_CHAR(YMD, 'DAY') AS YAMND
       ,TO_CHAR(YMD, 'D') AS YAMND_NUM
       ,CASE WHEN TO_CHAR(YMD, 'D') IN ('1', '7') THEN 1 END AS HLDY
FROM   BASE_DATE
;

조회가 잘되면 실제 테이블을 생성해줍니다.
첫 번째 목표인 주말은 정상적으로 잘 들어갔습니다. 다음으로는 공휴일 데이터를 가져오겠습니다.

2.공휴일 추가

공휴일 데이터는 네이버나, 다음에서 '공휴일'을 검색한뒤에 하나씩 추가해도 되지만... WORKING 테이블을 당장 한 해만 사용하고 버릴 것이 아니기에 다른 방법을 사용하겠습니다.

공공데이터포털에서 openAPI 형태로 제공하는 '한국천문연구원_특일정보' 데이터를 사용해보겠습니다.

해당 링크로 이동한 후

우측의 활용신청을 누른 뒤에(로그인 필요) 인증키를 발급받습니다.

파이썬을 사용하여 api를 호출하겠습니다.

import requests
import lxml
import pandas as pd
from bs4 import BeautifulSoup

year = 2022
df = pd.DataFrame(columns=['name', 'day'])
key = !!인증키를 넣어주세요!!

# 1월부터 12월까지
for month in range(1, 13): 
    # 한자리수의 월은 앞에 '0'을 붙여줘야함
    if month < 10:
        month = '0' + str(month)
    else:
        month = str(month)
    url = f"http://apis.data.go.kr/B090041/openapi/service/SpcdeInfoService/getRestDeInfo?solYear={year}&solMonth={month}&serviceKey={key}"
    res = requests.get(url)

    contents = BeautifulSoup(res.text, "lxml")
    # 응답받은 api의 item 태그 안에 공휴일 정보가 들어있음
    for item in contents.find_all('item'):
        name = item.datename.get_text()
        day = item.locdate.get_text()
        # 공휴일명과 날짜만 가져와서 dictionary 형태로 생성
        dic = {'name': name, '날짜': day}
        # dictionary 형태의 공휴일 정보를 데이터프레임에 넣어줌(dic형태는 ignore_index를 True로 설정해야 append 가능)
        df = df.append(dic, ignore_index=True)

파이썬의 pandas 데이터프레임을 이용하여 공휴일 정보를 저장해줍니다.
이때, api 호출 시 파라미터로 month를 넣어주어야하는데, integer는 반응이 없어 string 형식으로 넣어야합니다.
api호출결과 중 datename은 공휴일명, locdate는 날짜입니다.

데이터프레임을 출력해보면

        name       day
0       1월1일  20220101
1         설날  20220131
2         설날  20220201
3         설날  20220202
4        삼일절  20220301
5     대통령선거일  20220309
6       어린이날  20220505
7     부처님오신날  20220508
8   전국동시지방선거  20220601
9        현충일  20220606
10       광복절  20220815
11        추석  20220909
12        추석  20220910
13        추석  20220911
14     대체공휴일  20220912
15       개천절  20221003
16       한글날  20221009
17     대체공휴일  20221010
18     기독탄신일  20221225

공휴일과 날짜를 잘 얻어온 것을 볼 수 있습니다. 이제 이녀석과 위에서 만든 WORKING테이블을 비교하여 나머지 공휴일을 채워 넣도록 하겠습니다.

import psycopg2

# 데이터베이스 연결
with psycopg2.connect(connect) as conn:
  # 커서 생성
  curs = conn.cursor()
  # 공휴일 업데이트 쿼리
  sql = f"UPDATE TB_HLDY SET HLDY = 1 WHERE TO_CHAR(YMD, 'YYYYMMDD') IN {tuple(df['day'])}"

  # 업데이트 전에 반드시 쿼리 출력해서 확인
  print(sql)

  # 쿼리 실행
  curs.execute(sql)
  conn.commit()

파이썬의 psycopg2 모듈을 사용하여 데이터베이스에 연결한뒤 위에서 만들었던 데이터프레임의 정보를 가져와 업데이트 구문을 만들어줍니다. 업데이트 시에 조건 확인을 해야하는데 sql 쿼리는 IN 구문 뒤에 괄호가 들어가므로 파이썬의 tuple 자료형으로 변경 후 조건절에 추가해줍니다.

여기서 주의할 점은 쿼리문을 생성하고 바로 execute를 하는 것이 아니라 print로 출력한 뒤 이상이 없으면 실행시켜줍니다.

파이썬을 실행시키게 되면 위와 같이 공휴일 정보가 hldy 컬럼에 1이라는 값으로 들어가게 됩니다.

이렇게 만든 테이블을 이용하여 휴일을 체크할 수 있게 되었습니다.

SELECT HLDY FROM TB_HLDY WHERE YMD = NOW();

이런 식으로 조회하여 나온 결과가 1이면 문자나 메일을 발송하지 않고, null을 반환하면 발송하게끔 로직을 수정하면 됩니다.

SELECT YMD 
FROM   TB_HLDY 
WHERE  YMD >= (
                SELECT MAX(YMD) 
                FROM TB_HLDY
                WHERE YMD < NOW()::DATE
                AND HLDY IS NULL
                )
AND    YMD < NOW()::DATE
;

조금 더 응용해보면, 명절과 같이 긴 연휴를 보낼 때는 연휴간 쌓였던 업무 문자를 연휴가 지난 후에 한번에 보내는 방법도 생각해 볼 수 있습니다.(;;;;;)

3.마무리

대부분의 프로젝트에서는 이런식으로 WORKING 테이블을 만들어 관리하고 있다고 합니다. 문자나 이메일 발송뿐 아니라 다른 업무에서도 유용하게 사용할 듯 싶어 테이블을 만드는 과정을 정리했습니다. 공휴일이 얼마 되지 않기 때문에 손으로 하나씩 넣는 것도 방법이지만, API와 같이 프로그램으로 할 수 있는 부분을 통해 과정을 줄이는 연습을 해야겠습니다.