25.10.24 12일차 [ SQL 기초 개념 강의_{DCL_계정 생성,확인,삭제_외부 접속 허용, | sql활용 데이터 분석, 플로우,데이터베이스,데이터 인프라 스트럭쳐, 정량/정성 데이터, sql데이터 분석,ERD,,,,
Datadesigner2025. 10. 24. 12:54
오늘은 어제 수업에 이어서 DCL을 마무리한 후
SQL활용 데이터 분석의 다양한 종류에 대한 수업을 진행하였다.
DCL
계정 생성,확인,삭제
계정 생성
외부 접속 허용
권한 삭제
sql 데이터 분석
개요
플로우
데이터베이스
데이터 인프라 스트럭쳐
정량/정성 데이터
sql 활용 데이터 분석
ERD
DCL
Data Control Language
데이터 제어 / 접근 제어관리어
특정 계정 생성-> 권한 부여 -> 제거 -> 계정 삭제 역할
sql
GRANT
REVOKE
계정 생성, 확인, 삭제
초기 계정값이다.여기서 2번 쿼리를 실행시키면결과셋에 guest1이 추가된 것을 볼 수 있다.
계정 생성 : 아이디, 비번, host 정보 세팅
접속을 위한 아이디, 비밀번호
접속 제한을 위한 host정보
특정 ip만? ip 대역폭만 =? 원격 접속 허용
local만
17번 쿼리를 확인하면 아이디@호스트 그리고 비밀번호를 입력한 것이 위와 다르다.
현재 이 상테로는 원격접속(mysql이 설치된 os 외부에서 접속시) 권한이 없다 하이디 sql 세션관리자에서도 접근이 안 되는걸 확인할 수 있다. 기본 DB외에는 조회도 되지 않는다.
cmd에서 권한 확인, 기본 DB외에는 조회되지 않는다.
% 외부 접속 허용
비밀번호와 호스트를 입력하니 위와 다르게 비밀번호값도 추가된 것을 확인할 수 있다, %를 입력해야 외부에서도 접근이 가능하다.
계정 생성, 외부 접속, 특정 DB접속
권한 부여
원격으로 다른 DB에 접근할 권한까지 부여하는 과정
1. 호스트를 %로 두어 접근이 가능한 계저을 생성한다.2. GRANT ALL PRIVILEGES ON 데이터베이스명.*(전부라는뜻) TO 계정3. guest10에 대한 권한을 보여줘라~ 라는뜻이다. 권한이 부여된것을 확인cmd에서 확인한 데이터베이스명이다. guest10 계정에 대해 t1 데이터베이스에도 접근이 가능한 것을 확인할 수 있다.
4. revoke 함수로 guest10 계정에 권한을 삭제했다, 이제 t1 DB에 접속이 안된다.
sql을 이용한 데이터 분석
개요
sql 단독으로 진행하는 분석 행위는 제한적이다.
시각화 X
예측 모델 구성 X
sql을 이용하여 데이터를 다루는 최대치만 본 수업에서 진행
데이터 전처리, 필요한 데이터 추출
플로우
분석 계획 수립
질의 (전체)
신규 고객이 얼마나 유입되었는가?
마케팅, 이벤트 이후 결과 분석
판매 추이가 어떻게 변경되었는지?(이커머스 기반)
...
이를 위한 질문의 답을 구하는 과정
타겟 선정
데이터를 생성
기존 데이터 기반 => 필요한 데이터 생성
클릭, 구매, . . .
생성된 데이터를 저장소에 저장
데이터 저장 매체, 시스템 정의
데이터 웨어하우스
기업 내 여러 조직의 데이터를 하나의 중앙저장소로 통합한 데이터베이스
데이터 스토어
쿼리를 보낼 수 있는 모든 종류의 데이터 저장 시스템 통칭
데이터 마트
특정 목적을 위해서 구축된 데이터 웨어하우스의 서브 셋
데이터분석의 타겟 형태
데이터 레이크
데이터 자체를 형태변환 X ( 원시적 형태 )
있는 그대로 저장하는 데이터베이스
데이터베이스라는 제품은 위의 형태를 구성하기 위해 1개 혹은 N개 혹은 별도의 특수목적 형태로 구성될 수 있음
데이터 레이크를 위한 별도 제품 제공
데이터 웨어하우스를 위해 클라우드 기반 제공, 엔터프라이즈급으로 제품 제공
쿼리 및 분석 -> 데이터에 대한 통찰을 얻는 과정)
탐색 : exploring -> 스키마, 출처
테이블에 대한 구조
컬럼에 대한 이해
데이터에 대한 이해, 특징 파악
출처 ( 어디서 ) 데이터가 생성?
프로파일링 : profiling
데이터 분포 ( 시각화 필수 ) (히스토그램, 빈드, 정규분포, . . .) 확인하기 위한 데이터 준비
중복 탐지, 이상값 탐지, 결측 탐지
EDA의 한 과정
정제 : cleaning
결측치 처리
오류데이터( 결측, 이상치 ) 수정 (처리)
필요시 연속 데이터의 구간화 -> 범주형 조정
EDA의 한 과정
, . . .
쉐이핑 : shaping
피벗, . . .
분석 : analyzing
데이터를 통해서 트렌드 파악, 결론 도출
인사이트 도출 ( 이를 위해서 데이터 검토 ) 질의에 대한 해답!
발표
최종 결과물을 발표, 의사 결정 자료로 제공
시각화 도구 효과적 사용
통계 / 머신러닝 등 모델을 활용해서 표현
데이터베이스 ( RDS)
행 기반
오픈 소스
mysql, protgresql, . . .
상용
mssql, oracle, . . ..
열 기반
AWS 레드시프트, 스노우플레이크 , 버티카, . . .
데이터 인프라 스트럭쳐
sql의 한계를 극복하기 위해 등장
2007년 아이폰 등장 -> 모바일 디바이스 폭발적 증가 -> 데이터 폭죽 -> 빅데이터 등장 -> 처리하기에 RDS한계 존재
2010년 전후로 등장
하돕
HDFS형식
대량의 비정형 데이터 저장
sql 비호환
nosql
key - value
Redis(고속메모리)
Redis => 세션관리 등등
Document
MongoDB ( 문서 기반, nosql 대표 제품 )
MongoDB => 로그 저장
Column - Family
Cassandra, HBase ( 하돕과 연계해서 사용 )
Gragh
sns
검색 기반 데이터 스토어
엘라스틱 서치
검색엔진 제작 시 활용
LLM기반 RAG서비스 구성할 때 벡터(자연어 -> 수치화 -> 고정크기 -> 임베딩 -> 벡터화) 기반의 엘라스틱 서치 엔진 제공 (이를 활용)
벡터 DB진영 ( 많은 제품이 존재함)
스플렁크
벡터 디비
FLASS, Pinecone, Qdrant
ChromaDB, LanceDB, . . .
정량/ 정성 데이터
정량 데이터
사람, 물건, 이벤트 등등 수치로 정량화한 데이터
고객정보, 가격, 양, 방문기간
키,몸무게 등등등
분석가가 다루는 일반적 데이터
정성 데이터
텍스트로 기록된 비정형적 데이터 (뉘앙스가 그렇다고)
의견, 서술형 내용 => 수치화 할 수 없는 데이터
예시
온도, 습도 = 정량 데이터
덥다, 춥다고 느낀다 = 정성 데이터
이를 수치화해서 분석하면?
NLP(자연어 처리)분석이 기본
빈도, 중요도, 구조, ,,, 주목도
주식
주가,환율ㅡ=, 시세 = 정량 데이터
뉴스, 심리 = 정성 데이터
외국 AI기반 펀드운영회사 -> 알고리즘 매일 수정함 -> 퀀트 직업
SQL 데이터를 이용한 분석
타겟 데이터
car_xx 데이터
ERD 제공
ERD ( Entity - Relationship Diagram )
데이터 구조화를 위한 개체 = 관계 모델링 기법
요구사항 분석 -> 모델링 (ERD 설계진행)
데이터 마트 구성 - view 생성
n개의 테이블을 join하여 가상 테이블 구성
요구사항
데이터 마트 구성
조인을 이용하여 1개의 가상테이블 구성
view name = car_mart
car_order 테이블의 모든 데이터는 포함
이하 테이블들을 join한다
car_order 기준에 일치하지 않는 데이터가 존재할 수도 있음 (결측이 나올 수 있음, b, c, d, e 테이블에서 )
컬럼
car_order
4개 컬럼 모두 포함
car_orderdetail
제품 코드
주문 수량
컬럼 신규 생성
sales_amt
주문 수량 * 가격 (판매가)
해당 주문건에 대한 총 구매비용
car_product
브랜드
모델
가격
car_store
매장 주소
car_member
성별
연령
주소
가입일
1. 데이터마트 생성 - 주문데이터는 고정-> 그리고 컬럼을 통해 확장해야한다 - left join 사용
수많은 테이블들에서 필요한 컬럼만 추출하여 새로운 가상의 테이블인 car_mart를 생성해낸다. 이 때 price는 문자열이기 때문에 replace함수를 사용해서 , 을 없애주고 cast 함수를 사용해서 수치값으로 변형시킨후 주문수량과 곱하여 한 고객이 구매한 총 금액을 출력한다. 그 이후 car_order를 기준으로 left join을 통해서 각 테이블들을 조인시켜준다. 사실 조인이 먼저고 그 이후에 결과셋 구성이긴 하다, 문자열인 금액에 주문 횟수를 곱하여 총 주문금액인 sales_amt가 새 컬럼으로 들어왔다 더 보기 쉽게 view를 만들 때에 순서를 조정해도 좋다..
구매 고객 프로파일링 분석
고객
목적 : 성별, 연령, 지역정보등을 이용하여, 구매자의 특징, 특성등을 파악
실습
연령대를 기준으로 고객을 그룹화
1차 준비 -> 연령대 파생변수 구성
파생 컬럼명 age_band
해당 컬럼을 포함한 새로운 뷰 구성
view name = user_proflie_base
1. car_mart 테이블에 있는 모든 컬럼과 데이터를 가져올 것이다. 2. case when (조건식) then else end <- 이 문법을 절대 절대 잊지 마라 이 조건구문을 이용해서 각 데이터들의 나이를 통해서 age_band라는 새로운 컬럼을 만들었다, 3, 그리고 create view () as select 구문을 이용해서 user_profile_base 테이블을 새로 만든다,
세대별 고객 수, 중복고객 제거 1명의 고객이 여러 주문을 하더라도 1명의 고객이다. 출력 컬럼 : age_band, mem_count 집계를 이용하라
결과셋에 age_band와 고객번호를 카운트한다, 이 때 distinct를 사용해서 중복을 제거해준다.
이로써 세대별 차량 구매 수를 추출했다. 여기서 알 수 있는 사실을 도출해내야한다. 1. 20대 미만은 구매가 없다-> 구매력, 나이 때문? 2. 50대, 60대 이상이 구매력이 크니 그 쪽 나이를 타겟층으로 집중해야 한다. 등등
성별로도 결과를 볼 수 있다,
같은 쿼리에서 세대값만 성별로 바꿨다. 남성이 여성보다 1.5배가 더 많다. 여기서 세대 별, 성별로 나누려면
결과셋에 다시 세대를 추가하고 group by에 성별과 세대 둘 다 작성하면 이렇게 20대 남자별, 여자별 등등으로 볼 수 있다.
성별, 세대별, 20년도, 21년도 별 구매자 수를 파악한 내용이다. 전체적으로 20년도보다 21년도에 구매력이 증가한 것을 확인 할 수 있다, 이후 선형차트 그래프 등등을 이용해서 dashboard 시각화...
이렇게 가상의 테이블 하나로 성별, 세대별, 연도별로 다양한 데이터를 정리할 수 있게 된다.
RFM_고객 세분화 분석
R : Recency : 최근성
F : Frequency : 구매 빈도
M : Menetary : 구매 금액
해당 지표, 고객 프로파일링 내용, 사내 정책(마케팅 비용) 등등 고객별 등급 선정
요구사항
car_mart에서 데이터 추출, 데이터 마트 구성 -> rfm_base
컬럼
mem_no
고객별 총 구매액 : total_amt
고객별 총 구매빈도 : total_fr
조건
2020 <= data <= 2021 년도 한정
가정
2022년 1월1일부로 고객 등급 적용하는데 필요한 데이터베이스
기본 조건에 멤버별 구매횟수, 구매액으로 가정이 되어있기때문에 먼저 group by를 무조건 사용한다고 생각해야 한다. 그리고 총 금액, 총 구매횟수니까 sum()을 사용해서 결과셋에 3개의 컬럼을 지정하고 두번째 조건 2020~ 2021년도를 where case when then end로 지정해준다 마지막에 mem_no로 그룹화해주면 완성
회원 등급 적용
rfm_base 기반으로 rfm_base_level이라는 뷰 구성
고객 등급이 반영된 데이터마트
고객 등급 조건
VVIP : 구매금액이 20억 이상 AND 구매빈도 3회 이상
VIP : 구매금액이 10억 이상 AND 구매빈도 2회 이상
GOLD : 구매금액이 5억 이상
SILVER : 구매금액이 3억 이상
BRONZE : 구매횟수가 1회이상, 기본값
STONE : 그냥 가입한 고객
요구사항
view name = rfm_base_level
컬럼
level : 등급
car_member의 모든 컬럼
rfm_base의 총구매액(total_amt)
총 구매빈도 (total_fr)
먼저 car_member와 rfm_base를 join한다는 생각을 한다. 그 이후 car_member의 별칭 cm의 모든 컬럼을 추가하고 rfm_base에서 level컬럼을 추가해준다. 이 때, case when then else end 조건구문을 사용하며 각 데이터들은 가장 상위 조건부터 통과하며 True라면 걸리고 False라면 아래로 내려온다. 그 이후 다른 컬럼을 추가하고 create view를 해주면 완성
등급별 인원수, 총 구매액, 평균 구매액, 평균 구매빈도
그저 레벨별 인원수, 총 구매액, 총 구매빈도, 평균 구매액, 구매빈도를 구한 값
구매 전환율
2020년도에 구매 기록이 존재하는데 , 2021년도에도 구매를 했다면 y 출력, 아닐 시 n 출력
view name = but_record_base
컬럼
파생컬럼 : yn
회원번호
2020년 기반 car_mart 기반 고객정보(도식상 왼쪽)
left join 수행
2021년 기반 car_mart 기반 고객정보 (오른쪽)
2020년 데이터를 기반으로 2021년 정보를 left join 수행
일치되지 않는 데이터가 존재하면 null이 세팅됨(결측치 생성)
조금 복잡하지만 새로운 명령문이 나왔을 뿐이다. 1. 먼저 car_mart에서 2020년, 2021년 두 곳에서의 데이터를 뽑아올 것이다. 이것이 from과 left join에 각각 붙어있는 서브쿼리들이다.근데 2020년 데이터를 살려야하니까 left join으로 먼저 가져와서 그 옆에 2021년 데이터를 붙이는거다,
서브쿼리 1과서브쿼리 2가 left join 된다고 보면 조금 더 알아보기 쉬울까?
그 다음 새로 나온 내용이 파생컬럼 yn에 나온 is not null 이다.
평소 사용하는 case when then else end 조건구문이지만 해석하자면 언제? b.order_date(2021년 구매 데이터)가(is) 아닐 때(not) 0이(null) 2021년의 구매데이터가 0이 아닐때, 다시 말해 구매데이터가 존재할때, 파생컬럼 yn의 값은 'y'가 되는것이다. 그리고 else로 나머지 다 걸러서 'n' 추출.
구매 주기
매장별(매장코드 집계겠지) 로 제품이 구매되는 주기를 계산
구매주기 예시
구매를 총 4회 진행하였다면
구매주기 = (최근 구매일) - (최초 구매일) / (구매 횟수 -1)
날짜 정보 = car_mart 에서 추출
view name = buy_cycle_base
컬럼
store_cd
최근구매일
최초구매일
구매횟수-1
최종 결과물 => 구매횟수 -1 의 값이 2 이상으로 제한
먼저 구매주기를 계산하기 전에 새 뷰로 정리부터 해본다. 가게 코드인 store_cd로 그룹화하여 가게별 최소 주문날짜(시간 객체는 크기를 비교할 수 있다.)와 최대 주문날짜(가장 최근 주문 날짜), 그리고 각 주문간의 간격을 계산한다. 여기서 count(주문번호)에서 - 1 을 하는 이유는 주문 주기이기 때문이다. 만약 4개를 4일동안 구매하였으면 각 물건을 구매한 그 날짜 사이의 간격은 3개이기 때문에 주문 4개 - 1 개로 3개의 간격을 나눠야하기 때문이다. 헷갈린다 그리고 group by를 진행하였으니 having으로 최소 주문 개수가 2개 이상인 가게로 한정짓는다.
buy_cycle의 모든 컬럼과 더해서 datediff 함수를 사용했다. 이는 날짜와 날짜 사이의 시간을 찾아주는 함수로 datediff( 미래 날짜 , 과거 날짜 ) 는 그 사이의 일수를 찾아준다. buy_cycle = 구매 날짜 주기 / 총 주문 개수 로 1에 가까울 수록 하루에 한개씩 팔았다는 뜻이 된다. 마지막에 순서대로 정리해주며 2012번 매장과 2011번 매장이 잘 된다는 사실을 알 수 있다.
제품 성장률
car_mart 뷰를 기반으로 생성
컬럼 총 4개
브랜드 <- 집계 대상
모델 <- 집계 대상
2020년 판매액 = total_sales_2020
2021년 판매액 = total_sales_2021
view name = product_growth_base
1. 브랜드와 모델을 집계대상으로 삼아 group by를 진행한다. 2. order_date가 2020인, 그리고 2021인 곳의 sales_amt를 집계한다 sum()을 이용해서 3. 브랜드, 모델로 그룹이 나눠지고 그 브랜드의 모델의 2020,2021년도 판매금액이 집계된다.
여기서 성장률을 보려면
2021년도 판매액 / 2020 판매액 = 그 브랜드모델의 판매대금 성장률이다. 여기서 (sum(...) -1) * 100을 해주면 퍼센트로 볼 수 있다.
이런 식으로 기존의 수업내용을 응용해서 각 모델별 성장세 또한 순위를 매길 수 있다. row_number() over () 구문을 이용해서 랭크를 매기고 over(order by 성장률 desc(내림차순)) 으로 많이 오른 순위를 매겼다.
pratition by brand를 이용해서 각 브랜드별로 순위를 매길수도 있다.
12일차에 배운 내용과 13일차에 배운 내용이 너무 겹쳐서 13일인 10.27에 정리한다.
정말 쉽지 않았다..테이블에 join에 이전에 잠깐 지나갈 때 배웠던 내용들도 생각하면서 문제를 풀어야해서
이거 어디서 봤는데 하고 이전에 정리한 내용을 찾으러 가서 이건가?하다보면 실습시간이 끝난다.
그래도 그렇게 기억해내고 다시 사용하면 기억은 확실히 난다.
이번의 경우에도 datadiff, row_number() over(), case when then else end 등등의 구문을 좀 더 외울 수 있었고 사용할 수 있었다.
주말에도 나름 복습한다고 했는데 할 때마다 새롭다 진짜
왜이렇게 맨날 새롭지? 이제 좀 질릴때도 됐는데
파이썬 sql만 2주 3주는 해야될거같은데 벌써 sql 끝나고 오늘부터는 javascript로 들어간다.
