00: 마케터가 데이터분석도 해? 나는 그것도 해! 이렇게 해야 너를 대체할 수 없는 고급 인력이 된단다.
오케바리~~
SQL 기본문법
1. MariaDB란?
- MySQL에서 파생된 오픈 소스 관계형 데이터베이스 관리 시스템(RDBMS)
- MySQL과의 높은 호환성을 유지하면서도 성능 개선, 보안 강화, 새로운 스토리지 엔진 등의 기능을 제공
2. 데이터베이스(Database)란?
- 데이터베이스는 체계적으로 데이터를 저장하고 관리하는 시스템. 데이터베이스는 여러 개의 테이블로 구성되며, 테이블에는 다양한 형태의 데이터가 저장된다.
- 관계형 데이터베이스(RDBMS, Relational Database Management System)
- 데이터가 표(테이블) 형태로 저장되며, 테이블 간의 관계를 정의하여 관리
- 예) MariaDB, MySQL, PostgreSQL, Oracle, MSSQL 등
- 비관계형 데이터베이스(NoSQL, Not Only SQL)
- JSON, 키-값, 그래프 형태로 데이터를 저장하며, 스키마가 고정되지 않아 유연한 데이터 처리가 가능합니다.
- 예) MongoDB, Redis, Cassandra 등
3. SQL(Structured Query Language) 개요
- SQL은 관계형 데이터베이스에서 데이터를 관리하기 위한 언어이다.
- 주요 SQL 명령어
- DDL (Data Definition Language, 데이터 정의 언어)
- 데이터베이스, 테이블 등의 구조를 정의하는 명령어
- 예) CREATE, ALTER, DROP, TRUNCATE
- DML (Data Manipulation Language, 데이터 조작 언어)
- 데이터를 조회, 삽입, 수정, 삭제하는 명령어
- 예) SELECT, INSERT, UPDATE, DELETE
- DCL (Data Control Language, 데이터 제어 언어)
- 데이터 접근 권한을 제어하는 명령어
- 예) GRANT, REVOKE
- TCL (Transaction Control Language, 트랜잭션 제어 언어)
- 데이터베이스 내의 트랜잭션을 제어하는 명령어
- 예) COMMIT, ROLLBACK, SAVEPOINT
- DDL (Data Definition Language, 데이터 정의 언어)
4. Ubuntu에서 MariaDB root 계정으로 접속
1) MariaDB 설치 확인 및 시작
MariaDB가 설치되지 않은 경우 다음 명령어로 설치
sudo apt update
sudo apt install mariadb-server -y
설치 후 MariaDB 서비스를 시작하고 자동 실행을 설정
sudo systemctl start mariadb
sudo systemctl enable mariadb
2) MariaDB 보안 설정 (최초 실행 시-설정하지 않아도 됨)
sudo mysql_secure_installation
이 과정에서 다음을 설정할 수 있다.
- root 비밀번호 설정
- 익명 사용자 제거
- 원격 root 접속 비활성화
- 기본 테스트 데이터베이스 삭제
3) MariaDB root 계정으로 접속
sudo mysql -u root
- sudo를 사용하면 비밀번호 입력 없이 root 계정으로 MariaDB에 접속된다
- mysql -u root -p 명령을 사용하면 비밀번호 입력이 필요
5. 사용자 계정 관리
1) 사용자 계정 생성 및 비밀번호 설정
-- 사용자 계정 생성 및 비밀번호 설정
CREATE USER 'user1'@'localhost' IDENTIFIED BY '1234';
- 'user1'@'localhost' 사용자를 생성하고 비밀번호를 'password123'으로 설정
- 이 사용자는 로컬 서버(localhost)에서만 접속할 수 있다.
2) 사용자 삭제
-- 사용자 삭제
DROP USER 'user1'@'localhost';
- 'user1'@'localhost' 계정을 삭제
- 삭제된 사용자는 더 이상 MariaDB에 접속할 수 없다.
3) 비밀번호 변경
(1) root 계정에서 특정 사용자의 비밀번호 변경
-- 특정 사용자의 비밀번호 변경
ALTER USER 'user1'@'localhost' IDENTIFIED BY 'newpassword123';
- user1의 비밀번호를 'newpassword123'으로 변경
(2) 현재 로그인한 사용자가 자신의 비밀번호 변경
-- 로그인한 사용자가 직접 비밀번호 변경
SET PASSWORD = PASSWORD('newpassword123');
- 현재 로그인한 사용자의 비밀번호를 변경
4) 사용자 권한 관리
(1) 특정 데이터베이스에 대한 모든 권한 부여
-- 모든 권한 부여
GRANT ALL PRIVILEGES ON mydb.* TO 'user1'@'localhost';
- mydb 데이터베이스의 모든 권한을 user1에게 부여
(2) 특정 권한만 부여 (예: 읽기 및 쓰기 권한)
-- 특정 권한 부여
GRANT SELECT, INSERT, UPDATE ON mydb.* TO 'user1'@'localhost';
- user1은 SELECT, INSERT, UPDATE 명령어만 사용할 수 있다.
(3) 특정 권한 회수
-- 특정 권한 제거
REVOKE INSERT, UPDATE ON mydb.* FROM 'user1'@'localhost';
- user1에게 부여된 INSERT, UPDATE 권한을 제거한다.
(4) 권한 변경 사항 적용 (필수 실행)
FLUSH PRIVILEGES;
- 새로운 권한 설정이 즉시 반영되도록 적용
5) 사용자 계정 목록 조회
-- 등록된 사용자 계정 목록 확인
SELECT User, Host FROM mysql.user;
- MariaDB에 등록된 사용자 목록을 확인할 수 있습니다.
6) 정리된 명령어 목록
| 명령어 | 설명 |
| sudo mysql -u root | Ubuntu에서 root 계정으로 MariaDB 접속 |
| CREATE DATABASE mydb; | 데이터베이스 생성 |
| DROP DATABASE mydb; | 데이터베이스 삭제 |
| CREATE USER 'user1'@'localhost' IDENTIFIED BY 'password123'; | 사용자 계정 생성 |
| DROP USER 'user1'@'localhost'; | 사용자 계정 삭제 |
| ALTER USER 'user1'@'localhost' IDENTIFIED BY 'newpassword123'; | 특정 사용자의 비밀번호 변경 |
| SET PASSWORD = PASSWORD('newpassword123'); | 로그인한 사용자의 비밀번호 변경 |
| GRANT ALL PRIVILEGES ON mydb.* TO 'user1'@'localhost'; | 특정 데이터베이스의 모든 권한 부여 |
| GRANT SELECT, INSERT, UPDATE ON mydb.* TO 'user1'@'localhost'; | 특정 권한만 부여 |
| REVOKE INSERT, UPDATE ON mydb.* FROM 'user1'@'localhost'; | 특정 권한 회수 |
| FLUSH PRIVILEGES; | 권한 변경 사항 즉시 적용 |
| SELECT User, Host FROM mysql.user; | 사용자 목록 조회 |
SQL 용어 정리
1. 데이터베이스 (Database)
- 데이터를 체계적으로 저장하고 관리하는 공간.
- 여러 개의 테이블(Table)과 관계(Relationship)를 포함할 수 있음.
- 실제 사용 예시
CREATE DATABASE growth_marketing_db;
- growth_marketing_db라는 새로운 데이터베이스를 생성.
USE growth_marketing_db;
- 현재 사용 중인 데이터베이스를 growth_marketing_db로 변경.
- 그로스 마케팅에서의 활용 상황
- 마케팅 캠페인 데이터를 저장하는 전용 데이터베이스를 만들어, 광고 성과 데이터, 고객 행동 데이터, 이메일 캠페인 성과 등을 한 곳에서 관리할 수 있다.
2. DBMS (Database Management System)
- 데이터베이스를 관리하는 소프트웨어 시스템.
- SQL을 실행하여 데이터 저장, 검색, 수정, 삭제, 권한 부여 등의 기능을 수행.
- 대표적인 DBMS
- 관계형 데이터베이스: MySQL, PostgreSQL, MariaDB, Oracle, SQL Server
- 비관계형(NoSQL) 데이터베이스: MongoDB, Firebase, DynamoDB
- 실제 사용 예시
SHOW DATABASES;
- 현재 DBMS에서 관리하는 모든 데이터베이스 목록을 조회.
3. 스키마 (Schema)
- 데이터베이스의 구조(테이블, 뷰, 인덱스 등)를 정의하는 설계도.
- 데이터베이스 내에서 테이블의 구성, 관계 및 제약 조건을 포함.
CREATE SCHEMA marketing_data;
- marketing_data라는 새로운 스키마를 생성.
스키마의 역할
- 스키마(Schema)는 데이터베이스의 구조와 제약 조건을 정의하는 논리적 설계.
- 테이블, 뷰(View), 인덱스(Index), 트리거(Trigger), 저장 프로시저(Stored Procedure), 제약 조건(Constraint) 등을 포함한다.
- 데이터베이스의 전체적인 구조를 나타내며, 데이터가 어떻게 저장되고 관리되는지를 결정.
4. 테이블 (Table)
- 데이터를 행(Row)과 열(Column)으로 저장하는 구조.
- 관계형 데이터베이스의 기본 단위.
CREATE TABLE ad_campaigns (
id INT PRIMARY KEY AUTO_INCREMENT,
campaign_name VARCHAR(255),
platform VARCHAR(50),
impressions INT,
clicks INT,
conversions INT,
cost DECIMAL(10,2),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
- ad_campaigns 테이블을 생성하여 광고 캠페인 데이터를 저장.
- 각 광고 캠페인의 성과(노출 수, 클릭 수, 전환 수, 광고비용)를 저장하고, 캠페인 효과를 측정할 수 있다.
- 광고 성과 데이터 (ad_performance) 테이블 생성 및 초기 데이터 생성 연습
| ID | 캠페인 ID | 노출 수 (Impressions) |
클릭 수 (Clicks) |
전환 수 (Conversions) |
광고비용 (Cost) |
수익 (Revenue) |
생성일 (Created At) |
| 1 | 101 | 50000 | 1200 | 150 | 500.00 | 1500.00 | 2025-02-09 12:00:00 |
| 2 | 102 | 30000 | 800 | 90 | 300.00 | 1000.00 | 2025-02-09 12:10:00 |
| 3 | 103 | 60000 | 1500 | 200 | 600.00 | 1800.00 | 2025-02-09 12:20:00 |
CREATE TABLE ad_performance (
id INT PRIMARY KEY AUTO_INCREMENT,
campaign_id INT NOT NULL,
impressions INT NOT NULL,
clicks INT NOT NULL,
conversions INT NOT NULL,
cost DECIMAL(10,2) NOT NULL,
revenue DECIMAL(10,2) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO ad_performance (campaign_id, impressions, clicks, conversions, cost, revenue, created_at)
VALUES
(101, 50000, 1200, 150, 500.00, 1500.00, '2025-02-09 12:00:00'),
(102, 30000, 800, 90, 300.00, 1000.00, '2025-02-09 12:10:00'),
(103, 60000, 1500, 200, 600.00, 1800.00, '2025-02-09 12:20:00');
- 데이터 타입과 옵션
- VARCHAR(100)
- VARCHAR는 가변 길이 문자열을 저장하는 데이터 타입입니다.
괄호 안의 숫자(여기서는 100)는 이 문자열 필드에 저장할 수 있는 최대 문자 수를 의미
- VARCHAR는 가변 길이 문자열을 저장하는 데이터 타입입니다.
- INT
- 정수를 저장하는 데이터 타입으로 음수나 소수점이 없는 정수만 저장할 수 있다.
- DECIMAL(10,2)
- DECIMAL은 고정 소수점 숫자를 저장하는 데이터 타입
- 괄호 안의 첫 번째 숫자(10)는 전체 자릿수를 의미하고, 두 번째 숫자(2)는 소수점 아래 자릿수를 의미.
- DATE DEFAULT (CURRENT_DATE)
- DATE는 날짜를 저장하는 데이터 타입이다.
- DEFAULT (CURRENT_DATE)는 이 필드에 값이 지정되지 않았을 경우, 자동으로 현재 날짜를 기본값으로 사용하라는 의. 즉, 입사일이 명시되지 않으면 데이터가 입력된 날의 날짜가 자동으로 사용
- VARCHAR(100)
5. 레코드 (Record)
- 테이블에서 하나의 데이터 행(Row)을 의미.
- 테이블의 한 개 데이터 항목을 표현.
INSERT INTO ad_campaigns (campaign_name, platform, impressions, clicks, conversions, cost)
VALUES ('Black Friday Sale', 'Facebook', 100000, 5000, 200, 1500.00);
- ad_campaigns 테이블에 하나의 광고 캠페인 데이터를 삽입.
- 새로운 광고 캠페인이 시작될 때마다 자동으로 데이터가 추가되어 마케팅 분석에 활용
6. 필드 (Field)
- 테이블의 컬럼(Column)으로, 각 레코드의 속성(attribute)을 의미.
SELECT campaign_name, impressions, clicks FROM ad_campaigns;
- campaign_name, impressions, clicks 필드만 조회.
7. 인덱스 (Index)
- 데이터 검색 속도를 높이기 위해 특정 컬럼에 생성하는 자료구조.
CREATE INDEX idx_campaign_name ON ad_campaigns(campaign_name);
- campaign_name 컬럼에 대한 인덱스를 생성하여 검색 속도를 향상.
- 대량의 광고 데이터를 검색할 때 인덱스를 활용하면 조회 속도를 크게 단축할 수 있다.
8. 뷰 (View)
- 특정 SQL 쿼리 결과를 저장하는 가상의 테이블.
CREATE VIEW high_conversion AS
SELECT campaign_name, platform, conversions
FROM ad_campaigns
WHERE conversions > 100;
- 전환(conversions)이 100 이상인 캠페인만 저장하는 뷰 생성.
- 성과가 높은 캠페인만 따로 관리하여 최적화 전략을 수립할 수 있다.
9. 키 (Key)
- 데이터의 무결성과 중복 방지를 위해 사용하는 속성.
CREATE TABLE customers (
id INT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(255) UNIQUE
);
- id를 기본 키로 설정하여 중복 방지.
- 각 고객을 고유하게 식별하여 개인화된 마케팅을 제공할 수 있다.
10. 트랜잭션 (Transaction)
- 여러 SQL 연산을 하나의 단위로 묶어 일관성을 유지하는 개념.
START TRANSACTION;
UPDATE ad_campaigns SET cost = cost * 1.1 WHERE platform = 'Google';
COMMIT;
- Google 플랫폼의 광고 비용을 10% 인상하고 변경 내용을 저장.
11. 제약 조건 (Constraint)
- 데이터 무결성을 유지하기 위해 테이블에 적용하는 규칙.
- 고객 데이터 관리에서 이메일 중복 방지, 미성년자 가입 차단, 가입 날짜 자동 설정 등의 역할을 수행할 수 있다.
- 종류:
- PRIMARY KEY: 테이블에서 각 행을 고유하게 식별하는 키.
- FOREIGN KEY: 다른 테이블의 기본 키를 참조하는 키.
- NOT NULL: 해당 컬럼이 반드시 값을 가져야 함.
- UNIQUE: 중복된 값을 허용하지 않음.
- CHECK: 특정 조건을 만족하는 값만 허용.
- DEFAULT: 기본값을 설정하여 데이터 입력이 없을 경우 자동 적용.
CREATE TABLE customers (
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE,
age INT CHECK (age >= 18),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
- id는 기본 키로 중복 불가.
- name은 NULL을 허용하지 않음.
- email은 고유한 값만 허용.
- age는 18세 이상이어야 함.
- created_at은 기본적으로 현재 시간이 입력됨.
12. 정규화 (Normalization)
- 데이터 중복을 최소화하고 데이터 무결성을 유지하기 위해 테이블을 구조적으로 최적화하는 과정.
- 단계:
- 제1정규형(1NF): 모든 컬럼이 원자값(Atomic Value)만 포함해야 함.
- 제2정규형(2NF): 기본 키의 부분 종속 제거 (모든 컬럼이 기본 키에 완전히 종속되어야 함).
- 제3정규형(3NF): 이행적 종속 제거 (A → B, B → C 관계에서 A → C 종속성 제거).
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
name VARCHAR(50),
email VARCHAR(100) UNIQUE
);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
- orders 테이블에서 customer_id를 분리하여 데이터 중복을 최소화함.
13. 조인 (Join)
- 여러 테이블을 연결하여 데이터를 조회하는 방법.
- 종류:
- INNER JOIN: 두 테이블에서 공통된 데이터만 조회.
- LEFT JOIN: 왼쪽 테이블의 모든 데이터와 오른쪽 테이블의 일치하는 데이터 조회.
- RIGHT JOIN: 오른쪽 테이블의 모든 데이터와 왼쪽 테이블의 일치하는 데이터 조회.
- FULL OUTER JOIN: 두 테이블의 모든 데이터 조회.
SELECT customers.name, orders.order_date
FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id;
- 고객 테이블과 주문 테이블을 조인하여 고객 이름과 주문 날짜를 조회.
14. 서브쿼리 (Subquery)
- 하나의 SQL문 내에서 또 다른 SQL문을 실행하는 방식.
- 캠페인 성과 분석에서 평균 이상의 클릭률(CTR)을 기록한 광고만 선별하여 보고할 때 활용할 수 있다.
- 사용 목적:
- 특정 조건을 만족하는 데이터를 검색할 때 활용.
- 상위 쿼리의 필터링 역할 수행.
- 특정 값 계산 후 필터링 수행.
SELECT campaign_name
FROM ad_campaigns
WHERE impressions > (SELECT AVG(impressions) FROM ad_campaigns);
- 광고 캠페인 중 평균 노출 수(impressions)보다 높은 캠페인만 조회.
15. 머티리얼라이즈드 뷰 (Materialized View)
- 일반 뷰(View)와 달리 물리적으로 저장되는 뷰.
- 대량의 광고 데이터를 실시간으로 분석하는 경우, 반복 실행되는 복잡한 쿼리의 실행 속도를 최적화하는 데 사용
- 특징:
- 일반 VIEW는 가상의 테이블이지만 MATERIALIZED VIEW는 쿼리 실행 결과를 테이블 형태로 저장하여 조회 성능을 향상시킴.
- 일정 간격으로 REFRESH가 필요함.
CREATE MATERIALIZED VIEW campaign_summary AS
SELECT platform, AVG(conversions) AS avg_conversions
FROM ad_campaigns
GROUP BY platform;
- ad_campaigns 테이블을 기반으로 플랫폼별 평균 전환율을 미리 계산하여 저장.
테이블 생성 및 데이터 삽입
데이터 수정 및 삭제
1. 테이블 생성 (CREATE TABLE)
CREATE TABLE employees (
id INT AUTO_INCREMENT PRIMARY KEY, -- 자동 증가하는 기본 키
name VARCHAR(100) NOT NULL, -- 이름 (필수 입력)
position VARCHAR(50) NOT NULL, -- 직급
salary DECIMAL(10,2) NOT NULL, -- 급여 (소수점 2자리)
hire_date DATE DEFAULT (CURRENT_DATE) -- 입사일 (기본값: 현재 날짜)
);
2. 데이터 삽입 (INSERT INTO)
- 1개 데이터 삽입
INSERT INTO employees (name, position, salary)
VALUES ('홍길동', '개발자', 5000000);
- 여러 개 데이터 삽입
INSERT INTO employees (name, position, salary)
VALUES
('김철수', '디자이너', 4500000),
('이영희', '마케팅', 4800000),
('박민수', '개발자', 5200000),
('최수영', 'HR 매니저', 4700000);
3. 데이터 조회 (SELECT)
- 모든 데이터 조회
SELECT * FROM employees;
- 특정 조건으로 데이터 조회
SELECT * FROM employees WHERE position = '개발자';
4. 데이터 수정 (UPDATE)
- 특정 직원의 급여 수정
UPDATE employees
SET salary = 5500000
WHERE name = '홍길동';
- 여러 개 컬럼 동시에 수정
UPDATE employees
SET position = '시니어 개발자', salary = 6000000
WHERE name = '박민수';
- 특정 조건을 만족하는 모든 행 업데이트
UPDATE employees
SET salary = salary * 1.1 -- 모든 직원의 급여를 10% 인상
WHERE position = '디자이너';
5. 데이터 삭제 (DELETE)
- 특정 직원 삭제
DELETE FROM employees
WHERE name = '김철수';
- 특정 조건을 만족하는 여러 행 삭제
DELETE FROM employees
WHERE position = '마케팅';
6. 테이블 전체 데이터 삭제 (TRUNCATE)
TRUNCATE TABLE employees;
- TRUNCATE는 모든 데이터를 삭제하지만, 테이블 구조는 유지됩니다.
7. 테이블 삭제 (DROP TABLE)
DROP TABLE employees;
- DROP TABLE을 실행하면 테이블 자체가 삭제됩니다.
8. 그로스 마케팅 데이터 활용 예제
1) 전체 캠페인 성과 조회
SELECT * FROM marketing_campaigns;
2) 가장 높은 전환율(Conversion Rate)을 보인 캠페인 조회
- 전환율(%) = (전환 수 / 클릭 수) * 100
SELECT campaign_name, channel, (conversions / clicks) * 100 AS conversion_rate
FROM marketing_campaigns
ORDER BY conversion_rate DESC
LIMIT 1;
3) 광고 채널별 평균 클릭률(CTR) 계산
- 클릭률(%) = (클릭 수 / 노출 수) * 100
SELECT channel, AVG((clicks / impressions) * 100) AS avg_ctr
FROM marketing_campaigns
GROUP BY channel;
4) 5000회 이상 전환이 발생한 캠페인 목록 조회
SELECT campaign_name, channel, conversions
FROM marketing_campaigns
WHERE conversions >= 5000;
5) 총 마케팅 예산 대비 클릭당 비용(CPC) 계산
- 클릭당 비용(CPC) = 예산 / 클릭 수
SELECT campaign_name, channel, (budget / clicks) AS cpc
FROM marketing_campaigns
ORDER BY cpc ASC;
6) 특정 기간(예: 2024년 6월~8월) 동안 진행된 캠페인 조회
SELECT * FROM marketing_campaigns
WHERE start_date BETWEEN '2024-06-01' AND '2024-08-31';
7) 가장 많은 예산이 투입된 캠페인 조회
SELECT campaign_name, channel, budget
FROM marketing_campaigns
ORDER BY budget DESC
LIMIT 1;
8) 특정 채널(예: Google Ads)에서 진행된 캠페인의 총 전환 수 및 평균 전환율
SELECT channel, SUM(conversions) AS total_conversions, AVG((conversions / clicks) * 100) AS avg_conversion_rate
FROM marketing_campaigns
WHERE channel = 'Google Ads'
GROUP BY channel;
9) ROI(Return on Investment) 계산
- ROI(%) = ((전환 수 * 평균 구매 금액) - 예산) / 예산 * 100 (평균 구매 금액을 50,000원으로 가정)
SELECT campaign_name, channel, ((conversions * 50000 - budget) / budget) * 100 AS ROI
FROM marketing_campaigns
ORDER BY ROI DESC;
10) 가장 효과적인 마케팅 채널 분석
- 전환 수 대비 예산이 가장 적게 든 채널 찾기 (전환당 비용 CPA = 예산 / 전환 수)
SELECT channel, SUM(budget) / SUM(conversions) AS avg_cpa
FROM marketing_campaigns
GROUP BY channel
ORDER BY avg_cpa ASC
LIMIT 1;
