SQL 심화학습

1-1. 크리에이터 및 콘텐츠 소개

빅쿼리 : 빅데이터를 분석하고 관리하는 클라우드 기반 데이터 웨어하우스.

구글 클라우드 플랫폼(GCP, Google Cloud Platform)에서 사용할 수 있다.

클라우드 서비스이므로 인터넷만 연결된다면 어디서든 사용할 수 있다.

장점 : 데이터 유실 위험이 적을 뿐 아니라 테라바이트급 쿼리를 초 단위, 페타바이트급 쿼리는 분 단위로 빠르게 처리가능.

 

빅쿼리의 일반적인 용도

• 생성형 AI
• 데이터 웨어하우스 마이그레이션
• BigQuery로 데이터 전송
• 모든 데이터 유형의 가치 창출
• 사전 구성된 데이터 솔루션
• 실시간 분석
• 예측 분석
• 로그 분석
• 마케팅 분석
• 데이터 클린룸

https://cloud.google.com/bigquery?hl=ko#data-clean-rooms

 

1-2. 들어가며 ~ 1-3.빅데이터를 지탱하는 툴과 기술

• 빅데이터를 지탱하는 기술들
  스몰 데이터 : 관계형 데이터베이스 → SQL로 데이터 추출 → 분석/정리
  빅 데이터의 등장으로 관계형/비관계형(MongoDB, Cassandra) 데이터베이스 → (분산처리 시스템) → SQL로 데이터 추출 → 분석/정리
• 비관계형 데이터베이스 의 장점 
  유연한 데이터 모델, 다양한 데이터베이스 유형, 확장성(수평 확장성), 성능(빠른 읽기, 쓰기)
  

  

  수평적 확장: 새로운 서버 추가, 수직적 확장 : 서버의 리소스, 용량, 스펙을 늘려 성능을 향상

• 데이터 웨어하우스(데이터 창고) 에서 분산처리 시스템(실시간 데이터 처리) 

SQL은 분산처리 시스템 및 데이터 웨어하우스 에서 데이터 추출에 모두 사용된다.

작은 규모의 데이터 셋, 마틴 린드스트롬은 스몰 데이터를 "개인의 취향과 필요, 생활 양식 등의 사소한 행동에서 나오는 데이터"로 정의로 고객에 대한 사소한 정보까지 담을 수 있는 데이터로, 획기적인 아이디어가 탄생하고 브랜드를 변화시킬 수 있는 데이터

1- 4. 빅쿼리 실습 환경 설정

구글창에서 GCP검색후 진행 결재를 등록하고 무료시작하기로 일반활성화를 하면 안됨.

dev-aileron-447402-k9.modulabs.ex1 데이블 생성, 데이터 삽입에 동일하게 설정

 

쿼리 결과 테이블을 결과저장으로 CSV, Google Sheets로 저장하고 나중에 다시 불러 사용할 수 있다.

쿼리 저장이후 기존에 입력한 쿼리문은 주석처리 (단축키 윈도우: Ctrl + / , 맥: Command+ / )

 

※. Google BigQuery에서 쿼리 저장시  "리전" (Region) 항목은 데이터를 저장하고 처리하는 물리적 데이터 센터의 위치를 지정하는 설정. 이 항목은 BigQuery에서 쿼리 실행, 데이터 저장 및 처리, 데이터 보안 등에 중요한 역할을 합니다.

퀴리저장(기존)으로 할 경우 이름, 개인 을 설정 할 수 있습니다.

 

• 총 주문 금액이 높은 상위 3명의 손님 리스트를 출력하는 쿼리문
  SELECT user_id, SUM(price) AS total_spent
        FROM dev-aileron-447402-k9.modulabs.ex2
        GROUP BY user_id
  ORDER BY total_spent DESC LIMIT 3;

• 각 테이블의 식사 금액이 전체 평균 식사 금액보다 많은 경우를 찾아내어, 특히 많은 양의 음식을 주문한 큰손 손님들을 식별

WITH AverageBill AS

  (SELECT AVG(total_bill) AS avg_bill

  FROM dev-aileron-447402-k9.modulabs.ex3)

SELECT *

  FROM dev-aileron-447402-k9.modulabs.ex3 ex3, AverageBill

  WHERE ex3.total_bill > AverageBill.avg_bill;

데이터셋 내에서 각 행의 total_bill 값이 전체 total_bill의 평균(avg_bill)보다 큰 경우를 필터링하여 반환하는 역할

 

• 판매량 합계가 가장 많은 ‘제품 라인(Product Line) 을 찾기

SELECT  PRODUCT_LINE,  -- 나타내고하 하는 Column

   SUM(TOTAL_ORDER) AS TotalOrders  -- Total_order Column을 TotalOrders라고 함.

  FROM dev-aileron-447402-k9.modulabs.ex4   -- 테이블 ex4에

  GROUP BY PRODUCT_LINE    

  ORDER BY TotalOrders DESC 

  -- 데이터를 PRODUCT_LINE별로 그룹화한 후, 그룹의 특정 기준(예: TotalOrders)을 기준으로 내림차순으로 정렬

LIMIT 1;

 

예재)

리뷰를 가장 많이 남긴 회원의 리뷰를 조회

 

예재)

진료안된 환자중 예약을 취소하지 않은 다음 진료예약환자 찾기

 

SELECT 원하는 컬럼 명
FROM 테이블 명
WHERE 조건절
ORDER BY 컬럼 명 DESC
LIMIT 숫자

 

데이터 정렬

SELECT 집계함수(컬럼 명) AS 별칭
FROM 테이블 명 

SELECT 컬럼 명
FROM 테이블 명 
WHERE 조건절
GROUP BY 컬럼 명
ORDER BY 컬럼 명

데이터 그룹화

SELECT 컬럼명
FROM 테이블명 AS A
JOIN 테이블명 AS B
ON A.id = B.id
JOIN 테이블명 AS C
ON A.id = C.id

3개의 테이블을 JOIN할 경우

WITH 임시 테이블 명 AS (
  WITH 문으로 저장하고 싶은 SQL 쿼리문
)
SELECT * 
FROM 임시 테이블 명 

WITH문은 쿼리문의 가독성을 높이고 쿼리문을 반복하여 작성해야할 때