02월 26일자로 빅쿼리에 GROUP BY ALL 문법이 추가됐다. 기존에는 쿼리를 묶고 싶으면 GROUP BY에 컬럼을 지정해줘야 했는데, GROUP BY ALL 문법의 추가로 이제 빅쿼리가 SELECT 절에서 그룹화할 키를 알아서 묶게 할 수 있게 됐다.

묶는 조건은 단순한데, 집계 함수나 윈도우 함수 외 몇 가지 공식 문서에서 지정한 불가 형태 제외하고 나머지 컬럼은 다 그GROUP BY에 포함시키는 식이다. 이 때 상기 조건들을 제외하고 난 후, 남은 SELECT 항목 중 그룹화 불가한 유형이 있는 경우 오류가 발생한다. 

다음 예제에서 쿼리는 first_name과 last_name 기준으로 그룹화된다. total_points는 집계 함수 값이므로 제외됐다.

WITH PlayerStats AS (
  SELECT 'Adams' as LastName, 'Noam' as FirstName, 3 as PointsScored UNION ALL
  SELECT 'Buchanan', 'Jie', 0 UNION ALL
  SELECT 'Coolidge', 'Kiran', 1 UNION ALL
  SELECT 'Adams', 'Noam', 4 UNION ALL
  SELECT 'Buchanan', 'Jie', 13)
  
SELECT SUM(PointsScored) AS total_points
  		, FirstName AS first_name
  		, LastName AS last_name
FROM PlayerStats
GROUP BY ALL;

/*--------------+------------+-----------+
 | total_points | first_name | last_name |
 +--------------+------------+-----------+
 | 7            | Noam       | Adams     |
 | 13           | Jie        | Buchanan  |
 | 1            | Kiran      | Coolidge  |
 +--------------+------------+-----------*/

다음 예제 역시 쿼리는 first_name과 last_name 기준으로 그룹화된다. total_points는 윈도우 함수 값이므로 제외됐다.

WITH PlayerStats AS (
  SELECT 'Adams' as LastName, 'Noam' as FirstName, 3 as PointsScored UNION ALL
  SELECT 'Buchanan', 'Jie', 0 UNION ALL
  SELECT 'Coolidge', 'Kiran', 1 UNION ALL
  SELECT 'Adams', 'Noam', 4 UNION ALL
  SELECT 'Buchanan', 'Jie', 13)
  
SELECT COUNT(*) OVER () AS total_people
  		, FirstName AS first_name
  		, LastName AS last_name
FROM PlayerStats
GROUP BY ALL;

/*--------------+------------+-----------+
 | total_people | first_name | last_name |
 +--------------+------------+-----------+
 | 3            | Noam       | Adams     |
 | 3            | Jie        | Buchanan  |
 | 3            | Kiran      | Coolidge  |
 +--------------+------------+-----------*/

여러 SELECT 항목이 FROM 절의 동일한 부분을 참고한다면 경로를 가장 직접적으로 지정한 하나만 GROUP BY의 대상이 된다. 아래 예제에서 coordinates의 두 구성 요소인 Values.x와 Values.y가 이미 직접 경로로 지정돼 있는 상태이므로 coordinates 컬럼은 GROUP BY에서 제외됐다.

WITH Values AS (
  SELECT 1 AS x, 2 AS y
  UNION ALL SELECT 1 AS x, 4 AS y
  UNION ALL SELECT 2 AS x, 5 AS y
)

SELECT Values.x AS x_coordinate
  	, Values.y AS y_coordinate
  	, [Values.x, Values.y] AS coordinates
FROM Values
GROUP BY ALL

/*--------------+--------------+-------------+
 | x_coordinate | y_coordinate | coordinates |
 +--------------+--------------+-------------+
 | 1            | 4            | [1, 4]      |
 | 1            | 2            | [1, 2]      |
 | 2            | 5            | [2, 5]      |
 +--------------+--------------+-------------*/

서브 쿼리Subquery란 메인 쿼리Main query를 실행하는 데 필요한 데이터를 추가로 조회하기 위해 사용되는 부가적인 SELECT문을 말한다. 어떤 메인 쿼리에 포함되어 있다는 의미에서 내부 쿼리Inner query라고 부르기도 한다. 이때, 메인 쿼리는 외부 쿼리Outer query로 지칭될 수 있다. 

서브 쿼리의 형태 / 실행 조건

SELECT 열
FROM 테이블
WHERE 조건식 ( SELECT 열
                             FROM 테이블
                             WHERE 조건식 )

(1) 반드시 괄호에 감싸진 형태로 쓰인다.

(2) SELECT문의 형태로만 작성이 가능하다.

(3) 쿼리의 끝에서 ;세미 콜론을 사용할 수 없다.

(4) ORDER BY절을 삽입할 수 없다.

서브 쿼리의 특징

(1) 비교 혹은 조회의 대상이 될 때 조회 대상의 오른쪽에 놓인다.

(2) 메인 쿼리와의 비교를 위해 존재하는 쿼리이기 때문에 서브 쿼리의 SELECT 절은 메인 쿼리의 비교 대상과 같은 자료형과 같은 개수로 지정돼야 한다.

(3) 결과값 역시 메인 쿼리의 연산자 종류와 동일해야 한다.

(4) 메인 쿼리와의 관계는 단방향이다. 이는 Java 객체지향의 상속과도 유사한 형태인데, 상속당한 자식 객체는 부모 객체의 인스턴스를 사용할 수 있지만 부모는 자식 객체의 인스턴스를 사용할 수 없는 특징이 있다.

(5) 구조화를 통해 쿼리의 각 부분을 가독성 있고 명확하게 볼 수 있게 해 준다.

(6) 위의 특징 때문에, 복잡한 JOIN과 UNION을 명료하게 정리할 수 있는 대체재로 기능할 수 있다. 

서브 쿼리의 사용처

(1) SELECT 절에서 사용 / 스칼라 서브 쿼리Scalar Subquery

(2) FROM 절에서 사용 / 인라인 뷰Inline View

(3) WHERE 절에서 사용 / 중첩 서브 쿼리Nested Subquery

(4) HAVING 절에서 사용

(5) ORDER BY 절에서 사용

(6) INSERT문의 VALUES 값 대체재로써 사용

(7) UPDATE문의 SET 값 대체재로써 사용

서브쿼리의 사용

이번 포스트에서는 서브 쿼리의 사용처 중 SELECT, FROM, WHERE절에서 쓰이는 경우, 즉 스칼라 서브 쿼리, 인라인 뷰, 중첩 서브 쿼리에 대해 알아본다.

SELECT절, 스칼라 서브 쿼리Scalar Subquery

ㅡ스칼라 서브쿼리(Scalar Subquery)
  정대리 급여와 테이블 전체 평균 급여를 구하시오.

SELECT name
	, salary
   	, (SELECT ROUND(AVG(salary), -1) FROM employee) AS 평균급여
FROM employee
WHERE name = '정대리';

스칼라 서브 쿼리는 SELECT절에서 나타나는 서브 쿼리다. 다른 테이블에서 어떠한 값을 불러올 때 주로 쓰이며, '스칼라'라는 이름에서 보여지듯 단일 값만을 가져올 수 있다. 해당 서브 쿼리는 일치하는 값이 없다면 NULL을 리턴한다(해당 부분은 그룹 함수의 특징이기도 하다.).

FROM절, 인라인 뷰Inline View

ㅡ인라인 뷰(Inline View)
  직급이 사원인 사람들의 이름과 급여를 구하시오.

SELECT ex1.name
	, ex1.salary
FROM (SELECT * FROM employee li
WHERE li.office_worker= '사원') ex1;

인라인 뷰는 FROM 절에서 나타나는 서브 쿼리다. 특정 테이블에서 전체 데이터가 아니라 일부 데이터만 추출해 연결하고자 할 때 쓴다. 타 서브 쿼리와 달리 꼭 별칭alias을 지정해 줘야지만 사용이 가능하다. 참고로, WITH 문을 사용해도 결과물은 동일하기 때문에 손에 맞는 것을 취사선택해 쓰면 된다.

WITH
[별칭1] AS (SELECT문 1),
[별칭2] AS (SELECT문 2),
...,
[별칭N] AS (SELECT문 N)
SELECT 
FROM 별칭1, 별칭2, ..., 별칭N

WHERE절, 중첩 서브 쿼리Nested Subquery

ㅡNested Subquery - 단일 행 
  정대리라는 사람의 직급을 구하시오.

SELECT office_worker
FROM employee
WHERE office_worker = (SELECT office_worker FROM employee WHERE name = '정대리')

ㅡNested Subquery - 복수(다중) 행 
  정대리보다 급여가 높은 사람들을 구하시오.

SELECT *
FROM employee
WHERE salary > (SELECT salary FROM employee WHERE name = '정대리')

  직급이 사원인 사람들을 구하시오.

SELECT *
FROM employee
WHERE office_worker IN (SELECT office_worker FROM employee WHERE office_worker = '사원')

WHERE절에서 나타나는 서브 쿼리는 중첩 서브 쿼리라 부른다. 실행 결과의 행 수, 열 수에 따라 단일 행 서브 쿼리, 다중 행 서브 쿼리, 다중 열 서브 쿼리로 나누며 이번 포스트에서는 실무에서 주로 사용되는 단일 행/ 다중 행 서브 쿼리만 다룬다. 

단일 행 서브 쿼리는 >, >=, =, <=, <, <>, ^=, != 등의 연산자와 함께 쓰이며, 다중 행 서브 쿼리는 IN, ANY, SOME, ALL, EXISTS 등의 연산자와 함께 쓰인다. 출력하는 행 수와 연결지어서, 전자를 단일 행 연산자, 후자를 다중 행 연산자라 부르기도 한다. 

IN : 하나라도 일치한 데이터가 있다면 TRUE
ANY, SOME : 만족하는 결과가 하나 이상이면 TRUE
ALL : 결과가 모두 만족하면 TRUE
EXISTS : 결과가 존재하면 TRUE (행이 1개 이상이면 TRUE)

 <IN> 부서별 최고 급여와 동일한 급여를 받는 사원 정보

SELECT *
FROM emp
WHERE sal IN (SELECT MAX(sal) FROM emp GROUP BY deptno);

 <SOME> 등가 비교 연산자(=)와 ANY, SOME을 함께 사용하면 IN 연산자와 같은 기능 수행

SELECT *
FROM emp
WHERE sal = SOME (SELECT MAX(sal) FROM emp GROUP BY deptno);

 <ANY> 등가 비교 연산자가 아닌 대소 비교 연산자와 ANY를 함께 사용하면?
 < ANY : 부서 30 중 최대값보다 적은 급여를 가진 사원을 찾게 됨
 > ANY : 부서 30 중 최소값보다 높은 급여를 가진 사원을 찾게 됨

SELECT *
FROM emp
WHERE sal < ANY (SELECT sal FROM emp WHERE deptno = 30);

 <ALL> 부서 30 중 최대 급여보다 더 많은 급여를 받는 사원 출력

SELECT *
FROM emp
WHERE sal > ALL (SELECT sal FROM emp WHERE deptno = 30);

 <EXISTS> 부서50이 있으면 EMP 전체 출력

SELECT *
FROM emp
WHERE EXISTS (SELECT dname FROM dept WHERE deptno = 50);

출처

양디로그 블로그

Inpa Dev 블로그

java의 개발일기 블로그

<서론>

실무에서는 종종 두 개 이상의 테이블의 모든 데이터ㅡ기준키가 공백인 데이터를 포함한, 다시 말해 합집합ㅡ를 불러와야 할 경우가 생긴다. 타 데이터베이스에서는 이를 FULL OUTER JOIN이라는 이름으로 지원하지만, mysql에서는 아쉽게도 이를 공식적으로 지원하고 있지 않다. 그러면 mysql에서는 합집합을 구현할 수 없냐고? 그건 아니다. LEFT JOIN과 RIGHT JOIN을 결합하는 것으로 mysql에서도 이를 간접적으로 구현할 수 있다. 외국 블로그에 해당 내용에 대해 예제와 함께 표시한 포스트가 있어 번역해 가져와 본다.

<본문>

sales와 orders라는 두 테이블이 있다고 가정해 보자. sales와 orders에는 동일하게 id주문 아이디와 order_date(형식이 살짝 다른)주문 일자, amount주문 금액 데이터가 들어 있다.

SELECT *
FROM sales;

 +------+---------------------+--------+
 | id   | order_date          | amount |
 +------+---------------------+--------+
 |    1 | 2021-02-02 08:15:00 |    250 |
 |    2 | 2021-02-02 08:30:00 |    200 |
 |    3 | 2021-02-02 08:55:00 |    150 |
 |    4 | 2021-02-02 09:15:00 |    125 |
 |    5 | 2021-02-02 09:30:00 |    250 |
 |    6 | 2021-02-02 09:45:00 |    200 |
 |    7 | 2021-02-02 10:15:00 |    180 |
 |    8 | 2021-02-02 10:30:00 |    125 |
 |    9 | 2021-02-02 10:45:00 |    200 |
 |   10 | 2021-02-02 11:15:00 |    250 |
 |   11 | 2021-02-02 11:30:00 |    150 |
 |   12 | 2021-02-02 11:45:00 |    200 |
 +------+---------------------+--------+


SELECT *
FROM orders;

 +------+------------+--------+
 | id   | order_date | amount |
 +------+------------+--------+
 |    5 | 2021-01-28 |    250 |
 |    6 | 2021-01-29 |    250 |
 |    7 | 2021-01-30 |    250 |
 |    8 | 2021-01-31 |    250 |
 |    9 | 2021-02-01 |    250 |
 +------+------------+--------+

만약 당신이 전체 기간 동안의 매출 합계를 계산한다거나 하는 이유로 두 테이블을 합쳐야 할 필요성이 있다면 아래와 같이 LEFT JOIN과 RIGHT JOIN을 UNION ALL로 결합해 사용할 수 있다. 아래와 같은 식으로.

mysql> SELECT * FROM sales
       LEFT JOIN orders ON sales.id = orders.id
       UNION ALL
       SELECT * FROM sales
       RIGHT JOIN orders ON sales.id = orders.id
       WHERE sales.id IS NULL ;
       
 +------+---------------------+--------+------+------------+--------+
 | id   | order_date          | amount | id   | order_date | amount |
 +------+---------------------+--------+------+------------+--------+
 |    5 | 2021-02-02 09:30:00 |    250 |    5 | 2021-01-28 |    250 |
 |    6 | 2021-02-02 09:45:00 |    200 |    6 | 2021-01-29 |    250 |
 |    7 | 2021-02-02 10:15:00 |    180 |    7 | 2021-01-30 |    250 |
 |    8 | 2021-02-02 10:30:00 |    125 |    8 | 2021-01-31 |    250 |
 |    9 | 2021-02-02 10:45:00 |    200 |    9 | 2021-02-01 |    250 |
 |    1 | 2021-02-02 08:15:00 |    250 | NULL | NULL       |   NULL |
 |    2 | 2021-02-02 08:30:00 |    200 | NULL | NULL       |   NULL |
 |    3 | 2021-02-02 08:55:00 |    150 | NULL | NULL       |   NULL |
 |    4 | 2021-02-02 09:15:00 |    125 | NULL | NULL       |   NULL |
 |   10 | 2021-02-02 11:15:00 |    250 | NULL | NULL       |   NULL |
 |   11 | 2021-02-02 11:30:00 |    150 | NULL | NULL       |   NULL |
 |   12 | 2021-02-02 11:45:00 |    200 | NULL | NULL       |   NULL |
 +------+---------------------+--------+------+------------+--------+

다만 UNION ALL의 경우 합집합이기 때문에 중복 행이 섞여 나올 수 있다. 이럴 경우에는 UNION을 사용해 주면 되는데, UNION의 경우에는 t1과 t2 사이의 중복 값을 걸러서 출력하는 것이어서 유니크 값을 거르는 인덱스가 하나 더 추가된다고 한다. 그래서 ㅡ중복을 거르지 않는 UNION ALL과 대비해서ㅡ 데이터의 크기에 따라 1.5배에서 4배까지 성능 차이가 날 수도 있다고.

그러니까, 웬만하면 FULL OUTER JOIN을 사용할 때는 UNION ALL을 쓰는 게 효율적이겠다. (+ 최소한으로 SELECT해서.)물론 애초에 모델링 차원에서 결합할 일을 배제하도록 설계하는 게 가장 좋겠지만.

<정리>

마지막으로 문법을 정리해 두고 글을 마친다. 순서대로 UNION ALL중복 미제거, UNION중복 제거으로 FULL OUTER JOIN을 적용하는 쿼리다.

SELECT * FROM t1 
LEFT JOIN t2 ON t1.id = t2.id 
UNION ALL 
SELECT * FROM t1 
RIGHT JOIN t2 ON t1.id = t2.id 
WHERE t1.id IS NULL

SELECT * FROM t1 
LEFT JOIN t2 ON t1.id = t2.id 
UNION 
SELECT * FROM t1 
RIGHT JOIN t2 ON t1.id = t2.id

<참고>

ubiq 블로그

sewonzzang님 블로그

<GROUP BY와 윈도우 함수Window Function는 어떻게 다른가>

윈도우 함수Window Function는 '행을 조건에 맞게 구분해 주는' 함수다. 이쯤 말하면 당신 머릿속에 떠오른 문법이 하나 있을 것이다. 맞다. GROUP BY. 이것 역시 행을 조건에 맞게 구분해 준다. 그러면 의문이 든다. GROUP BY 절과 대비해 윈도우 함수는 어떤 차이점이 있고 그래서 결국 어떤 상황에 사용되는 걸까. 이번 포스트는 이처럼 유사한 문맥에서 사용되는 두 문법, GROUP BY 절과 윈도우 함수의 차이에 대해 탐구해볼 것이다.

<'행을 묶는다' 는 행동에 대한 이해>

문법에 대해 다루기 앞서서 즉 '행을 묶는다'는 행동에 대해 먼저 이해할 필요가 있다. 행을 묶는다는 것은 일차적으로 행 사이의 공통점을 찾는다는 뜻이다. 아래와 같은 엑셀 파일이 있다고 가정해 보자. 

이 엑셀 파일에 '행을 묶으면' 어디에 포커스를 두느냐에 따라 아래와 같이 두 가지 결과물이 나올 수 있다.

1) 행에 포커스를 두는 경우. 아래는 성별 기준으로 행을 묶어서 성별 별 최연장자를 각각 출력한 경우다.

2) 연산한 값에 포커스를 두는 경우. 아래는 이름의 첫 글자, 성 기준으로 행을 묶어서 성 별 나이 최대값을 각각 출력한 경우다.

둘 모두 포커스만 다르지 '행을 묶었다'고 부를 만하다. 이때 후자는 GROUP BY로 구현할 수 있지만, 전자는 어렵다. GROUP BY는 특성상 동일한 기준을 가진 로우들을 하나로 '합치고' 들어가기 때문이다. 그래서 GROUP BY를 가진 쿼리의 SELECT 절에는 GROUP BY된 컬럼과 합쳐진 값들을 연산하는 집계함수밖에 올 수 없는 것이다. 이때 전자는 윈도우 함수로 구현될 수 있다.

<윈도우 함수Window Fuction>

'행과 행 간의 관계를 정의하거나 비교, 연산을 수행할 수 있는 함수'가 윈도우 함수에 대한 정의다. 위에서 알아봤듯이 GROUP BY는 동일한 기준을 가지는 로우들을 다 합쳐 버리지만, 윈도우 함수는 보존해 준다. 이 때문에 GROUP BY로 하지 못 하는 결과를 출력 가능하다. 그룹 내에서 순위/ 비율 뽑기, 연산에 맞는 결과 로우 출력하기(최대값을 가진 사람의 이름을 불러온다거나) 등.

윈도우 함수는 아래와 같은 형식으로 쓰인다.

SELECT WINDOW_FUNCTION (ARGUMENTS) OVER 
( [PARTITION BY 컬럼] [ORDER BY 컬럼] [WINDOWING 절] )
FROM 테이블명 ; 

   1) WINDOW_FUNCTION : 윈도우 함수 

   2) ARGUMENTS(인수) : 함수에 따라 0 ~ N개 인수가 지정될 수 있다. 

   3) PARTITION BY 절 : 전체 집합을 기준에 의해 소그룹으로 나눌 수 있다.   

   4) ORDER BY 절 : 어떤 항목에 대해 순위를 지정할 지 order by 절을 기술한다.

   5) WINDOWING 절 : WINDOWING 절은 함수의 대상이 되는 행 기준의 범위를 강력하게 지정할 수 있다. 

이때의 WINDOW_FUNCTION에는 전용 윈도우 함수 외에도 다양한 기존 함수를 가져다 쓸 수 있는데, 이 부분을 이 포스트에서 정리하지는 않고 참고 포스트 중 해당 내용에 대해 잘 정리한 포스트가 있어 재인용한다*링크는 SQL 전문가 가이드 2013 Edition를 요약했다고 한다. 해당 포스트를 참조해 쓰면, WINDOW_FUNCTION에는 다음과 같은 함수들이 올 수 있다.

   1) 순위 함수: RANK, DENSE_RANK, ROW_NUMBER

   2) 집계 함수: SUM, MAX, MIN, AVG, COUNT

   3) 비율 함수: CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT

윈도우 함수는 SQL의 다른 문법들보단 꽤 난이도가 있어 진입장벽이 높지만, 일단 익혀두고 나면 쿼리 다이어트에 도움을 주는 효자 함수다. 시간 내서 익숙해져 보자.

CASE WHEN(CASE 문이라고도 한다.)은 SQL의 조건문이다. 기준 컬럼 A가 B라는 조건을 만족하면 C라는 값을 출력하는 형태로 'CASE WHEN A = B THEN C'와 같이 표현된다. "조건에 맞는 답을 출력"하는 식이어서 주로 SELECT 절이나 집계함수에서 쓰인다. 이번 포스트에서는 샘플 데이터베이스 기반으로 아직 집계되지 않은 유의미한 지표를 찾아 반환해 보고 이를 통해 CASE WHEN의 SELECT절 용례를 살펴보려 한다.

사용 데이터베이스

아래로 한 데이터베이스가 있다. (mySQL에서 기본적으로 제공하는 샘플이다.) 우리는 이 데이터베이스의 테이블들을 살펴보고 CASE WHEN을 통해 회사 운영 전반에 유의미한 정보를 만들어 볼 것이다. 이 데이터베이스에는 여덟 개의 테이블이 있다. customers, employees, offices, orderdetails, orders, payments, productline, products...... 이 회사에서 무슨 일을 하는지는 아직 알 수 없지만 프로덕트 정보, 주문 정보를 가진 것으로 보아 어떤 프로덕트를 판매하는 커머스임을 우선 알 수 있다. 그게 유형의 제품이든, 무형의 제품이든간에. 우선 products를 한 번 볼까.

위 사진은 products의 데이터를 일부 출력한 것이다. 익숙한 자동차/ 오토바이 브랜드가 보이는데, 전반적으로 엄청 옛날 것이고(productName) 재고 수량도 너무 많다. 좀 더 살펴 보니 productScale이라는 컬럼이 있다. 1:10, 1:12...... 자동차 미니어처를 파는 회사임을 알 수 있다. 제조 벤더사(productVender)가 여럿인 것으로 봐선 제조사보다는 유통사에 가까운 회사겠다.

중요한 부분은 주로 뒤에 있다. 재고 수량(quantityInstock), 구매원가(buyPrice), 판매가(MSRP). 판매 데이터와 함께 보면 뜯어볼 부분이 많아 보인다. 하지만 이 회사 데이터베이스엔 판매 데이터가 없기 때문에 주문 데이터를 통해 추출해내야 할 것 같다.

다음으로 살펴 볼 테이블은 orderdetails 테이블이다. 제품의 개당 단가와 주문 수량이 있고 productCode로 products 테이블과 연결돼 있다. 주문 수량이 쭉 나열돼 있기 때문에 products와 orders를 함께 보면 제품의 평균 주문 수량이나 총 매출액, 잔여 재고수량, 안전 재고 등의 확인이 가능하겠다. 아쉬운 것은, 주문 일자가 없다. 기간별로 그룹해 볼 수 없으니 유의미한 정보를 만들어낼 수는 없다. 그러면 테이블 하나가 더 필요하겠다. 

바로 orders 테이블이다. 이 테이블에는 주문 일자(orderDate)와 주문한 사람의 고객 번호(customerNumber), 그리고 주문 번호가 적혀 있다. 테이블을 살펴 보니 2003년 01월부터 2005년 05월까지의 데이터가 저장돼 있다. 이걸로 필요한 데이터는 모두 모아졌다. 이제 데이터를 예쁘게 다듬어보자.

INNER JOIN: 여러 테이블 모아서 보기

CASE WHEN은 조건을 다는 데 수량 제한이 없다. 원하는 수량만큼 충분히 덧붙일 수 있단 소리다. SELECT 절에서 CASE WHEN을 사용하면 새로운 컬럼에서 구간에 맞춰 값을 다시 부여함으로써 로우들을 세분화할 수 있다. 하지만 한 테이블에서만 진행하기는 조금 어렵다. 각 정보값이 두 테이블에서 공유하는 값(기본키)을 기준으로 나눠져 있기 때문이다. 이를 테면, orderdetails에서는 제품의 값과 주문 수량이 있지만 주문 일자와 주문자명이 없다. 이 두 정보값은 orders 테이블에서 가져와야 한다.

우리는 이미 이전 포스트에서 JOIN에 관해 배웠다. INNER JOIN을 활용하면 각기 두 테이블에서 원하는 컬럼을 각각 가져와 재조립해 SELECT 할 수 있다. 아래와 같이 말이다.

SELECT ods.orderDate
, ods.customerNumber
, odd.orderNumber
, odd.productCode
        , odd.quantityOrdered
        , odd.priceEach
FROM orderdetails odd, orders ods
WHERE odd.orderNumber = ods.orderNumber

이렇게 INNER JOIN으로 정보를 각각 가져와 주면, 일자별로 어떤 상품이 누구에게 얼마나 팔렸는지 알 수 있다. 이 테이블을 기준으로 2003년 기준으로 제품별 매출 순위를 뽑아 볼 수 있을 것이다.

WHERE, GROUP BY: 1년치 제품별 매출 순위 뽑기

SELECT ods.orderDate
, ods.customerNumber
, odd.orderNumber
, odd.productCode
        , odd.quantityOrdered
        , odd.priceEach
FROM orderdetails odd, orders ods
WHERE odd.orderNumber = ods.orderNumber
AND orderDate LIKE '2003%'
ORDER BY productCode;

WHERE 절에 조건을 하나 더 추가해 줬다. 2003년으로 시작하는 문자열 전체를 반환하는 조건이다. ORDER BY는 productCode로 줬다. 제품 코드 별로 데이터가 정렬된 게 보인다. (시기나 고객 집단의 차이에 따라 최종 판매가는 오르내리는 게 보인다.) 여기다 상품별 연매출액을 뽑고 정렬해 주면 예쁘게 볼 수 있겠다.

SELECT productCode
, ROUND(AVG(priceEach), 2) AS avg_priceEach
    , SUM(quantityOrdered) AS sum_quantity
    , (ROUND(AVG(priceEach), 2) * SUM(quantityOrdered)) AS year_sales
FROM orderdetails odd, orders ods
WHERE odd.orderNumber = ods.orderNumber
AND orderDate LIKE '2003%'
GROUP BY productCode
ORDER BY year_sales DESC;

훨씬 보기 편해졌다. 여기서 들 수 있는 의문을 해소하고 갈까. 왜 priceEach는 평균을 구하고 quantity는 합계 값을 구하는가가 궁금할 수 있다. 그건 priceEach가 구매액(개당 단가*수량)이 아니라 개당 단가여서 그렇다. 해당 회사의 경우 도매로만 거래하기 때문에 제품 가격이 고정되어 있지 않고 협의 여부와 상황에 따라 유동적으로 단가가 조정된다. 그래서 연매출, year_sales를 뽑기 위해서는 이 개당 실판매가의 평균과 판매 개수를 곱해야만 했다.

CASE WHEN: 상품 세분화해서 각기 다른 프로모션 전략 짜기

보면 알겠지만, 매출 1위 상품과 2위 상품의 편차가 크다. 쭉 내려보면 막순위 상품은 1만 달러 정도로 1위와 거의 10배 가량 매출 차이가 나는데 상위권 상품 대비해서 제품 단가도 낮고 판매 수량 좀 부진하다. 전반적으로 보면 그룹별로 나눠서 관리해줘야 할 필요가 있다. 

SELECT productCode
, ROUND(AVG(priceEach), 2) AS avg_priceEach
    , SUM(quantityOrdered) AS sum_quantity
    , (ROUND(AVG(priceEach), 2) * SUM(quantityOrdered)) AS year_sales
    , ROW_NUMBER () 
OVER (
ORDER BY (ROUND(AVG(priceEach), 2) * SUM(quantityOrdered)) DESC
            ) AS sales_rank
FROM orderdetails odd, orders ods
WHERE odd.orderNumber = ods.orderNumber
AND orderDate LIKE '2003%'
GROUP BY productCode
ORDER BY year_sales DESC;

year_sales를 조금 더 직관적으로 보고, 차후에 다른 년도별 세분화 진행 시에도 사용 가능하도록 순위를 먼저 뽑았다. 이 데이터를 기반으로 상품을 'high', 'medium', 'low' 세 가지 집단으로 나눠볼 것이다. 'high'는 매출 상위권으로 재고를 지속 관리해줘야 하며 판매 동향을 지속적으로 체크해야 할 상위 십 퍼센트, 반대로 'low'는 재고가 과다하다면 최우선으로 소진하고 재고를 감축 운영해야 할 하위 십 퍼센트다.

퍼센트를 직접 집계하는 쿼리를 짜고 싶었는데 서칭 실력이 부족했던 건지 결국 못 찾아서, 전체 리스트를 10으로 나눠서 위 아래로 11위씩 빼 줬다(...)

SELECT productCode
, ROUND(AVG(priceEach), 2) AS avg_priceEach
    , SUM(quantityOrdered) AS sum_quantity
    , (ROUND(AVG(priceEach), 2) * SUM(quantityOrdered)) AS year_sales
    , ROW_NUMBER () 
OVER (
ORDER BY (ROUND(AVG(priceEach), 2) * SUM(quantityOrdered)) DESC
            ) AS sales_rank
, CASE 
WHEN ROW_NUMBER () 
OVER (
ORDER BY (ROUND(AVG(priceEach), 2) * SUM(quantityOrdered)) DESC
            ) BETWEEN 1 AND 11 THEN 'high'
WHEN ROW_NUMBER () 
OVER (
ORDER BY (ROUND(AVG(priceEach), 2) * SUM(quantityOrdered)) DESC
            ) BETWEEN 98 AND 109 THEN 'low'
ELSE 'medium'
END AS sales_seg
FROM orderdetails odd, orders ods
WHERE odd.orderNumber = ods.orderNumber
AND orderDate LIKE '2003%'
GROUP BY productCode;

결과적으론 잘 나왔다! 가독성 위해 BETWEEN으로 값을 지정해 줬는데 연산부호로 잡아 줘도 무방하겠다. 굳이 순위를 집계한 까닭은 이후에 일일이 범위를 지정할 필요가 없어서다. year_sales를 조건으로 잡다 보니 많이 길어졌는데, 더 효율 좋게 짤 수 있는 방법이 분명 있지 않나 싶다. 차후 알게 되면 와서 덧붙여 보겠다.

CASE WHEN을 사용할 때 주의할 점이 몇 가지 있다. 1) 상위 조건이 우선순위로 잡혀 있어서 우선순위 설정 시 겹치지 않게 넣어 줘야 한다는 거. 만약에 위 쿼리를 연산기호로 조건을 겹칠 수 있게 잡아 뒀다면 먼저 선언된 한 가지 집단에 값들이 다 몰려 버리는 결과가 나올 수 있다. 해당 문법은 한 번 포함된 이후의 만족 조건을 무시하기 때문이다. 2) ELSE를 넣지 않아도 동작하지만 지정해주지 않으면 세분화되지 않는 로우는 전부 다 NULL 값이 들어간다는 거다. 해당 두 가지를 주의해서 짜 주자.

참고

suy379님 티스토리

mySQL 샘플 데이터베이스

분석 툴을 사용해 데이터를 뽑다 보면 가끔 당황스러울 때가 있다. 고유 번호까지 동일한 데이터가 여러 개 나오는 거다. 이유가 어떻든(우리가 변별력 없는 조건을 설정했든, 말 그대로 컴퓨터가 실수를 했든) 간에 이 값들은 걸러져야 하고 반대로 수집 프로세스 자체의 보수를 위해 집계되기도 해야 한다.

아래 order_list는 가상의 브랜드의 주문 정보 수집 내역이다. 이 중 주문번호 10251은 두 번 나오는데 보다시피 고객ID를 포함한 모든 정보값이 동일하다. 기재하진 않았지만 수집된 시간까지. 오늘은 이 데이터를 기준으로 중복 값 제거/ 집계 관련한 쿼리 작업을 진행해보겠다.

order_list

* learnsql 포스트를 번역해 공유한 것.

문제를 풀기 위해서, 우선 '중복'의 조건을 정의할 필요가 있다. 중복이 발생하려면 완전히 동일한 데이터가 2개 이상 존재해야 한다. '완전 동일' 을 판단하는 기준이 돼 줄 컬럼은 명확하다. 중복될 수 없는 고유한 값, 기본 키인 '주문번호'다. 그렇다면 주문번호끼리 그룹을 묶어 정렬하고 그룹별 수량을 센다면 중복이 조금 더 명확히 보이지 않을까.

SELECT 주문번호

               , COUNT(주문번호) AS 중복체크 FROM order_list
GROUP BY 주문번호
HAVING COUNT(주문번호);

order_list

주문번호의 중복이 잡힌 10251 말고는 전체가 1로 체크되는 걸 알 수 있다. 그렇다면, 여기서 집계 기준을 '1보다 큰 값'으로 바꿔 준다면 중복 값만 죽 출력해 리스트로 얻을 수 있겠다.

SELECT 주문번호

               , COUNT(주문번호) AS 중복체크 FROM order_list
GROUP BY 주문번호
HAVING COUNT(주문번호) > 1;

중복 데이터가 몇 개 없다면 지워버리면 그만이겠지만, 과하게 발생하고 있다면 컴퓨터의 실수보단 내 실수일 가능성이 크다. 이런 상황에서는 전체 컬럼을 띄워 놓고 데이터 사이의 접점을 체크해보면 되겠다. HAVING을 단순히 조건 집계용으로 사용하는 방식이다. 쿼리와 표를 마지막으로 보여주며 마무리한다.

SELECT 주문번호, 고객ID, 주문일자, 배송ID

               , COUNT(주문번호) AS 중복체크 FROM order_list
GROUP BY 주문번호, 고객ID, 주문일자, 배송ID
HAVING COUNT(주문번호) > 1;

참고

learnsql 홈페이지

이번 포스트에서는 Join에 대해 다룬다. Join은 두 개 이상의 테이블에서 나온 결과를 종합해 하나의 결과 집합으로 출력하는 키워드다. 정보를 여러 테이블에 분리해 보관하는 관계형 데이터베이스(RDBMS)와 두 테이블 이상의 접점 혹은 차이점에서 유의미한 인사이트를 도출해내는 집합은 뗄레야 뗄 수 없는 사이라 하겠다.

Join은 기본적으로 두 테이블을 갖다 놓고 기준점과 출력 범위를 어떻게 두냐에 따라 다양하게 세분화되어 있고 이는 집합의 개념과 거의 유사하다. 다만 교집합, 차집합 등 일부 집합 구현 기능은 mySQL에서 지원하지 않기 때문에, exists 구문으로 직접 만들어 구현해야 한다. 이번 포스트에서는 위에서 언급한 문법들을 아래 순서로 데이터를 통해 느슨하게 톺아볼 것이다.

1) Inner Join

2) (Left/ Right) Outer Join

3) intersect/ minus

추가로, 데이터는 금일자 기준 29cm의 실시간 베스트를 크롤링한 뒤, 관계형 데이터베이스를 최대한 구현할 수 있는 환경으로 가공해 올렸다. (혹시 궁금하신 분이 있으실까 해서 덧붙이면 크롤링에는 listly, 크롤링 파일의 sql식 가공에는 tableconvert.com를 사용했다.) 사용 데이터는 아래와 같다.

select * from lrank;  *상품코드, 제품의 정상가, 매출 순위 데이터를 보유하고 있다.
select * from linfo;  *카테고리, 브랜드, 상품명, 좋아요 수, 상품평 수 데이터를 보유하고 있다.

(상품코드와 순위는 분석을 위해 내가 임의로 부여한 값이다.)

1) Inner Join

Inner Join은 기준 테이블에 대한 교집합 개념이다. (쌍방 교집합은 아니다. 해당 개념 관련해서는 3) intersect 파트를 보면 된다.) on에 지정해 준 중심이 되는 컬럼(Primary Key, PK: 기본키) 기준으로 데이터를 합쳐서 보여 준다. 가장 최소값으로 Inner Join을 적용해 본다면 다음과 같은 쿼리가 나오고 출력값은 아래와 같다. 

select * from linfo li
inner join lrank lr on li.상품코드 = lr.상품코드;

데이터가 많이 줄었다. 두 테이블의 볼륨 차이 때문이다. linfo는 전체 등록 상품의 데이터를 가지고 있으니 볼륨이 크지만 lrank는 개중 매출 탑 식스만 뽑은 것이니 그렇다. 이렇게 두 테이블을 Inner Join 방식으로 정제하면 데이터를 집어넣지 못 한 null 값이 빠진 상태로 나온다. 여기서 정보값을 좀 줄여 준다면 직관적인 표가 될 수 있겠다.

select lr.순위, lr.상품코드, li.카테고리, li.브랜드명, li.상품명 from linfo li
inner join lrank lr on li.상품코드 = lr.상품코드
order by lr.순위;

이렇게 잡아 주면 뒤죽박죽이던 순위가 일렬로 정렬되고 상품코드, 카테고리, 브랜드명, 상품명까지 직관적으로 보인다. 

또 Inner Join은 (mySQL에서) 기본 조인이기 때문에 from에 추가 테이블을 넣어주고 where 절에 조건을 추가해주는 형식으로도 구현이 가능하다. 다음과 같이.

select lr.순위, lr.상품코드, li.브랜드명, li.상품명, li.좋아요, li.상품평 
from linfo li, lrank lr
where li.상품코드 = lr.상품코드
order by lr.순위;

이것도 위와 동일하게 출력되기 때문에 둘 중 편한 쪽을 취사선택해서 쓰면 되겠다. 또, 해당 방식은 조건을 여러 개 걸어줄 수 있다. 해당 데이터 중 상품평이 적은 것은 신상품인데 매출이 잘 나오는 것이니 프로모션 등으로 밀어볼 필요가 있을 것이다. 이럴 때는,

select lr.순위, lr.상품코드, li.브랜드명, li.상품명, li.좋아요, li.상품평 
from linfo li, lrank lr
where li.상품코드 = lr.상품코드 and li.상품평 < 10
order by lr.순위;

해당 방식으로 상품평이 적은 걸 걸러서 유의미한 표를 만들어도 좋겠다.

2) (Left/ Right) Outer Join

Outer Join에는 세 가지가 있다. 각자 기준 테이블을 어디로 잡느냐에 따라 각각 Left/ Right/ Full Outer Join으로 나뉜다. 이 중 성능 상 거의 쓸 일이 없는 Full Outer Join을 제하고 여기서는 Left와 Right Join만 다룬다. 둘은 성능 차이 없이 완전히 동일해서, 동일한 데이터베이스에서 둘 이상이 쿼리 작업할 경우에는 왼쪽이든 오른쪽이든 한 쪽으로 통일하길 권한다고 한다. 가독성이 심히 안 좋다고......

Outer Join은 기준 테이블에 나머지 한 테이블이 합쳐지는 형태다. 만약 기준 테이블에 존재하지 않는 값이라면 합쳐지는 테이블에도 그 값은 지워진다. 온전한 기준 테이블에 나머지 한 테이블의 교집합까지만 살아남는 것이다.  

select lr.순위, li.상품명, lr.정상가 from linfo li
right outer join lrank lr on li.상품코드 = lr.상품코드;

select lr.순위, li.상품명, lr.정상가 from linfo li
left outer join lrank lr on li.상품코드 = lr.상품코드;

여기서 질문, 이 두 쿼리의 결과값은 차이가 있을까 없을까? 두 쿼리는 right와 left만 빼고는 다 동일하다.

차이가 별로 없을 것 같았지만 보다시피 차이가 크다.  두 Join 간의 기준 테이블 설정이 달라지기 때문에 생기는 현상이다. 위에서 언급한 바 있듯이, linfo는 전체 상품정보 테이블이다. lrank는 그 중 매출 상위권 상품만 랭킹에 올리면서 집계된 데이터이다. 데이터의 자체 크기는 뭐가 더 클까? 당연히 linfo이다. linfo가 lrank보다 데이터의 볼륨이 훨씬 크다. 상품등록이 되지 않은 상품이 랭킹에 오를 리 없으니 linfo가 lrank를 아예 포함한다고 말해도 좋겠다.

Outer Join은 기준 테이블에 나머지 한 테이블이 합쳐지는 형태라고 했다. lrank에 Left Join을 한다면 기준 테이블은 lrank가 되기 때문에 기존과 같이 출력되지만, lrank에 Right Join을 한다면 기준 테이블은 linfo가 되기 때문에 lrank에서 가지고 있지 않은 나머지 상품들까지 같이 뜬다. Outer Join 시에는 어떤 걸 써도 상관 없지만 기준 테이블이 어딘지를 명확히 하고 작업해야 한다.

추가로 덧붙이면, Outer Join으로 Inner Join을 구현할 수 있다.

select * from lrank lr 
left outer join linfo li on lr.상품코드 = li.상품코드 
where li.상품코드 is not null;

on구 뒤에 where절을 주고 is not null로 null값을 제거해 주면 된다. 하지만 이렇게 하느니 그냥 Inner Join을 거는 게 빠르고 깔끔하겠다.

3) intersect/ minus

위에서 Inner와 Outer Join을 다뤘다. 이쯤 되면 궁금하다. 아니, Join이 그게 끝이냐. 진심이야? 집합 설명하려면 한참 남았잖아. 그래. 나도 설명하고 싶다. 하지만 아쉽게도 mySQL의 경우에는 교집합과 차집합에 해당하는 기능을 지원하지 않는다. 그래서 우리가 별도로 만들어 써야 한다. 이렇게.

intersect (교집합) 구문
select * from linfo a
where exists (select 1 from lrank b where a.상품코드 = b.상품코드);

이때 select 뒤의 1은 단순히 exists(있는지 없는지만 찾으면 되는 함수)가 앞에 왔기 때문에 빠른 출력을 위해 써줬다. select 1 from 형식으로 쿼리를 치면 실제 값을 출력하지 않고 값이 있냐 없냐만 검증하고 있으면 1 쳐 준다. 전체 데이터를 출력하는 것보다 훨씬 빠르다. 

minus 구문
select * from linfo a
where not exists (select 1 from lrank b where a.상품코드 = b.상품코드);

교집합의 반대를 생각해 보자. 차집합이다. 많이 바꿀 필요 없이 exists 앞에 not 붙여서 교집합이 아닌 값만 불러와 주면 된다.

여기까지, 두 개 이상의 테이블을 불러와 한 눈에 보게 해 주는 Join에 대해 알아봤다. 쓰면서 느낀 게, 애초에 뭘 보여줄 수 있는 샘플 데이터를 가져와야 죽이 되든 밥이 되든 할 거 같다. 다음 포스팅에서는 서브쿼리에 대해 다뤄볼 건데, 예쁜 샘플 데이터 가져와서 잘 설명해 보도록 하겠다.

참고

Inpa dev 님 블로그

BK_Paul 님 블로그

데이터베이스/ 테이블 관련 키워드

데이터 타입

데이터베이스/ 테이블/ 컬럼 이름 정하기 규칙

1) 문자, 숫자, _ 를 사용한다.
2) 이름에 쓰이는 문자는 주로 영문 소문자를 쓴다. 한글도 가능하지만 인코딩 이슈 발생할 수 있음. 
보통 키워드나 함수명은 대문자, 사용자가 정의한 이름에는 소문자 사용.
3) 이미 키워드, 함수명 등 문법적으로 사용되고 있는 "예약어"는 이름으로 사용할 수 없다.
4) 단어와 단어 사이에는 빈칸 대신 _를 사용한다.
5) 문자로 시작해야 한다. 숫자, _ 불가.
6) 데이터베이스 이름은 중복될 수 없다. 테이블 이름 역시 하나의 데이터베이스 내에서 중복될 
수 없으며 컬럼 이름은 하나의 테이블 내에서 중복될 수 없다.

주요 쿼리 키워드

와일드카드

% / 0개 이상의 문자
_ / 1개의 문자

'%e' / e로 끝나는 문자열
e, ee, eevee, apple, pineapple

'e%' / e로 시작하는 문자열
e, ee, eevee, eric

'%e%' / e가 포함된 문자열
e, ee, eevee, apple, pineapple, aespa

'_e' / e로 끝나고 e 앞에 1개의 문자가 있는 문자열
ae, ee, ce

'%_e' / e로 끝나고 e 앞에 1개 이상의 문자가 있는 문자열
ee, eevee, apple, pineapple

'%_e_%' / e를 포함하고 e 앞뒤로 각각 1개 이상의 문자가 있는 문자열
eevee, aespa

순위 함수

[함수 이름] () OVER (ORDER BY [컬럼 이름])

그룹 함수

GROUP BY가 없는 쿼리에서 사용 시 전체 로우에 적용됨

주요 연산자

주요 문자형 데이터 함수

주요 숫자형 데이터 함수

주요 날짜형 데이터 함수

조건 만들기

테이블 합치기

여러 테이블 한 번에 다루기

함수 만들기 쿼리

SET GLOVAL log_bin_trust_function_creators=1; #사용자 계정에 function create 권한 생성

DELIMITER // # 함수의 시작 지정

CREATE FUNCTION [함수 이름] ([입력값 이름] [데이터 타입], ...)
RETURNS [결과값 데이터 타입]
BEGIN
DECLARE [임시값 이름] [데이터 타입];
SET [임시값 이름] = [입력값 이름];
쿼리;
RETURN 결과값
END

//
DELIMITER; #함수의 끝 지정