[MCP학원/MCSE/MCSE학원] 회원유형 분석 기본 SQL 구문

[MCP학원/MCSE/MCSE학원] 회원유형 분석 기본 SQL 구문

log_t TABLE 구조

Column_name Type length
---------------------------------------
resno char 14
type tinyint 2 
regdate smalldatetime 4 

1. 회원유형 - 성별

select
case no
when 1 then type
else 10 end as 유형,
sum(m) as 남자,
sum(f) as 여자,
sum(cnt) as 소계
from ( 
select type,
isnull(sum(case when sex = 1 or sex = 3 then cnt end),0) as m,
isnull(sum(case when sex = 2 or sex = 4 then cnt end),0) as f,
isnull(sum(cnt),0) as cnt
from ( 
select type,substring(resno,8,1) as sex,count(*) as cnt FROM log_t
group BY type,substring(resno,8,1)
) x 
group BY type
) y, ( SELECT 1 as no union SELECT 2 as no) c 
group BY
case no
when 1 then type
else 10 end

> result
유형 남자 여자 소계
----------- ----------- ----------- -----------
1 12024 2814 14838
2 113 16 129
3 3 0 3 
4 10 2 12
10 12150 2832 14982

* 유형의 10은 총계임

2. 회원유형 - 연령

select
case no
when 1 then type
else 10 end as 유형,
sum(cnt1) as '10 이하',
sum(cnt2) as '10대',
sum(cnt3) as '20대',
sum(cnt4) as '30대',
sum(cnt5) as '40대',
sum(cnt6) as '50대 이상',
sum(cnt) as 소계
from ( 
select type,
isnull(sum(case when gen = 0 then cnt end),0) as cnt1,
isnull(sum(case when gen = 10 then cnt end),0) as cnt2,
isnull(sum(case when gen = 20 then cnt end),0) as cnt3,
isnull(sum(case when gen = 30 then cnt end),0) as cnt4,
isnull(sum(case when gen = 40 then cnt end),0) as cnt5,
isnull(sum(case when gen >= 50 then cnt end),0) as cnt6,
isnull(sum(cnt),0) as cnt
from ( 
select type,
(year(getdate())-
case substring(resno,8,1)
when 1 then '19' + substring(resno,1,2)
when 2 then '19' + substring(resno,1,2)
when 3 then '20' + substring(resno,1,2)
when 4 then '20' + substring(resno,1,2)
end)/10*10 as gen,
count(*) as cnt
from log_t
group BY type,
(year(getdate())-
case substring(resno,8,1)
when 1 then '19' + substring(resno,1,2)
when 2 then '19' + substring(resno,1,2)
when 3 then '20' + substring(resno,1,2)
when 4 then '20' + substring(resno,1,2)
end)/10*10
) x 
group BY type
) y, ( SELECT 1 as no union SELECT 2 as no) c 
group BY
case no
when 1 then type
else 10 end


> result
유형 10 이하 10대 20대 30대 40대 50대 이상 소계
----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1 3 977 9494 3925 360 79 14838
2 0 9 85 35 0 0 129
3 0 0 3 0 0 0 3 
4 0 0 12 0 0 0 12
10 3 986 9594 3960 360 79 14982

(5 row(s) affected)

출처 : http://www.it-bank.or.kr/ms/main_1.htm