[oracle] 가장 많이 나오는 어절 출력, 등록금이 가장 높은 대학 출력 / REGEXP_SUBSTR / RANK / WHERE

1. 스티브 잡스가 2005년도에 스탠포드에서 한 연설문 CSV - 가장 많이 나오는 어절 출력

-- CSV 파일 저장할 테이블 생성
CREATE TABLE STEVE_SPEECH
(SPEECH_TEXT VARCHAR2(1000));

-- 연설문 문장 건수 확인
SELECT COUNT(*) 
FROM STEVE_SPEECH;

[OUT]

COUNT

143

REGEXP_SUBSTR(대상 문자, 패턴, 시작 위치(최소값1),매칭순번)

• '[^ ]+' 는 공백이 아니면서 철자가 여러개가 있는 것을 뜻하는 것으로 문자열에서 어절을 의미한다.
• 1, 2 는 첫번째 어절(I)부터 시작하는, 두번째 어절(never)을 가져오라는 뜻이다.
• 1,1 의 결과는 I 이고, 1, 3의 결과는 graduated이다.

-- REGEXP_SUBSTR 정교하게 문자열에서 원하는 단어나 철자를 추출하는 함수
SELECT REGEXP_SUBSTR('I never graduated from college', '[^ ]+', 1, 2 ) word
FROM DUAL;

[OUT]
WORD
never

 

Q1) 연설문에서 143개의 문장을 모두 어절 단위로 출력하기

SELECT REGEXP_SUBSTR(lower(speech_text), '[^ ]+', 1, a) word
FROM STEVE_SPEECH, (SELECT level a
                    FROM DUAL
                    CONNECT BY level <= 52); --가장 긴 문장의 어절 개수는 52

 

Q2) 연설문에서 가장 많이 나오는 단어 추출하기

SELECT word, count(*)
FROM (SELECT REGEXP_SUBSTR(lower(speech_text), '[^ ]+', 1, a) word
        FROM STEVE_SPEECH, (SELECT level a 
                            FROM DUAL
                            CONNECT BY level <= 52) --가장 긴 문장의 어절 개수는 52
     )
    WHERE word is not null
    GROUP BY word
    ORDER BY count(*) desc;

 

Q3) 연설에서 긍정 단어가 많은지 부정 단어가 많은지 출력하기

-- 연설문에서 143개의 문장을 모두 어절 단위로 출력 쿼리를 SPEECH_VIEW로 생성
CREATE OR REPLACE VIEW SPEECH_VIEW
AS
SELECT REGEXP_SUBSTR(lower(speech_text), '[^ ]+', 1, a) word
            FROM STEVE_SPEECH, (SELECT level a
                                FROM dual
                                CONNECT BY level <= 52);
                                
                                
SELECT count(word) as 긍정단어
    FROM speech_view
    WHERE lower(word) IN (SELECT lower(p_text) -- p_text 컬럼명
                             FROM positive );

 

---

 

2. 우리나라 대학 등록금 CSV - 등록금이 가장 높은 대학 출력

-- CSV 파일 저장할 테이블 생성
CREATE TABLE UNIVERSITY_FEE
(DIVISION       VARCHAR2(20),
 TYPE           VARCHAR2(20),
 UNIVERSITY     VARCHAR2(60),
 LOC            VARCHAR2(40),
 ADMISSION_CNT  NUMBER(20),
 ADMISSION_FEE  NUMBER(20),
 TUITION_FEE    NUMBER(20) ) ;

-- 평균등록금 (TUITION_FEE)이 가장 높은 대학 출력
SELECT *
FROM (SELECT UNIVERSITY, TUITION_FEE,
             RANK() over (ORDER BY TUITION_FEE DESC NULLS LAST) 순위
       FROM UNIVERSITY_FEE )
WHERE  순위 = 1;

 

 

NULLS LAST : null 값을 가장 마지막에 정렬

NULLS FIRST : null 값을 가장 먼저 정렬 

-- 입학정원 순위도 같이 출력
SELECT *
FROM (SELECT UNIVERSITY, TUITION_FEE, ADMISSION_CNT,
            RANK() over (ORDER BY TUITION_FEE DESC NULLS LAST) AS "평균등록금순위"
            ,DENSE_RANK() over (ORDER BY ADMISSION_CNT DESC) AS "입학정원"
        FROM UNIVERSITY_FEE );

 

-- 등록금순위가 1위인곳 입학정원순위 출력
SELECT *
FROM (SELECT UNIVERSITY, TUITION_FEE, ADMISSION_CNT,
            RANK() over (ORDER BY TUITION_FEE DESC NULLS LAST) AS "평균등록금순위"
            ,DENSE_RANK() over (ORDER BY ADMISSION_CNT DESC) AS "입학정원"
        FROM UNIVERSITY_FEE )
WHERE 평균등록금순위 = 1;

 

---

DESC : 내림차순 정렬 (5,4,3,2,1)

ASC : 오름차순 정렬 (1,2,3,4,5) , 생략 가능

SELECT empno
FROM emp
ORDER BY empno DESC