SQL로 액셀의 NETWORKDAYS 기능 구현하기

안녕하세요. 데이터베이스 사랑넷에 두 날짜사이에서 주말을 제외한 일수를 구하는 방법에 대한 질문이 있어서 이것 저것 찾아보고 시험해봤습니다.

 

기능에 대해서 잘 모르다보니 working day sql postgresql 키워드로 구글에 검색해보니 비슷한 기능을 구현한 예제가 많이 있었습니다. 먼저 질문 내용입니다.

아래 쿼리는 mysql에서 두 날짜사이 주말일수를 제외한 일수를 구하는 sql문인데
PostgreSQl에서 적용되게 변환하고 싶습니다.
부탁드립니다.

select ABS(DATEDIFF('2020-04-23', '2020-04-26')) + 1 - ABS(DATEDIFF(ADDDATE('2020-04-23', INTERVAL 1 - DAYOFWEEK('2020-04-23') DAY),
       ADDDATE('2020-04-26', INTERVAL 1 - DAYOFWEEK('2020-04-26') DAY))) / 7 * 2
       - (DAYOFWEEK(IF('2020-04-26' < '2020-04-23', '2020-04-26', '2020-04-23')) = 1)
       - (DAYOFWEEK(IF('2020-04-26' > '2020-04-23', '2020-04-26', '2020-04-23')) = 7);

찬찬히 살펴보면 주말일수를 먼저 빼고 그외의 경우에 대한 조건이 포함되어 있습니다. 이것 저것 경우의 수를 고려한 것인데 mysql에서 날짜를 바꾸어서 실행해보아도 맞는 결과가 나오는 것 같습니다.

 

그래서 최대한 위의 것을 참고해서 PostgreSQL 문법에 맞게 만들어 보았습니다.

SELECT ABS(DATE '2020-04-04' - DATE '2020-04-26') - 
       ABS((DATE '2020-04-04' - EXTRACT(DOW FROM DATE '2020-04-04')::INT) -
           (DATE '2020-04-26' - EXTRACT(DOW FROM DATE '2020-04-26')::INT))/7*2 -
       CASE WHEN EXTRACT(DOW FROM DATE '2020-04-04') = 0 THEN 1 ELSE 0 END +
       CASE WHEN EXTRACT(DOW FROM DATE '2020-04-26') = 0 THEN 1 ELSE 0 END

질문하신 분의 계산식과 유사하게 만들었지만, 여기서는 빠른 날짜에서 이후 날짜를 빼는 식이라 날짜 순서가 바뀌면 쿼리도 바뀌어야 합니다. 이런식으로도 불가능하진 않지만 상당히 복잡해질 수 있습니다.

 

그래서 좀더 나은 방법이 없을까 해서 구글을 찾아보니 액셀의 NETWORKDAYS라는 함수가 이것과 동일한 기능인 것을 알게 됐습니다. 그럼 더 나아가 PostgreSQL에 NETWORKDAYS를 구현한 사례도 있겠거니해서 찾아보니 아래와 같은 예제를 찾았습니다.

select start_date, end_date, 
       sum(case when extract (dow from dt) in (1,2,3,4,5) then 1 else 0 end) as thediff
from (
       select start_date, end_date, 
              generate_series(start_date, end_date, '1 day'::interval) as dt
       from   tbl
     ) t
group by start_date, end_date;

PostgreSQL에서 요일을 확인하려면 extract (dow from date) 와 같은식으로 사용하는데 0(일요일)에서 6(토요일)까지 규정되어 있습니다. 이것 저것 따질 것도 없이 날짜를 쭉 나열하고 요일을 판별하는 식으로 아주 직관적입니다.

다만 날짜 구간이 길면 성능이 어떨지 모르겠군요.

 

우선 여기까지 질문에 대해서는 해결이 되었는데 인포믹스에서 사용하는 방법도 정리해두면 좋을 것 같아 위의 방법을 따라 해보았습니다. 인포믹스도 0은 일요일을 나타내고 1은 월요일을 나타내는 식입니다.

select sum(case when weekday(dt) in (1,2,3,4,5) then 1 else 0 end) as thediff
from (
       select '2020-04-04'::date + level units day as  dt connect by level < abs('2020-04-04'::date - '2020-04-26'::date)
     ) t


         thediff

              15

1 row(s) retrieved.

 

참고사이트

https://www.postgresql.org/docs/8.1/functions-datetime.html

https://dba.stackexchange.com/questions/207701/count-business-days-between-2-dates-in-postgresql

https://stackoverflow.com/questions/7388420/get-datediff-excluding-weekends-using-sql-server

https://stackoverflow.com/questions/1964544/timestamp-difference-in-hours-for-postgresql