#15-2. 15일차 퀴즈에 대한 문제풀이

1. 다음의 테이블을 생성한 후, seoul_new.txt 파일 데이터를 적재하고(305건), 아래와 같은 형식으로 출력

create table seoul_new(txt varchar2(200));

 

** 출력형식

id      title                           rdate           cnt
305     무료법률상담에 대한 부탁의 말씀 입니다.       2017-09-27      2 
304     [교통불편접수] 6715 버스(신월동->상암동)    2017-09-26      2 
303     경기도 시흥시 아파트 화재~               2017-09-22      145 
.....

[내 답안]

 

select *
  from seoul_new;

 

 

-- 정규식 그룹 나누기
select txt,
       regexp_substr(txt, '(\d{1,})\s(.*)(\d+-\d+-\d+)\s\d+') as 그룹
  from seoul_new;

-- 테이블명 부여
select txt,
       regexp_substr(txt, '(\d{1,})\s(.*)\s(\d+-\d+-\d+)\s(\d+)', 1, 1, null, 1) as id,
       regexp_substr(txt, '(\d{1,})\s(.*)\s(\d+-\d+-\d+)\s(\d+)', 1, 1, null, 2) as title,
       regexp_substr(txt, '(\d{1,})\s(.*)\s(\d+-\d+-\d+)\s(\d+)', 1, 1, null, 3) as rdate,
       regexp_substr(txt, '(\d{1,})\s(.*)\s(\d+-\d+-\d+)\s(\d+)', 1, 1, null, 4) as cnt
  from seoul_new;

 

 

[문제풀이]

 

# . 은 무조건 공백을 포함하기 때문에 그 다음까지의 패턴을 지정해줘야한다.

 

select txt,
       regexp_substr(txt, '^\d+') as id,
       regexp_substr(txt, '^\d+ (.+) \d{4}-\d{2}-\d{2} \d+', 1, 1, null, 1) as title,
       regexp_substr(txt, '\d{4}-\d{2}-\d{2}') as rdate,
       regexp_substr(txt, '\d+ $') as cnt
  from seoul_new;

 

-- 테이블 생성까지 진행하게 된다면 : 

 

create table seoul_new_2
as
select txt,
       to_number(regexp_substr(txt, '^\d+')) as id,
       regexp_substr(txt, '^\d+ (.+) \d{4}-\d{2}-\d{2} \d+', 1, 1, null, 1) as title,
       to_date(regexp_substr(txt, '\d{4}-\d{2}-\d{2}'), 'yyyy/mm/dd') as rdate,
       to_number(regexp_substr(txt, '\d+ $')) as cnt
  from seoul_new;

2. 다음의 테이블 생성 후, 한국소비자원2.csv 파일 데이터를 적재하고(540781건)
상품별로 가격이 가장 저렴한 판매업소를 출력
단, 상품명은 괄호 안의 내용 모두 제거하여 출력(머거본 꿀땅콩(135g) -> 머거본 꿀땅콩)
create table item_price(
상품명     varchar2(200),
조사일     date,
판매가격    number,
판매업소    varchar2(200),
제조사     varchar2(200),
세일여부    varchar2(5),
원플러스원   varchar2(5));

[내 답안]

 

select *
  from item_price;

 

select *
  from item_price;

select regexp_replace(i1.상품명, '\(.+\)') as "상품명 출력", i1.판매가격, i1.판매업소
  from item_price i1,
       (select 상품명, min(판매가격) as 최저가
          from item_price 
         group by 상품명) i2
 where i1.상품명 = i2.상품명
   and i1.판매가격 = i2.최저가; 
   

 

 

[문제풀이]

 

-- 판매업소별 평균 가격 확인
select 상품명, 판매업소, round(avg(판매가격)) 판매가격
  from item_price
 group by 상품명, 판매업소;
 
-- 각 상품별 최저가 확인
select 상품명, 판매업소, round(avg(판매가격)) 판매가격, 
       min(round(avg(판매가격))) over(partition by 상품명) 최저가
  from item_price
 group by 상품명, 판매업소;
 
-- 서브쿼리 사용, 최저가만 골라내기
select *
  from (select 상품명, 판매업소, round(avg(판매가격)) 판매가격, 
               min(round(avg(판매가격))) over(partition by 상품명) 최저가
          from item_price
         group by 상품명, 판매업소
 where 판매가격 = 최저가;
 
-- **추가) 판매업소 다듬기
-- 이마트 수색점 -> 이마트

select regexp_replace(상품명, '\(.+\)') 상품명,
       판매업소, 판매가격
  from(select 상품명, 
              regexp_substr(판매업소, '롯데슈퍼|신세계백화점|시장|농협|유통|이마트|롯데백화점|CU|GS25|세븐일레븐|GS더프레시|부전마켓타운|롯데마트|현대백화점') 판매업소,
              round(avg(판매가격)) 판매가격,
              min(round(avg(판매가격))) over(partition by 상품명) 최저가
         from item_price
        group by 상품명,
                 regexp_substr(판매업소, '롯데슈퍼|신세계백화점|시장|농협|유통|이마트|롯데백화점|CU|GS25|세븐일레븐|GS더프레시|부전마켓타운|롯데마트|현대백화점'))
 where 판매가격 = 최저가;