[Oracle] Oracle 함수

날짜 타입
    extract() : 날짜 데이터로부터 원하는 값을 반환
    extract(일 또는 월 또는 년 from 필드명)

    sysdate : 현재 날짜 반환
    add_months() : 특정 날짜의 월에 정수를 더한 다음 해당 날짜를 반환
    months_between() : 날짜 간의 차이를 월로 반환

 

가입 월 출력

select mem_userid, mem_name, mem_gender, extract(month from mem_regdate) as reg_month from tb_member;

 

가입 분기 출력

가입 월을 가져와 4로 나누어서 ceil()을 이용하여 반올림.

select mem_userid, mem_name, mem_gender, mem_regdate, ceil(extract(month from mem_regdate) / 3) as quarter from tb_member;

 

 

회원테이블 데이터 삽입

insert into tb_member values (seq_member.nextval, 'kim', '1111', '김씨', '여자', 'kim@apple.com', '010-1111-1111', '11111', '서울', '서초구', '양재동', sysdate, 0);
insert into tb_member values (seq_member.nextval, 'park', '2222', '박씨', '여자', 'park@banana.com', '010-2222-2222', '22222', '서울', '서초구', '양재동', sysdate, 0);
insert into tb_member values (seq_member.nextval, 'oh', '3333', '오씨', '남자', 'ph@orange.com', '010-3333-3333', '33333', '서울', '서초구', '양재동', sysdate, 0);
insert into tb_member values (seq_member.nextval, 'yoo', '4444', '유씨', '남자', 'yoo@melon.com', '010-4444-4444', '44444', '서울', '서초구', '양재동', sysdate, 0);
insert into tb_member values (seq_member.nextval, 'choi', '5555', '최씨', '여자', 'choi@cherry.com', '010-5555-5555', '55555', '서울', '서초구', '양재동', sysdate, 0);

 

가입한 월이 짝수면 짝수월, 홀수면 홀수월 출력

select mem_userid, mem_name, mem_gender, mem_regdate, extract(month from mem_regdate) as month,
    case mod(Extract(month from mem_regdate), 2) 
        when 0 then '짝수월'
        else '홀수월'
        end as mod_month from tb_member;

 

width_bucket()

    최소값과 최대값을 설정하고 버킷을 설정한 다음 지정한 값이 범위에서 어느 위치에 있는지 반환

    width_bucket(값, 최소값, 최대값, 버킷)

    width_bucket(70, 1, 101, 2) -> 2
    1 ~ 100 까지 2로 나누었을 때 70이 어느 위치에 있는지 반환 -> 2
    width_bucket(30, 1, 101, 4) -> 2
    1 ~ 100 까지 4로 나누었을 때 30이 어느 위치에 있는지 반환 -> 2

 

2021년 상반기, 하반기 가입한 회원 수

select case
    width_bucket(extract(month from mem_regdate), 1, 13, 2)
    when 1 then '상반기'
    else '하반기'
    end as half_year,
    count(mem_idx) as count from tb_member where extract(year from mem_regdate) = 2021
    group by width_bucket(extract(month from mem_regdate), 1, 13, 2);

 

현재 날짜 출력

select sysdate from dual;

현재 날짜의 일에 정수를 더한 날짜 출력

select sysdate + 10 from dual;

현재 날짜의 월에 정수를 더한 날짜 출력

select add_months(sysdate, 1) from dual;

가입일로부터 현재 날짜까지 몇 달 차이가 나는지 출력

select mem_userid, mem_name, mem_regdate, floor(months_between(sysdate, mem_regdate)) as month from tb_member;

 

가입일로부터 현재 날짜까지 며칠 차이가 나는지 출력

select mem_userid, mem_name, mem_regdate, floor(sysdate - mem_regdate) as day from tb_member;

 

날짜를 문자타입으로 변환
    to_char() : 날짜를 문자형 데이터로 변환
    D : 주(1~7) 1은 일요일, 7은 토요일
    DD : 일(1~31)
    DDD : 1년 중 날짜 (1~366)
    HH24 : 시간(0~23)
    IW : 1년중 몇 주(1~53)
    MI : 분(0~59)
    SS : 초(0~59)
    MM : 월(01~12)
    Q : 분기(1, 2, 3, 4)
    YYYY : 년
    W : 월 중 몇 주(1~5)

 

현재 시간 시간:분:초 출력

select to_char(sysdate, 'HH24:MI:SS') as time from dual;

 

2021년 가입한 회원들의 목록
분기별 가입자 수
    q c
    1 2
    2 4

select to_char(mem_regdate, 'Q') as q, count(mem_idx) as c from tb_member where extract (year from mem_regdate) = 2021 group by to_char(mem_regdate, 'Q') order by q;

 

decode() 

    - sql문에 조건문(if문)을 사용할 수 있도록 제공

    - decode(컬럼, 조건1, 결과1, 조건2, 결과2, ... 결과n)
    - 찾는 값을 비교할 때 equal 연산만 가능(비교 연산 불가능)

 

성별이 남자이면 'male', 여자이면 'female'로 출력

- case 문

select mem_userid, mem_name, case mem_gender
    when '남자' then 'male'
    when '여자' then 'female'
    end as gender from tb_member;

- decode()

select mem_userid, mem_name, decode(mem_gnder, '남자', 'male', mem_gender, '여자', 'female') as gender from tb_member;

 

rank()

    - 순위를 표현할 떄 사용하는 함수
    - order by는 필수
    
    분석함수
    rank() over(정렬) : 동일 순위인 경우 1, 1, 3 형식으로 출력

select mem_userid, mem_name, mem_gender, rank() over(order by mem_point desc) as ranking from tb_member;

    row_number() over(정렬) : 동일 순위인 경우 1, 2, 3 형식으로 출력

select mem_userid, mem_name, mem_gender, row_number() over(order by mem_point desc) as ranking from tb_member;

    dense_rank() over(정렬) : 동일 순위인 경우 1, 1, 2 형식으로 출력

select mem_userid, mem_name, mem_gender, dense_rank() over(order by mem_point desc) as ranking from tb_member;

 

percent_rank()

    - 백분율로 나타냄
    over() : 분석함수에 대한 설정
    분석함수 설정
    PARTITION BY : group by 와 동일(그룹을 사용해 결과를 출력)

 

성별로 그룹을 나누고 인원수로 나눠서 포인트 순위대로 출력

select mem_userid, mem_name, mem_gender, mem_point,
    percent_rank() over(partition by mem_gender order by mem_point desc) * 100 as top from tb_member;

 

올해 가입자들 중에서 성별별 포인트의 순위
mem_userid, mem_name, mem_gender, mem_point, mem_regdate, top

select mem_userid, mem_name, mem_gender, mem_point,
    percent_rank() over(partition by mem_gender order by mem_point desc) * 100 as top from tb_member where extract(year from mem_regdate) = 2022;