[백문이불여일타] 고급 SQL

목차

1. DML (Data Manipulation Language)

2. ERD (Entity Relationship Diagram)

3. 서브쿼리

4. 윈도우 함수

5. 정규 표현식

6. MySQL 함수

 

 

1. DML

   1. INSERT INTO table VALUES (value list);

       INSERT INTO table (column list) VALUES (value list); : 지정하지 않은 컬럼에는 NULL값

 

   2. UPDATE table SET column = value;

       컬럼의 전체 레코드 값을 변경할 때
       여기서 =는 비교연산자가 아니라 대입연산자

       UPDATE table SET column = value WHERE condition;

       특정 행의 값만 변경할 때

 

   3. DELETE FROM table (WHERE condition);

 

 

2. ERD 

    1. entity: 개체, 테이블

    2. attribute: entity의 특징, 컬럼

        1) 컬럼 명

        2) 데이터 타입

         + PK: primary key, 1개만 가질 수 있음

            FK: foreign key, 외부 식별자, 다른 테이블에서 참조되는 키 -> 다른 테이블과의 관계 정의

    3. relationship
        1) 1 to many: orders - order_details

        2) many to many

 

• 데이터 타입
  - boolean
  - 숫자
       정수: int()
       실수: decimal() -  정밀, 돈
                double() - 대부분
                float() - 정확도 낮고 속도는 높음
  - 문자: varchar() - 다양한 글자 (이름, 주소 등)
           char() - 글자 수 일정
  - 날짜, 시간: date() - yyyy-mm-dd
                     datetime() - yyyy-mm-dd 00:00:00.000000
                     timestamp() - datetime + timezone
      -> 컬럼명은 date 이지만 타입이 string이면 함수를 이용해서 datetime형으로 바꿈

 

 

3. 서브쿼리

    1. FROM절 서브쿼리
        가상의 테이블을 만듦.

        예시 1) week, date로 group by해서 매일 발생한 범죄 count

                2) 다시 주차별로 group by 해서 평균 값 구함 (AVG(incidents_daily))

       만약 하루가 범죄가 발생하지 않았다면 AVG하면 /6으로 계산됨!

       -> 날짜가 빠질 수 있는 데이터라면 AVG 쓰지말고 sum()/7로 할 것

    2. WHERE절 서브쿼리

         = (서브쿼리) : 결과물 1개

         IN (서브쿼리) : 결과물 여러개

         예시) 가장 최근 날짜 데이터들만 보고싶을 때

 

 

4. 윈도우 함수
    1. 집계 함수
        - group by는 한 줄로 요약한 결과, row들을 합쳐서 계산 -> 결과 값이 원래 테이블에서 변형 (row 수 감소)

       - 윈도우 함수는 row 수 변화없이 원래 있던 데이터를 그대로 보여주면서 새로운 결과 값을 추가
        - FUNCTION (column) OVER (PARTITION BY column ORDER BY column)
           SUM, AVG 등등                         =GROUP BY
        - partition by/order by 는 둘 중에 하나만 있을 수도 둘 다 있을 수도 있다

• 각 부서별로 가장 많이 버는 사람?
  MAX(Salary) OVER (PARTITION BY departmentId) AS MaxSalary
• SUM(column) OVER (ORDER BY column) : 누적합
  SUM(kg) OVER (ORDER BY Line) AS CumSum
  -> Line 컬럼의 순서대로 kg 값의 누적합을 구함
• SUM(column) OVER (ORDER BY column PARTITION BY column) : partition을 기준으로 누적합
  SUM(kg) OVER (ORDER BY Line PARTITON BY id) AS CumSum
  -> 같은 id 들의 누적합을 구함, id가 바뀌면 다시 0부터 시작
  
  * but 윈도우 함수는 최신 버전 SQL에서만 지원함

 

    2. 순위 정하기
        1) ROW_NUMBER () OVER (ORDER BY column) : column을 기준으로 순위를 정함, 중복순위 X
        2) RANK () OVER (ORDER BY column) : 값이 똑같을 때 중복순위 O, 중복순위 다음 순위는 그만큼 건너 뜀
        3) DENSE_RANK () OVER (ORDER BY column) : 중복순위 O, 연속으로 순위 부여
             -> 3개 모두 함수 안에 인자가 들어가지 않음

    3. 데이터 위치 바꾸기
        1) LAG () : 이전 데이터를 가져옴
            LAG(temperature) OVER (ORDER BY recordDate) 
            -> recordDate 순서를 기준으로 temperature의 전 값을 가져옴
                 맨 앞에 null 데이터
        2) LEAD () : 다음 데이터를 가져옴
             LEAD(temperature) OVER (ORDER BY recordDate) 
            -> recordDate 순서를 기준으로 temperature의 다음 값을 가져옴
                 맨 뒤에 null 데이터

• LAG/LEAD(column, 칸 수, default) OVER (PARTITION BY column ORDER BY column)
  default: null 대신 들어가는 값

 

 

5. 정규 표현식





6. MySQL 함수









심화

• 윈도우 함수 없이 누적합 구하기 => select 서브쿼리,  조인
  SUM(kg)OVER (ORDER BY Line PARTITON BY id) AS CumSum 

SELECT e1.Id
	, e1.Name
    , e1.Line
    , (SELECT SUM(e2.kg)
    	From Elevator e2		— partition
        WHERE e1.Id = e2.Id
        	and e1.Line >= e2.Line) AS CumSum
FROM Elevator e1

SELECT e1.Id
	, e1.Name
    , e1.kg
    , e1.Line
    , SUM(e2.kg) AS CumSum
FROM Elavator e1
	INNER JOIN Elevator e2
		on e1.Id = e2.ID	— partition
        	and e1.Line >= e2.Line
GROUP BY 1,2,3,4

 

본 내용은 데이터리안 'SQL 데이터 분석 캠프 실전반' 을 수강하며 작성한 내용입니다.