75강 SQL 초석 다지기 : PostgreSQL NULLIF function

PostgreSQL 기초강의, PostgreSQL 기초강좌

75강 SQL 초석 다지기 : PostgreSQL NULLIF function   

 

SQL 초석 다지기 75강 시작합니다.

 

이번 시간에는

▶ PostgreSQL NULLIF function

에 대해서 알아보는 시간입니다.

 

NULLIF는 COALESCE와 같은 동작을 하는 것 같지만 다릅니다.

 

이번 시간에는 PostgreSQL NULLIF  기능을 사용하여 null 값을 처리하는 방법을 보여 줍니다. NULLIF 함수를 사용하는 몇 가지 예를 보여드리겠습니다.

---

PostgreSQL NULLIF function syntax   

NULLIF 함수는 PostgreSQL에서 제공하는 가장 일반적인 조건식 중 하나입니다. 다음은 NULLIF 함수의 구문을 보여줍니다:

NULLIF(argument_1,argument_2); 

 

NULLIF 함수는 인수_1이 인수_2와 같으면 null 값을 반환하고, 그렇지 않으면 인수_1을 반환합니다.

 

다음 예를 참조하십시오:

SELECT
     NULLIF (1, 1); -- return NULL

SELECT
     NULLIF (1, 0); -- return 1

SELECT
     NULLIF ('A', 'B'); -- return A  

---

PostgreSQL NULLIF function example   

NULLIF 함수를 사용하는 예를 살펴보겠습니다.

먼저, 우리는 다음과 같이 posts라는 이름의 테이블을 만듭니다:

CREATE TABLE posts (
     id serial primary key,
     title VARCHAR (255) NOT NULL,
     excerpt VARCHAR (150),
     body TEXT,
     created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
     updated_at TIMESTAMP
); 

 

둘째, posts 테이블에 샘플 데이터를 삽입합니다.

INSERT INTO posts (title, excerpt, body)
VALUES
      ('test post 1','test post excerpt 1','test post body 1'),
      ('test post 2','','test post body 2'),
      ('test post 3', null ,'test post body 3'); 

 

셋째, 우리는 각 posts의 title과 excerpt을 보여주는 posts 검색 페이지를 표시하는 것입니다. excerpt 가 제공되지 않는 경우, 우리는 body의 처음 40자를 사용합니다. 다음 쿼리를 사용하여 posts 테이블의 모든 행을 가져올 수 있습니다.

SELECT
     ID,
     title,
     excerpt
FROM
     posts; 

결과

excerpt  열에 null 값이 표시됩니다. 이 null 값을 대체하기 위해 다음과 같이 COALESCE 함수를 사용할 수 있습니다:

SELECT
     id,
     title,
     COALESCE (excerpt, LEFT(body, 40))
FROM
     posts; 

결과

안타깝게도 excerpt 열에는 null 값과 "(공백)이 있습니다. 이것이 NULLIF 함수를 사용해야 하는 이유입니다:

SELECT
     id,
     title,
     COALESCE (
          NULLIF (excerpt, ''),
          LEFT (body, 40)
     )
FROM
     posts;  

결과

식을 좀 더 자세히 살펴봅시다:

• 첫째, NULLIF 함수는 추출이 비어 있으면 null 값을 반환하고, 그렇지 않으면 추출을 반환합니다. NULLIF 함수의 결과는 COALESCE 함수에 의해 사용됩니다. 2번째 행은 NULLIF(공백, 공백)이어서 NULL반환. 3번째 행은 NULLIF(NULL, 공백)이어서 NULL 반환
• 둘째, COALESCE 함수는 NULLIF 함수가 제공하는 첫 번째 인수가 NULL이면 body의 처음 40자를 반환하고, 그렇지 않으면 excerpt 가 NULL이 아닐 경우 excerpt를 반환합니다.

이해하셨나요? 좀 어렵죠?

복잡하다 하시면, 앞에서 배운 CASE문을 사용하셔서 SQL을 작성할 수도 있습니다.

SELECT
     id, 
     title,
     CASE  WHEN excerpt = ''    THEN LEFT (body, 40)
           WHEN excerpt IS NULL THEN LEFT (body, 40)
           ELSE excerpt
     END   excerpt    
FROM
     posts;   

결과

---

Use NULLIF to prevent division-by-zero error   

NULLIF 함수를 사용하는 또 다른 좋은 예는 0으로 나누기 오류를 방지하는 것입니다. 다음 예를 살펴보겠습니다.

 

먼저 members라는 새 테이블을 만듭니다:

CREATE TABLE members (
     ID serial PRIMARY KEY,
     first_name VARCHAR (50) NOT NULL,
     last_name VARCHAR (50) NOT NULL,
     gender SMALLINT NOT NULL -- 1: male, 2 female
);   

 

 

둘째, 테스트를 위해 몇 개의 행을 삽입합니다:

INSERT INTO members (
     first_name,
     last_name,
     gender
)
VALUES
     ('John', 'Doe', 1),
     ('David', 'Dave', 1),
     ('Bush', 'Lily', 2); 

 

 

셋째, 남녀 구성원의 비율을 계산하고자 할 때 다음과 같은 질문을 사용한다:

SELECT
     (SUM (
          CASE
          WHEN gender = 1 THEN
               1
          ELSE
               0
          END
     ) / SUM (
          CASE
          WHEN gender = 2 THEN
               1
          ELSE
               0
          END
     ) ) * 100 AS "Male/Female ratio"
FROM
     members;   

총 남성 멤버 수를 계산하기 위해 SUM 함수와 CASE 식을 사용합니다. 성별이 1이면 CASE 식은 1을 반환하고, 그렇지 않으면 0을 반환합니다. SUM 함수는 남성 멤버의 합계를 계산하는 데 사용됩니다. 전체 여성 회원 수를 계산하는 데도 같은 논리가 적용된다.

그런 다음 남성 회원의 총합을 여성 회원의 총합으로 나누어 비율을 반환한다. 이 경우 200%를 반환합니다.

결과

 

네 번째, 여성 멤버를 제거합니다:

DELETE
FROM
     members
WHERE
     gender = 2;   

 

 

그리고 쿼리를 실행하여 남성/여성 비율을 다시 계산하면 다음과 같은 오류 메시지가 표시됩니다:

오류

그 이유는 여성의 수가 0이기 때문입니다. 

이를 해결하기 위해 다음과 같이 NULLIF 함수를 사용합니다:

SELECT
     (
          SUM (
               CASE
               WHEN gender = 1 THEN
                    1
               ELSE
                    0
               END
          ) / NULLIF (
               SUM (
                    CASE
                    WHEN gender = 2 THEN
                         1
                    ELSE
                         0
                    END
               ),
               0
          )
     ) * 100 AS "Male/Female ratio"
FROM
     members;   

NULLIF 함수는 여성 멤버 수가 0인지 확인하고 null을 반환합니다. 총 남성 멤버 수를 null 값으로 나눈 값은 null 값을 반환하는데, 이 값은 정확합니다.

결과

---

이번 강의에서는 NULLIF 함수를 적용하여 NULL 값을 대체하여 데이터를 표시하고 0에 의한 나누기를 방지하는 방법을 보여드렸습니다.

 

감사합니다.

---

본 강의는 PostgreSQL Tutorial을 참조합니다.  https://www.postgresqltutorial.com/

Do it! SQL을 찾아 주셔서 감사합니다. 공감 ♥ , 댓글이 큰 힘이 됩니다.