제 48회 SQLP 실기 복기

Kyojun Jin·2023년 3월 20일

SQLP

목록 보기

34/34

1번

다음 쿼리를 재작성하라.

문제 쿼리

select 
    '일별' 기간구분,
    판매일시,
    고객등급,
    sum(판매금액) 판매금액
from (
    select 
        substr(sale_dt, 1, 8) 판매일시,
        fnc_get_cust_grade(cust_id) 고객등급,
        sale_amt 판매금액
    from 
        sale
    where
        sale_dt between '20220101' and '20220731'
    )
group by 판매일시, 고객등급
union all
select 
    '월별' 기간구분,
    판매일시,
    고객등급,
    sum(판매금액) 판매금액
from (
    select 
        substr(sale_dt, 1, 6) 판매일시,
        fnc_get_cust_grade(cust_id) 고객등급,
        sale_amt 판매금액
    from 
        sale
    where
        sale_dt between '20220101' and '20220731'
    )
group by 판매일시, 고객등급
order by 1, 2, 3
;

샘플 데이터 생성 쿼리

-- 판매 테이블/인덱스 생성
create table sale (
    sale_id number primary key,
    sale_dt varchar(12),
    sale_amt number,
    cust_id number
)
partition by range(sale_dt) (
    partition q1 values less than ('202204010000'),
    partition q2 values less than ('202207010000'),
    partition q3 values less than ('202210010000'), 
    partition q4 values less than (maxvalue)
);

create index sale_idx01 on sale(sale_dt) local;


-- 고객 테이블/인덱스 생성
create table customer (
    cust_id number primary key,
    cust_grade varchar(5)
);

create index customer_idx01 on customer(cust_id, cust_grade);


-- 함수 생성
create or replace function fnc_get_cust_grade(p_cust_id number)
    return varchar
is
    v_cust_grade varchar(5);
begin
    select cust_grade
    into v_cust_grade
    from customer
    where cust_id = p_cust_id;
    
    return v_cust_grade;
end;
/


-- 판매, 고객 데이터 생성
insert into sale 
select 1, '202201100000', 100, 1 from dual
union all
select 2, '202201150000', 200, 1 from dual
union all
select 3, '202203310000', 300, 3 from dual
union all
select 4, '202204050000', 500, 4 from dual
union all
select 5, '202205130000', 200, 2 from dual
union all
select 6, '202205210000', 100, 1 from dual
union all
select 7, '202206060000', 400, 2 from dual
union all
select 8, '202206210000', 600, 4 from dual
union all
select 9, '202207220000', 200, 3 from dual
union all
select 10, '202206060000', 200, 5 from dual
union all
select 11, '202201150000', 400, 6 from dual
; 

insert into customer
select 1, 'A' from dual
union all
select 2, 'B' from dual
union all
select 3, 'C' from dual
union all
select 4, 'D' from dual
union all 
select 5, 'B' from dual
union all 
select 6, 'A' from dual
;

commit;

제출 답안 쿼리

select 
    case when 일 is not null then '일별' else '월별' end 기간구분,
    case when 일 is not null then 일 else 월 end 판매일시,
    고객등급, 
    판매금액
from (
    select 일, 월, 고객등급, sum(판매금액) 판매금액
    from (
        select 
            substr(sale_dt, 1, 8) 일,
            substr(sale_dt, 1, 6) 월,
            sale_amt 판매금액,
            cust_grade 고객등급
        from
            sale s, customer c
        where
            s.cust_id = c.cust_id 
            and sale_dt between '20220101' and '20220731'
    )
    group by grouping sets((일, 고객등급), (월, 고객등급))
)
order by 1, 2, 3
;