2021.08.10 TIL

새롭게 배운 것 Done

운동

독서

알고리즘

SQL 고득점 kit - GROUP BY 예제 - 입양 시각 구하기(2)

MYSQL WITH 재귀(RECURSIVE) 쿼리

• MySQL 5.7 이하는 미지원
• 메모리 상에 가상 테이블 저장
• 재귀 쿼리를 이용하여 실제로 테이블을 생성하거나 데이터를 삽입하지 않아도 가상 테이블 생성 가능

  WITH RECURSIVE 테이블명 AS
  (
  SELECT 초기값 AS 컬럼별명1
  UNION ALL
  SELECT 컬럼별명1 계산식 FROM 테이블명 WHERE 제어문
  )
  

• ANIMAL_OUTS 테이블의 DATETIME 컬럼은 입양일을 나타냄
• 0시부터 23시까지 각 시간대별로 입양이 몇 건이나 발생했는지 조회, 시간대 순으로 조회

WITH RECURSIVE REC AS
(
    SELECT 0 AS 시간
    UNION ALL
    SELECT 시간 + 1 FROM REC WHERE 시간 < 23
)
SELECT REC.시간, COUNT(HOUR(OUTS.DATETIME)) AS 건수
FROM REC
LEFT JOIN ANIMAL_OUTS as OUTS on REC.시간 = HOUR(OUTS.DATETIME)
GROUP BY REC.시간;

• WITH RECURSIVE로 가상 테이블을 만들고 0시부터 23시까지 해당되는 컬럼
  • 건수가 존재하지 않는 시간대까지 표기 하기 위해 사용
• DATETIME TYPE의 컬럼에서 시간대 별 COUNT를 구하기 위해 HOUR()사용
• LEFT JOIN으로 가상 테이블과 ANIMAL_OUTS를 JOIN
  • 시간대를 기준으로 JOIN
• 결과 - 예약어 혼동을 방지하기 위해 컬럼 명을 한글로 명시

코딩

MySQL

• 오픈소스, 썬 -> 오라클 인수
• MariaDB는 MySQL과 호환 되는 오픈소스
  • 인터페이스는 MySQL과 동일하나 성능은 더 좋음
• Community Server(무) / Enterprise Server(유)
• 한 때 웹 개발 표준 기술 스택 중 하나
  • LAMP(Linux, Apache, MySQL, PHP)
• 지금도 Postgres와 함꼐 가장 널리는 프로덕션용 관계형 베이스
• 용량 증대(Scaling) 방식 1 - Scale-Up: 서버에 CPU와 Memory 추가
  • 불필요한 데이터가 있는 것은 아닌가 확인할 필요가 있음
• 용량 증대(Scaling) 방식 2 - Scale-Out: Master-Slave 구성
  • 일반적 클러스터 구성 but MySQL은 지원 X
  • ⏩ SQL 하나(master)가 갖고 있는 내용을 다수의 서버(slave)에 복사하기, 읽기 전용에 더 큰 용량을 주는 방식

클라우드

• 컴퓨팅 자원을 네트워크를 통해 서비스 형태로 제공
• 자원을 필요한만큼 실시간 할당, 사용한 만큼 지불
• 탄력적으로 자원 유지하는 것이 중요
• 클라우드 컴퓨팅이 없다면?
  • 서버/네트워크/스토리지 구매와 설정을 직접 수행
  • 피크 타임을 기준으로 Capacity planning 해야 함
• 클라우드 컴퓨팅 장점
  • 초기 투자 비용 줄어듬
    • CAPEX(Capital Expenditure, 자본적 지출, 초기 투자 비용) «< OPEX(Operating Expense, 운영 지출, 영업 비용, 유지 보수 비용)
  • 리소스 준비를 위한 대기 시간 대폭 감소
    • Shorter Time to Market
  • 노는 리소스 제거 -> 비용 감소
  • 글로벌 확장 용이
  • 소프트웨어 개발 시간 단축
    • Managed Service(SaaS - Software as a Service) 이용

AWS

• 가장 큰 클라우드 컴퓨팅 서비스 업체
• 다양한 종류의 소프트웨어/플랫폼 서비스 제공
• EC2 - Elastic Cloud Compute
  • AWS 서버 호스팅 서비스
• S3 - Simple Storage Service
  • 대용량 클라우드 스토리지 서비스
• RDS - Relational Database Service
  • AWS가 제공해주는 다양한 관계형 데이터베이스 서비스
  • MySQL, MariaDB 제공
  • 다양한 NoSQL 제공

Docker

• 특정 프로그램과 그 프로그램을 실행하는데 필요한 기타 소프트웨어들을 하나의 패키지로 만들어서 해당 프로그램의 개발과 사용을 도와주는 오픈소스 플랫폼
• 패키지를 파일 시스템 형태로 제공 -> Docker Image
  • 공유 가능 - 공유소: Docker Registry(Docker Hub)
  • 이미지 실행: Docker Container 응용 프로그램

테이블 필드의 중요 속성

Primary Key

• 테이블에서 레코드 유일성 정의하는 필드
• Composite primary key: primary key가 두 개 이상 필드로 정의되는 경우
• Primary Key unique constraint: primary key로 지정된 필드가 있는 경우 데이터베이스 단에서 중복된 값을 갖는 레코드가 생성되는 경우를 방지
• AUTO_INCREMENT: id 값 순차적으로 자동 생성됨

Foreign Key

• 테이블의 특정 필드가 다른 테이블의 필드에서 오는 값을 갖는 경우

NOT NULL

• 필드값 항상 존재

DEFAULT value

• 필드에 값 주어지지 않는 경우 기본값 정의
• timestamp 타입의 경우 CURRENT_TIMESTAMP 사용하면 현재 시간으로 설정됨

예제 데이터 설명 및 sql로 테이블 생성

CREATE TABLE session (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  channel_id INT NOT NULL,
  --PRIMARY KEY(id), 이렇게 여기서 기본키 설정도 가능
  FOREIGN KEY(channel_id) REFERENCES channel(id)
);

CREATE TABLE channel (
  id INT AUTO_INCREMENT PRIMARY KEY,
  channel VARCHAR(32) NOT NULL
);

깨달은 점 FEELING

• 강의에서 Docker에 MySQL을 설치하는 실습이 있어서 흥미로웠다. 사실 이미 Ubuntu 로컬에 MySQL을 설치해놔서 추가적으로 설치하지는 않았지만 도커에 설치하는 법을 알아 두는 것도 좋을 것 같다.
• AWS EC2 인스턴스에 올려놓은 토이 프로젝트가 있었는데 잊고 있다가 이번 수업 때 AWS 수업을 들으면서 생각나서 확인해봤다. 다행히 과금은 되고 있지 않았다! 조만간 AWS를 쓰면서 여러 가지 설정들을 다시 하게 될텐데 예전의 기억을 더듬어야 할 것 같다. 이번엔 미래를 위해 정리를 잘 해놔야겠다.
• 쿼리 문제를 풀면서 가상 테이블을 만드는 쿼리를 알게 되었다. WITH으로 가상 테이블을 만들고 RECURSIVE를 사용해서 재귀를 활용한다는 내용인데 신기했다. 나는 기준이 되는 필드의 값이 0이 될 때의 값을 표현해야 한다는 생각에 사로잡혀서 IFNULL 이런 명령어만을 떠올렸는데 가상테이블로 미리 만들어놓고 조인 하는 방법이 훨씬 효율적인 것 같다.