문자를 포함한 컬럼데이타를 배열화해서 Max, Min값 구하기

ex) AA 컬럼 값 : 4.50B, 5.00B, 5.50B, 6.00B

주어진 문제를 해결하기 위해 알파벳 문자를 제거하고 값들을 쉼표(,)로 분리한 다음, 값이 2개 이상일 경우 가장 작은 값,  큰 값을 찾는 SQL 쿼리를 작성할 수 있습니다.

다음은 Oracle SQL에서 이를 구현하는 방법입니다.

---

1. 문제 접근 방식

• 알파벳 제거: REGEXP_REPLACE를 사용하여 숫자와 점(.)만 남깁니다.
• 쉼표로 분리: REGEXP_SUBSTR를 사용하여 문자열을 나누어 각각의 값을 추출합니다.
• 최소값 계산: 숫자 값들 중 가장 작은 값을 계산합니다.
• 2개 이상의 값 확인: 쉼표의 개수를 기준으로 값이 2개 이상인지 확인합니다.

---

2. SQL 쿼리

WITH split_values AS (
    SELECT 
        id,
        REGEXP_SUBSTR(REGEXP_REPLACE(AA, '[^0-9,.]', ''), '[^,]+', 1, LEVEL) AS num_value
    FROM 
        your_table
    CONNECT BY 
        LEVEL <= REGEXP_COUNT(REGEXP_REPLACE(AA, '[^0-9,.]', ''), ',') + 1
        AND PRIOR id = id
        AND PRIOR SYS_GUID() IS NOT NULL
), 
filtered AS (
    SELECT 
        id,
        MIN(TO_NUMBER(num_value)) AS min_value,
        COUNT(*) AS value_count
    FROM 
        split_values
    WHERE 
        num_value IS NOT NULL
    GROUP BY 
        id
)
SELECT 
    id, 
    CASE 
        WHEN value_count >= 2 THEN min_value
        ELSE NULL
    END AS smallest_value
FROM 
    filtered;

---

3. 쿼리 설명

1. REGEXP_REPLACE(AA, '[^0-9,.]', '')
   • AA 컬럼에서 숫자, 쉼표(,), 점(.)을 제외한 모든 문자를 제거합니다. 예: '4.50B, 5.00B' → '4.50,5.00'
2. REGEXP_SUBSTR(..., '[^,]+', 1, LEVEL)
   • 쉼표(,)로 값을 분리합니다. 예: '4.50,5.00' → 개별 값 '4.50', '5.00'
3. CONNECT BY
   • 쉼표 개수 + 1 만큼 각 행을 나눕니다.
4. MIN(TO_NUMBER(num_value))
   • 나눈 값들 중 숫자(TO_NUMBER)로 변환한 후 최소값을 구합니다.
5. COUNT(*)
   • 쉼표로 분리된 값들의 개수를 셉니다.
6. CASE WHEN value_count >= 2 THEN min_value ELSE NULL END
   • 분리된 값이 2개 이상인 경우 최소값을 반환하고, 그렇지 않으면 NULL 반환합니다.

---

4. 예제 데이터와 결과

데이터:

ID AA

1	4.50B, 5.00B, 5.50B, 6.00B
2	7.00B
3	3.20B, 2.10B

결과:

ID SMALLEST_VALUE

1	4.50
2	NULL
3	2.10

---

cf) 1.한줄 쿼리로 변경

SELECT 
    CASE 
        WHEN REGEXP_COUNT(REGEXP_REPLACE(AA, '[^0-9,.]', ''), ',') >= 1 THEN 
            MIN(TO_NUMBER(REGEXP_SUBSTR(REGEXP_REPLACE(AA, '[^0-9,.]', ''), '[^,]+', 1, LEVEL)))
        ELSE 
            NULL 
    END AS smallest_value
FROM 
    your_table
CONNECT BY 
    LEVEL <= REGEXP_COUNT(REGEXP_REPLACE(AA, '[^0-9,.]', ''), ',') + 1
    AND PRIOR SYS_GUID() IS NOT NULL
    AND PRIOR ROWID = ROWID;

 

cf) 2.숫자값과 문자값 같이 출력

SELECT 
    MIN(TO_NUMBER(REGEXP_SUBSTR(REGEXP_REPLACE(AA, '[^0-9,.]', ''), '[^,]+', 1, LEVEL))) AS smallest_numeric_value,
    MIN(REGEXP_SUBSTR(AA, '[^,]+', 1, LEVEL)) KEEP (
        DENSE_RANK FIRST ORDER BY TO_NUMBER(REGEXP_SUBSTR(REGEXP_REPLACE(AA, '[^0-9,.]', ''), '[^,]+', 1, LEVEL))
    ) AS smallest_original_value
FROM 
    your_table
CONNECT BY 
    LEVEL <= REGEXP_COUNT(AA, ',') + 1
    AND PRIOR SYS_GUID() IS NOT NULL
    AND PRIOR ROWID = ROWID;