[postgreSQL] with 절과 count filter를 이용하여 특정 칼럼에 대한 비율 구하기

기록용..

WITH

▷ 구조

WITH alias_name AS (SELECT * FROM table_name WHERE..) SELECT ....FROM alias_name WHERE ..

▷ 사용하는 경우 

• 따로 추출하여 공통적으로 사용할 수 있다.
• FROM 에 이미 가공된 데이터를 넣고 싶은 경우 좀 더 가독성 있게 사용할 수 있다.

COUNT FILTER

▷ 구조

COUNT(1) FILTER (WHERE ...) AS ..

▷ 사용하는 경우 

• 하나의 SELECT 문에서 다른 조건을 갖는 COUNT를 여러개 구하고싶을  때

COALESCE

▷ 구조

COALESCE(대상 , b)

▷ 사용하는 경우 

• 대상이 null이 아닐경우 대상 그대로 출력, null 일 경우 b 출력
• count 할 때 null일 경우 0으로 치환하고자 사용해보았다.

 

예시

(강의 - 질문 - 답변 )

Q. 강의 별 질문 답변이 이루어진 비율을 구해보자. 

WITH "answer_count" AS (
        SELECT
            "q"."course_id",
            COUNT(1) AS "count",
            COUNT(1) FILTER (WHERE "p."answer_cmt" > 0) AS "questions_has_answer"
        FROM "questions" AS "q"
        WHERE "q"."deleted_at" IS NULL
        GROUP BY "q"."course_id"
    )
  SELECT
      "qc"."course_id", 
      ("qc"."questions_has_answer" * 100.0 / "qc"."count") AS "response_rate"
  FROM "question_count" AS "qc"

답변이 달린 질문/전체 질문 을 구해야하므로 구한 count와 questions_has_answer 의 비율을 구해야하므로 with절 내에서 count한 부분을 최종적으로 추출해야하는 select 문에 사용해주었다.

이런 경우는 questions 테이블에 질문의 답변 수 (awswer_cmt ) 가 있을 경우인데 없는 경우에는 with절 내에서 조건에 따라 각각 추출해서 가져올 수 있다.

Q. 강의 별 질문 답변이 이루어진 비율을 구해보자. 
(질문 테이블에 답변의 수를 저장하고 있지 않을 때)

 

질문에 따른 답변의 수를 저장하고 있지 않을 때에는 다음과 같이 해줄 수 있다.

WITH 
	"questions_has_answer" as (
    	SELECT 
        	"q"."course_id",
            count(distinct "q"."id") as "count"
     	FROM "questions" AS "q"
        INNER JOIN "answers" as "a" ON "q"."id" = "a"."question_id"
        WHERE "q"."deleted_at" IS NULL
        GROUP BY "q"."course_id"
    )
	"answer_count" AS (
        SELECT
            "q"."course_id",
            COUNT("q"."id") AS "count",
        FROM "questions" AS "q"
        WHERE "q"."deleted_at" IS NULL
        GROUP BY "q"."course_id"
    )
  SELECT
      "qc"."course_id", 
      COALESCE(("qc"."questions_has_answer" * 100.0 / "ac"."count"), 0) AS "response_rate"
    FROM "answer_count" as "ac"
    LEFT JOIN "questions_has_answer" as "pc"
        on "pc"."course_id" = "ac"."course_id"

위 쿼리는 Question 테이블에 answer_cmt가 없는 경우 inner join을 통해 답변 있는 경우 답변 없는 경우를 판별한다. 

 

먼저 with에서 전체 질문 수 , 답변이 달린 질문 수 를 각각 구해온다.

이를 조합해서 비율을 계산하면 된다. 전체 질문과  답변이 있는 질문의 비율을 구하는데 각각 구해서 left join하므로 답변이 없는 질문일 경우 null이다.

이 때 한쪽이 null일 경우 0으로 count가 되지 않고 무시되기 때문에 비율이 0인 경우는 구할 수가 없어진다.

이런 경우 COALESCE를 사용해서 null일 경우 0으로 모두 치환할 수 있다.

 

결론: 비율을 구할 때 with, count filter, coalesce 등을 사용해서 편하게 구해보자..