[SQL] SQL 피벗과 UNION/IN 연산자

목차

1. 피벗
2. JSON(JavaScript Object Notation)
3. UNION / UNION ALL / NOT IN / IN

---

피벗

SQL 피벗은 행을 열로 변환하여 데이터를 효과적으로 바꾸거나 '피벗'하는 데 사용되는 기술입니다. 보고 또는 분석과 같이 데이터를 보다 구조화되거나 요약된 형식으로 표시해야 할 때 유용합니다. MySQL에는 기본 제공 PIVOT 키워드가 없지만 집계 함수(예: SUM 또는 COUNT)와 CASE 문 또는 IF 함수의 조합을 사용하여 피벗 작업을 수행할 수 있습니다.

다음은 SQL 피벗을 사용하여 개념을 설명하는 예입니다. 다음과 같은 열이 있는 판매 테이블이 있다고 가정해 보겠습니다: 'product', 'region', 'sales_amount' 열이 있다고 가정해 보겠습니다. 지역을 열로 사용하여 지역별 제품당 총 매출을 표 형식으로 표시하려고 합니다.

1. 판매 테이블 데이터 샘플

제픔	지역	판매_금액
A	서울	100
A	인천	200
B	서울	150
B	인천	250

2. 원하는 출력 형식

제품	서울	인천
A	100	200
B	150	250

 

MySQL에서 SQL 피벗을 사용하여 이 작업을 수행하려면 다음 쿼리를 사용할 수 있습니다:

SELECT
    product,
    SUM(CASE WHEN region = '서울' THEN sales_amount ELSE 0 END) AS 서울,
    SUM(CASE WHEN region = '인천' THEN sales_amount ELSE 0 END) AS 인천
FROM
    판매_금액
GROUP BY
    제품;

이 쿼리에서는 SUM() 함수 내에서 CASE 문을 사용하여 'region' 열의 값을 확인합니다. 지역이 원하는 열 이름(예: '서울' 또는 '인천')과 일치하면 '판매_금액'을 합계에 포함시키고, 그렇지 않으면 0을 사용합니다. 

결과적으로 각 제품의 총 매출이 지역별로 세분화되고 지역이 열로 표시됩니다.

이 예에서는 'region' 열의 고유 값을 알고 있다고 가정합니다. 피벗을 위한 동적 값 집합이 있는 경우 동적 SQL 쿼리를 사용하여 피벗 테이블을 작성해야 할 수 있습니다.

---

JSON(JavaScript Object Notation)

JSON(JavaScript Object Notation)은 사람이 쉽게 읽고 쓸 수 있고 기계가 쉽게 구문 분석하고 생성할 수 있는 경량 데이터 교환 형식입니다. JSON은 웹 애플리케이션에서 클라이언트와 서버 간에 데이터를 전송하거나 데이터베이스 또는 구성 파일에 구조화된 데이터를 저장하는 데 자주 사용됩니다.

JSON 데이터는 키-값 쌍으로 구성되며, 키는 문자열이고 값은 문자열, 숫자, 부울(참 또는 거짓), 널, 배열 또는 기타 JSON 객체일 수 있습니다. JSON 객체는 중괄호 {}를 사용하여 표시하고 배열은 대괄호 []를 사용하여 표시합니다. 다음은 JSON 객체의 예입니다.

{
  "name": "Alice",
  "age": 30,
  "isStudent": false,
  "courses": [
    {
      "name": "수학",
      "grade": "A"
    },
    {
      "name": "History",
      "grade": "B"
    }
  ]
}

MySQL과 같은 데이터베이스의 경우 JSON 데이터 유형을 가진 열에 JSON 데이터를 저장할 수 있습니다. 이를 통해 복잡하고 구조화된 데이터를 단일 열에 저장하고 MySQL에서 제공하는 기본 제공 JSON 함수를 사용하여 쿼리하거나 조작할 수 있습니다.

1. json_extract(): JSON 경로를 사용하여 JSON 객체 또는 배열에서 값을 추출합니다.

구문: JSON_EXTRACT(json_data, json_path)

2. JSON_INSERT(): JSON 객체 또는 배열에 새 값을 삽입합니다.

구문: JSON_INSERT(json_data, json_path, value)

3. json_replace(): JSON 객체 또는 배열의 기존 값을 바꿉니다.

구문: JSON_REPLACE(json_data, json_path, value)

4. JSON_REMOVE(): JSON 객체 또는 배열에서 값을 제거합니다.

구문: JSON_REMOVE(json_data, json_path)

5. JSON_OBJECT(): 키-값 쌍의 목록에서 JSON 객체를 생성합니다.

구문: JSON_OBJECT(key1, value1, key2, value2, ...)

6. JSON_ARRAY(): 값 목록에서 JSON 배열을 생성합니다.

구문: JSON_ARRAY(value1, value2, ...)

---

UNION / UNION ALL / NOT IN / IN

1. UNION

UNION 연산자는 두 개 이상의 SELECT 쿼리의 결과 집합을 단일 결과 집합으로 결합하는 데 사용됩니다. 이 연산자는 결합된 결과 집합에서 중복 행을 제거합니다. UNION 연산이 작동하려면 각 SELECT 쿼리에 있는 열의 수와 데이터 유형이 동일해야 합니다.

SELECT ...
FROM table1
UNION
SELECT ...
FROM table2;

2. UNION ALL
UNION ALL 연산자는 UNION과 유사하지만 결합된 결과 집합에서 중복 행을 제거하지 않습니다. UNION ALL은 중복을 제거하기 위한 추가 처리가 필요하지 않으므로 UNION보다 빠릅니다.

SELECT ...
FROM table1
UNION ALL
SELECT ...
FROM table2;

3. IN
IN 연산자는 열의 값이 값 목록 또는 하위 쿼리의 결과 집합에 있는지 여부에 따라 행을 필터링하는 데 사용됩니다. 값이 목록 또는 결과 집합에 있으면 TRUE를 반환하고, 그렇지 않으면 FALSE를 반환합니다.

구문(값 목록 포함):

SELECT ...
FROM table1
WHERE column1 IN (value1, value2, value3);

구문(하위 쿼리 포함):

SELECT ...
FROM table1
WHERE column1 IN (SELECT column2 FROM table2);

4. NOT IN
NOT IN 연산자는 IN 연산자와 반대입니다. 이 연산자는 열의 값이 값 목록이나 하위 쿼리의 결과 집합에 없는지 여부에 따라 행을 필터링합니다. 값이 목록이나 결과 집합에 없으면 TRUE를 반환하고, 그렇지 않으면 FALSE를 반환합니다.

구문(값 목록 포함):

SELECT ...
FROM table1
WHERE column1 NOT IN (value1, value2, value3);

구문(하위 쿼리 포함):

SELECT ...
FROM table1
WHERE column1 NOT IN (SELECT column2 FROM table2);

이러한 연산자를 사용하면 여러 테이블 또는 결과 집합의 데이터를 결합, 필터링 및 조작할 수 있으므로 SQL 쿼리에서 검색되는 데이터를 더 잘 제어할 수 있습니다.

---