데이터베이스(SELECT) - 2

2023.07.05 - [백엔드/데이터베이스] - 데이터베이스(SELECT)

데이터베이스(SELECT)

2023.07.11수업

  
● SQL SELECT

- webuser 작업, 새SQL편집기 열기

1. GROUP BY

- 데이터 분석의 첫 시작
* 목적 : 데이터 분석의 가장 기본적인 단계로 데이터들을 특정 기준으로 모아 그 기준으로 집계하여 살펴보는 것
예) 과목별 반 학생들의 전체 평균점수는 얼마인가?
   최근 10년간 장마가 계속된 기간은 얼마인가? 
데이터를 모아 특정 기준으로 판단을 해야 원하는 목적을 찾을 수 있음
이때 필요한 기초기술이 SQL을 집계해 결과를 조회하는 방법

[구문]  

    SELECT
            컬럼들...                  ---- 1)
        FROM
            테이블들...                ---- 2)
        WHERE
            검색 조건...                ---- 3)
        GROUP BY
            그룹으로 집계할 표현식       ---- 4)
        HAVING
            그룹결과에 적용될 검색 조건   ---- 5)
        ORDER BY
            정렬할 컬럼들...            ---- 6)

* 실행순서 : 
2)테이블에서 
1)원하는 컬럼을 
3)검색조건으로 검색하여 
4)그룹으로 모은 결과를
5)원하는 조건을 적용하여 남은 결과 데이터들을 
6)정렬설정에 맞추어 정렬해서 결과를 반환

* 사용방법
- 기본적으로 수집된 데이터들을 그룹으로 모아 집계함수를 이용해서 데이터를 처리한다.
- HAVING조건절을 이용하여 그룹으로 검색제한을 할 수 있다.
- WHERE절과 ORDER BY절과 같이 사용(생략 가능)
- GROUP BY에 명기된 컬럼이나 그룹을 만드는데 사용한 표현식은 반드시 SELECT컬럼절에 나타나야 (명시해야) 한다

/* GROUP BY */
-- 지하철역 이름 출력하기(STATION_NAME으로 그룹화하기)
-- 승객이 승차한 역이라는 조건을 걸어 전체 역수가 감소
-- 지하철역명이라는 그룹으로 제한되어있어 전체 출력내용(컬럼구조) 자체는 달라지지 않는다
SELECT 
		STATION_NAME
	FROM 
		SUBWAY_STATISTICS ss
	WHERE 	
		GUBUN = '승차'
	GROUP BY 
		STATION_NAME;

1-1) GROUP BY에 필터(조건) 추가하기 - HAVING
- 그룹으로 검색된 내용중에서 그룹내용에 조건을 추가할때 사용
[구문]

SELECT 컬럼들...
FROM 테이블명
WHERE 전체 테이블에 적용될 제한조건
GROUP BY 그룹으로 묶을 컬럼들
HAVING 그룹결과에 적용할 제한 조건
ORDER BY 정렬조건...

           WHERE        HAVING
범위  전체레코드      GROUP BY
조건   모든 컬럼        모든 컬럼
         집계연산 불가
         (MIN, MAX 등)    

/* GROUP BY에 필터(조건) 추가하기 - HAVING */
-- 출근시간에 가장 많이 하차하는 역 찾기
SELECT 
	STATION_NAME, BOARD_TIME, GUBUN,
	MIN(PASSENGER_NUMBER) MIN_VALUE,
	MAX(PASSENGER_NUMBER) MAX_VALUE,
	SUM(PASSENGER_NUMBER) SUM_VALUE
FROM
	SUBWAY_STATISTICS
GROUP BY
	STATION_NAME, BOARD_TIME, GUBUN
HAVING GUBUN = '하차' -- 그룹에 적용할 조건	
ORDER BY
	6 DESC,
	SUM_VALUE DESC; -- 6번 컬럼
	
-- HAVING에서 제한조건을 써야하는 진정한 이유
-- 승객수가 10000부터 15000명 사이인 역 찾기
SELECT
	STATION_NAME, BOARD_TIME, GUBUN,
	MIN(PASSENGER_NUMBER) MIN_VALUE,
	MAX(PASSENGER_NUMBER) MAX_VALUE,
	SUM(PASSENGER_NUMBER) SUM_VALUE
FROM
	SUBWAY_STATISTICS ss
-- WHERE 
	-- 집계함수 조건식을 쓸 수 없음(그룹 함수는 WHERE에서 사용 불가)
GROUP BY
	STATION_NAME, BOARD_TIME, GUBUN
HAVING SUM(PASSENGER_NUMBER) BETWEEN 10000 AND 15000	
ORDER BY
	6 DESC;

* 집계함수(Aggregate Function)
- GROUP BY로 필터된 컬럼의 여러 레코드들을 입력값으로 하여 한 개의 원하는 결과(관점)을 반환하는 함수

[대표적 집계함수]
ㄱ. COUNT(expr) : expr의 전체 개수를 구한다. 
-   일반적으로 expr에 '*'기호 사용, SELECT문으로 조회한 전체 데이터건수를 의미(NULL값 포함)
ㄴ. MAX(expr) : expr의 최댓값 반환
ㄷ. MIN(expr) : expr의 최솟값 반환
ㄹ. SUM(expr) : expr의 합계 반환
ㅁ. AVG(expr) : expr의 평균값 반환
ㅂ. VARIANCE(expr) : expr의 분산을 반환
ㅅ. STDDEV(expr) : expr의 표준편차를 반환

-- 집계함수 사용해보기
SELECT 
	COUNT(*) "전체레코드 수",
	MIN(PASSENGER_NUMBER) "최저 승객수",  -- 공식적인 컬럼 이름이 없음
	MAX(PASSENGER_NUMBER) "최고 승객수", 
	SUM(PASSENGER_NUMBER) "누적 승객수",
	AVG(PASSENGER_NUMBER) "평균 승객수" -- 최저, 최고 승객수, 평균이 의미가 있나?
FROM
	SUBWAY_STATISTICS ss;
-- 집계함수는 GROUP BY로 관련집합을 구하고 같이 사용해야 의미가 있음
-- GROUP BY + 집계함수	
-- 의미가 있는 예제
SELECT 
	STATION_NAME,
	COUNT(*) "역당 가진 레코드 수",
	MIN(PASSENGER_NUMBER) "역당 가장 작은 승객수",
	MAX(PASSENGER_NUMBER) "역당 가장 많은 승객수",
	SUM(PASSENGER_NUMBER) "역당 총 이용승객수",
	AVG(PASSENGER_NUMBER) "역당 평균 이용객수" 
FROM 
	SUBWAY_STATISTICS ss 
WHERE 
	GUBUN = '승차'
GROUP BY 
	STATION_NAME
ORDER BY
	STATION_NAME;

-- 고속터미널역에서 승차한 레코드 예 보기
SELECT * FROM SUBWAY_STATISTICS ss 
	WHERE GUBUN = '승차' AND STATION_NAME LIKE '고속%'
	ORDER BY PASSENGER_NUMBER ASC;

-- 역당 이용객수가 많은 역 중에서 '구로디지털단지(232)' 분석(GROUP BY 활용)
SELECT
	STATION_NAME, BOARD_TIME, GUBUN,
	MIN(PASSENGER_NUMBER) MIN_VALUE,
	MAX(PASSENGER_NUMBER) MAX_VALUE,
	SUM(PASSENGER_NUMBER) SUM_VALUE
FROM
	SUBWAY_STATISTICS ss
WHERE 
	STATION_NAME IN ('구로디지털단지(232)')
GROUP BY
	STATION_NAME, BOARD_TIME, GUBUN
ORDER BY -- 정렬
	STATION_NAME, BOARD_TIME, GUBUN;

* (참고)IN

- WHERE IN 절은 주어진 값 집합 중 하나와 일치하는 레코드를 검색하고, 여러 값을 검색할 때 사용 
- 이산데이터
- OR, BETWEEN과 유사

[구문]

SELECT column1, column2, ...
    FROM table_name
    	WHERE column_name IN (value1, value2, ...);

- 컬럼이 value1이거나 value2중 하나인 레코드를 검색