SQL로 조합 생성하기

문제: 10가지 토핑 재료들 중에서 3가지를 선택하여 해당 조합의 총 가격 구하기

 

조합을 구하는 방법은 여러 가지 방법이 있다. 그 중 4가지 방법을 알아보자.

---

 

1. INNER JOIN 으로 조합 만들기

 

SELECT *
FROM pizza_toppings AS p1
JOIN pizza_toppings AS p2
  ON p1.topping_name < p2.topping_name
JOIN pizza_toppings AS p3
  ON p2.topping_name < p3.topping_name

 

테이블을 세 번 JOIN 하여 3개의 조합을 만들어준다. 이때 < 연산자를 사용하여 왼쪽 테이블의 topping_name이 더 작은 조건을 만족하는 경우에만 이어준다. 

 

5 Combination 3 의 조합 개수가 생긴다. (=120)

-- 최종 코드
SELECT 
  CONCAT(p1.topping_name, ',', p2.topping_name, ',', p3.topping_name) AS pizza_combination,
  (p1.ingredient_cost + p2.ingredient_cost + p3.ingredient_cost) AS total_cost
FROM pizza_toppings AS p1
JOIN pizza_toppings AS p2
  ON p1.topping_name < p2.topping_name
JOIN pizza_toppings AS p3
  ON p2.topping_name < p3.topping_name
ORDER BY total_cost DESC, pizza_combination

---

 

2. CROSS JOIN 으로 조합 구하기

SELECT *
FROM pizza_toppings AS p1
CROSS JOIN pizza_toppings AS p2
CROSS JOIN pizza_toppings AS p3
WHERE p1.topping_name < p2.topping_name
  AND p2.topping_name < p3.topping_name
ORDER BY total_cost DESC, pizza_combination

 

위와 동일하게 조건을 걸어주면 CROSS JOIN으로도 구현이 가능하다.

---

3. 재귀 쿼리로 조합 구하기

 

WITH RECURSIVE all_toppings AS (
  -- Anchor Query
  SELECT
    topping_name::VARCHAR,
    ingredient_cost::DECIMAL AS total_cost,
    1 AS topping_numbers
  FROM pizza_toppings
)
SELECT * FROM all_toppings

 

먼저 anchor 쿼리를 작성해준다. (postgre에서는 에러가 발생할 수 있으니 데이터 타입을 명시해주자)

topping의 수를 topping_numbers 컬럼으로 생성해줄 것이다.

 

WITH RECURSIVE all_toppings AS (
  -- Anchor Query
  SELECT
    topping_name::VARCHAR,
    ingredient_cost::DECIMAL AS total_cost,
    1 AS topping_numbers
  FROM pizza_toppings

  UNION ALL
  
  SELECT 
    CONCAT(addon.topping_name, ',', anchor.topping_name) AS topping_name,
    addon.ingredient_cost + anchor.total_cost AS total_cost,
    topping_numbers + 1
  FROM
    pizza_toppings AS addon,
    all_toppings AS anchor
  WHERE anchor.topping_name < addon.topping_name
)
SELECT * FROM all_toppings
WHERE topping_numbers = 3

 

다음으로 재귀 쿼리를 완성해줍니다. 쿼리 테이블이 기존 테이블을 참조하면서 반복적으로 데이터를 생성한다. WHERE 조건이 성립하지 않을 때까지 테이블을 생성한다. 10가지 토핑이 존재하므로 위의 조건에서 topping_numbers가 10이 될 때까지 데이터를 생성힌다.

 

다음으로 3가지 조합을 구하는 것이 목표임으로 topping_numbers가 3인 행들만 필터링한다.

 

여기서 해당 쿼리는 topping_name 컬럼의 요소들이 오름차순(알파벳 기준)으로 정렬되어있지 않아서 추가적인 작업이 필요하다.

 

REGEXP_SPLIT_TO_TABLE 함수는 정규식(Regex)을 사용하여 문자열을 여러 개의 행으로 분리하는 함수이다.

아래 테이블과 쿼리를 예시로 설명해보면,

all_topping 테이블 일부

SELECT REGEXP_SPLIT_TO_TABLE(topping_name, ',') AS single_topping
FROM all_topping

 

 위의 테이블에서 topping_name의 각 행에서의 ',' 로 구분되어 있는 문자열을 하나씩 분리해서 하나의 행으로 만든다.

1. Spinach, Sausage, Pepperoni 를 예를 들면, 다른 컬럼의 값은 동일하게 가져가면서

1. Spinach

2. Sausage

3. Pepperoni

이렇게 세 개의 행으로 변환해준다.

 

STRING_AGG 함수는 여러 개의 행 데이터를 하나의 문자열로 결합하는 함수이다.

앞서 변환한 데이터를 거꾸로 이어주는 작업이라고 볼 수 있다.

1. Spinach

2. Sausage

3. Pepperoni

과 같이 3개의 행을 Spinach, Sausage, Pepperoni 처럼 하나의 행으로 만들어준다.

이때 정렬을 해준 다음 연결할 수 있는 것이 포인트다.

구분자와 함께 ORDER BY 컬럼명 을 통해 정렬을 진행해준다.

SELECT
    STRING_AGG(single_topping, ',' ORDER BY single_topping) AS pizza

 

-- 최종 쿼리
WITH RECURSIVE all_toppings AS (
  -- Anchor Query
  SELECT
    topping_name::VARCHAR,
    ingredient_cost::DECIMAL AS total_cost,
    1 AS topping_numbers
  FROM pizza_toppings

  UNION ALL
  
  SELECT 
    CONCAT(addon.topping_name, ',', anchor.topping_name) AS topping_name,
    addon.ingredient_cost + anchor.total_cost AS total_cost,
    topping_numbers + 1
  FROM
    pizza_toppings AS addon,
    all_toppings AS anchor
  WHERE anchor.topping_name < addon.topping_name
)
SELECT 
  STRING_AGG(single_topping, ',' ORDER BY single_topping) AS pizza
  , total_cost
FROM 
  all_toppings 
  , REGEXP_SPLIT_TO_TABLE(topping_name, ',') AS single_topping
  WHERE topping_numbers = 3
GROUP BY topping_name, total_cost
ORDER BY total_cost DESC, pizza;

---

4. ARRAY 함수로 조합 구하기

 

재귀 쿼리까지는 앞과 동일하다.

REGEXP_SPLIT_TO_TABLE 함수를 ARRAY 함수로 구현하는 것이 차이점이다.

WITH RECURSIVE all_toppings AS (
SELECT
  topping_name::VARCHAR,
  ingredient_cost::DECIMAL AS total_cost,
  1 AS topping_numbers
FROM pizza_toppings

UNION ALL

SELECT
  CONCAT(addon.topping_name, ',', anchor.topping_name) AS topping_name,
  addon.ingredient_cost + anchor.total_cost AS total_cost,
  topping_numbers + 1
FROM 
  pizza_toppings AS addon,
  all_toppings AS anchor
WHERE anchor.topping_name < addon.topping_name
), 
arrange AS (
SELECT
  topping_name,
  UNNEST(STRING_TO_ARRAY(topping_name, ',')) AS single_topping,
  total_cost
FROM all_toppings
WHERE topping_numbers = 3
)
select * from arrange

 

먼저 STRING_TO_ARRAY(topping_name, ',') 함수는 문자열을 배열로 변환한다.

예를 들어, 다음과 같은 쿼리에서

SELECT STRING_TO_ARRAY(topping_name, ',');

 

 

쉼표(,)를 기준으로 다음과 같이 배열을 반환한다.

 

그런 다음, 생성된 배열을 UNNEST 함수를 통해 개별 행으로 변환해준다.

 

이렇게 변환된 행은 3번째 방법과 동일하게 STRING_AGG 함수를 통해 정렬한 뒤 하나의 행으로 다시 한번 변환해준다.

---

이렇게 조합을 구하는 4가지 방법에 대해 알아보았다. PostgreSQL 기준으로 작성하였고, MySQL의 경우 STRING_TO_ARRAY 함수가 지원되질 않는다. 대신 JSON_ARRAY 함수를 활용하면된다.

-- JSON_ARRAY를 활용한 대체
SELECT JSON_ARRAYAGG(value)
FROM (SELECT 'a' AS value UNION ALL SELECT 'b' UNION ALL SELECT 'c') AS t;

 

또한, UNNEST 함수 역시 지원되지않으므로

JSON_TABLE 함수

를 사용하여 대체한다.

-- JSON_ARRAY를 활용한 대체
SELECT jt.value
FROM (
    SELECT JSON_ARRAYAGG(value) AS json_data
    FROM (SELECT 'a' AS value UNION ALL SELECT 'b' UNION ALL SELECT 'c') AS t
) sub,
JSON_TABLE(sub.json_data, '$[*]' COLUMNS(value VARCHAR(255) PATH '$')) AS jt;

 

 

STRING_AGG 함수는 GROUP_CONCAT 함수를 사용하면 된다.

• PostgreSQL : STRING_AGG(컬럼명, ',')
• MySQL : GROUP_CONCAT(컬럼명 SEPARATOR ',')

SELECT GROUP_CONCAT(column_name SEPARATOR ', ') AS concatenated_values
FROM my_table;