leetcode 1907. Count Salary Categories (열 -> 행으로 변환)

 

https://leetcode.com/problems/count-salary-categories/description/?envType=study-plan-v2&envId=top-sql-50

Count Salary Categories - LeetCode

 

문제

Table. accounts

+-------------+------+
| Column Name | Type |
+-------------+------+
| account_id  | int  |
| income      | int  |
+-------------+------+

 account 마다 income 이 기입 되어 있는 테이블에서, income의 범위 별로 카테고리를 나누어주고 각 카테고리마다 속한 account 숫자를 집계해주는 문제이다. 간단하지만 주의해주어야할 부분은 특정 범주에 속하는 계좌가 하나도 없는 경우가 있을 수 있다. 이 때 단순 group by로 집계해주면 해당 범주가 아예 집계되지 않는데, 이를 0으로 나타내주어야하는것이 포인트이다.

• "Low Salary": All the salaries strictly less than $20000.
• "Average Salary": All the salaries in the inclusive range [$20000, $50000].
• "High Salary": All the salaries strictly greater than $50000. 

output 형태

+----------------+----------------+
| category       | accounts_count |
+----------------+----------------+
| Low Salary     | 1              |
| Average Salary | 0              |
| High Salary    | 3              |
+----------------+----------------+

위 예시에서 Average Salary 범주에 있는 account가 하나도 없어서 0으로 표기되어 있다.

 

접근 방법

1. 각 범주에 해당하는 계좌 수를 집계하여 열로 표현 -> 0개로 집계되는 것 까지 포함하기 위해

SELECT SUM(IF(INCOME<20000,1,0)) AS LOW, 
        SUM(IF(INCOME>=20000 AND INCOME <=50000,1,0)) AVERAGE,
        SUM(IF(INCOME>50000,1,0)) AS HIGH
FROM ACCOUNTS

2.  Cross Join 활용해서 IDX 추가해주기

select *
FROM(SELECT SUM(IF(INCOME<20000,1,0)) AS LOW, 
        SUM(IF(INCOME>=20000 AND INCOME <=50000,1,0)) AVERAGE,
        SUM(IF(INCOME>50000,1,0)) AS HIGH
FROM ACCOUNTS) AS S
CROSS JOIN (
                SELECT 1 AS IDX
    UNION ALL   SELECT 2 AS IDX
    UNION ALL   SELECT 3 AS IDX
) AS P

3. CASE WHEN 사용해서 행으로 변경

1) Category 컬럼 생성하여, IDX 별로 필요한 범주 명 넣어주기

2) accounts_count 컬럼 생성하여, IDX 별로 필요한 컬럼의 값 넣어주기 

SELECT  CASE 
            WHEN IDX = 1 THEN 'Low Salary'
            WHEN IDX = 2 THEN 'Average Salary'
            WHEN IDX = 3 THEN 'High Salary'
            END AS category,
        CASE 
            WHEN IDX = 1 THEN s.LOW
            WHEN IDX = 2 THEN s.AVERAGE
            WHEN IDX = 3 THEN s.HIGH
            END AS accounts_count
FROM(SELECT SUM(IF(INCOME<20000,1,0)) AS LOW, 
        SUM(IF(INCOME>=20000 AND INCOME <=50000,1,0)) AVERAGE,
        SUM(IF(INCOME>50000,1,0)) AS HIGH
FROM ACCOUNTS) AS S
CROSS JOIN (
                SELECT 1 AS IDX
    UNION ALL   SELECT 2 AS IDX
    UNION ALL   SELECT 3 AS IDX
) AS P

4. 결과

더 좋은 코드

결과는 뽑았고, 이 과정에서 열을 행으로 변환해보는 코드 작업을 해볼 수 있었다. 하지만, 지금은 범주가 3개라 cross join 했을 때 3줄 밖에 중복이 없지만 값이 많아질수록 중복이 늘어나게 된다. 이렇게 하지 않고도 간단하게 0인 값을 집계할 수 있다.

 

UNION + WHERE 사용하기

 

SELECT 'Low Salary' as category, COUNT(*) as accounts_count
FROM Accounts
WHERE income<20000
UNION
SELECT 'Average Salary' as category, COUNT(*)
FROM Accounts
WHERE income>=20000 AND income<=50000
UNION
SELECT 'High Salary' as category, COUNT(*)
FROM Accounts
WHERE income>50000

 

중간에 Average Salary 범주에 속하는 값이 없을 때 count(*)에 0으로 집계되면서 UNION 해주었을 때 원하는 결과로 추가될 수 있다.