[팀프로젝트] sql에 csv 데이터 넣기

create database database_name

후 use 데이터베이스


1. 첫번째

create table rent_bic(
bike_id varchar(20),
rent_dt varchar(100),
rent_id varchar(20),
rent_nm varchar(100),
rent_hold varchar(20),
rtn_dt varchar(100),
rtn_id varchar(20),
rtn_nm varchar(100),
rtn_hold varchar(20),
use_min varchar(20),
use_dst varchar(20),
birth_year varchar(20),
sex_cd varchar(20),
usr_cls_cd varchar(20),
rent_station_id varchar(20),
rtn_station_id varchar(20),
bike_se_cd varchar(20));


LOAD DATA LOCAL INFILE 'C:/Users/Jwjung/Desktop/data_analysis/real_csv/rent_bk9.csv'
INTO TABLE rent_bic
FIELDS TERMINATED BY '|'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

select * from rent_bic;

select sex_cd,count(1) from rent_bic group by 1;

select count(counting) from rent_bic;


2. 두 번째

create table rent_area(
rent_no varchar(20),
rent_nm varchar(20),
sta_add1 varchar(20),
sta_add2 varchar(50),
sta_lat varchar(20),
sta_long varchar(20),
ins_dt varchar(20),
lcd varchar(20),
qr varchar(20),
sty varchar(20));

LOAD DATA LOCAL INFILE 'C:/Users/Jwjung/Desktop/data_analysis/real_csv/rent_area2.csv'
INTO TABLE rent_area
FIELDS TERMINATED BY '|'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

select * from rent_area;

select count(rent_no) from rent_area;




3. 세 번째

create table rent_a(
rent_dt varchar(20),
rent_id varchar(20),
rent_nm varchar(100),
rtn_dt varchar(20),
rtn_id varchar(20),
rtn_nm varchar(100),
birth_year varchar(20),
sex_cd varchar(20));



insert into rent_a(rent_dt,rent_id,rent_nm,rtn_dt,rtn_id,rtn_nm,birth_year,sex_cd)
select rent_dt,rent_id,rent_nm,rtn_dt,rtn_id,rtn_nm,birth_year,sex_cd from rent_bic;


select a.rent_dt, 
       a.rent_id, 
       a.rent_nm, 
       a.rtn_dt,
       a.rtn_id, a.rtn_nm, a.birth_year, a.sex_cd, b.sta_add1
from rent_a a join rent_area b on a.rent_id=b.rent_no
where sta_add1='강남구' or sta_add1='송파구' or sta_add1='서초구';


4. 네 번째

create table rent_data(
rent_dt datetime,
rent_id int,
rent_nm varchar(100),
rtn_dt datetime,
rtn_id int,
rtn_nm varchar(100),
birth varchar(20),
sex varchar(20),
gu varchar(20));


insert into rent_data(rent_dt,rent_id,rent_nm,rtn_dt,rtn_id,rtn_nm,birth,sex,gu)

select *
from (select a.rent_dt, 
             a.rent_id, 
             a.rent_nm, 
             a.rtn_dt,
             a.rtn_id,
             case when a.rtn_nm=' ' then null 
                  else a.rtn_nm end rtn_nm,
             case when a.birth_year=' ' then null 
                  else a.birth_year end birth,
             case when a.sex_cd=' ' then null 
                  else a.sex_cd end sex,
             b.sta_add1
      from rent_a a 
      inner join rent_area b 
      on a.rent_id = b.rent_no
      where sta_add1='강남구' or sta_add1='송파구' or sta_add1='서초구'
)a
where rtn_nm is not null and sex is not null and birth is not null and birth between 1924 and 2008;


## 기초 통계

### 전체 데이터
select * from rent_data;

### 성별
select sex '성별',
count(1) '계'
from rent_data group by 1 order by 2;


### 나이대
select case when birth > 1995 then '20th'
when birth between 1985 and 1996 then '30th'
when birth between 1975 and 1986 then '40th'
when birth between 1965 and 1976 then '50th'
when birth between 1955 and 1966 then '60th'
when birth between 1945 and 1956 then '70th'
when birth <= 1944 then '80th_over' end '생년',
count(1) '계'
from rent_data group by 1 order by 1;


### 대여 많이한 대여소
select a.rent_id,b.sta_add1,b.rent_nm,count(1)
from rent_data a join rent_area b
on a.rent_id=b.rent_no group by rent_id order by 4 desc;


### 반납 많이한 대여소
select a.rtn_id,b.sta_add1,b.rent_nm,count(1)
from rent_data a join rent_area b
on a.rtn_id=b.rent_no group by rtn_id order by 4 desc;