5. 집계쿼리

5.1. LIMIT, DISTINCT

5.1.1. LIMIT

SELECT
			column1
FROM Table1
LIMIT limiting number

SELECT 
      employee_id 
    , first_name 
    , last_name
FROM `HR.employees`
LIMIT 10;

5.1.2. DISTINCT

SELECT 
			DISTINCT column1
FROM Table1

SELECT 
			DISTINCT job_id
FROM `HR.employees`;

SELECT 
			DISTINCT job_id,department_id
FROM `HR.employees`;

예를 들어, DISTINCT로 “job_id”와 “department_id”를 조회하였을 때, 오른쪽 이미지의 2행과 3행을 보면 “job_id” 컬럼에서는 “AD_VP”로 값이 동일하며 “department_id” 컬럼에서도 90이라는 각각 동일한 값을 같습니다. 이러한 경우에는 중복되는 조합 중 하나의 조합만 출력됩니다.

 

하지만 오른쪽 이미지를 보면 “job_id” 컬럼은 “SA_REP”로 동일 값이지만 “department_id” 컬럼에서는 80과 “null”이라는 다른 값을 보입니다. 이러한 경우에는 78행 조합과 79행 조합은 중복제거되지 않고 모두 출력됩니다.

5.2. 집계함수

5.2.1. AVG

SELECT
			AVG(column1)
FROM Table1

SELECT
		  ROUND(AVG(salary), 2) AS average_salary
FROM `HR.employees`;

5.2.2. SUM

SELECT
			SUM(column1)
FROM Table1

SELECT
		  SUM(salary) as total_salary
FROM `HR.employees`;

SELECT
			job_id
		, EXP(SUM(LOG(salary))) AS multiplied_salary
FROM `HR.employees`
GROUP BY job_id;

5.2.3. COUNT

SELECT
			COUNT(column1)
FROM Table1

SELECT 
			COUNT(manager_id) AS null_x
		, COUNT(*) AS null_o
FROM `HR.employees`;

5.2.4. MAX/ MIN

• MAX

SELECT 
			MAX(column1)
FROM Table1

SELECT 
			MAX(salary) as max_salary
FROM `HR.employees`;

• MIN

SELECT 
			MIN(column1)
FROM Table1

SELECT 
			MIN(salary) AS min_salary
FROM `HR.employees`;

5.2.5. VARIANCE/ STDDEV

• VARIANCE

SELECT 
			VARIANCE(column1)
FROM Table1

SELECT 
			ROUND(VARIANCE(salary), 2) AS var_salary
FROM `HR.employees`;

• STDDEV

SELECT
			STDDEV(column1)
FROM Table1

SELECT 
			ROUND(STDDEV(salary), 2) AS std_salary
FROM `HR.employees`;

5.3. GROUP BY

5.3.1 GROUP BY

SELECT 
			column1
		, column2
		, ...
FROM Table1
GROUP BY grouping column1, column2, ...;

• ALIAS 관련 규칙

SELECT 
			column1 AS COL1
		, SUM(column2)
FROM Table1
GROUP BY COL1

• DISTINCT 와 GROUP BY 차이

SELECT 
      job_id
    , COUNT(employee_id) AS count_employees
FROM `HR.employees` 
WHERE salary >= 10000
GROUP BY job_id;

SELECT 
      job_id
    , department_id
    , COUNT(employee_id) AS count_employees
	  , AVG(salary) AS avg_salary
FROM `HR.employees` 
WHERE salary >= 10000
GROUP BY job_id, department_id;

SELECT 
      job_id AS j
		, MIN(salary) AS avg_salary
FROM `HR.employees` 
GROUP BY j;

5.4. HAVING, ORDER BY

5.4.1. HAVING

SELECT 
			column1
		, column2
		, ...
FROM Table1
GROUP BY grouping column1, column2, ...
HAVING Conditions

SELECT 
			column1 AS COL1
		, SUM(column2) AS sum_column2
FROM Table1
GROUP BY COL1 
HAVING sum_column2 > 10;

• WHERE와 HAVING 차이

SELECT 
      job_id
    , COUNT(employee_id) AS count_employees
FROM `HR.employees` 
WHERE salary >= 10000
GROUP BY job_id
HAVING count_employees >= 3;

5.4.2. ORDER BY

SELECT
			column1
		, column2
		, ...
FROM Table1
ORDER BY column1 ASC, column2 DESC ...

• ALIAS 관련 규칙
SQL 쿼리에서 ALIAS(별칭)를 사용하는 규칙은 다음과 같습니다
• WHERE: SELECT에서 정의한 ALIAS를 사용할 수 없습니다.
• GROUP BY: SELECT에서 정의한 ALIAS를 사용할 수 있습니다. 이 기능은 2023년에 도입된 것으로, GROUP BY에서도 ALIAS를 사용하는 것이 가능해졌습니다.
• HAVING: SELECT에서 정의한 ALIAS를 사용할 수 있습니다.
• ORDER BY: SELECT에서 정의한 ALIAS를 사용할 수 있습니다.

• 실행순서
SQL에서 성능 최적화를 위해 동작하는 순서를 살펴보면 다음과 같습니다.
1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. DISTINCT
7. ORDER BY
8. LIMIT

SELECT 
      job_id
    , COUNT(employee_id) AS count_employees
FROM `HR.employees` 
GROUP BY job_id
ORDER BY count_employees DESC
LIMIT 5;

SELECT 
      job_id
    , department_id
    , COUNT(employee_id) AS count_employees
FROM `HR.employees` 
WHERE salary < 10000
GROUP BY job_id, department_id
HAVING count_employees >= 5 
ORDER BY count_employees DESC;

SELECT 
      job_id
    , department_id
    , COUNT(employee_id) AS count_employees
FROM `HR.employees` 
WHERE salary < 10000
GROUP BY job_id, department_id
HAVING count_employees >= 5 
ORDER BY count_employees DESC, department_id ASC;

5.5. ROLLUP

5.5.1. GROUP BY 컬럼이 1개인 경우

GROUP BY ROLLUP(column1)

SELECT 
			job_id
    , SUM(salary) AS sal_sum
FROM `HR.employees`
GROUP BY ROLLUP(job_id)
LIMIT 10;

SELECT 
			job_id
    , SUM(salary) AS sal_sum
    , AVG(salary) AS sal_avg  
    , MIN(salary) AS sal_min
    , MAX(salary) AS sal_max
FROM `HR.employees`
GROUP BY ROLLUP(job_id)
ORDER BY sal_sum DESC, sal_avg DESC, sal_min ASC, sal_max DESC
LIMIT 10; 

5.5.2. GROUP BY 컬럼이 2개 이상인 경우

GROUP BY ROLLUP(column1, column2 ..)

SELECT 
	    job_id
    , department_id
    , manager_id
    , SUM(salary) AS sal_sum
FROM `HR.employees`
GROUP BY ROLLUP(job_id, department_id, manager_id)
LIMIT 11;

• 결합 컬럼을 사용한 ROLLUP

GROUP BY ROLLUP(column1, (column2, column3 ..))

SELECT 
      job_id
    , department_id
    , manager_id
    , SUM(salary) AS sal_sum
FROM `HR.employees`
GROUP BY ROLLUP(job_id, (department_id, manager_id))
LIMIT 10;

• 소계 없이 총계만 조회하는 방법

GROUP BY ROLLUP((column1, column2 ,column3..))

SELECT 
	    job_id
    , department_id
    , manager_id
    , SUM(salary) AS sal_sum
FROM `HR.employees`
GROUP BY ROLLUP((job_id, department_id, manager_id))
LIMIT 10;

5.5.3. ROLLUP외 그룹함수

• CUBE

GROUP BY CUBE(column1, column2 ,column3..)

SELECT
      department_id
    , job_id
    , AVG(salary) AS avg_sal
FROM `HR.employees`
GROUP BY CUBE(department_id, job_id)
LIMIT 9;

• GROUPING SETS

GROUP BY GROUPING SETS(column1, column2 ,column3..)

SELECT 
      department_id
    , job_id
    , AVG(salary) AS average_salary
FROM `HR.employees`
GROUP BY GROUPING SETS((), department_id, job_id, (department_id, job_id))
LIMIT 12;

SELECT 
      AVG(salary) AS avg_sal
FROM `HR.employees`;

 

SELECT 
      department_id
    , AVG(salary) avg_sal
FROM HR.employees
GROUP BY department_id;

 

SELECT 
      job_id
    , AVG(salary) avg_sal
FROM HR.employees
GROUP BY job_id;

SELECT 
      department_id
    , job_id
    , AVG(salary) avg_sal
FROM HR.employees
GROUP BY department_id, job_id;