Database 분석 (2)

Database를 활용한 헬스케어 데이터 분석

3일차: SQL 실무 + MySQL 실습


2일차 복습

GROUP BY/ HAVING / LIMIT / Aggression


INNER JOIN


LEFT JOIN


JOIN + WHERE


ALIAS 별칭 / DISTINCT


GROUP BY


HAVING


LIMIT


SELECT

FROM

WHERE

GROUP BY

HAVING

ORDER BY

LIMIT


FROM

WHERE

GROUP BY

HAVING

SELECT

ORDER BY

LIMIT



SELECT

    patients.province_id

FROM

    patients 

INNER JOIN

    province_names 

ON

    patients.province_id = province_names.province_id 

WHERE

    province_names.province_name LIKE '%a' 

GROUP BY

    province_names.province_name

ORDER BY

    province_names.province_name desc

LIMIT 3;




SELECT*FROM patients

INNER JOIN province_names ON patients.province_id = province_names.province_id

GROUP BY provice_names.province_name

HAVING province_names.province_name LIKE '%a'

ORDER BY province_names.province_name DESC

LIMIT 3;


Aggregation 집계함수

COUNT()

SUM()

AVG()

MIN, MAX()

STDDEV()


SELECT 

COUNT (patient_id), SUM(height), AVG(weight), MIN(height), MAX(weight)

FROM patients;


COUNT()

SUM()

AVG()

MIN(),MAX()

STDDEV()


SELECT 

    province_id, 

    AVG(height) AS avg_height

FROM 

    patients

GROUP BY 

    province_id

HAVING 

    AVG(height) >= 155

ORDER BY 

    avg_height DESC

LIMIT 3;


SELECT AVG(patients.height) FROM patients

INNER JOIN province_names ON patients.province_id=province_names.province_id

GROUP BY province_names.province_name

HAVING AVG(patients.height) >= 155

ORDER BY AVG(patients.height) DESC

LIMIT 3;


https://docs.google.com/document/d/14FPzr8tovZsMALHi64nMcowd4Wf4Um1zR20EFxjVnv8/edit?tab=t.0#heading=h.o2ylpaynwmk1


SQL 기본 작업


Create -> INSERT

Read -> SELECT

Update -> UPDATE

Delete -> DELETE


데이터베이스 생성 & 삭제

생성 및 사용

CREATE DATABASE db_namer

USE db_name;


DROP DATABASE db_name;

Workbench에는 여러 개의 DB가 있을 수 있음

USE로 작업 대상 DB를 선택해야 함


CREATE DATABASE mytest1;

/*

*/

--

--


CREATE TABLE table_name

column1 datatype constraints,

column2 datatype constraints,

);


DROP TABLE 


create database mytest;

use mytest;


CREATE TABLE patient_info(

id INT auto_increment primary key,

name varchar(50) not null,

gender char(1),

birth_date date

city varchar(50) default 'Unknown'

);


DROP TABLE


CREATE TABLE labs (

lab_id INT AUTO_INCREMENT PRIMARY KEY,

patient_id INT,

lab_name VARCHAR(100),

result_value DECIMAL(10,4),

result_date DATE,

CONSTRAINT fk_labs_patient

FOREGIN KEY (patient_id) REFERENCES patients(patient_id)


DESCRIBE : 스키마 요약본 읽기

DESCRIBE table_name;

약자 DESC


SHOW

테이블 목록 확인

SHOW TABLES;


테이블 생성 SQL 전체 확인

SHOW CREATE TABLE table_name;


ALTER TABLE


DROP COLUMN


MODIFY


CREATE TABLE


DB Table 3개 생성


_______________________________________________________________________


create database mytest;

use mytest;


CREATE TABLE patient_info(

id INT auto_increment primary key,

name varchar(50) not null,

gender char(1),

birth_date date

city varchar(50) default 'Unknown'

);

_______________________________________________________________________


CREATE TABLE patients(

id INT auto_increment primary key,

name varchar(50) not null,

gender char(1),

birth_date date


);


CREATE TABLE diagnoses(

diag_id INT auto_increment primary key,

patient_id INT foreign key,

diagnosis_name varchar(50),

birth_date date


);


CREATE TABLE visits(

visit_id INT auto_increment primary key,

patient_id INT foreign key,

visit_date DATETIME,

doctor VARCHAR(50)


);


__________________________________________________________


-- 데이터베이스가 이미 존재할 경우 삭제하고 새로 생성합니다.

DROP DATABASE IF EXISTS mytest20251203;


-- 새 데이터베이스를 생성합니다.

CREATE DATABASE mytest20251203;


-- mytest20251203 데이터베이스를 사용합니다.

USE mytest20251203;


-- patients 테이블 생성

CREATE TABLE patients (

    id INT AUTO_INCREMENT PRIMARY KEY,

    name VARCHAR(50) NOT NULL,

    gender CHAR(1),

    birth_date DATE

);


-- diagnoses 테이블 생성

-- patient_id에 외래 키 제약 조건 추가

CREATE TABLE diagnoses (

    diag_id INT AUTO_INCREMENT PRIMARY KEY,

    patient_id INT,

    diagnosis_name VARCHAR(50),

    -- birth_date 컬럼은 diagnoses 테이블에서 불필요해 보여 제거했습니다.

    FOREIGN KEY (patient_id) REFERENCES patients(id)

);


-- visits 테이블 생성

-- patient_id에 외래 키 제약 조건 추가

CREATE TABLE visits (

    visit_id INT AUTO_INCREMENT PRIMARY KEY,

    patient_id INT,

    visit_date DATETIME,

    doctor VARCHAR(50),

    FOREIGN KEY (patient_id) REFERENCES patients(id)

);


일반적인 데이터베이스 설계에서는 birth_date와 같은 속성값보다는 환자의 고유 식별자인 patient_id (PK)를 외래 키로 사용하는 것이 훨씬 더 권장됩니다. 

birth_date를 외래 키로 사용하면 같은 생년월일을 가진 두 명 이상의 환자를 patients 테이블에 입력할 수 없게 됩니다.

__________________________________________________________


insert into patient_info (name, gender, birth_date, city)

values('Emily Park','F','1990-07-21','Seoul');


UPDATE

UPDATE table_name

SET column1 = value 1, column2 = value2

WHERE 조건;


WHERE를 빼면 전체 데이터가 변경된다.


DELETE

DELETE FROM table_name

WHERE 조건;

실문에서는 DELETE 대신 soft delete 사용

특히 병원 시스템에서는 DELETE 거의 금지

__________________________________________________________


SELECT patients


__________________________________________________________


Unhandled exception: 'cp949' codec can't decode byte 0xeb

in position 3: illegal multibyte sequence


 __________________________________________________________


create database mytest;

use mytest;


CREATE TABLE patient_info(

id INT auto_increment primary key,

name varchar(50) not null,

gender char(1),

birth_date date

city varchar(50) default 'Unknown'

);



CREATE TABLE patients(

id INT auto_increment primary key,

name varchar(50) not null,

gender char(1),

birth_date date


);


CREATE TABLE diagnoses(

diag_id INT auto_increment primary key,

patient_id INT foreign key,

diagnosis_name varchar(50),

birth_date date


);


CREATE TABLE visits(

visit_id INT auto_increment primary key,

patient_id INT foreign key,

visit_date DATETIME,

doctor VARCHAR(50)


);


__________________________________________________________


-- 데이터베이스가 이미 존재할 경우 삭제하고 새로 생성합니다.

DROP DATABASE IF EXISTS mytest20251203;


-- 새 데이터베이스를 생성합니다.

CREATE DATABASE mytest20251203;


-- mytest20251203 데이터베이스를 사용합니다.

USE mytest20251203;


-- patients 테이블 생성

CREATE TABLE patients (

    id INT AUTO_INCREMENT PRIMARY KEY,

    name VARCHAR(50) NOT NULL,

    gender CHAR(1),

    birth_date DATE

);


-- diagnoses 테이블 생성

-- patient_id에 외래 키 제약 조건 추가

CREATE TABLE diagnoses (

    diag_id INT AUTO_INCREMENT PRIMARY KEY,

    patient_id INT,

    diagnosis_name VARCHAR(50),

    -- birth_date 컬럼은 diagnoses 테이블에서 불필요해 보여 제거했습니다.

    FOREIGN KEY (patient_id) REFERENCES patients(id)

);


-- visits 테이블 생성

-- patient_id에 외래 키 제약 조건 추가

CREATE TABLE visits (

    visit_id INT AUTO_INCREMENT PRIMARY KEY,

    patient_id INT,

    visit_date DATETIME,

    doctor VARCHAR(50),

    FOREIGN KEY (patient_id) REFERENCES patients(id)

);


일반적인 데이터베이스 설계에서는 birth_date와 같은 속성값보다는 환자의 고유 식별자인 patient_id (PK)를 외래 키로 사용하는 것이 훨씬 더 권장됩니다. 

birth_date를 외래 키로 사용하면 같은 생년월일을 가진 두 명 이상의 환자를 patients 테이블에 입력할 수 없게 됩니다.

__________________________________________________________


USE health_ai;


create table patients(

id INT AUTO_INCREMENT PRIMARY KEY,

name varchar(50) not null,

gender enum('M','F'),

birth_date date

);


create table diagnoses

diag_id


__________________________________________________________

USE mytest20251203;


ALTER TABLE patients ADD age INT;


ALTER TABLE visits CHANGE doctor hospital VARCHAR(100);

_________________________________________________________________


-- 환자 (patients) 테이블 데이터 삽입

INSERT INTO patients (id, name, age, gender, birth_date) VALUES

(1, 'Charlie', 65, 'M', '1960-07-07'),

(2, 'Baley', 55, 'M', '1970-04-04'),

(3, 'Alfredo', 32, 'M', '1993-03-08'),

(4, 'Janet', 81, 'F', '1944-04-02'),

(5, 'Thomas', 21, 'M', '2004-02-01'),

(6, 'George', 8, 'M', '2017-07-09'),

(7, 'Louis', 70, 'M', '1955-05-11'),

(8, 'Karen', 57, 'F', '1968-06-06'),

(9, 'Morris', 66, 'F', '1959-05-18'),

(10, 'Yamin', 43, 'M', '1982-08-18');


-- 진단 (diagnosis) 테이블 데이터 삽입

-- diagnosis_id 컬럼은 이미지를 기반으로 임의의 ID를 사용했습니다.

INSERT INTO diagnosis (diagnosis_id, patient_id, diagnosis_name, diag_date) VALUES

(480, 1, 'Hypertension', '2016-07-08'),

(280, 2, 'Diabetes', '2012-04-18'),

(880, 3, 'Concussion', '2015-11-19'),

(90, 4, 'Fracture', '2014-04-14'),

(777, 5, 'Heartburn', '2025-04-01'),

(444, 6, 'Eczema', '2013-03-13'),

(987, 7, 'HeartFailure', '2016-06-06'),

(909, 8, 'Cataract', '2018-08-18'),

(432, 9, 'Glaucoma', '2012-02-12'),

(1208, 10, 'KidneyStone', '2017-07-07');


-- 방문 (visits) 테이블 데이터 삽입

-- visit_id 컬럼은 문자열 형식이므로 VARCHAR 타입이 필요합니다.

INSERT INTO visits (visit_id, patient_id, visit_date, hospital) VALUES

('00001-1', 1, '2016-07-08', 'Seoul Clinic'),

('00001-2', 2, '2012-04-18', 'Mayo Hospital'),

('00001-3', 3, '2015-11-19', 'Springfield Clinic'),

('00001-4', 4, '2014-04-14', 'Springfield Clinic'),

('00001-5', 5, '2025-04-01', 'Seoul Clinic'),

('00001-6', 6, '2013-03-13', 'Mayo Hospital'),

('00001-7', 7, '2016-06-06', 'Springfield Clinic'),

('00001-8', 8, '2018-08-18', 'Seoul Clinic'),

('00001-9', 9, '2012-02-12', 'Springfield Clinic'),

('00001-10', 10, '2017-07-07', 'Mayo Hospital');


댓글

댓글 쓰기

이 블로그의 인기 게시물

베이스 캠프에서 (1)

이미지
베이스 캠프에서는 우선 시작을 독려해주는 글과 함께 Visual Studio Code를 설치할 것을 요청했다. 일단 VS Code의 경우 이미 내가 사용하는 기기에 설치가 되어 있었기 때문에 extensions만 추가로 설치했다.  우선 Python이 설치를 하였고 그 외에도 코드의 가독성을 높이는  Indent Rainbow, Better Comments, Colorize 등을 설치하였다. 한국어로 진행되는 과정인 만큼 VS Code용 한국어 팩도 설치했다. 다음으로 계속해서 캠프 미션을 따라갔다. 터미널(MAC을 사용할 경우. Windows는 PowerShell 이다.)에 대해 알아보았다. 터미널이란 command line interface로 서버 관리, 개발, 자동화 작업 등에 사용되는 명령어 등을 입력할 수 있는 환경이다. 터미널에서 사용되는 명령어는 파일 관리, 프로세스 제어, 네트워크 설정 등의 작업에 사용되고 시스템 자원에 접근하고 제어하는 도구다. Command Line Interface 와 Graphic User Interface에는 차이가 있다. 우리가 흔히 컴퓨터를 사용할 때 마우스 등을 통해 작업 실행을 하는 것이 Graphic User Interface이다. 반면에 Command Line Interface의 경우 키보드 등으로 명령어를 입력하고 텍스트로 출력되는 형태로 시스템을 제어하는 것이다. Git, Node.js, Docker, Kubernetes, AWS CLI, Terraform 등은 모두 CLI를 기반으로 동작하며 여러 서버는 CLI 환경에서 관리한다. 특히 리눅스가 그러하며 원격으로 서버에 접속해 관리하거나 배포할 경우 이용되어야 하기 때문에  개발자들은 필수로 사용법을 익혀야 하는 사항이다. 그 다음 목차는 Git 과 GitHub에 관한 내용이다. Git 이란 코드의 변경 이력을 추적하고 관리하는 데 이용하는 관리 시스템으로 개인 컴퓨터에서 동작하며 인터넷 연결 없이도 관리가 가능한 시스템이다. G...
자세한 내용 보기

베이스 캠프에서 (2)

이미지
 HTML이나 XML 같은 마크업 언어로 스타일 시트 언어인 CSS 언어에 대해서 베이스 캠프의 링크를 타고 들어가 배우고 실습에 도전했다. 처음에는 오류가 나 명령어가 구현된 화면에 나타나는 것을 볼 수 있었다. 끝인 줄 알았지만 " ‘실습 도전하기’만 bold처리 하세요."로 되어 있었다. <! DOCTYPE html > < html lang = "ko" > < head >     < meta charset = "UTF-8" >     < title > 오늘의 할 일 </ title >     < style >         body {             background-color : #f4f4f4 ;             font-family : Arial , sans-serif ;         }         h1 { color : #3498db ; }         p { font-size : 18px ; }         a { text-decoration : none ; }     </ style > </ head > < body >     < a href = "https://ozcodingschool.com/" >< h1 > 오늘의 할 일 </ h1 ></ a >     < ul >         < li > HTML 예습하...
자세한 내용 보기

Database 분석 (4)

이미지
오늘은 데이터베이스 프로젝트를 진행했다.  문제는 다음과 같았다. # 📝 문제 리스트 아래 문제 중 **4개 이상 (필수 1개 + 선택 3개 이상)** 을 선택하여 수행합니다.   ## [필수] 1) 고심박(high HR) 위험군 탐지 - 일정 기준(예: heart_rate > 150)을 넘는 순간을 찾아서   - 발생 시각     - 해당 활동     - 발생 빈도를 분석한다. ## 2) 활동(activity)별 평균 생체지표 분석 - 활동별로 heart_rate, step_count, resp_rate, body_temp의 평균을 계산하고 비교한다. - 어떤 활동이 가장 높은 생체 지표를 보이는지 분석한다. ## 3) 체온 이상치(high fever) 탐지 - body_temp ≥ 38.0°C인 구간을 찾아     - 어떤 활동에서 많이 나타나는지     - 위험 패턴이 존재하는지 분석한다. ## 4) 산소포화도(SpO₂) 저하 위험 탐지 - blood_oxygen < 94% 데이터를 필터링하여     - 발생 시각     - activity를 분석한다. ## 5) 활동별 활동량(step_count) 분석 - 활동별로 step_count 합계와 평균을 구하고   - 가장 활동량이 많은 활동을 찾는다. ## 6) 센서 오류(is_tampered) 데이터 탐지 - is_tampered = 1 이 발생한 횟수를 파악하고   - 오류가 어떤 활동에서 많이 발생하는지 분석한다. ## 7) 시간대별 생체지표 분석 - 시간(HOUR) 기준으로 데이터를 그룹화하여     - 시간대별 평균 heart_rate     - 시간대별 평균 body_temp 패턴을 분석한다. ## 8) ...
자세한 내용 보기