누적(누계)
----- ***** !!!!! 누적(누계)에 대해서 알아보기 !!!!! ***** -----
sum(누적되어야할 컬럼명) over(order by 누적되어질 기준이 되는 컬럼명 asc[desc] )
sum(누적되어야할 컬럼명) over(partition by 그룹화 되어질 컬럼명
order by 누적되어질 기준이 되는 컬럼명 asc[desc] )
--- *** tbl_panmae 테이블에서 '새우깡'에 대한 일별판매량과 일별누적판매량을 나타내기 *** ---
select to_char(panmaedate, 'yyyy-mm-dd') AS 판매일자
, sum(panmaesu) AS 일별판매량
, sum( sum(panmaesu) ) over( order by to_char(panmaedate, 'yyyy-mm-dd') ) AS 일별누적판매량
from tbl_panmae
where jepumname = '새우깡'
group by to_char(panmaedate, 'yyyy-mm-dd');
/*
--------------------------------------
판매일자 일별판매량 일별누적판매량
--------------------------------------
2022-05-06 10 10
2022-05-07 15 25
2022-05-09 13 38
2022-06-07 8 46
2022-07-06 30 76
*/
--- *** tbl_panmae 테이블에서 '모든제품'에 대한 일별판매량과 일별누적판매량을 나타내기 *** ---
select jepumname
, to_char(panmaedate, 'yyyy-mm-dd') AS 판매일자
, sum(panmaesu) AS 일별판매량
, sum( sum(panmaesu) ) over(partition by jepumname
order by to_char(panmaedate, 'yyyy-mm-dd') ) AS 일별누적판매량
from tbl_panmae
group by jepumname, to_char(panmaedate, 'yyyy-mm-dd');
/*
--------------------------------------------------
제품명 판매일자 일별판매량 일별누적판매량
-------------------------------------------------
감자깡 2022-05-08 20 20
... ... ... ...
새우깡 2022-05-06 10 10
새우깡 2022-05-07 15 25
새우깡 2022-05-09 13 38
새우깡 2022-06-07 8 46
새우깡 2022-07-06 30 76
-------------------------------------------------
*/
stored view를 생성해서 감자깡과 새우깡 구하기
create or replace view view_panmae
as
select jepumname
, to_char(panmaedate, 'yyyy-mm-dd') AS 판매일자
, sum(panmaesu) AS 일별판매량
, sum( sum(panmaesu) ) over(partition by jepumname 제품명
order by to_char(panmaedate, 'yyyy-mm-dd') ) AS 일별누적판매량
from tbl_panmae
group by jepumname, to_char(panmaedate, 'yyyy-mm-dd');
-- View VIEW_PANMAE이(가) 생성되었습니다.
select *
from view_panmae;
select *
from view_panmae
where 제품명 in ('감자깡', '새우깡');
--- *** [퀴즈] tbl_panmae 테이블에서 판매일자가 1개월전 '01'일(즉, 현재가 2022-07-06 이므로 2022-06-01 ) 부터
--- 현재까지 판매된 모든 제품에 대해 일별판매량과 일별누적판매량을 나타내기 *** ---
-- 1개월 전 '01'일 구하는 두가지 방법
select last_day(add_months(sysdate, -2)) +1
, to_char(last_day(add_months(sysdate, -2)) +1, 'yyyy-mm-dd hh24:mi:ss')
from dual;
-- 22/06/01 2022-06-01 15:34:53
select to_date(to_char(add_months(sysdate, -1), 'yyyy-mm-')||'01', 'yyyy-mm-dd')
, to_char(to_date(to_char(add_months(sysdate, -1), 'yyyy-mm-')||'01', 'yyyy-mm-dd'), 'yyyy-mm-dd hh24:mi:ss')
from dual;
-- 22/06/01 2022-06-01 00:00:00-- [첫번째 방법]
select jepumname
, to_char(panmaedate, 'yyyy-mm-dd') AS 판매일자
, sum(panmaesu) AS 일별판매량
, sum( sum(panmaesu) ) over(partition by jepumname
order by to_char(panmaedate, 'yyyy-mm-dd') ) AS 일별누적판매량
from tbl_panmae
where panmaedate > '2022-06-01'
group by jepumname, to_char(panmaedate, 'yyyy-mm-dd');
-- [두번째 방법]
select jepumname 제품명
, to_char(panmaedate, 'yyyy-mm-dd') AS 판매일자
, sum(panmaesu) AS 일별판매량
, sum( sum(panmaesu) ) over(partition by jepumname
order by to_char(panmaedate, 'yyyy-mm-dd') ) AS 일별누적판매량
from tbl_panmae
where panmaedate >= to_date(to_char(add_months(sysdate, -1), 'yyyy-mm-') || '01', 'yyyy-mm-dd')
group by jepumname, to_char(panmaedate, 'yyyy-mm-dd');------- ==== **** [퀴즈] 아래처럼 나오게 만들기 **** ==== -------
---------------------------------------------
전체사원수 10대 20대 30대 40대 50대 60대
---------------------------------------------
107 16 19 21 21 16 14
---------------------------------------------
select count(age_line) 전체사원수
, sum( decode( age_line, 10, 1 ) ) AS "10대"
, sum( decode( age_line, 20, 1 ) ) AS "20대"
, sum( decode( age_line, 30, 1 ) ) AS "30대"
, sum( decode( age_line, 40, 1 ) ) AS "40대"
, sum( decode( age_line, 50, 1 ) ) AS "50대"
, sum( decode( age_line, 60, 1 ) ) AS "60대"
from
(
select trunc(extract(year from sysdate) - to_number( substr(jubun, 1, 2)+ case when substr(jubun,7,1) in ('1','2') then 1900 else 2000 end) +1 , -1) AS age_line
from employees
) V;------ ==== [퀴즈] 아래처럼 만들기 ==== ------
--------------------------------------------------------------------------------------------------
직종ID 남자기본급여평균 여성기본급여평균 기본급여평균 남성급여의평균과의차액 여성급여의평균과의차액
--------------------------------------------------------------------------------------------------
..... .... .... ..... .... ....
FI_ACCOUNT 7900 7950 7920 -20 30
IT_PROG 5700 6000 5760 -60 240
select job_id AS 직종ID
, trunc( avg( decode( gender, '남', salary ) ) ) AS 남자기본급여평균
, trunc( avg( decode( gender, '여', salary ) ) ) AS 여성기본급여평균
, trunc( avg( salary ) ) AS 기본급여평균
, trunc( avg( decode( gender, '남', salary ) ) ) - trunc( avg( salary ) ) AS 남성급여의평균과의차액
, trunc( avg( decode( gender, '여', salary ) ) ) - trunc( avg( salary ) ) AS 여성급여의평균과의차액
from
(
select salary, job_id
, case when substr(jubun, 7, 1) in (2,4) then '여' else '남' end AS GENDER
from employees
) V
group by job_id
order by 1;