jj_study.log

SQL - Python with MySQL

Tue, 19 Mar 2024 11:35:41 GMT

1. MySQL 연결

VSCode에서 jupyter Notebook 실행 ds_study 환경 작업
Jupyter Notebook MySQL은 모두 sql_ws 같은 폴더에서 작업
AWS RDS가 생성한 databae-1이 접속 상태인지 확인
ds_study 환경으로 새로운 Jupyter Notebook 파일 생성
Python에서 MySQL 사용을 위한 MySQL Driver 설치

(1) MySQL 접속

MySQL 접속위한 connect 생성

pip install mysql-connector-python #cmd 창 주의!!! 연결 후는 반드시 연결 해제할 것 (예제)

LOCAL DB 연결 시 및 연결해제

mydb = mysql.connector.connect(
   host = "localhost",
   user = "root",
   password = "******",
   database = "databae_name"
)
mydb.close()

AWS RDS (database-1) 연결 시 및 연결해제

mydb = mysql.connector.connect(
   host = "database-1.*********.rds.amazonaws.com",             #RDS 엔드포인트
   port = 3306,
   user = "admin",
   password = "********"
   database = "database_name"
)
mydb.close()

(2) Query 실행

SQL 쿼리 실행하기 위한 cursor 객체 생성
AWS RDS로 연결시

      mydb = mysql.connector.connect(
         host = "database-1.*********.rds.amazonaws.com",             #RDS 엔드포인트
         port = 3306,
         user = "admin",
         password = "********"
         database = "database_name"
      )
      mycursor = mydb.cursor()
      mycursor.execute("query_statement")
      mydb.close()

(예제) (1) TABLE 생성

  ```
   remote = mysql.connector.connect(
      host = "database-1.us-east-2.rds.amazonaws.com",
      port = 3306,
      user = "admin",
      password = "***********",
      database = "zerobase"
  )

  cur = remote.cursor()
  cur.execute("CREATE TABLE sql_file (id int, filename varchar(16))")
  ```

TABLE 확인

```

desc sql_file;

```

(2) TABLE 삭제

  cur = remote.cursor()
  cur.execute("DROP TABLE sql_file")

  remote.close()

(2) SQL파일 실행

SQL파일 실행을 위한 코드

  import mysql.connector

  mydb = mysql.connector.connect(
          host = "host_name",
          port = port_number,
          user = "user_name",
          password = "password",
          database = "database_name"
  )
  mycursor = mydb.cursor()

  sql = open("file_name.sql").read()

  ### * One Query
      mycursor.execute(sql)

  ### * Multi Query : Query가 여러개 존재하는 경우
  mycursor.execute(sql, multi=True)

  mydb.close()

- ONE QUERY

(예제1) test03.sql 파일 생성 (CREATE TABLE sql_file( id int, filename varchar(16)); 후 실행

  remote = mysql.connector.connect(
           host = "database-1.***.amazonaws.com",
           port = 3306,
           user = "admin",
           password = "***********",
           database = "zerobase"
  )
  cur = remote.cursor()
  sql = open("test03.sql").read()
  cur.execute(sql)

  remote.close()

- MULTI QUERY

(예제 2) test04.sql 파일 생성

    INSERT INT sql_file VALUES (1, "test01.sql")
   INSERT INT sql_file VALUES (2, "test02.sql")
   INSERT INT sql_file VALUES (3, "test03.sql")
   INSERT INT sql_file VALUES (4, "test04.sql")

   후에 실행
   ```
   ```
      remote = mysql.connector.connect(
               host = "database-1.us-east-2.rds.amazonaws.com",
               port = 3306,
               user = "admin",
               password = "************",
               database = "zerobase"
      )

      cur = remote.cursor()
      sql = open("test04.sql").read()

      for result_iterator in cur.execute(sql, multi=True):
          if result_iterator.with_rows:
              print(result_iterator.fetchall())
          else:
              print(result_iterator.statement)

      remote.commit()
      remote.close()
      ```

FETCH ALL

cursor.fetchall()

SELECT 문 실행해 테이블의 tuple데이터 가지고 올 때 사용
읽어올 데이터 양이 많은 경우 buffered = True옵션

(예제) sql_file 테이블 조회

remote = mysql.connector.connect(
        host = "database-1.us-east-2.rds.amazonaws.com",
        port = 3306,
        user = "admin",
        password = "*************",
        database = "zerobase"
)

cur = remote.cursor(buffered=True)
cur.execute("SELECT * FROM sql_file")

result = cur.fetchall()
print(result)

for result_iter in result:
   print(result_iter)

remote.close()

=====================================================

2. CSV 데이터로 DB 테이블 만들기

CSV에 있는 데이터를 Python으로 읽어와 MySQL(INSERT)를 이용해 데이터베이스 테이블로 만듬

(1) CSV 파일 읽기 - police_station.csv 파일을 Pandas로 읽어와 데이터를 확인 (한글 깨질때 encoding = 'euc-kr') ``` import pandas as pd

df = pd.read_csv("police_station.csv")
```

(2) MySQL연결 - AWS RDS의 (database-1)으로 연결 import mysql.connector

    connector = mysql.connector.connect(
                host = "database-1.*****.rds.amazonaws.com",
                port = 3306,
                user = "zero",
                password = "zerobase",
                database = "zerobase"
    )

`` ``` (3) CURSOR 생성 - 읽어올 데이터 양이 많으면 cursor 생성 시 buffer = True

```
cursor = connector.cursor(buffered=True)
```

(4) INSERT 문 - pandas.DataFrame 형태의 데이터를 police_station 테이블의 데이터로 넣기 위한 INSERT Query문 생성 sql = "INSERT INTO police_station VALUES (%s, %s)"

(5) 데이터 입력 ``` mysql.connector execute() commit() : 데이터베이스에 적용하기

 for idx, row in df.iterrows():
    cursor.execute(sql, tuple(row))
    connector.commit()
 ```

(6) 결과 확인 데이터베이스에 데이터가 잘 반영되어 있는지 확인

```
cursor.execute("SELECT * FROM police_station")
result = cursor.fetchall()
for row in result:
    print(row)

connector.close()
```

(7) DataFrame으로 결과읽기

```
 result_df = pd.DataFrame(result)
```

SQL - AWS RDS

Tue, 19 Mar 2024 10:21:01 GMT

1. AWS RDS

- Amazon Relational Database Service는 AWS에서 제공하는 관계형 데이터베이스 서비스 <사용방법> (1) AWS RDS에 생성한 DB 'database-1'에 접속 mysql -h <엔드포인트> -P <포트> -u<마스터 사용자> -p<패스워드> (2) DB(zerobase) 생성
```
  CREATE DATABASE zerobase DEFAULT CHARACTER SET utf8mb4  #이모지 사용가능
  SHOW DATABASES;
```
  (3) 사용자 (zero) 추가하고 확인 (사용자 추가)
```
  CREATE USER 'zero'@'%' IDENTIFIED BY 'zerobase';
  # 사용자 삭제를 하고 싶으면
  # DROP USER 'zero'@'%';

  USE (사용하고자 하는 database);
  SELECT host, user FROM user;
```
  - (4) 사용자 (zero)에게 외부에서 접근해 zerobase 데이터 베이스 사용할 수 있는 권한 부여하고 접근
```
GRANT ALL ON zerobase.* TO 'zero'@'%';
REVOKE ALL ON zerobase.* FROM 'zero'@'%';
```
  권한 확인
  
  SHOW GRANTS FOR 'zero'@'%';
```
(5) 현재 접속 종료하고 AWS RDS를 통해 zerobase Database에 접근하고자 하는 경우
```
  exit mysql - h <엔드포인트> -P<포트> -u zero -pzerobase ```
  
  2. SQL FILE

(1) SQL 파일 생성

  Visual Studio 의 sql_ws폴더에서 새 파일 만듦  (test01.sql)
  그 안에 police_station 만듬
  ```
  CREATE TABLE police_station
  (
      name VARCHAR(16),
      address VARCHAR(128)
  );
  ```

(2) SQL 파일 실행

  - MySQL내서 실행
      - MySQL에 로그인 상태에서 SQL 파일 실행
      - source : SQL 파일 실행하는 명령어 대신 \. 사용 가능
          ```
           mysql> source /path/file_name.sql
          또는
          mysql> \. /path/file_name.sql
          ```
      - SQL 파일이 있는 현재 폴더에서 로그인 했다면 path는 생략
     ```
      # sql file이 있는 현재 폴더에서 MySQL에 로그인함과 동시에 DB에 바로 접근
      C:\Documents\sql_ws % mysql -uroot -p********* db_name

      # sql file 실행
      mysql> source file_name.sql
      ``` 
    - MySQL밖에서 실행
      - MySQL에 로그인 하지 않은 상태로 외부에서 SQL파일을 바로 실행
      ```
      DB_name < /path/file_name.sql에서 <은 파일을 DB로 갸져오도록 함
      >mysql -u user_name -p password DB_name < /path/file_name.sql
      ```

3. BACKUP SQL FILE

데이터베이스나 데이터베이스 내 테이블을 SQL파일로 만들어 사용가능 (1) 백업파일 생성

  - mysqldump 명령어 : 명령으를 사용하는 현재 폴더에 백업파일 생성
```
DB_name > backup_file.sql에서 >은 데이터베이스의 데이터를 파일로 내보냄

# 특정 DB의 백업 파일 생성
>mysqldump -u user_name -p DB_name > backup_file.sql
# 모든 DB의 백업 파일 생성
>mysqldump -u user_name -p --all-databases > backup_file.sql
```

4. DATABASE 복구

DB 백업한 SQL 실행하여 백업한 그 시점에서 복구

5. DATABASE 이전

DB 백업한 SQL 파일 활용해 다른 DB로 이전 가능

로컬 아닌 외부 데이터베이스인 AWS RDS로 이전 가능

mysql -h(host) <엔드포인트> -P(Port) <포트> -u <마스터> -p

zerobase DB 백업 파일을 AWS RDS (database-1)로 이전 후 접속

 C:\Documents\sql_ws>mysql -h database-1.us-east-2.rds.amazonaws.com
                            -P 3306 -u admin -p
 mysql> CREATE DATABASE zerobase DEFAULT CHARACTER SET utf8mb4;
 mysql> USE zerobase;
 mysql> source backup_DB_zerobase.sql

6. 테이블 BACKUP

Table을 SQL 파일로 만들 때도 mysqldump 명령어 사용 (백업파일 만들고자하는 폴더에서 명령어 실행)
```
>mysqldump -u user_name -p DB_name Table_name > backup_file.sql
```

테이블 내 데이터를 제외하고 테이블 생성 쿼리만 백업
- 특정 테이블 schema Backup
```
   mysqldump -d -u user_name -p db_name table_name > backup_file.sql
```
- DB내 모든 테이블 schema Backup
```
   mysqldump -d -u user_name -p db_name > backup_file.sql
```
  7. 복구 / 이전
- Table을 백업한 SQL 파일을 실행하여 백업한 그 시점으로 복구
- Table을 백업한 SQL 파일을 활용해 다른 DB로 Table을 이전 가능
```
# 삭제해도  백업한 파일로 복구
mysql> DROP TABLE celeb;
mysql> \. backup_TB_celeb.sql
```

SQL - UNION / JOIN / Subquery

Tue, 19 Mar 2024 08:58:15 GMT

1. UNION - 수직결합

여러 개의 SQL문을 합쳐서 하나의 SQL문으로 만들어 줌
각 SQL문의 컬럼의 개수가 동일해야 오류 생기지 않음

(1) UNION : 중복값 제거 후 검색 반환 (2) UNION ALL : 중복된 값을 포함해 모두 반환

    SELECT column1, column2, ... FROM table_A;
    UNION | UNION ALL
    SELECT column1, column2, ... FROM table_B;

(예제) (1) 성별이 여자인 데이터를 검색하는 쿼리와 소속사가 YG엔터테이먼트인 데이터를 검색하는 쿼리를 UNION으로 실행

SELECT name, sex, agency FROM celeb WHERE sex = "f"
UNION ALL
SELECT name, sex, agency FROM celeb WHERE agency = "YG엔터테인먼트";

(2) 가수가 직업인 연예인의 이름, 직업을 검색하는 쿼리와, 1980년대에 태어난 연예인의 이름, 생년월일, 나이를 검색하는 쿼리를 UNION으로 실행

이 경우 컬럼 개수가 달라서 오류 발생

SELECT name, job_title FROM celeb WHERE job_title LIKE "%가수%"
UNION
SELECT name, birthday, age FROM celeb WHERE birthday BETWEEN '1980-01-01' AND '1989-12-31';

2. JOIN - 수평결합

두 개 이상의 테이블을 특정 컬럼 기준으로 결합하는 명령어
INNER JOIN / FULL OUTER / LEFT JOIN / RIGHT JOIN
snl_show 테이블 이용 (ID SEASON EPISODE BROADCAST_DATE HOST)

(1) INNER JOIN 두 개의 테이블에서 공통된 요소들을 통합해 결합하는 조인 방식. 일종의 교집합 ON : 조인의 기준. 해당 컬럼의 값이 같을 때 조인

SELECT tableA.column1, tableA.column2, ..., tableB.column1,         tableB.column2, ...
FROM tableA
INNER JOIN tableB
ON tableA.column_name = tableB.column_name WHERE condition;

(예제) (1) snl_show에 호스트로 출연한 셀럽을 기준으로 celeb Table과 snl_show Table을 INNER JOIN

SELECT celeb.id, celeb.name, snl_show.id, snl_show.host
FROM celeb INNER JOIN snl_show
ON celeb.name = snl_show.host;

(2) LEFT JOIN 두 개 테이블에서 공통 영역 포함하고 왼쪽 테이블에만 있는 다른 데이터도 포함한 조인 방식

SELECT tableA.column1, tableA.column2, ..., tableB.column1, tableB.column2, 
FROM tableA       # 왼쪽 table
LEFT JOIN tableB  # 오른쪽 table
ON tableA.column_name = tableB.column_name WHERE condition;

(예제) (1) snl_show에 호스트로 출연한 셀럽을 기준으로 celeb Table과 snl_show Table을 LEFT JOIN

SELECT celeb.id, celeb.name, snl_show.id, snl_show.host
FROM celeb 
LEFT JOIN snl_show
ON celeb.name = snl_show.host;

(2) RIGHT JOIN 두 개 테이블에서 공통 영역 포함하고 오른쪽 테이블에만 있는 다른 데이터도 포함한 조인 방식

SELECT tableA.column1, tableA.column2, ..., tableB.column1, tableB.column2,  FROM tableA
RIGHT JOIN tableB
ON tableA.column_name = tableB.column_name WHERE condition;

(예제) (1) snl_show에 호스트로 출연한 셀럽을 기준으로 celeb Table과 snl_show Table을 RIGHT JOIN

SELECT celeb.id, celeb.name, snl_show.id, snl_show.host
FROM celeb
RIGHT JOIN snl_show
ON celeb.name = snl_show.host;

(3) FULL OUTER JOIN

두 개 테이블에서 공통 영역 포함하고 양쪽 테이블의 다른 영역도 모두 포함시키는 조인 방식 (MySQL 에서 지원하지 않아서 FULL OUTER JOIN 과 같은 결과를 내는 다른 방식)

SELECT tableA.column1, tableA.column2, ..., tableB.column1, tableB.column2, 
FROM tableA FULL OUTER JOIN tableB
ON tableA.column_name = tableB.column_name WHERE condition;

(예제) (1) snl_show에 호스트로 출연한 셀럽을 기준으로 celeb Table과 snl_show Table을 FULL OUTER JOIN

SELECT celeb.id, celeb.name, snl_show.id, snl_show.host
FROM celeb LEFT JOIN snl_show ON celeb.name = snl_show.host
UNION
SELECT celeb.id, celeb.name, snl_show.id, snl_show.host
FROM celeb RIGHT JOIN snl_show ON celeb.name = snl_show.host;

(4) SELF JOIN

INNER JOIN과 같은 결과를 도출하는 조인 방식. 조인되는 테이블 간에 공통된 데이터를 가져 옴
```
SELECT tableA.column1, tableA.column2, ..., tableB.column1, tableB.column2, FROM tableA, tableB, ...
WHERE condition;  # WHERE 절에 결합의 기준을 명시
```
(예제) (1) celeb Table의 연예인 중 snl_show에 host로 출연했고 영화배우는 아니면서 YG엔터테이먼트 소속이거나 40세 이상이면서 YG엔터테이먼트 소속이 아닌 연예인의 이름과 나이, 직업, 소속사, 시즌, 에피소드 정보를 검색
```
SELECT celeb.name, celeb.age, celeb.job_title, celeb.agency, snl_show.episode
FROM celeb, snl_show
WHERE celeb.name = snl_show.host AND ((job_title NOT LIKE "%영화배우%" AND agency!="YG엔터테인먼트") OR (age >= 40 AND agency !="YG엔터테인먼트"));
```
====================================================================
3. SQL Subquery
하나의 SQL문 안에 포함되어 있는 또 다른 SQL문
메인 쿼리가 서브쿼리 포함
주의사항 : 서브쿼리는 괄호로 묶어 사용 / 단일 행 혹은 복수 행 비교연산자와 함께 사용 가능 / 서브쿼리에서는 ORDER BY 사용할 수 없음

(1) Scalar Subquery - SELECT 절에 사용 - 결과는 하나의 컬럼 SELECT column1, (SELECT column2 FROM table2_name WHERE condition) FROM table1_name WHERE condition; (예제) (1) 서울은평경찰서의 강도 검거 건수와 서울시 경찰서 전체의 평균 강도 검거 건수를 조회

    SELECT case_number 은평경찰서 강도검거건수, (SELECT AVG(case_number) FROM crime_status WHERE crime_type LIKE '강도' AND status_type LIKE '검거') 서울시 경찰서 검거 평균
    FROM crime_status
    WHERE police_station = "은평" AND crime_type = "강도" AND status_type = "검거";

(2) Inline View - FROM 절에 사용 - 메인 쿼리에서는 인라인뷰에서 조회한 컬럼만 사용 가능 SELECT a.column_name, b.column_name, ... FROM table1_name a, (SELECT column1, column2, ... FROM table2_name) b WHERE condition; (예제) (1) 경찰서 별로 가장 많이 발생한 범죄 건수와 범죄 유형을 조회 SELECT c.police_station, c.crime_type, c.case_number FROM crime_status c, (SELECT police_station, MAX(case_number) count FROM crime_status WHERE status_type = "발생" GROUP BY police_station) m WHERE c.police_station = m.police_station AND c.case_number = m.count (3) Nested Subquery - WHERE 절에서 사용 ((1)) (SINGLE ROW) : 하나의 행 검색하는 서브쿼리 (서브쿼리가 비교연산자와 사용하는 경우) 괄호가 없거나 한 개 이상의 결과값 가지는 경우 에러 발생 SELECT column_names,... FROM table_name WHERE column_name = (SELECT column_name FROM table_name WHERE condition) ORDER BY column_names; (예제) (1) SNL에 출연한 연예인 중 id가 1인 연예인 SELECT name FROM celeb WHERE name = (SELECT host FROM snl_show WHERE id=1); ((2)) Multiple Row - 하나 이상의 행을 검색하는 서브쿼리 - IN : 서브쿼리 결과 중 포함되는 경우 SELECT column_names,... FROM table_name WHERE column_name IN (SELECT column_name FROM table_name WHERE condition) ORDER BY column_names; (예제) (1) SNL에 출연한 영화배우를 조회 SELECT host FROM snl_show WHERE host IN (SELECT name FROM celeb WHERE job_title LIKE '%영화배우%'; - EXISTS : 서브쿼리 결과에 값이 있으면 True 반환 SELECT column_names,... FROM table_name WHERE EXISTS (SELECT column_name FROM table_name WHERE condition) ORDER BY column_names; (예제) (1) 범죄 검거 혹은 발생 건수가 2000건 보다 큰 경찰서 조회 SELECT name FROM police_station p WHERE EXISTS (SELECT police_station FROM crime_status c WHERE p.name = c.reference AND case_number > 2000); - ANY : 서브쿼리 결과 중에 최소한 하나라도 만족하면 반환 SELECT column_names,... FROM table_name WHERE column_name = ANY (SELECT column_name FROM table_name WHERE condition) ORDER BY column_names; (예제) (1) SNL에 출연한 적이 있는 연예인 이름 조회 SELECT name FROM celeb WHERE name = ANY (SELECT host FROM snl_show); - ALL : 서브쿼리 결과를 모두 만족하면 반환 (비교연산자 사용) SELECT column_names,... FROM table_name WHERE column_name = ALL (SELECT column_name FROM table_name WHERE condition) ORDER BY column_names; (예제) (1) SNL에 출연한 적이 있는 연예인 이름 조회 SELECT name FROM celeb WHERE name = ALL (SELECT host FROM snl_show WHERE id=1); ((3)) Multiple Column - 하나 이상의 열을 검색하는 서브쿼리 - 서브쿼리 내에 메인 쿼리 컬럼을 가져와 같이 사용되는 경우 SELECT column_names, ... FROM table1_name a WHERE (a.column1, a.column2, ...) IN (SELECT b.column1, b.column2, ... FROM table2_name b WHERE a.column_name = b.column_name) ORDER BY column_names; (예제) (1) 강동원과 성별, 소속사가 같은 연예인의 이름, 성별, 소속사를 조회 SELECT name, sex, agency FROM celeb WHERE (sex, agency) IN (SELECT sex, agency FROM celeb WHERE name = "강동원");

SQL - CONCAT/ALIAS/DISTINCT/LIMIT/Scalar Functions/PRIMARY KEY/FOREIGN KEY

Tue, 19 Mar 2024 06:28:26 GMT

1. CONCAT

여러 문자열을 하나로 합치거나 연결
```
SELECT CONCAT('string1', 'string2', ...);
```
예제 SELECT CONCAT('concat', ' ', 'test'); +-------------------------------+ | CONCAT('concat', ' ', 'test') | +-------------------------------+ | concat test | +-------------------------------+

SELECT CONCAT('이름 : ', name) FROM celeb; +---------------------------+ | CONCAT('이름 : ', name) | +---------------------------+ | 이름 : 아이유 | | 이름 : 이미주 | | 이름 : 송강 | | 이름 : 강동원 | | 이름 : 유재석 | | 이름 : 차승원 | | 이름 : 이수현 | +---------------------------+

2. ALIAS
컬럼이나 테이블에 붙이는 별칭

ALIAS가 출력 됨 (~AS 는 생략 가능)

SELECT column1 AS alias1, column2 AS alias2, ... FROM table_name;
SELECT column1 alias1, column2 alias2, ... FROM table_name alias;
SELECT column1, column2, ... FROM table_name AS alias;

3. DISTINCT

검색에 대한 중복 제거
```
SELECT DISTINCT column1, column2, ... FROM table_name;
```
예제 (1) 연예인 소속사 정보 검색
중복 데이터 출력하며 다 보여주기
```
SELECT agency FROM celeb;
```
중복 데이터 제외하며 보여주기
```
SELECT DISTINCT agency FROM celeb;
```
(2) 가수 중 성별과 직업별 종류 검색

중복 데이터 출력하며 다 보여주기

SELECT sex, job_title FROM celeb WHERE job_title LIKE '%가수%';

중복 데이터 제외하며 보여주기

SELECT DISTINCT sex, job_title FROM celeb WHERE job_title LIKE '%가수%';

4. LIMIT

검색 결과를 정렬된 순에서 LIMIT로 주어진 숫자만큼 조회
```
SELECT column1, column2, ... FROM table_name WHERE condition LIMIT number;
```
예제 (1) celeb 테이블에서 id로 정렬된 데이터 3개
```
SELECT * FROM celeb LIMIT 3;
```
(2) 나이가 가장 적은 연예인 4명 검색
```
SELECT * FROM celeb ORDER BY age LIMIT 4;
```
===============================================================

5. SCALAR Functions

실습환경 : AWS RDS (database-1) zerobase DB, sandwich 테이블
입력값을 기준으로 단일 값을 반환

* UCASE

영문을 대문자로 변환
```
  SELECT UCASE("string");
```
SELECT UCASE('This is ucase test.'); +------------------------------+ | UCASE('This is ucase test.') | +------------------------------+ | THIS IS UCASE TEST. | +------------------------------+
(예제) $15넘는 메뉴를 대문자로 조회
```
SELECT UCASE(menu), price FROM sandwich WHERE price > 15;
```
* LCASE
영문을 소문자로 변환
```
SELECT LCASE("string");
```

SELECT LCASE("This IS LCASE Test."); +------------------------------+ | LCASE("This IS LCASE Test.") | +------------------------------+ | this is lcase test. | +------------------------------+

(예제) 가격이 $5 보다 작은 메뉴를 소문자 조회

SELECT LCASE(menu), price FROM sandwich WHERE price < 5;

#### * MID
- 문자열의 부분을 위치 등에 따라 골라서 반환

SELECT MID("string", start_position, lenth); (string, start, length)

예제
(1) 1번 위치에서 4글자 조회

SELECT MID('This is mid test', 1, 4);

(2) 6번 위치에서 5글자 조회

SELECT MID('This is mid test', 6, 5);

(3) -4번 위치(뒤에서 4번째 위치)에서 4글자 조회

SELECT MID('This is mid test', -4, 4);

(4) -8번 위치(뒤에서 8번째 위치)에서 3글자를 조회

SELECT MID('This is mid test', -8, 3);

(5) 11위 카페이름 중 두번째 단어만 조회: 6번 위치에서 4글자
하나씩 단계별 조회해서 추출할 단어 위치를 찾기

mysql> SELECT cafe FROM sandwich WHERE ranking=11; +-----------+ | cafe | +-----------+ | Lula Cafe | +-----------+

mysql> SELECT MID(cafe, 6, 4) FROM sandwich WHERE ranking=11;
+-----------------+
| MID(cafe, 6, 4) |
+-----------------+
| Cafe            |
+-----------------+

#### * LENGTH
- 문자열 길이 반환

SELECT LENGTH("string");

(예제)
(1) 문장의 길이 조회

SELECT LENGTH('This is len test.');

(2) ''에 공백이 없는 경우 길이는 0

SELECT LENGTH('');

(3) ''에 공백이 있는 경우 길이는 1

SELECT LENGTH(' ');

(4) NULL의 경우 길이가 없으므로 결과는 NULL

SELECT LENGTH(NULL);

(5) sandwich Table에서 Top 3의 주소 길이를 검색

SELECT LENGTH(address), address FROM sandwich ORDER BY ranking LIMIT 3; 또는 SELECT LENGTH(address), address FROM sandwich WHERE ranking <= 3;

#### * ROUND
- 지정한 자리에서 숫자 반올림 (0은 소수점 첫째 자리)

SELECT ROUND(number, deciamls_place);

(예제)
(1) 위치를 지정하지 않을 경우, 소수점 첫번째 자리(0)에서 반올림

SELECT ROUND(315.625); 답: 316

(2) 소수점 첫번째 위치는 0

SELECT ROUND(315.625, 0); 답: 316

(3) 두번째 소수점 위치는 1

SELECT ROUND(315.625, 1); 답: 315.6

(4) 세번째 소수점 위치는 2

SELECT ROUND(315.625, 2); 답: 315.63

(5) 자연수 일단위 위치는 -1

SELECT ROUND(315.625, -1); 답: 320

(6) 자연수 십단위 위치는 -2

SELECT ROUND(315.625, -2); 답: 300

(7) sandwich 테이블에서 소수점 자리는 반올림하여 1달러 단위까지만 표시 (단, 최하위 3개만 표시)

SELECT ranking, price, ROUND(price, 0) FROM sandwich ORDER BY ranking DESC LIMIT 3

#### * NOW
- 현재 날짜 및 시간 반환

SELECT NOW(); 2024-02-27 17:42:33 이렇게 반환

#### * FORMAT
- 문자나 숫자를 천단위 콤마 있는 형식으로 반환

SELECT FORMAT(number, decimals_place); decimal은 소수점 위치

(예제)
(1) 소수점을 표시하지 않을 경우 0

SELECT FORMAT(12345.6789, 0); 정답 : 12,346

(2) 소수점 두자리까지 표시할 경우 2

SELECT FORMAT(12345.6789, 2); 정답 : 12,345.68

(3) 소수점 열자리까지 표시

SELECT FORMAT(12345.6789, 10);

(4) oil_price Table에서 가격이 백원단위에서 반올림 했을 때 2000원 이상인 경우. 천원 단위에 콤마를 넣어서 조회

SELECT FORMAT(가격, 0) FROM oil_price WHERE ROUND(가격, -3) >= 2000;

===============================================================
### 6. PRIMARY KEY
- 후보키 (Candidate Key)들 중 개체(Table) 구별을 위해 선택된 하나의 키
- 후보키 (Candidate Key) - 하나의 릴레이션(Relation)에서 유일성과 최소성을 가지고 있는 모든 속성 혹은 속성 집합
- 기본키로 선택된 속성은 중복되지 않는 고유값 보유
- 정의되지 않은 NULL 값을 포함할 수 없다. → NOT NULL
- 다른 테이블의 외래키에 의해 참조될 수 있음

#### (1) PRIMARY KEY 문법  
- 테이블 생성시 지정
    ```
    CREATE TABLE table_name(
    column1 datatype NOT NULL,
    column2 datatype NOT NULL,
    ...
    [CONSTRAINT constraint_name] PRIMARY KEY (column1, column2, ...)
    );
    --> CONSTRAINT은 생략 가능. 생략하면 자동으로 생성 됨.
    ```

- 이미 정의되어 있는 테이블에 기본키를 설정하는 방법
    ```
    ALTER TABLE table_name ADD PRIMARY KEY (column1, column2, ...)
    ```
---------------------------------------------------------------------
- 테이블 삭제시
    ```
    ALTER TABLE table_name DROP PRIMARY KEY;
    ```
---------------------------------------------------------------------
(예제)
(1) person 테이블 생성하면서 Primary Key 지정
    ```
    CREATE TABLE person(
        pid int NOT NULL,
        name varchar(16),
        age int,
        sex char,
        PRIMARY KEY (pid)
    );
    ```
(2) person 테이블 기본키 삭제
    ```
     ALTER TABLE person DROP PRIMARY KEY;
    ```
(3) 이미 정의 되어 있는 person 테이블의 기본키 설정
    ```
     ALTER TABLE person ADD PRIMARY KEY (pid);
    ```
====================================================================
### 7. FOREIGN KEY
- 외래키는 두 Relation 간의 관계를 맺어줄 때 사용되는 키
- 외래키로 정의된 속성(Attribute)는 반드시 상대 Relation의 기본키여야 함
- 기본키와 달리 외래키는 한 테이블 내에 여러 개일 수 있음. 즉, 여러 개의 테이블 참조 가능
    - 외래키 확인을 위해 CONSTRAINT으로 지정한 이름을 확인 가능
    - CONSTRAINT를 생략하면 이름이 자동 생성
    - 이 constraint_name을 알아야 해당 외래키를 삭제(DROP) 가능

#### (1) FOREIGN KEY 문법  
- 테이블 생성 후에도 ALTER TABLE을 통해 FOREIGN KEY를 지정 가능

*** 테이블 생성 시 지정

CREATE TABLE table_name(
        column1 datatype NOT NULL,
        column2 datatype NOT NULL,
        column3 datatype,
        column4 datatype,
        ...
        [CONSTRAINT constraint_name] PRIMARY KEY (column1, column2, ...)
        [CONSTRAINT constraint_name]
           PRIMARY KEY (column3, column4, ...) REFERENCES                     REF_tablename(REF_column)
);

- 기존 테이블에서 외래키 설정

ALTER TABLE table_name ADD FOREIGN KEY (column) REFERENCES REF_tablename(REF_column)
--> CONSTRAINT이 생략되어 있지만 자동으로 생성

- 자동 생성된 CONSTRAINT 확인

SHOW CREATE TABLE table_name;

*** 테이블 삭제 시 지정
    - 외래키는 한 테이블에 여러 개가 존재할 수 있으므로 삭제하려는 외래키를 지정해 줘야 함

ALTER TABLE table_name DROP FOREIGN KEY FK_constrain_name;

(예제)
(1)  CREATE TABLE 에서 외래키 지정

 CREATE TABLE orders(
        oid int NOT NULL,
        order_no varchar(16),
        pid int,
        PRIMARY KEY (oid),
        [CONSTRAINT FK_person] FOREIGN KEY (pid) REFERENCES person(pid)
);
SHOW CREATE TABLE orders;   #pid의 key가 MUL로 나옴

(2) CREATE TABLE에서 외래키를 지정하는 경우, CONSTRAINT를 생략 가능

CREATE TABLE job( jid int NOT NULL, name varchar(16), pid int, PRIMARY KEY (jid), FOREIGN KEY (pid) REFERENCES person(pid) ); SHOW CREATE TABLE job;

(3) 이미 정의 되어 있는 orders 테이블에서 외래키 설정

ALTER TABLE orders ADD [CONSTRAINT FK_person] FOREIGN KEY (pid) REFERENCES person(pid); ```

SQL - 부속 질의어

Mon, 18 Mar 2024 15:07:12 GMT

1. Affiliated DML (부속 질의어)

celeb 테이블 생성 ID (int) Name (varchar) BIRTHDAY (date) AGE (int) SEX (char) JOB_TITLE (varchar) AGENCY (varchar)

(1) WHERE (조건 추가) - Table 내에서 조건을 만족하는 데이터 조회

    SELECT column1, colunm2, ... FROM table_name WHERE condition;

(2) 비교 연산자

관련 문제 이름이 '아이유'인 데이터의 이름과 나이, 성별, 직업, 소속사를 조회
```
select name, age, sex, job_title, agency from celeb where name='아이유';
```
소속사가 'YG엔터테이먼트'가 이닌 데이터의 이름, 직업, 소속사를 조회
```
select name, job_title, agency from celeb where agency!='YG엔터테이먼트';
```
나이가 50세 미만인 데이터의 성별, 소속사, 이름, 나이를 조회
```
select sex, agency, name, age from celeb where age < 50;
```
(3) 논리 연산자

AND : 조건을 모두 만족하는 경우 TRUE

SELECT column1, column2, ... FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;

AND 연산자가 OR 연산자보다 우선 순위가 높음

select * from celeb
where (age<29 AND sex='f') OR (age>30 AND sex='m')
order by age;

NOT : 조건을 만족하지 않을 때
```
SELECT column1, column2, ... FROM table_name WHERE NOT condition;
```
예제 (1) 남자이면서 YG엔터테이먼트 소속이거나, 나이가 30세 보다 작은 데이터를 나이, 소속사 순으로 정렬하여 검색
```
select * from celeb
where (sex='m' AND agency='YG엔터테이먼트') OR age<30
order by age, agency;
```
(2) 아이디가 홀수면서 성별이 남자이거나, 아이디가 짝수면서 소속사가 YG엔터테이먼트인 데이터를 나이순으로 정렬하여 검색
```
select * from celeb
where ((id%2)=1 and sex='m') or ((id%2)=0 and agency='YG엔터테이먼트')
order by age;
```
(3) 생일이 1990년 이후이면서 여자가 아니거나, 생일이 1979년 이전이면서 소속사가 안테나가 아닌 데이터를 검색
```
select * from celeb
where (birthday>19891231 AND NOT sex='f') OR (birthday<19800101 AND NOT agency='안테나');
```
================================================ (4) BETWEEN A AND B 조건값이 A ≤ column_value ≤ B 범위 사이
```
SELECT column1, column2, ... FROM table_name
WHERE col_name BETWEEN p_value AND q_value;
```

-- 둘은 같은 결과 도출 -- WHERE col_name >= p_value AND col_name <= q_value;

예제
(1) 생년월일이 1980년에서 1995년 사이가 아니면서 여자이거나, 소속사가 'YG엔터테이먼트'이면서 나이가 20세에서 45세 사이가 아닌 데이터 검색

select * from celeb where (NOT birthday BETWEEN 19800101 AND 19951231 and sex='f') or (agency='YG엔터테이먼트' and NOT age BETWEEN 20 AND 45);

(2) 나이가 30세에서 60세 사이이고 성별이 남자인 데이터를 나이순으로 정렬하여 조회

select * from celeb where age BETWEEN 30 AND 60 and sex='m' order by age;

(3) 나이가 30세에서 60세 사이가 아니거나 'YG엔터테이먼트' 소속인 데이터를 나이의 역순으로 정렬하여 조회

select * from celeb where NOT age BETWEEN 30 AND 60 or agency='YG엔터테이먼트' order by age desc;

(4) 아이디가 1에서 5 사이의 값이면서 성별이 여자이거나, 아이디가 홀수이면서 성별이 남자이면서 나이가 20에서 30세 사이인 데이터를 조회

select * from celeb where (id BETWEEN 1 AND 5 and sex='f') or ((id%2)=1 and sex='m' and age BETWEEN 20 AND 30);

====================================
(5) IN / NOT IN
- 집합 연산자로 조건값이 목록에 있음

SELECT column1, column2,... FROM table_name WHERE col_name IN (value1, value2,...);

SELECT column1, column2, ... FROM table_name WHERE NOT column_name IN (value1, value2, ...);

예제
(1) 소속사가 나무엑터스, 안테나, 울림엔터테이먼트가 아니면서, 성별이 여자이거나 나이가 45세 이상인 데이터 검색

select * from celeb where NOT agency IN ('나무엑터스', '안테나', '울림엔터테이먼트') and (sex='f' or age>=45);

(2) 아이유, 이미주, 송강 중에서 소속사가 '나무엑터스'인 데이터를 조회

select * from celeb where name IN ('아이유','이미주','송강') and agency='나무엑터스';

(3) 소속사가 안테나, YG엔터테이먼트 중 하나가 아니고, 성별이 여자인 데이터를 조회

select * from celeb where NOT agency IN ('안테나', 'YG엔터테이먼트') and sex='f';

(4) 아이유, 송강, 강동원, 차승원 중에 YG엔터테이먼트 소속이 아니거나 나이가 40세에서 50세 사이인 사람을 찾아본다.

select * from celeb where name IN ('아이유', '송강', '강동원', '차승원') and (agency!='YG엔터테이먼트' or age between 40 and 50);

(6) LIKE / NOT LIKE
- 패턴 연산자로 조건값이 패턴에 맞는지

SELECT column1, column2, ... FROM table_name WHERE col_name LIKE pettern;

예제
(1) 소속사 이름이 'YG'로 시작하는 패턴

SELECT * FROM celeb WHERE agency LIKE 'YG%';

(2) '엔터테이먼트'로 끝나는 소속사 이름을 가진 데이터 검색

SELECT * FROM celeb WHERE agency LIKE '%엔터에이먼트';

(3) 직업명에 '가수'가 포함된 패턴의 데이터 검색 '%가수%'는 '가수'로 시작해도 TRUE, '가수'로 끝나도 TRUE

SELECT * FROM celeb WHERE job_title LIKE '%가수%';

(4) 소속사 이름의 두번째 글자가 'G'인 패턴의 데이터를 검색  언더바로 글자수를 확보한다. 언더바가 두개면 세번째 글자가 G인 패턴이 된다.

SELECT * FROM celeb WHERE agency LIKE '_G%';

(5) 직업명이 '가'로 시작하고 최소 2글자 이상인 패턴의 데이터를 검색  언더바로 최소 두글자를 확보한다.

SELECT * FROM celeb WHERE job_title LIKE '가_%';

(6) 직업명이 '가'로 시작하고 최소 4글자 이상인 패턴의 데이터를 검색  언더바를 4개를 추가하면 최소 다섯글자를 확보할 수 있다.

SELECT * FROM celeb WHERE job_title LIKE '가_ _ _ _%';

(7) 직업명이 '영'으로 시작하고 '모델'로 끝나는 데이터를 검색  '영%모델' --> '영'으로 시작하고 '모델'로 끝나면서 그 사이에 몇 글자가 와도 상관 없다는 것임

SELECT * FROM celeb WHERE job_title LIKE '영%모델';

(8) 직업이 하나 이상인 연예인 중 영화배우 혹은 텔런트가 아닌 연예인 검색

SELECT * FROM celeb WHERE job_title LIKE '%,%' AND NOT (job_title LIKE '%영화배우%' OR job_title LIKE '%텔런트%');

==============================================
(7) ORDER BY (데이터를 특정 컬럼을 기준으로 오름차순(ASC, Ascending) 혹은 내림차순(DESC, Descending)으로 정렬)

SELECT col1, col2, ... FROM table_name ORDER BY col_1, col_2, ... ASC;

SELECT col1, col2, ... FROM table_name ORDER BY col_1, col_2, ... DESC;

예제
(1) celeb TABLE에서 이름, 생년월일, 성별, 소속사 컬럼을 소속사 순으로 정렬하여 조회

SELECT NAME, BIRTHDAY, AGE, AGENCY FROM celeb ORDER BY AGENCY;

(2) celeb TABLE에서 전체 컬럼을 소속사, 이름 순으로 정렬하여 조회

SELECT * FROM celeb ORDER BY AGENCY, NAME;

(3) 이름, 나이, 직업, 소속사 컬럼을 1. 소속사 순, 2. 나이 역순으로 정렬하여 조회

SELECT NAME, AGE, JOP_TITLE, AGENCY FROM celeb ORDER BY AGENCY ASC, AGE DESC;

========================================================================
### 2. Aggregate Functions (집계함수 - 하나의 결과값 반환 )

(1) COUNT (해당 컬럼의 총 tuple 수)
- 속성(Column)의 Tuple 데이터 총 개수를 계산하는 함수로 이때 'NULL' 값은 제외 됨

SELECT COUNT(column_name) FROM table_name WHERE condition;

예제
(1) police_station 테이블에서 데이터는 총 몇개?

SELECT COUNT(*) FROM police_station;

(2) crime_station 테이블에서 경찰서는 총 몇 개?

SELECT COUNT(DISTINCT police_station) FROM crime_status;

(3) crime_type (범죄 유형)은 총 몇가지?

SELECT COUNT(DISTINCT crime_type) FROM crime_status;

============================================
(2) SUM (해당 컬럼의 숫자 데이터의 합계 계산)

SELECT SUM(column_name) FROM table_name WHERE condition;

예제
(1) 범죄 총 발생 건수?

SELECT SUM(case_number) FROM crime_status WHERE status_type = "발생";

(2) 범죄 유형 중 살인의 총 발생 건수?

SELECT SUM(case_number) FROM crime_status WHERE status_type = "발생" AND crime_type = "살인";

(3) 중부 경찰서에서 검거된 총 범죄 건수?

SELECT SUM(case_number) FROM crime_status WHERE status_type = "검거" AND police_station = "중부";

===============================================
(3) AVG (해당 컬럼에 있는 숫자 데이터들의 평균 계산)

SELECT AVG(column_name) FROM table_name WHERE condition;

예제
(1) 평균 폭력 검거 건수?

SELECT AVG(case_number) FROM crime_status WHERE crime_type LIKE "폭력" AND status_type LIKE "검거";


(2) 중부경찰서의 범죄 평균발생 건수?

SELECT AVG(case_number) FROM crime_status WHERE status_type = "발생" AND police_station LIKE "중부";

============================================================
(4) MIN (해당 컬럼에 있는 숫자 데이터들 중 최소값)

SELECT MIN(column_name) FROM table_name WHERE condition;

예제
(1) 강도 발생 건수가 가장 적은 경우 건수?

SELECT police_station, MIN(case_number) FROM crime_status WHERE crime_type LIKE "강도" AND status_type LIKE "발생";


(2) 중부경찰서에서 가장 낮은 검거 건수?

SELECT MIN(case_number) FROM crime_status WHERE status_type LIKE "검거" AND police_station LIKE "중부"

=============================================================
(5) MAX (해당 컬럼에 있는 숫자 데이터들 중 최대값)

SELECT MAX(column_name) FROM tabale_name WHERE condition;

예제
(1) 살인이 가장 많이 검거된 건수?

SELECT MAX(case_number) FROM crime_status WHERE crime_type LIKE "살인";

(2) 강남경찰서에서 가장 맣이 발생한 범죄건수?

SELECT MAX(case_number) FROM crime_status WHERE police_station LIKE "강남" AND status_type LIKE "발생";

================================================================
(6) GROUP BY ~ HAVING (GROUP BY로 그룹화하여 데이터를 조회.  HAVING은 GROUP BY의 조건자 역할)
- 그룹별 데이터 조회시 집계함수 사용
- 조건에 집계함수가 들어갈 때 WHERE 대신 HAVING 사용

SELECT column1, column2, ... FROM table_name WHERE condition GROUP BY column1, column2, ... --> 그룹화 HAVING condition(Aggregate Functions) ORDER BY column1, column2, ...; --> 정렬

예제
(1) 경찰서 별로 발생한 범죄 건수의 합이 4000 건보다 큰 경우 검색

SELECT police_station, SUM(case_number) count FROM crime_type WHERE status_type LIKE "발생" GROUP BY police_station HAVING count > 4000;

(2) 경찰서 별로 발생한 폭력과 절도의 범죄 건수 평균이 2000 이상인 경우를 검색

SELECT police_station, AVG(case_number) FROM crime_status WHERE crime_type IN ("폭력" AND "절도") AND status_type LIKE "발생" GROUP BY police_station HAVING AVG(case_number) > 2000; ```

SQL - DDL / DML

Mon, 18 Mar 2024 14:21:16 GMT

1. DDL (Data Definition Language) : 테이블 생성, 변경, 삭제

Table : 데이터베이스 내에 데이터가 저장되는 형태로 행(Row) 과 열(Column)으로 구성된 데이터 집합

(1) CREATE (Table 생성)

    # 테이블 생성
    CREATE TABLE table_name(
          colunm1 data_type,
          colunm2 data_type,
          ...
    );

    # 현재 데이터베이스 내에 모든 테이블의 목록 확인
    SHOW TABLES;

    # Table 정보 확인
    DESC table_name;

(2) ALTER (Table 변경/수정)

테이블 이름 변경

  ALTER TABLE tbl_name RENAME new_tbl_name;

테이블 컬럼 추가

  ALTER TABLE tbl_name ADD COLUMN col_name datatype;

테이블 컬럼 변경

  # 데이터 자료형 변경
  ALTER TABLE tbl_name MODIFY COLUMN col_name datatype;

  # 컬럼명 변경
  ALTER TABLE tbl_name CHANGE COLUMN old_col_name new_col_name datatype;

테이블 컬럼 삭제

  ALTER TABLE table_name DROP COLUMN column_name;

(3) DROP (Table 삭제)

    DROP TABLE table_name;

=================================================

2. DML (Data Manipulation Language) : 테이블 tuple 생성, 삭제, 편집, 조회

데이터베이스 생성

  # 기본문자는  utf-8, mb4 설정
  CREATE DATABASE zerobase DEFAULT CHARACTER SET utf8mb4

사용할 데이터베이스로 이동, 실습용 테이블 생성

  USE zerobase;
  CREATE TABLE person(
          id int,
          name varchar(16),
          age int,
          sex char
  );
  DESC person;

(1) INSERT INTO (Table에 데이터 추가)

  INSERT INTO table_name (col1, col2, ...) VALUES (val1, val2, ...);

모든 컬럼에 값을 추가하는 경우 컬럼 이름을 지정하지 않아도 됨. 그러나 입력하는 데이터의 순서가 테이블의 컬럼 순서와 동일해야 함
```
  INSERT INTO person (id, name, age, sex) VALUES (1, '이효리', 43, 'F');
  INSERT INTO person VALUES (2, '이상순', 48, 'M');
```
(2) SELECT FROM (Table의 데이터 조회)
```
  SELECT column1, column2, ... FROM table_name;
  SELECT * FROM table_name; 
```
(3) UPDATE SET (Table의 데이터 수정)
수정할 데이터 구체적으로 지정하지 않으면 해당 컬럼의 모든 데이터 값이 변하므로 WHERE 조건으로 지정
```
 UPDATE table_name SET column1=value1, column2=value2, ...
     WHERE condition;
```

(4) DELETE FROM (Table의 데이터 삭제)

Where는 삭제할 데이터의 조건을 추가
```
  DELETE FROM table_name WHERE condition;
```
WHERE로 조건이 없이면 해당 컬럼 모든 데이터가 삭제 됨. 테이블 자체를 삭제하고 싶으면 DROP table

SQL - Database 기본

Mon, 18 Mar 2024 13:57:16 GMT

1. 데이터베이스 정의

여러 사람이 공유하여 사용할 목적으로 체계화해 통합, 관리하는 데이터 집합체

2. DBMS (Database Management System)

사용자와 데이터베이스 사이에서 사용자의 요구에 따라 정보를 생성해주고 데이터베이스를 관리

3. RDB (Relational Database : 관계형 데이터베이스)

서로간에 관계가 있는 데이터 Table들을 모아둔 데이터 저장 공간

행[row] : 튜플[tuple], 레코드[record]
열[column] : 속성[attribute], 필드[field]

=================================================

1. SQL (Structured Query Language)

터미널에서 데이터베이스를 검색하기 위해 주로 사용되는 독립 언어
SQL은 데이터베이스에서 데이터를 정의, 조작, 제어하기 위해 사용하는 언어

* 데이터 정의어 [DDL] : CREATE / ALTER / DROP (Table과 관련)

* 데이터 조작어 [DML] : SELECT / UPDATE / INSERT (Data 조작과 관련)

* 데이터 제어어 [DCL] : GRANT / REVOKE / COMMIT / ROLLBACK (권한부여/해제, 트랜젝션 집행, 변경 취소 원상복귀 와 관련)

1. DB 관리하기

설치한 MySQL의 root 계정으로 접속

  > mysql -u root -p
  > Enter password: ************

  # 혹은 password를 p 뒤에 이어 입력하고 실행할 수도 있음    
  > mysql -u root -p*****

데이터베이스 목록 확인
```
SHOW DATABASES;
```
데이터베이스 생성
```
CREATE DATABASE db_name;
```
사용하게 되는 데이터베이스 지정 (로그인 후 지정)
```
USE db_name;
```
데이터베이스 삭제
```
DROP DATABASE db_name;
```

### 2. 사용자 관리
- mysql 데이터베이스 내 user 테이블에서 사용자 정보를 확인

USE mysql; SELECT host, user FROM user;

- 사용자 생성

로컬 PC에서만 접속 가능한 사용자

CREATE USER 'user_name'@'localhost' IDENTIFIED BY 'password';

외부에서 접속 가능한 사용자

CREATE USER 'user_name'@'%' IDENTIFIED BY 'password';

- 사용자 삭제
접근 범위에 따라 같은 이름의 사용자여도 별도로 삭제해주어야 함

DROP USER 'user_name'@'localhost' DROP USER 'user_name'@'%'

- 사용자에게 특정 데이터베이스의 모든 권한 부여

GRANT ALL ON db_name.* TO 'user_name'@'localhost' GRANT ALL ON db_name.* TO 'user_name'@'%'

- 사용자에게 특정 데이터베이스의 모든 권한을 삭제

REVOKE ALL ON db_name.* FROM 'user_name'@'localhost' REVOKE ALL ON db_name.* FROM 'user_name'@'%'

- 사용자에게 부여된 모든 권한 목록 확인

SHOW GRANTS FOR 'user_name'@'localhost' SHOW GRANTS FOR 'user_name'@'%'

프로젝트 - 인구 현황 데이터 분석 (catogram 시각화)

Mon, 18 Mar 2024 13:27:45 GMT

카토그램

지리적 공간을 나타내는 지도에서 지역의 상대적 크기를 표현하는 방식
특정 변수의 값을 반영하여 지역의 크기를 변형하여 나타냄. (일반적으로 지도에서는 지역의 면적이나 크기가 실제 지리적 크기와 일치)
통계적 혹은 시각적으로 중요한 데이터를 강조하거나 특정 지역 간의 비교를 용이하게 하기 위해 사용
일반적으로 인구, 경제적 측면, 환경적 특성 등과 같은 변수를 나타내는 경우 사용

1. 인구 소멸 위기 지역 파악

(1) 데이터 정제

import numpy as np
import pandas as pd

population = pd.read_excel("../data/07_population_raw_data.xlsx")
population.head()

Excel 데이터 cell 병합으로 인해 컬럼이 2중으로 되어 있음

읽어올 때 필요없는 맨 윗줄 (column_level=0)은 제거하고 가져옴 header = 1부터 사용한다는 것임
```
population = pd.read_excel("../data/07_population_raw_data.xlsx", header=1)
```

fillna() : NaN 값 채우기

population.fillna(method="pad", inplace=True)
population

행과 열 편집, 값 변경 등 데이터 정제

(2) 인구 소멸 위험 지역 조사

65세 고령 인구 대비 20-39세 여성인구 비중으로 비율이 < 0.5 이면 30년 내 소멸 가능성이 크다고 봄
소멸 위험지수 = (2-19세 여성인구수) / (65세 이상 인구수)
소멸위험지수가 < 0.5 면 인구 소멸 위허 지역으로 구분

20~39세 여성 인구와 65세 이상 인구수를 구하기 위해 나뉘어진 나이 컬럼을 합

  population["20-39세"] = (
        population["20 - 24세"] + population["25 - 29세"]
      + population["30 - 34세"] + population["35 - 39세"]
  )

  population["65세이상"] = (
        population["65 - 69세"] + population["70 - 74세"]
      + population["75 - 79세"] + population["80 - 84세"]
      + population["85 - 89세"] + population["90 - 94세"]
      + population["95 - 99세"] + population["100+"]
  )

인구 소멸 위험 지역을 조사하기 위해 필요한 컬럼들을 모아 새로운 DataFrame 생성

popul_pivot = pd.pivot_table(
        data=population,
        index=["광역시도", "시도"],
        columns=["구분"],
        values=["인구수", "20-39세", "65세이상"]
  )

노인 인구 대비 2030 여성 인구 비율 계산

popul_pivot["소멸지수"]
    = popul_pivot["20-39세", "여자"] / popul_pivot["65세이상", "합계"]

"소멸위험지역" 컬럼 만들고 "소멸지수"가 < 0.5 인 지역은 True값 아니면 False
```
popul_pivot["소멸위기지역"] = popul_pivot["소멸지수"] < 0.5
```

Multi index와 Multi column을 단일값으로 만들어 줌

# index 재정렬
popul_pivot.reset_index(inplace=True)
  popul_pivot.head()

column 추가

tmp_cols = [ popul_pivot.columns.get_level_values(0)[n] + popul_pivot.columns.get_level_values(1)[n] for n in range(len(popul_pivot.columns.get_level_values(0))) ] popul_pivot.columns = tmp_cols

![](https://velog.velcdn.com/images/jj_study/post/e5b5f235-89eb-4a5b-aaa2-67851607906d/image.png)
---------------------------
(3) 지역별 ID 만들기
- 지도 시각화를 위해 지역별 ID 필요
- 일반 시에 있는 행정구 데이터 정리

admin_gu = {
    "수원": ["장안구", "권선구", "팔달구", "영통구"],
    "성남": ["수정구", "중원구", "분당구"],
    "안양": ["만안구", "동안구"],
    "안산": ["상록구", "단원구"],
    "고양": ["덕양구", "일산동구", "일산서구"],
    "용인": ["처인구", "기흥구", "수지구"],
    "청주": ["상당구", "서원구", "흥덕구", "청원구"],
    "천안": ["동남구", "서북구"],
    "전주": ["완산구", "덕진구"],
    "포항": ["남구", "북구"],
    "창원": ["의창구", "성산구", "진해구", "마산합포구", "마산회원구"],
    "부천": ["오정구", "원미구", "소사구"],
}

- 일반 시
    - 일반 도에 포함된 일반 시나 군의 지역 ID는 "시"를 뺀 지역명으로 함
    - row["시도"][:-1] --> 마지막 한 글자를 제외한 전체 (ex. '강릉시' --> '강릉', '옥천군' --> '옥천')
    - 고성군은 강원도와 경상남도에 동일한 이름으로 존재하므로 구분해 줄 것. (ex. 강원도 '고성군' --> '고성(강원)', 경상남도 '고성군' --> '고성(경남)')
- 세종특별자치시는 유일함

- 일반 구
    - 특별시, 광역시의 구는 해당 시의 앞 두글자[:2]와 구 이름을 합침
    - 구 이름이 세글자 이상이면 '구'를 제외한 글자[:-1]를 합침

- 행정구
    - 일반시(12개)의 행정구도 해상 시의 앞 두글자와 구이름을 합침
    - 구 이름이 세글자 이상이면 '구'를 제외한 글자[:-1]만 합침

def find_key_by_value(dictionary, value): for key, val in dictionary.items(): if value in val: return key return None // 값이 없는 경우

for idx, row in popul_pivot[["광역시도", "시도"]].iterrows(): if row["광역시도"][-3:] not in ["광역시", "특별시", "자치시"]: # 행정구 if row["시도"][-1:] == "구": key = find_key_by_value(admin_gu, row["시도"])
if len(key) > 0: if len(row["시도"]) == 2: sidoID[idx] = key + " " + row["시도"]
elif row["시도"] in ["마산합포구", "마산회원구"]: sidoID[idx] = key + " " + row["시도"][2:-1]
else: sidoID[idx] = key + " " + row["시도"][:-1] # 고성군 elif row["시도"] == "고성군": if row["광역시도"] == "강원도": sidoID[idx] = "고성(강원)" else: sidoID[idx] = "고성(경남)" # 일반시 else: sidoID[idx] = row["시도"][:-1]

  # 세종특별자치시
  elif row["광역시도"] == "세종특별자치시":
      sidoID[idx] = "세종"

  # 특별시/광역시 구
  else:
      if len(row["시도"]) == 2: // 서구, 중구, 북구, ...
          sidoID[idx] = row["광역시도"][:2] + " " + row["시도"]
      else:
          sidoID[idx] = row["광역시도"][:2] + " " + row["시도"][:-1]

![](https://velog.velcdn.com/images/jj_study/post/a4094f87-0d89-4278-be3e-9000f6ff01c1/image.png)
=======================
### 2. 카토그램 시각화

한글화 작업

import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import set_matplotlib_hangul
import warnings
warnings.filterwarnings(action="ignore")
%matplotlib inline

(1) 카토그램 구조 
- Excel로 만든 카토그램 큰 구조 읽기

draw_korea_raw = pd.read_excel("../data/07_draw_korea_raw.xlsx")

![](https://velog.velcdn.com/images/jj_study/post/dcc65789-c16b-47b7-8e5b-3e03e7c098eb/image.png)

(2) 시각화할 좌표 값 지정

BORDER_LINES = [
    [(5, 1), (5, 2), (7, 2), (7, 3), (11, 3), (11, 0)], # 인천
    [(5, 4), (5, 5), (2, 5), (2, 7), (4, 7), (4, 9), (7, 9),
     (7, 7), (9, 7), (9, 5), (10, 5), (10, 4), (5, 4)], # 서울
    [(1, 7), (1, 8), (3, 8), (3, 10), (10, 10), (10, 7),
     (12, 7), (12, 6), (11, 6), (11, 5), (12, 5), (12, 4),
     (11, 4), (11, 3)], # 경기도
    [(8, 10), (8, 11), (6, 11), (6, 12)], # 강원도
    [(12, 5), (13, 5), (13, 4), (14, 4), (14, 5),
     (15, 5), (15, 4), (16, 4), (16, 2)], # 충청북도
    [(16, 4), (17, 4), (17, 5), (16, 5), (16, 6), (19, 6), (19, 5),
     (20, 5), (20, 4), (21, 4), (21, 3), (19, 3), (19, 1)], # 전북
    [(13, 5), (13, 6), (16, 6)], # 대전시
    [(13, 5), (14, 5)], # 세종시
    [(21, 2), (21, 3), (22, 3), (22, 4), (24, 4), (24, 2), (21, 2)], # 광주
    [(20, 5), (21, 5), (21, 6), (23, 6)], # 전남
    [(10, 8), (12, 8), (12, 9), (14, 9), (14, 8), (16, 8), (16, 6)], # 충북
    [(14, 9), (14, 11), (14, 12), (13, 12), (13, 13)], # 경북
    [(15, 8), (17, 8), (17, 10), (16, 10), (16, 11), (14, 11)], # 대구
    [(17, 9), (18, 9), (18, 8), (19, 8), (19, 9),
     (20, 9), (20, 10), (21, 10)], # 부산
    [(16, 11), (16, 13)], # 울산
    [(27, 5), (27, 6), (25, 6)] # 제주도
]

- stack()
Excel에 그려진 각 행정구역 별 위치 좌표 얻어 옴

draw_kr_carto = pd.DataFrame(draw_korea_raw.stack()) draw_kr_carto.reset_index(inplace=True) draw_kr_carto.rename( columns={"level_0": "y", "level_1": "x", 0: "ID"}, inplace=True )

(3) Sample 카토그램
![](https://velog.velcdn.com/images/jj_study/post/f1ad31a1-4c63-4832-a5d3-1e1cee6e640e/image.png)

(4) 데이터 병합
![](https://velog.velcdn.com/images/jj_study/post/38b41fc7-94d1-4ff6-b7a9-23eec3206474/image.png)

- 인구 데이터와 Cartogram 좌표 데이터를 병합하기 전 검정

set(draw_kr_carto["ID"].unique()) - set(population["ID"].unique())
//==> set()

tmp_list = list(
  set(population["ID"].unique()) - set(draw_kr_carto["ID"].unique())
)
#==> ['수원', '전주', '청주', '천안', '성남', '부천',
#     '고양', '포항', '안양', '용인', '창원', '안산']

for item in tmp_list:
    population.drop(
        population[population["ID"] == item].index,
        inplace=True
    )

set(population["ID"].unique()) - set(draw_kr_carto["ID"].unique())
//==> set()

- 두 DataFrame을  ID 기준으로 병합

population = pd.merge( population, draw_kr_carto, how="left", on="ID" )

(5) 카토그램 그리기
- blockedMap : 인구현황 (population)
- targetData : population에서 그리고 싶은 컬럼

def get_data_info(targetData, blockedMap): vmin = min(blockedMap[targetData]) vmax = max(blockedMap[targetData])
white_label_min = (vmax - vmin) * 0.25 + vmin
map_data = blockedMap.pivot_table( index="y", columns="x", values=targetData )
return map_data, vmin, vmax, white_label_min

def get_data_info_for_zero_center(targetData, blockedMap): white_label_min = 5
tmp_max = max( [np.abs(min(blockedMap[targetData])), np.abs(max(blockedMap[targetData]))] ) vmin, vmax = -tmp_max, tmp_max
map_data = blockedMap.pivot_table( index="y", columns="x", values=targetData )
return map_data, vmin, vmax, white_label_min

def plot_text(targetData, blockedMap, white_label_min):
for idx, row in blockedMap.iterrows():
if len(row["ID"].split()) == 2: dispname = "{}\n{}".format( row["ID"].split()[0], row["ID"].split()[1] ) elif row["ID"][:2] == "고성": dispname = "고성" else: dispname = row["ID"]

      if len(dispname.splitlines()[-1]) >= 3:
          fontsize, linespacing = 9.5, 1.5 
      else:
          fontsize, linespacing = 11, 1.2

      annocolor = "white" if np.abs(row[targetData]) > white_label_min else "black"

      plt.annotate(
          dispname,
          (row["x"] + 0.5, row["y"] + 0.5), # 글자가 중앙에 위치
          weight="bold",
          color=annocolor,
          fontsize=fontsize,
          linespacing=linespacing,
          ha="center", # 수평 정렬
          va="center", # 수직 정렬 
      )

def drawKoreaCartogram(targetData, blockedMap, cmapName, zeroCenter=False): if zeroCenter: masked_mapdata, vmin, vmax, white_label_min = get_data_info_for_zero_center(targetData, blockedMap) else: masked_mapdata, vmin, vmax, white_label_min = get_data_info(targetData, blockedMap)

  plt.figure(figsize=(9, 11))
  plt.pcolor(
      masked_mapdata,
      vmin=vmin,
      vmax=vmax,
      cmap=cmapName,
      edgecolor="#aaaaaa",
      linewidth=0.5
  )    
  plot_text(targetData, blockedMap, white_label_min)

  for coord in BORDER_LINES:
      ys, xs = zip(*coord)
      plt.plot(xs, ys, c="black", lw=1.5)        

  plt.gca().invert_yaxis()
  plt.axis("off")
  plt.tight_layout()

  cb = plt.colorbar(shrink=0.1, aspect=10)
  cb.set_label(targetData)

  plt.show()

(6) 인구수 합계

drawKoreaCartogram( targetData="인구수합계", blockedMap=population, cmapName="Blues" )

![](https://velog.velcdn.com/images/jj_study/post/71538a80-76fc-4732-8e40-4d97fb6d09a6/image.png)

(7) 소멸 위기 지역

population["소멸위기지역"] = [ 1 if val else 0 for val in population["소멸위기지역"] ] drawKoreaCartogram("소멸위기지역", population, "Reds")

![](https://velog.velcdn.com/images/jj_study/post/6cadc74f-1319-4831-8b95-046b0760bcbf/image.png)

### 3. 지도 시각화

import pandas as pd import folium import json

geo_path = "../data/07_skorea_municipalities_geo_simple.json" geo_json = json.load(open(geo_path, encoding="utf-8"))

(1) 인구수 합계

popmap = folium.Map(location=[36.3002, 127.554], zoom_start=7)

folium.Choropleth(
        geo_data=geo_json,
        data=popul_folium["인구수합계"],
        key_on="feature.id",
        columns=[popul_folium.index, popul_folium["인구수합계"]],
        fill_color="YlGnBu"
).add_to(popmap)

popmap

![](https://velog.velcdn.com/images/jj_study/post/0613ae75-9c3a-48e8-a4d7-1c81b5578ceb/image.png)

(2) 소멸 위기 지역

popmap = folium.Map(location=[36.3002, 127.554], zoom_start=7)

folium.Choropleth(
        geo_data=geo_json,
        data=popul_folium["인구수합계"],
        key_on="feature.id",
        columns=[popul_folium.index, popul_folium["인구수합계"]],
        fill_color="YlGnBu"
).add_to(popmap)

popmap

![](https://velog.velcdn.com/images/jj_study/post/c1d03fe0-6c61-4eba-b622-591b8df6ac94/image.png)

### 4. Cartogram / 지도
![](https://velog.velcdn.com/images/jj_study/post/b230cc25-1416-4b9b-9e2e-65be63ca5351/image.png)

![](https://velog.velcdn.com/images/jj_study/post/bb1c2ad6-0543-47bc-be80-eee994e048a4/image.png)

프로젝트 - 네이버 쇼핑 검색 API

Mon, 18 Mar 2024 12:42:22 GMT

상품 "몰스킨" 검색, 쇼핑 정보 취득과정

1. gen_search_url()

검색 요청 URL 생성하는 함수
파라미터들을 URL에 포함시켜야 함
한번에 표시될 수 있는 검색 결과(display)의 최대 수 - 100개
만약 1000개의 데이터 얻고자 하면 start 파라미터 이용해 시작갑 start = 1, start = 101,..., start = 901 방식으로 분리해 지정

   import urllib

    def gen_search_url(api_node, search_text, start_num, disp_num):
        base = "https://openapi.naver.com/v1/search"
        node = "/" + api_node + ".json"
        param_query = "?query=" + urllib.parse.quote(search_text)
        param_start = "&start=" + str(start_num)
        param_disp = "&display=" + str(disp_num)

        return base + node + param_query + param_start + param_disp

    gen_search_url("shop", "Test", 10, 3)
    // 'https://openapi.naver.com/v1/search/shop.json?query=Test&start=10&display=3'

get_result_onepage()

url로 웹에 접근해 내용을 json으로 가져와 글자로 decode 해 줌

  import json
  import datetime
  from urllib.request import Request, urlopen

  def get_result_onepage(url):
      client_id = "*************"
      client_secret = "******"

      request = Request(url)
      request.add_header("X-Naver-Client-Id", client_id)
      request.add_header("X-Naver-Client-Secret", client_secret)

      response = urlopen(request)
      print("[%s] URL Request Success" % datetime.datetime.now())
      return json.loads(response.read().decode("utf-8"))
  url = gen_search_url("shop", "몰스킨", 1, 5)
  one_result = get_result_onepage(url)

  one_result

<결과>

3. get_fields()

검색 정보를 pandas DataFrame으로 만듬

  import pandas as pd

  def get_fields(json_data):
      title = [each["title"] for each in json_data["items"]]
      link = [each["link"] for each in json_data["items"]]
      lprice = [each["lprice"] for each in json_data["items"]]
      mall_name = [each["mallName"] for each in json_data["items"]]

      # title / lprice / link / mall이 디스플레이됨
      result_df = pd.DataFrame({
          "title": title,
          "lprice": lprice,
          "link": link,
          "mall": mall_name,
      })
      return result_df

  get_fields(one_result)

4. delete_tag()

title 부분 텍스트에 붙어 있는 태그 제거

def delete_tag(input_str): input_str = input_str.replace("", "") input_str = input_str.replace("", "") return input_str import pandas as pd def get_fields(json_data): title = [delete_tag(each["title"]) for each in json_data["items"]] link = [each["link"] for each in json_data["items"]] lprice = [each["lprice"] for each in json_data["items"]] mall_name = [each["mallName"] for each in json_data["items"]] result_df = pd.DataFrame({ "title": title, "lprice": lprice, "link": link, "mall": mall_name, }) return result_df get_fields(one_result)
5. actMain()

지금까지 과정을 통해 1000개의 데이터를 모두 모음

def actMain(api_node, search_text): total_result = [] # 1부터 1000까지 100개씩 for n in range(1, 1000, 100): url = gen_search_url(api_node, search_text, n, 100) json_result = get_result_onepage(url) df_result = get_fields(json_result) total_result.append(df_result) total_result = pd.concat(total_result) return total_result result_molskin = actMain("shop", "몰스킨") result_molskin
6. EXCEL로 저장

파이썬으로 엑셀을 다루는 xlsxwriter 모듈 이용해 데이터 저장

writer = pd.ExcelWriter( "./result_data/06_molskin_diary_in_naver_shop.xlsx", engine="xlsxwriter" ) result_molskin.to_excel(writer, sheet_name="Sheet1") workbook = writer.book worksheet = writer.sheets["Sheet1"] worksheet.set_column("A:A", 4) worksheet.set_column("B:B", 70) worksheet.set_column("C:C", 10) worksheet.set_column("D:D", 50) worksheet.set_column("E:E", 30) worksheet.set_column("F:F", 10) worksheet.conditional_format("C2:C1001", {"type": "3_color_scale"}) writer.close()
7. 시각화

몰스킨 제품이 팔리는 쇼핑몰 별로 보여줌

plt.figure(figsize=(15, 7)) sns.countplot( data=result_molskin, x=result_molskin["mall"], palette="RdYlGn", order=result_molskin["mall"].value_counts().index ) plt.xticks(rotation=90) plt.show()

네이버 검색 API 사용하기

Mon, 18 Mar 2024 11:56:09 GMT

1. 네이버 API 사용 등록

네이버 개발자 센터 https://developers.naver.com/main/

Application

애플리케이션 등록

애플리케이션 이름 ds_study

사용 API

검색

데이터랩 (검색어 트랜드)

데이터랩 (쇼핑인사이트)

환경추가

WEB설정

http://localhost

Client ID : 본인 소유

Client Secret : 본인 소유 https://developers.naver.com/apps/#/myapps/EsB6wpVn9dhqFyierBQi/overview

2. 네이버 검색 API 사용

네이버 검색 open API 예제 - 블로그 검색 https://developers.naver.com/docs/serviceapi/search/blog/blog.md#python

urllib : http 프로토콜에 따라서 서버의 요청/응답을 처리하기 위한 모듈

urllib.request : 웹서버에 웹페이지 요청하고 응답받기 위해 일반적으로ㅗ 브라우저 사용. 클라이언트의 요청을 처리하는 모듈

urllib.response : 서버의 응답을 처리

urllib.parse : URL 주소에 대한 분석

import os import sys import urllib.request client_id = "My_Client_ID" client_secret = "My_Client_SECRET" # request url 생성 # 한글을 URL에 추가하기 위해서 UTF-8 형식으로 URL인코딩 encText = urllib.parse.quote("파이썬") # JSON 요청 URL (여기서/search/blog?query 하면 blog, 책이면 book, 카페면 cafe, 쇼핑이면 shopping, 백과사전이면 encyc 대체해서 넣기) url = "https://openapi.naver.com/v1/search/blog?query=" + encText # encText에 이어 + "&display=3&sort=count" 를 붙여 (따옴표 포함 요청 URL + 요청 변수 보여주기. naver api사이트에서 확인가능) # XML 요청 URL (여기서/search/blog?query 하면 blog, 책이면 book, 카페면 cafe, 쇼핑이면 shopping, 백과사전이면 encyc 대체해서 넣기) url = "https://openapi.naver.com/v1/search/blog.xml?query=" + encText # REQUEST 객체 (URL요청과 관련된 정보를 담는 추상화된 클래스) 생성 및 Header 추가. add_header 메소드로 헤더정보 추가. 네이버 API 사용하려면 클라이언트 정보를 헤더에 포함시켜야 함 request = urllib.request.Request(url) request.add_header("X-Naver-Client-Id",client_id) request.add_header("X-Naver-Client-Secret",client_secret) # urlopen 및 response 획득 (url string 혹은 request객체를 전달 받음. header 정보 포함한 request 객체 전달) response = urllib.request.urlopen(request) rescode = response.getcode() # response의 HTTP status code를 리턴 # HTTP status code 확인 및 response 데이터 디코딩 # getcode 메서드 통해 response의 HTTP status code 확인 if(rescode==200): response_body = response.read() print(response_body.decode('utf-8')) else: # 네이버 오픈 API 에러 코드 목록 print("Error Code:" + rescode)

다른 카테고리 검색시 위에서 url만 바꿀것 예) 책 : book 카페 : cafe 쇼핑 : shop 백과사정 : encyc url = "https://openapi.naver.com/v1/search/<검색카테고리>?query=" + encText

https://developers.naver.com/docs/serviceapi/search/book/book.md

JSON 형식의 결과 데이터는 json.loads() 메소드로 파이썬에서 활용 가능한 객체 (예: dict)로 변환하여 사용함
import json json_rt = response.read().decode('utf-8') py_rt = json.loads(json_rt)
==========================================

프로젝트 - 시계열 데이터 웹 유입량 데이터 분석

Mon, 18 Mar 2024 08:20:06 GMT

1. Prophet 이용한 1년치 웹 유입량 데이터 분석 (pinkwink 블로그)

import numpy as np import pandas as pd import matplotlib.pyplot as plt from prophet import Prophet %matplotlib inline
(1) 데이터 읽어오기 * 데이터 읽어온 후 Nan 값 제거

pinkwink_web = pd.read_csv( "../data/05_PinkWink_Web_Traffic.csv", encoding="utf-8", thousands=",", names=["date", "hit"], index_col=0 ) pinkwink_web pinkwink_web = pinkwink_web[pinkwink_web["hit"].notnull()]

전체 데이터 시각화
pinkwink_web["hit"].plot(figsize=(12, 4), grid=True);

(2) 경향분석 (Numpy 이용) - Numpy 이용 경향성 분석 (trend) - 다항식 회귀(Polynomial Regression) 모델을 사용. 다항식 함수 사용하여 데이터 근사하는 회귀 모델
` (x, y 값 형성) - x값은 0부터 pinkwink_web 데이터 길이까지 365개의 값 ``` time = np.arange(0, len(pinkwink_web))

- y축은 PinkWink 블로그의 일일 유입량 ``` traffic = pinkwink_web["hit"].values
(회귀함수 생성)

TREND 파악을 위한 다항식 회귀함수 생성

np.polyfit() : 데이터에 적합한 다항식의 계수 구함

np.poly1d() : 계수 적용한 다항식 생성

다항식은 1차, 2차, 3차, 15차 함수까지 4개 생성 `` pf1 = np.polyfit(time, traffic, 1) f1 = np.poly1d(pf1)

pf2 = np.polyfit(time, traffic, 2) f2 = np.poly1d(pf2)

pf3 = np.polyfit(time, traffic, 3) f3 = np.poly1d(pf3)

pf15 = np.polyfit(time, traffic, 15) f15 = np.poly1d(pf15)

(경향성과 데이터 비교)

numpy로 만든 4종류의 trend 함수들과 실제 데이터를 그래프로 확인

np.polyfit() : 데이터에 적합한 다항식의 계수 구함

np.poly1d() : 계수 적용한 다항식 생성

다항식은 1차, 2차, 3차, 15차 함수까지 4개 생성

실제 데이터에 가장 맞는 경향성을 보이는 것은f15로 보임

(3) 오차 검증 (RMSE: rmse average: 제곱근오차평균) *-- 경향성 모델의 정확성 확인 지표 (RMSE: rmse average: 제곱근오차평균) *-- Numpy로 계산한 경향성과 실제 데이터의 오차가 얼마인지 확인해줄 error함수 생성

def error(f, x, y): return np.sqrt(np.mean((f(x) - y) ** 2)) print(error(f1, time, traffic)) print(error(f2, time, traffic)) print(error(f3, time, traffic)) print(error(f15, time, traffic)) --> 430.85973081109626 --> 430.6284101894695 --> 429.5328046676293 --> 330.47773079342267
error() 함수로 검증한 결과도 f15가 가장 작음

(4) Prophet이용한 경향 분석 및 미래 데이터 예측
*-- 데이터 생성 (Phrophet에 학습시킬 DataFrame 형식 - 웹 유입량 데이터 변형시킴)

df = pd.DataFrame({ "ds": pinkwink_web.index, "y": pinkwink_web["hit"] }) df.reset_index(inplace=True) del df["date"] df["ds"] = pd.to_datetime(df["ds"], format="%y. %m. %d.")
*-- 미래값 예측 (Phrophet으로 모델 생성 후 학습시킨 후 미래 예측값 얻어옴)

model = Prophet(yearly_seasonality=True, daily_seasonality=True) model.fit(df); future = model.make_future_dataframe(periods=60) forecast = model.predict(future) forecast[["ds", "yhat", "yhat_lower", "yhat_upper"]].tail()
*-- 예측값 시각화

model.plot(forecast);

*-- 경향분석 (미래 예측 데이터 포함 경향 분석)

model.plot_components(forecast);

*-- 오차 검증 (제곱근 오차 평균)

np.sqrt(np.mean((forecast["trend"]-df["y"])**2)) //--> 536.0321309635455
Prophet이 분석한 경향은 실제 데이터 분포와 상당한 오차 존재하는 편

2. 종합분석

실제 데이터 vs. Numpy로 계산한 trend vs. Prophet으로 예측한 trend

plt.figure(figsize=(14, 6)) plt.scatter(time, traffic, s=10) plt.plot(fx, f1(fx), lw=4, label='Numpy Trend') ptrend = forecast.loc[:364, "trend"].values plt.plot(time, ptrend, lw=4, label='Prophet Trend') plt.grid(True, linestyle="-", color="0.75") plt.legend(loc=2) plt.show()
Prophet이 분석한 경향보다 Numpy로 계산한 경향이 실제 데이터 추세와 더 잘 맞아 보임

프로젝트 - OIL PRICE분석

Mon, 18 Mar 2024 06:31:45 GMT

유가 (OIL PRICE) 분석 - 셀프 주유소가 저렴한가?

대한민국 주유가격 알아보는 사이트 오피넷 (OPINET)

1. 분석 데이터 웹 스크랩핑

웹 데이터 가져오기 (1) Selenium으로 Chrome 제어창 열기
from selenium import webdriver from selenium.webdriver.chrome.service import Service

chrome_driver_path = "../driver/chromedriver.exe" service = Service(executable_path = chrome_driver_path) options = webdriver.ChromeOptions()

driver = webdriver.Chrome(options = options, service = service)

- Opiniet 에서 싼 주유소 찾기 / 지역별 페이지로 접근
url = "https://www.opinet.co.kr/searRgSelect.do" driver.get(url)

- __ (2) id = "SIDO_NM0" 아이디 속성으로 접근 / 지역 / 시도/데이터 입력하기 ![](https://velog.velcdn.com/images/jj_study/post/001bc723-a6e3-4751-a1df-86152d2dc0f9/image.png)
sido_list_raw = driver.find_element(By.ID, "SIDO_NM0") sido_list = sido_list_raw.find_elements(By.TAG_NAME, "option") sido_names = [option.get_attribute("value") for option in sido_list] sido_names[:5] // ['', '서울특별시', '부산광역시', '대구광역시', '인천광역시'] del sido_names[0]

sido_list_raw.send_keys(sido_names[0]) // '서울' 입력
- __ (3) id = "SIGUNGU_NM0" 아이디 속성으로 접근해서 25개의 구 데이터 가져오기
부모 태그: