관리자 글쓰기
[mySQL] 집계함수: COUNT, SUM, AVG, GROUP BY
2020. 2. 4. 16:06 - MunJunHyeok

저번 게시글에서 sampleDB를 이용해 WHERE 구의 사용과 연산자 등에 대해 살펴보았습니다.

 

Sample DB 설정 방법은 다음 링크를 참조하세요. (https://m-datastudy.tistory.com/5)

 

이번에는 SELECT ~ FROM 쿼리(구문)에 사용할 수 있는 여러가지 함수를 알아볼것입니다. 저번 게시물 (https://m-datastudy.tistory.com/9)를 보신 분들은 이제 원하는 조건을 지정해서 테이블로부터 레코드를 불러 올 수 있습니다. 

그런데 만약 레코드의 개수가 엄청나게 많아서 개수조차 세기 힘들 때에는 어떻게 해야 할까요?

 

그럴 때는 집계함수를 이용해 한 컬럼 내의 레코드의 개수를 세거나 합과 산술평균 등을 낼 수 있습니다. 

 

1. 집계함수 : COUNT(개수), SUM(총합), AVG(평균), GROUP BY(묶음)

 

 

◈ COUNT 함수는 컬럼내 레코드의 개수를 세주는 함수로 다음과 같이 사용합니다.

-> SELECT COUNT(컬럼명1) FROM 테이블명;

-> SELECT COUNT(컬럼명1), COUNT(컬럼명2), ... FROM 테이블명;

다음은 예시입니다.

customerNumber의 레코드 개수는 122개이다.

COUNT 함수는 레코드의 자료형에 상관없이 사용할 수 있습니다. 

다만 특별한 경우를 제외하면, 집계함수일반 컬럼을 함께 사용할 경우 오류가 발생합니다.

즉, 다음과 같은 경우에는 오류가 발생합니다.

-> SELECT COUNT(customerNumber), customerNumber FROM customers; 

 

 

◈ SUM 함수는 컬럼 내 레코드 값의 총합을 내주는 함수입니다.

다음과 같이 사용합니다.

-> SELECT SUM(컬럼명1) FROM 테이블명;

-> SELECT SUM(컬럼명1), SUM(컬럼명2), ... FROM 테이블명;

다음은 예시입니다.

customerNumber의 총 합은 36,161이다. 물론 고객번호를 더해봤자 의미있는 숫자가 나오는 것은 아니다. 어디까지나 연습용임을 잊지말자.

SUM 함수는 컬럼의 자료형이 숫자형일때만 사용할 수 있습니다. 

그리고 같은 집계함수끼리는 다음과 같이 함께 사용할 수 있습니다.

 

위와 달리 집계함수가 사용되지 않은 컬럼이 함께 쿼리로 작성될 시엔 오류가 발생한다.

 

◈ AVG 함수는 컬럼 내 레코드 값의 평균을 구해줍니다. 

다음과 같이 사용합니다.

-> SELECT AVG(컬럼명1) FROM 테이블명;

-> SELECT AVG(컬럼명1), AVG(컬럼명2), ... FROM 테이블명;

다음은 예시입니다.

customerNumber의 평균은 296.4016이다.

마찬가지로 AVG 함수는 컬럼의 자료형이 수치형일 때만 사용할 수 있습니다.

 

 

◈ GROUP BY 함수는 종류가 같은 것들이 하나로 묶입니다.

GROUP BY는 쿼리(구문)의 가장 마지막에 사용합니다. 기준을 제시하면, 그 기준에 따라 모아줍니다.

말로 설명하면 이해하기 힘들 수 있으므로 예시로 확인하겠습니다.

 

customers 테이블에서 city 컬럼만 불러와 30개의 레코드만 출력합니다.

도시가 중복되는 것이 있는데 같은 도시가 여러번 나오기도 하고 중구난방으로 배열되어있어서 알아보기 힘듭니다.

 

그때 GROUP BY를 사용하면 더 보기 편해집니다. 중복되는 데이터는 하나로 모아줍니다.

 

그럼 다음과 같이 테이블 내에 표시된 모든 도시의 종류를 한번에 볼 수 있습니다.

 

중복되는 도시를 제외하고 각 도시를 하나씩만 보여줍니다.

GROUP BY 함수는 자료형 상관없이 사용할 수 있습니다. 

 

그리고 다른 집계함수와 연계하여 사용하면 그 진가를 발휘합니다.

 

어떤 도시에 몇 명의 고객이 있는지 한눈에 파악할 수 있다.

예외적으로 GROUP BY 함수에 기준으로 제시된 컬럼은 위와 같이 집계함수와 함께 사용될 수 있습니다.

 

또한 중요한 것으로 모든 집계함수는 WHERE구와 함께 사용할 수 없고, 대신 HAVING 구를 사용하여 조건식을 지정할 수 있습니다.

구문이 처리되는 순서는 GROUP BY -> SELECT -> ORDER BY 순으로 처리됩니다.

 

[mySQL] SELECT 구문의 확장. WHERE .
2020. 1. 17. 14:45 - MunJunHyeok

저번 게시글에서 sampleDB와 SELECT 예약어(SELECT, FROM, * 등의 구문을 예약어라고 합니다)를 이용해 간단한 테이블을 불러오고, 테이블에 대한 정보를 확인하는 법을 살펴보았습니다.

 

Sample DB 설정 방법은 다음 링크를 참조하세요. (https://m-datastudy.tistory.com/5)

 

이번에는 SELECT~FROM 예약어를 더 활용해보겠습니다. 저번 게시물 (https://m-datastudy.tistory.com/6?category=856604)를 보신 분들은 이제 테이블의 컬럼을 지정해서 불러올 수 있습니다. 그렇다면 컬럼 내에서 원하는 데이터를 선택하려면 어떻게 해야 할까요?

 

1. WHERE 구에서 레코드(row) 지정하기.

다음은 customer라는 테이블에서 customerNumber, customerName, city 컬럼을 불러오고, 그 중 customerNumber가 200 이하인 고객의 정보만 불러오는 명령입니다.

 

다음과 같이 WHERE 구는 컬럼에 조건을 설정해 특정 데이터만을 불러 올 수 있게 해줍니다. WHERE 구를 사용하기 위해서는 먼저 알아야 하는 것이 데이터의 자료형과 연산자 입니다.

 

2. 데이터의 자료형과 연산자.

 

일상생활에선 글을 쓸 때 한 페이지에 숫자이든, 글자이든 그저 손으로 적으면 됩니다. 하지만 컴퓨터는 이런 작업을 할 수 없습니다. 한 페이지마다 숫자를 사용할지, 글자를 사용할지, 숫자나 글자를 몇개 넣을지, 아니면 숫자나 글자를 같이 사용할지 등을 따로 정한 뒤에 써야합니다. 가장 많이 사용되는 자료형은 다음과 같습니다.

저번 게시글에서 desc 명령어를 통해 테이블의 구조를 살펴보았습니다. 그때 이미 int와 varchar는 본 적이 있습니다. char와 varchar의 차이는 char는 정확히 입력한 용량만큼 글자가 들어가야합니다. 더 적거나 많아도 안됩니다. varchar는 이러한 제약으로부터 자유롭습니다. 대신 char보다 컴퓨터의 처리속도를 더 많이 깎아먹게 됩니다.

 

다음은 비교연산자입니다. 이러한 비교연산자들은 다른 코딩 프로그램에서도 거의 같게 사용됩니다.

 

테이블이나 컬럼의 자료형이 문자형일 때는 =, <, >, <=, >= 연산자는 사용할 수 없음에 유의해주세요.

 

3. (추가) 출력 레코드의 제한. LIMIT ~ OFFSET ~

LMIT ~ OFFSET ~ 구문을 이용해 출력할 레코드의 수를 조정할 수 있습니다.

 

offset 0 (0번째 줄부터) limit 3 (3줄만)

LIMIT은 출력할 레코드의 개수를 지정하는 것입니다.

OFFSET은 어느 레코드부터 개수를 지정할지 정하는 것입니다.

 

[Practice] Web Crawling 특강 정리
2019. 12. 21. 18:42 - MunJunHyeok
Seminar note

2019년 11월 26일 명지대학교에서 진행된 박찬엽 (현) 코빗 재무팀 데이터 담당자(https://mrchypark.github.io) 님의 웹 크롤링 특강 중, 간단한 웹크롤링 방법을 정리합니다.

# txt를 긁어올 링크를 tar변수에 저장.
tar <- "https://news.naver.com/main/read.nhn?mode=LSD&mid=shm&sid1=100&oid=005&aid=0001262837"
# 서버에 data 요청 후 html 문서화.
read_html(tar) %>% 
 html_nodes("h3#articleTitle") %>%
 html_text()
## [1] "지소미아 발표 후 日대사관 “죄송하다… 이건 정무차관 메시지”"


# 전문을 긁어오지만 html코드등이 포함되어 다른 방식으로 처리해야함.
read_html(tar) %>%
  html_nodes("div#articleBodyContents") %>%
  as.character()
## [1] "<div id=\"articleBodyContents\" class=\"_article_body_contents\">\n\t<!-- 본문 내용 -->\n\t<!-- TV플레이어 -->\n\n<!-- // TV플레이어 -->\n<script type=\"text/javascript\">\n// flash 오류를 우회하기 위한 함수 추가\nfunction _flash_removeCallback() {}\n</script><span class=\"end_photo_org\"><img src=\"https://imgnews.pstatic.net/image/005/2019/11/26/611112110013969135_1_20191126141804901.jpg?type=w647\" alt=\"\"></span><br><br>일본 정부가 한일 군사정보보호협정(GSOMIA·지소미아) 조건부 종료 연기 결정에 대한 양국 합의내용을 실제와 달리 발표한 데 대해 외무성 차관의 사과 메시지를 한국 측에 전달했던 것으로 확인됐다. 또 우리 정부가 일본 측 발표내용이 실제와 다르다고 공개적으로 반박한 데 대해 일본 정부는 아직까지 우리 정부에 공식 항의하지 않은 것으로 전해졌다.<br><br>26일 복수의 정부 관계자에 따르면, 지소미아 조건부 종료 연기 결정이 양국에서 발표된 22일 오후 9시가 넘은 시각 외교부는 주한일본대사관 정무공사를 불러 들였다. 이날 오후 6시 우리 정부의 지소미아 조건부 종료 연기 결정 직후 일본 경제산업성(경산성)이 ‘반도체 관련 3개 품목 수출 규제 및 화이트리스트 제외 조치에 당장 변화는 없다’고 발표한 데 대해 항의하고자 한 것이었다.<br><br>일본 발표 전 청와대 고위관계자는 기자들에게 ‘현안 해결에 기여하도록 국장급 대화를 해 양국 수출관리를 상호 확인한다’ ‘한일 간 건전한 수출실적 축적 및 한국 측의 적정한 수출관리 운용을 위해 (규제대상 품목 관련) 재검토가 가능해진다’ 등의 내용이 담길 것이라고 설명했다.<br><br>외교부는 이같은 양국간 합의 내용과 다르게 일본 정부 입장이 보도된 데 대해 강하게 문제를 제기했다고 한다. 이에 일본 대사관 정무공사는 경산성의 발표에 대해 ‘죄송하다’는 표현과 함께 사과를 하면서 이는 정무공사 개인의 입장이 아니라 일본 외무성 차관의 메시지라고 밝혔다는 것이 정부 관계자들의 설명이다.<br><br>일본 정부가 이렇게 사과의 뜻을 밝혔음에도 24일 아베 신조(安倍晋三) 일본 총리는 ‘일본은 아무것도 양보하지 않았다’고 말했다. 이에 청와대는 “그 발언이 사실이면 지극히 실망”이라면서 “일본 정부 지도자로서 과연 양심을 갖고 할 수 있는 말인지 되묻지 않을 수 없다”고 강하게 비판했다.<br><br>정의용 청와대 국가안보실장도 나서 일본 측이 ‘한국이 지적한 입장을 이해한다’면서 ‘경산성에서 부풀린 내용으로 발표한 데 대해 사과한다’고 밝혔다고 전했다. 논란이 계속되자 25일에는 윤도한 청와대 국민소통수석은 서면브리핑을 통해 “일본 측은 분명히 사과했다”며 “일본 측이 사과한 적이 없다면 공식 루트를 통해 항의해 올 것”이라고 재차 강조했다.<br><br>이같은 청와대 반응에 대해 26일 현재 일본 정부로부터 공식적으로 항의가 들어온 것은 없는 것으로 알려졌다.<br><br>김남중 기자 njkim@kmib.co.kr<br><br><b><a href=\"http://naver.me/GxmvUNz3\" target=\"_blank\"><font color=\"f98b10\">[국민일보 채널 구독하기]</font></a></b><br><b><a href=\"https://m.post.naver.com/my.nhn?memberNo=12282441\" target=\"_blank\"><font color=\"f98b10\">[취향저격 뉴스는 여기]</font></a> <a href=\"https://www.youtube.com/channel/UCb-AbqZutk9nTlJLZRcBinw\" target=\"_blank\"><font color=\"f98b10\">[의뢰하세요 취재대행소 왱]</font></a></b><br><br>GoodNews paper ⓒ <a href=\"http://www.kmib.co.kr\" target=\"_blank\">국민일보(www.kmib.co.kr)</a>, 무단전재 및 재배포금지\n\t<!-- // 본문 내용 -->\n\t</div>"



- 다음은 특강 이후에 추가적으로 알아낸 wikipedia에서 table을 긁어와 이를 histogram으로 그리는 방법입니다.

# table을 가져올 링크를 tar2에 저장합니다.
tar2 <- "https://en.wikipedia.org/wiki/List_of_countries_by_GDP_(nominal)_per_capita"
# 링크에서 필요한 테이블을 Xpath를 이용해 특정합니다. 이후 df에 저장합니다.
df <- read_html(tar2) %>%
  html_nodes(xpath = '//*[@id="mw-content-text"]/div/table/tbody/tr[2]/td[1]/table') %>%
  html_table(fill = T) %>% 
  as.data.frame()
# df의 구조 확인.
str(df)
## 'data.frame':    193 obs. of  3 variables:
##  $ Rank             : chr  "1" "2" "—" "3" ...
##  $ Country.Territory: chr  "Luxembourg" "Switzerland" "Macau" "Norway" ...
##  $ US.              : chr  "114,234" "82,950" "82,388" "81,695" ...
# 1인당 GDP 항목의 데이터가 character이고 숫자 사이에 쉼표가 들어가 있어 바로 numeric으로 바꿀 시에 오류가 있을 수 있음을 알 수 있습니다.
head(df$US.)
## [1] "114,234" "82,950"  "82,388"  "81,695"  "76,099"  "74,278"
# column명을 알기 쉽도록 변경하고, character로 되어있는 GDP 순위와 1인당 GDP column을 numeric으로 바꿉니다.
names(df) <- c('Rank.GDP','Country','US.dollar')
df$Rank.GDP <- as.numeric(df$Rank.GDP)
## Warning: 강제형변환에 의해 생성된 NA 입니다
# 데이터가 너무 많기에 상위 30위까지만 선택합니다.
df <- subset(df, df$Rank.GDP <= 30)
# 1인당 GDP 항목을 gsub 함수를 이용해 숫자 사이에 들어간 쉼표를 제거 후 numeric으로 바꿔줍니다.
df$US.dollar <- as.numeric(gsub(",", "", df$US.dollar))
df$Country <- as.factor(df$Country)
# 다시 확인해 봅니다.
str(df)
## 'data.frame':    30 obs. of  3 variables:
##  $ Rank.GDP : num  1 2 3 4 5 6 7 8 9 10 ...
##  $ Country  : Factor w/ 30 levels "Australia","Austria",..: 18 27 22 12 11 23 25 30 7 1 ...
##  $ US.dollar: num  114234 82950 81695 76099 74278 ...
head(df$US.dollar)
## [1] 114234  82950  81695  76099  74278  70780



- 이후 histogram을 그려봅니다.

df %>%
  ggplot(mapping = aes(x = reorder(Country, Rank.GDP), y = US.dollar)) +
  geom_histogram(stat = 'identity')
## Warning: Ignoring unknown parameters: binwidth, bins, pad

 

  • 남녀 경제 활동 참가율 분석 (해외)



1. 데이터 불러오기 및 데이터 정리

par <- read.csv('women_work.csv')
str(par)
## 'data.frame':    2000 obs. of  13 variables:
##  $ c1           : num  -0.436 0.352 1.077 1.021 -0.443 ...
##  $ c2           : num  -0.0969 0.3005 -1.596 -1.7105 0.3083 ...
##  $ u            : num  -0.218 0.176 0.539 0.511 -0.221 ...
##  $ v            : num  -0.3757 0.4612 -0.3762 -0.497 -0.0925 ...
##  $ county       : int  1 2 3 4 5 6 7 8 9 0 ...
##  $ age          : int  22 36 28 37 39 33 57 45 39 25 ...
##  $ education    : int  10 10 10 10 10 10 10 16 12 10 ...
##  $ married      : int  1 1 1 1 1 1 1 1 1 0 ...
##  $ children     : int  0 0 0 0 1 2 1 0 0 3 ...
##  $ select       : num  16.8 32.4 19.2 21.3 32 ...
##  $ wagefull     : num  12.8 20.3 23.1 24.5 16.1 ...
##  $ wage         : num  NA 20.3 NA NA 16.1 ...
##  $ participation: int  0 1 0 0 1 1 1 1 0 1 ...
par$married <- factor(par$married,
                      labels = c('single',
                                 'married'))
table(par$married)
## 
##  single married 
##     659    1341



* 참가율(%) 산출

## # A tibble: 2 x 3
##   participation     n percent
##           <int> <int>   <dbl>
## 1             0   657    32.8
## 2             1  1343    67.2




2. linear probaility model (linear regression), 선형회귀모델. age, education, married, children이 한 단위 늘었을 때, 경제활동 참가를 할 ’확률’이 얼마나 증가하는가?

## 
## Call:
## lm(formula = participation ~ age + education + married + children, 
##     data = par)
## 
## Residuals:
##     Min      1Q  Median      3Q     Max 
## -1.0703 -0.4142  0.1372  0.3437  0.8060 
## 
## Coefficients:
##                 Estimate Std. Error t value Pr(>|t|)    
## (Intercept)    -0.207323   0.054111  -3.831 0.000131 ***
## age             0.010255   0.001227   8.358  < 2e-16 ***
## education       0.018601   0.003250   5.724 1.20e-08 ***
## marriedmarried  0.111112   0.021948   5.063 4.52e-07 ***
## children        0.115308   0.006772  17.028  < 2e-16 ***
## ---
## Signif. codes:  0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
## 
## Residual standard error: 0.4199 on 1995 degrees of freedom
## Multiple R-squared:  0.2026, Adjusted R-squared:  0.201 
## F-statistic: 126.7 on 4 and 1995 DF,  p-value: < 2.2e-16


* 선형회귀분석한 값을 이용한 예측모형과 그래프 (결혼 여부에 따른 차이)


-> 예측에 사용하려 했으나 그래프의 범위가 0과 1 사이를 벗어나 예측이 불가능하다.

3. generalized linear model (apply logistic function)
* age, education, married, children이 한 단위 늘었을 때, ’single index’가 얼마나 증가하는가? 즉, 확률이 늘어나는가? 혹은 줄어드는가? 여부만 알 수 있다.

## 
## Call:
## glm(formula = participation ~ age + education + married + children, 
##     family = "binomial", data = par)
## 
## Deviance Residuals: 
##     Min       1Q   Median       3Q      Max  
## -2.6212  -0.9292   0.4614   0.8340   2.0455  
## 
## Coefficients:
##                 Estimate Std. Error z value Pr(>|z|)    
## (Intercept)    -4.159247   0.332040 -12.526  < 2e-16 ***
## age             0.057930   0.007221   8.022 1.04e-15 ***
## education       0.098251   0.018652   5.268 1.38e-07 ***
## marriedmarried  0.741777   0.126471   5.865 4.49e-09 ***
## children        0.764488   0.051529  14.836  < 2e-16 ***
## ---
## Signif. codes:  0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
## 
## (Dispersion parameter for binomial family taken to be 1)
## 
##     Null deviance: 2532.4  on 1999  degrees of freedom
## Residual deviance: 2055.8  on 1995  degrees of freedom
## AIC: 2065.8
## 
## Number of Fisher Scoring iterations: 5


-> age, education, married, children 이 한 단위 증가할 때, 경제활동참가율이 (어느 정도인지는 모르나) 증가한다.

* glm의 그래프


-> 모든 값이 0과 1 사이에 들어와 예측모형에 활용 할 수 있다.
* 일반적으로 경제학자들은 이 그래프의 추세선의 기울기, 머신러닝 분야에서는 이 모델을 활용한 예측에 관심이 있다.

 

SELECT - FROM 구문은 우리가 구축한 DB의 TABLE에서 데이터를 불러올 수 있는 구문입니다. 일반적으로 다음과 같이 씁니다.

 

SELECT "컬럼1","컬럼2" FROM "테이블명";

Ex1) sample1이라는 테이블에서 모든 컬럼(열) 데이터 가져오기

       -> SELECT * FROM sample1; 

Ex2) sample1이라는 테이블에서 a, b 컬럼만 가져오기

       -> SELECT a, b FROM sample1;

 

mySQL에서 제공한 Sample DB를 이용해 직접 해보겠습니다.

Sample DB 설정 방법은 다음 링크를 참고하세요(https://m-datastudy.tistory.com/5)

 

1. 테이블 목록 확인하기. show tables;

 

테이블 목록을 확인 할 수 있습니다.

 

SELECT 구문을 사용하기 위해서는 먼저 DB에 어떤 테이블 들이 있는지 확인할 필요가 있습니다.

show tables; 명령어를 통해 간단하게 확인 할 수 있습니다. 저희는 customers 테이블을 사용해보겠습니다.

 

 

 

 

 

 

2. 테이블 구성 확인하기. desc tables; 

테이블의 컬럼과 데이터 타입을 확인 할 수 있습니다.

 

SELECT 구문으로 컬럼을 가져오기 위해서는 테이블 내에 어떤 컬럼이 있는지 확인해봐야합니다.

desc "테이블명"; 을 통해서 모든 테이블을 각각 확인해 볼 수 있습니다. 위 사진은 customers테이블의 컬럼과 데이터 타입을 보여줍니다.

 

Type은 컴퓨터가 어떤 데이터를 저장 할 수 있는지 보여줍니다. 예를 들어 int(11)은 숫자 데이터를 11byte만큼 저장 할 수 있다는 뜻입니다. varchar(50)은 문자 데이터를 50byte만큼 저장 할 수 있다는 뜻입니다.

 

customerName과 city, country가 눈에 띄입니다. 한번 불러와 보겠습니다.

 

Ex1) SELECT customerName, city, country FROM customers;

 

많은 목록이 뜹니다.

 

컬럼명은 꼭 순서대로 불러와야 하는 것이 아닙니다. city와 country를 앞에 불러올 수 도 있습니다.

 

Ex2) SELECT city, country, customerName FROM customers;

 

customerName이 가장 오른쪽으로 왔다.

 

즉 SELECT 다음에 오는 컬럼명은 사용자가 원하는 순서대로 붙히면 됩니다.

 

 

 

 

 

 

3. 목록 정렬하기. ORDER BY 구문.

 

테이블을 불러와도 이름이 정렬되어있지 않아 보기가 힘듭니다. 이 때는 ORDER BY를 사용할 수 있습니다. ORDER BY에는 asc와 desc 두가지 방식이 있습니다. asc(Ascend의 약자)는 오름차순, desc(Descend의 약자)는 내림차순입니다.

 

그런데 desc는 아까 테이블의 구성요소를 확인하기 위해 썼습니다. 두가지 desc는 서로 다른 단어의 약자입니다.

이는 테이블의 구성요소를 확인하기 위해 사용하는 desc 명령어가 엄밀히 따지자면 SQL 명령어가 아니기 때문에 약자가 겹치게 되었습니다.

다른 SQL 프로그램에서는 desc(Describe의 약자)가 아닌 str(Structure의 약자) 일 수도 있습니다.

 

ORDER BY는 일반적으로 이렇게 사용합니다.

SELECT "컬럼1", "컬럼2" FROM "테이블명" ORDER BY "컬럼명";

Ex1) SELECT * FROM table1 ORDER BY a asc;

Ex2) SELECT a, b FROM table1 ORDER BY b desc;

 

직접 해보겠습니다.

Ex3) SELECT customerName FROM customers ORDER BY customerName;

 

 

asc나 desc를 설정해주지 않았을 때에는 자동으로 asc로 정렬됩니다.

 

 

 

+ (추가) 별명을 지어주는 AS '이름'

 

-> SELECT customerName AS name FROM customers;

위와 같은 방식으로 컬럼이름을 일시적으로 바꿀 수 있습니다. 구문을 작성할 때에만 이용되고 테이블 자체의 이름이 바뀌는 것이 아닙니다.

 

그러나 이 AS 구를 사용할 때에는 구문이 처리되는 순서를 이해할 필요가 있습니다.

 

구문은 일반적으로 WHERE 구가 가장 먼저, 그 다음 SELECT, 마지막에 ORDER BY 구가 처리되는 형식입니다.

WHERE -> SELECT -> ORDER BY


우리는 AS 구를 SELECT 구에서 사용하여 별명을 설정합니다. 따라서 ORDER BY를 처리할 때 별명을 사용할 수 있습니다. 그러나 WHERE 구에서는 사용할 수 없습니다. WHERE 구가 처리될 시점에 아직 별명이 설정되지 않았기 때문입니다. 이 점을 유의해야 합니다.

 

 

 

SELECT 구문은 SQL에서 가장 대표적인 구문인 만큼 다른 구문과 함께 사용할 수 있습니다. 다음 게시물에서는 그것을 알아볼 것입니다.

[mySQL] 학습용 DB구축하기
2019. 12. 4. 23:22 - MunJunHyeok

mySQL로 구문을 조작하는 연습을 하기 위해서는 먼저 테이블이 저장되는 데이터베이스가 따로 있어야 합니다. 그런데 이 데이터베이스 설계가 간단한 일이 아닌만큼 mySQL에서는 당장 연습에 사용할 수 있게 해주는 sample DB를 제공합니다.

 

1. mySQL Sample Database(http://www.mysqltutorial.org/mysql-sample-database.aspx) 다운로드

 

파란 박스안의 주황색 부분을 클릭하면 바로 다운이 시작됩니다. 압축해제 해주세요.

 

 

압축해제한 파일을 C:\temp 경로에 붙여넣어주세요.

 

 

 

이후 cmd를 실행해 mysql을 실행해줍니다.

 

 

mysql -u root -p를 치고 mysql이 실행되면 source c:\temp\mysqlsampledatabase.sql을 쳐줍니다.

 

무언가 시행된 뒤, 다시 mysql> 가 cmd 하단에 뜰 때 까지 기다립니다.

mysql>가 뜨면, 다음과 같이 입력해줍니다.

 

show databases; 를 통해 현재 사용할 수 있는 데이터베이스들을 확인할 수 있습니다. 원하는 데이터베이스를 사용하기 위해서는 USE "DB이름"; 을 해주면 됩니다. 우리가 사용할 데이터는 classicmodels 입니다. 

 

 

다음부터 mySQL을 실행하면 항상 USE classicmodels;를 입력해줘야합니다.

[mySQL] Windows 10 mySQL server 설치
2019. 12. 4. 22:57 - MunJunHyeok

가장 먼저 알아두어야 할 점은, mySQL은 원래 오픈소스였으나 현재는 Oracle에 인수되어 무료버전과 유료버전으로 나뉘어버렸다는 것입니다. 따라서 저작권 문제가 발생할까봐 두렵거나, Oracle에 반감을 가지고 있거나, 오픈소스 개발에 참여하고 싶거나 하시다면 SQL 커뮤니티에서 mysql의 소스코드를 이용해 새롭게 만든 오픈소스 SQL 프로그램인 mariaDB를 이용하시는 것을 추천합니다. mySQL과 mariaDB는 사실상 같은 프로그램이라고 봐도 되지만 오히려 mySQL보다 mariaDB가 더 편리한 점이 많습니다.

현재 제가 파악한 가장 큰 차이점 몇 가지를 적어보자면. 1. mariaDB는 mySQL과는 달리 현재 사용하고 있는 DB가 무엇인지 항상 확인할 수 있게 개선해두었고 2. mySQL에서는 현재 사용하고 있는 테이블을 한 구문 내에서 다시 참조 할 수 없지만 mariaDB에서는 가능하도록 되어있어 좀 더 쾌적한 구문 작성이 가능합니다.

 

그럼 곧바로 설치를 시작해보겠습니다.

 

1. MySQL 홈페이지(https://www.mysql.com/products/community/) 에서 community edition 다운로드

 

Oracle에 인수되어 나뉘어져버린 무료버전과 유료버전 중 무료버전입니다. 영리적인 목적으로만 사용하지 않으시면 됩니다.(기업에서 업무 용도로 사용 등)

 

홈페이지에서 빨간색 사각형 부분의 링크를 누르면 됩니다

 

 

 

윈도우 인스텔러 다운로드 링크

 

 

 

1번 파일은 인터넷이 연결되어 있을 경우. 2번 파일은 인터넷이 연결되어 있지 않은 곳에서 설치 할 수 있는 파일입니다.

 

 

 

Oracle 계정을 만들 것인지 이미 있는 계정으로 로그인 할 것인지 묻는 페이지지만, 그냥 다운로드 할 수 있습니다.

 

 

 

 

 

 

 

 

 

 

 

 

 

2. 다운로드 받은 파일 실행 후 인스톨

 

다운로드 받은 파일을 실행 시켜주세요. 실행시키면 다음과 같은 화면이 나옵니다.

 

개발자가 일반적으로 필요한 툴 들이 모두 포함되어 있습니다. 가장 맘 편히 설치 할 수 있습니다. next를 눌러주세요

 

 

이 화면에서 VS나 Excel을 MySQL과 연결하여 쓸 생각이라면 Execute를, 아니라면 next를 누르고 yes를 선택하여 다음 화면으로.

 

 

 

Execute를 눌러 설치를 시작합니다

 

빨간색 박스 부분이 모두 Complete로 되었을 때 next를 눌러주세요. connector가 필요 없다면 무시해도 되지만 MySQL Server와 다른 필수 파일들은 반드시 설치해야합니다. failed가 표시되면 try again을 누르시면 됩니다.

 

 

 

next를 눌러주세요

 

 

 

InnoDB라는 클라우드 시스템을 사용할 것인지 물어보는 겁니다. 필요하시다면 선택하시고 아니라면 next

 

 

 

잘 모르신다면 next. 어떤 포트를 사용할 지 직접 정하고 여타 고급 옵션을 사용하실 거라면 설정 후 next.

 

 

 

어떤 암호화 방식을 설정할 지 정합니다. next.

 

 

 

Root는 기본 사용자를 뜻합니다. 기본 비밀번호를 정합니다. 혹은 추가 유저를 설정합니다. 이곳에서 정한 암호는 잊어버리게 되면 mySQL을 사용할 수 없게 될지도 모르니 신중히 입력해주세요.

 

 

 

 

mysql80이라는 이름으로 service된다는 뜻. 아래는 어떤 윈도우 계정으로 mySQL 서버를 사용할 지 정합니다. 그대로 두는걸 추천드립니다.

 

 

 

이전 화면에서 설정한 환경을 적용하는 페이지입니다. Execute를 눌러 다음으로 넘어갑니다.

 

 

 

어떤 파일을 설치하는지 확인하는 화면. next.

 

 

 

마찬가지로 잘 모르신다면 finish. 아까 설정한 InnoDB를 사용할 것이라면 체크한 뒤 환경설정해줍니다. 이후 finish.

 

 

 

아까 설정했던 Root 계정의 비밀번호를 입력한뒤, check를 눌러 암호를 확인하고, next를 눌러줍니다.

 

 

 

다시 환경설정 적용하는 화면. Execute를 눌러줍니다.

 

이후 몇번의 next를 누르면 설치가 완료됩니다. 

 

 

 

 

 

 

 

 

 

 

 

 

 

3. 설치된 mySQL 실행하기

 

바로 cmd를 사용해 실행할 수 있으면 좋겠지만 먼저 path를 설정해줘야 명령어가 먹힙니다.

 

먼저 내가 설치한 mySQL Server의 경로를 찾아야합니다. 일반적으로 이 위치에 있습니다. bin 폴더까지 들어가야합니다. 그리고 경로를 복사해줍니다.

 

 

 

이후 내 PC를 우클릭 - 속성으로 들어가줍니다. 혹은 window키 + pause break를 눌러도 됩니다.

 

 

 

고급 시스템 설정을 클릭해줍니다.

 

 

 

시스템 속성창이 열리면 1. 환경변수 클릭 2. 시스템 변수 목록에서 path라는 변수 찾아 클릭 3. 편집 클릭 4. 편집 창에서 새로만들기 클릭 5. 아까 복사한 경로 붙여넣기 6. 모두 확인

 

 

 

이후 cmd창을 열어 mysql -u root -p를 치고, 설치할 때 설정했던 비밀번호를 입력해주면 mySQL Server가 실행됩니다.

 

 

Introducing SQL
2019. 11. 26. 15:08 - MunJunHyeok

SQL과 DB

SQL은 Structured Query Language의 준말로 RDB를 관리하기위해 만들어낸 프로그램 언어입니다. RDB는 관계형 데이터베이스를 의미합니다. Realated Data Base의 약자이기도 합니다. 관계형 데이터베이스는 간단하게 말하면 테이블 형태의 데이터를 모아놓은 것입니다.

 

RDB, RDBMS를 관리하기 위해 SQL을 사용한다면 흔히 얘기하는 DB, DBMS는 뭔가?

DB는 아시다시피 데이터베이스를 뜻합니다. 데이터의 집합입니다. 데이터를 정리하는데에는 다양한 방법이 있습니다만 그 중에 테이블 형태로 데이터를 모아놓는 것을 RDB, 관계형 데이터베이스라고 합니다. DB를 관리하는 툴이 DBMS(Data Base Management System)이며 보통 DBMS에서 DB를 생성, 갱신, 삭제 할 수 있습니다. 마찬가지로 RDB를 관리하는 Management System이 RDBMS입니다. DBMS라는 범주에 RDBMS가 속하게 됩니다. 다만 DBMS 중 RDBMS를 가장 많이 사용하므로 SQL을 다룰 때 DB 혹은 DBMS 혹은 RDBMS 등을 이야기해도 듣는 사람이 일반적으로 같은 것으로 받아들입니다. 그리고 이러한 DBMS는 정형(Structured) 데이터를 관리하게 됩니다. 

 

noSQL의 시대

제 블로그의 첫 글(Introducing Data Science: https://m-datastudy.tistory.com/1)에 제가 Bigdata를 다룰 때에는 정형 데이터를 다루는것 보단 비정형(Unstructured) 데이터를 다루는 것이 더 어렵다고 적어두었습니다. RDBMS의 데이터는 테이블이라는 특정한 형식으로 저장이 되어있기 때문에 데이터를 불러오고 조작하는데에 큰 어려움이 없습니다. 그러나 Bigdata에서 정보를 불러올 때는 다양한 형식의 텍스트 들이 복잡하게 섞여있기 때문에 정형 데이터를 조작하는 방식으로는 도저히 다룰 수 가 없게 되었습니다. 그래서 나타난 것이 noSQL입니다. SQL이 아니다 (no)의 의미가 아닌 not only SQL의 약자로 데이터를 다룰 때 SQL만 사용하지는 않는다는 뜻입니다. mongoDB, Cassandra등 이런 noSQL 프로그램들이 대세인 상황에서 정형 데이터만을 다루는 SQL의 입지는 비교적 줄어들었다고 얘기할 수 있을 것 같습니다. 당장 한국 Oracle만 해도 몇 주 전 (2019.11.19)에 Bigdata 시대의 꽃인 클라우드 트렌드를 따라가지 못해 대규모 정리해고를 단행했으니까요.

 

그럼 noSQL의 시대에 SQL이 웬말이냐

not only SQL이라는 말을 풀어보면 not only Structured Query Language이 됩니다. 이는 정형 질의어(Query Language)만 사용하지 않겠다는 뜻이 됩니다. 즉 다른 질의어나 관리 방식을 추가적으로 사용한다는 뜻이지 SQL을 완전히 배제하겠다는 뜻이 아닙니다. 또한 SQL은 비정형 데이터를 다룰 필요가 없는 영역에서는 당연히 noSQL보다 선호됩니다. noSQL보다는 SQL이 비교적 표준화되어있기 때문입니다. 프로그램마다 특성이 다른 noSQL 보다는 사용하기 쉬울 것입니다.

 

결론

noSQL의 등장으로 비교적 SQL의 입지가 줄어들었지만, 배울 가치가 없다는 것은 어불성설입니다. Bigdata 시대의 모든 기술은 Bigdata 이전 시대로부터 나온 것입니다. 완전히 새로운 것은 없습니다. 따라서 저는 SQL을 공부하며 이 블로그에 정리해나갈 예정입니다.

[Practice] Estimation of Return to Schooling
2019. 11. 19. 17:22 - MunJunHyeok

 

교육 수익률의 추정

시작하기

  • Reading data: labor_supply_female.csv
  • Create New Chunk at MarkDown : ctrl + alt + I

  • 라이브러리 로드

library(tidyverse)
library(readr)
library(gridExtra)
library(stargazer)
library(showtext)
font_add_google('Nanum Gothic','nanumgothic')
showtext::showtext_auto()
  • 데이터 로드 및 편집
labor.sup <- readr::read_csv('labor_supply.csv')
labor.sup$w2edu <- factor(labor.sup$w2edu,
                          labels = c('무학','초졸','중졸','고졸','전문대졸','4년제','석사','박사')
                          )
  • plot으로 데이터 확인하기
labor.sup %>% 
  group_by(w2edu) %>%
  # x 축을 나이, y 축을 log 변환한 시간당 임금으로 설정
  ggplot(mapping = aes(x     = age,
                       y     = ln_wage_hourly)) +
  
  # Scatter gram과 추세선을 그림
  geom_point(aes(col = w2edu)) +
  geom_smooth(method = 'glm',
              formula = y ~ poly(x,2),
              color = 'steelblue',
              se = FALSE,
              linetype = 'dashed') +
  scale_color_brewer(palette = 'RdYlBu') +
  xlim(18, 80) +
  xlab('age') +
  ylab('log_hourly_wage')

Regression (회귀분석)

  1. age를 독립변수로 갖는 회귀분석
  2. age와 age의 제곱을 독립변수로 갖는 회귀분석
  3. age와 age의 제곱, 교육수준을 독립변수로 갖는 회귀분석
  4. age와 age의 제곱, 교육기간을 독립변수로 갖는 회귀분석
# no.1
lm.1 <- lm(ln_wage_hourly ~ age,
           data = labor.sup)
summary(lm.1)
## 
## Call:
## lm(formula = ln_wage_hourly ~ age, data = labor.sup)
## 
## Residuals:
##     Min      1Q  Median      3Q     Max 
## -3.7605 -0.4103 -0.0118  0.4344  2.6761 
## 
## Coefficients:
##              Estimate Std. Error t value Pr(>|t|)    
## (Intercept)  0.152733   0.076711   1.991   0.0467 *  
## age         -0.017896   0.001847  -9.689   <2e-16 ***
## ---
## Signif. codes:  0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
## 
## Residual standard error: 0.774 on 1123 degrees of freedom
##   (694 observations deleted due to missingness)
## Multiple R-squared:  0.07714,    Adjusted R-squared:  0.07632 
## F-statistic: 93.87 on 1 and 1123 DF,  p-value: < 2.2e-16
# 결론 : age의 coefficient가 음수이다. 즉, 나이가 많을 수록 임금이 떨어진다.
# 모형이 제약적이기 때문에 예상과 다른 결과가 나온다.
# overfit: 지나치게 fitting을 해서 미래 예측이 불가한 상태.
# no.2
lm.2 <- lm(ln_wage_hourly ~ age + I(age^2),
           data = labor.sup)
summary(lm.2)
## 
## Call:
## lm(formula = ln_wage_hourly ~ age + I(age^2), data = labor.sup)
## 
## Residuals:
##      Min       1Q   Median       3Q      Max 
## -2.87855 -0.40389 -0.02607  0.44161  2.97720 
## 
## Coefficients:
##               Estimate Std. Error t value Pr(>|t|)    
## (Intercept) -1.7420240  0.2244846  -7.760 1.90e-14 ***
## age          0.0787824  0.0109577   7.190 1.18e-12 ***
## I(age^2)    -0.0011215  0.0001254  -8.942  < 2e-16 ***
## ---
## Signif. codes:  0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
## 
## Residual standard error: 0.7481 on 1122 degrees of freedom
##   (694 observations deleted due to missingness)
## Multiple R-squared:  0.1385, Adjusted R-squared:  0.137 
## F-statistic: 90.22 on 2 and 1122 DF,  p-value: < 2.2e-16
# 결론 a : age의 coefficient가 양수로 바뀌었다. 즉, 나이가 많을 수록 시간당 임금이 증가한다.
# age가 1년 증가할 때 마다 시간당 임금이 7.9% 올라가는 경향이 있다.
# 결론 b : 제곱항의 coefficient가 음수이다. 위로 볼록한 2차 함수의 형태를 띈다.
# 즉, 연령에 따른 한계수확은 나이가 많을수록 체감한다.
  • add the education variable, w2edu
# no.3
lm.3 <- lm(ln_wage_hourly ~ age + I(age^2) + w2edu,
           data = labor.sup)
summary(lm.3)
## 
## Call:
## lm(formula = ln_wage_hourly ~ age + I(age^2) + w2edu, data = labor.sup)
## 
## Residuals:
##     Min      1Q  Median      3Q     Max 
## -3.3140 -0.3123  0.0553  0.4167  1.9190 
## 
## Coefficients:
##                 Estimate Std. Error t value Pr(>|t|)    
## (Intercept)   -2.8605677  0.2419628 -11.822  < 2e-16 ***
## age            0.0742900  0.0112049   6.630 5.22e-11 ***
## I(age^2)      -0.0008390  0.0001336  -6.277 4.92e-10 ***
## w2edu초졸      0.1421755  0.1541257   0.922 0.356486    
## w2edu중졸      0.3391013  0.1627300   2.084 0.037403 *  
## w2edu고졸      0.6199602  0.1651687   3.753 0.000183 ***
## w2edu전문대졸  0.9276296  0.1752402   5.293 1.44e-07 ***
## w2edu4년제     1.2356706  0.1706671   7.240 8.33e-13 ***
## w2edu석사      1.6530789  0.1913012   8.641  < 2e-16 ***
## w2edu박사      1.8454967  0.3457613   5.337 1.14e-07 ***
## ---
## Signif. codes:  0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
## 
## Residual standard error: 0.6758 on 1115 degrees of freedom
##   (694 observations deleted due to missingness)
## Multiple R-squared:  0.3015, Adjusted R-squared:  0.2958 
## F-statistic: 53.46 on 9 and 1115 DF,  p-value: < 2.2e-16
# 다중공선성 문제 해결을 위해 자유도를 제한한다. '무학' 독립변수를 제거한다.
# 제거된 독릴변수에 대비해 추정량 평가.
# 결론 : '무학' 교육수준에 비해 '고졸' 교육수준인 사람은 시간당 임금이 61% 높다 등.

Report the results of model 2,3,4

# no.4
lm.4 <- lm(ln_wage_hourly ~ age + I(age^2) + educ_year,
           data = labor.sup)
summary(lm.4)
## 
## Call:
## lm(formula = ln_wage_hourly ~ age + I(age^2) + educ_year, data = labor.sup)
## 
## Residuals:
##     Min      1Q  Median      3Q     Max 
## -3.7045 -0.3190  0.0404  0.4383  1.9297 
## 
## Coefficients:
##               Estimate Std. Error t value Pr(>|t|)    
## (Intercept) -3.1247333  0.2257963 -13.839  < 2e-16 ***
## age          0.0505973  0.0102098   4.956 8.31e-07 ***
## I(age^2)    -0.0005288  0.0001216  -4.348 1.50e-05 ***
## educ_year    0.1165055  0.0078876  14.771  < 2e-16 ***
## ---
## Signif. codes:  0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
## 
## Residual standard error: 0.6848 on 1121 degrees of freedom
##   (694 observations deleted due to missingness)
## Multiple R-squared:  0.2789, Adjusted R-squared:  0.277 
## F-statistic: 144.5 on 3 and 1121 DF,  p-value: < 2.2e-16
stargazer::stargazer(lm.2,lm.3,lm.4,
                     type = 'text')
## 
## ===============================================================================================
##                                                 Dependent variable:                            
##                     ---------------------------------------------------------------------------
##                                                   ln_wage_hourly                               
##                               (1)                      (2)                       (3)           
## -----------------------------------------------------------------------------------------------
## age                         0.079***                 0.074***                 0.051***         
##                             (0.011)                  (0.011)                   (0.010)         
##                                                                                                
## I(age2)                    -0.001***                -0.001***                 -0.001***        
##                             (0.0001)                 (0.0001)                 (0.0001)         
##                                                                                                
## w2edu초졸                                               0.142                                    
##                                                      (0.154)                                   
##                                                                                                
## w2edu중졸                                              0.339**                                   
##                                                      (0.163)                                   
##                                                                                                
## w2edu고졸                                              0.620***                                  
##                                                      (0.165)                                   
##                                                                                                
## w2edu전문대졸                                            0.928***                                  
##                                                      (0.175)                                   
##                                                                                                
## w2edu4년제                                             1.236***                                  
##                                                      (0.171)                                   
##                                                                                                
## w2edu석사                                              1.653***                                  
##                                                      (0.191)                                   
##                                                                                                
## w2edu박사                                              1.845***                                  
##                                                      (0.346)                                   
##                                                                                                
## educ_year                                                                     0.117***         
##                                                                                (0.008)         
##                                                                                                
## Constant                   -1.742***                -2.861***                 -3.125***        
##                             (0.224)                  (0.242)                   (0.226)         
##                                                                                                
## -----------------------------------------------------------------------------------------------
## Observations                 1,125                    1,125                     1,125          
## R2                           0.139                    0.301                     0.279          
## Adjusted R2                  0.137                    0.296                     0.277          
## Residual Std. Error    0.748 (df = 1122)        0.676 (df = 1115)         0.685 (df = 1121)    
## F Statistic         90.220*** (df = 2; 1122) 53.464*** (df = 9; 1115) 144.514*** (df = 3; 1121)
## ===============================================================================================
## Note:                                                               *p<0.1; **p<0.05; ***p<0.01
# 결론 : 교육 기간이 1년 증가했을 때, 임금이 약 11.7% 증가한다.