3초기억력

MS-SQL SubString으로 할수있는 연령대별 회원분석 쿼리문 본문

쿼리_MSSQL

MS-SQL SubString으로 할수있는 연령대별 회원분석 쿼리문

잠수콩 2008. 11. 13. 18:01
336x280(권장), 300x250(권장), 250x250, 200x200 크기의 광고 코드만 넣을 수 있습니다.
SELECT age_name, sex_name, SUM(total_count) AS total_count
FROM
(
 SELECT    
  CASE WHEN CAST(substring(CAST(CONVERT(varchar(10), getdate(), 121) AS varchar(10)), 1, 4) AS int)
    - CAST('19' + substring(jumin, 1, 2) AS int) + 1 BETWEEN 0 AND 20 THEN '20'
    WHEN CAST(substring(CAST(CONVERT(varchar(10), getdate(), 121) AS varchar(10)), 1, 4) AS int)
    - CAST('19' + substring(jumin, 1, 2) AS int) + 1 BETWEEN 21 AND 25 THEN '25'
    WHEN CAST(substring(CAST(CONVERT(varchar(10), getdate(), 121) AS varchar(10)), 1, 4) AS int)
    - CAST('19' + substring(jumin, 1, 2) AS int) + 1 BETWEEN 26 AND 30 THEN '30'
    WHEN CAST(substring(CAST(CONVERT(varchar(10), getdate(), 121) AS varchar(10)), 1, 4) AS int)
    - CAST('19' + substring(jumin, 1, 2) AS int) + 1 BETWEEN 31 AND 35 THEN '35'
    WHEN CAST(substring(CAST(CONVERT(varchar(10), getdate(), 121) AS varchar(10)), 1, 4) AS int)
    - CAST('19' + substring(jumin, 1, 2) AS int) + 1 BETWEEN 36 AND 40 THEN '40'
    WHEN CAST(substring(CAST(CONVERT(varchar(10), getdate(), 121) AS varchar(10)), 1, 4) AS int)
    - CAST('19' + substring(jumin, 1, 2) AS int) + 1 BETWEEN 41 AND 45 THEN '45'
    WHEN CAST(substring(CAST(CONVERT(varchar(10), getdate(), 121) AS varchar(10)), 1, 4) AS int)
    - CAST('19' + substring(jumin, 1, 2) AS int) + 1 BETWEEN 46 AND 50 THEN '50'
    WHEN CAST(substring(CAST(CONVERT(varchar(10), getdate(), 121) AS varchar(10)), 1, 4) AS int)
    - CAST('19' + substring(jumin, 1, 2) AS int) + 1 BETWEEN 51 AND 55 THEN '55'
    WHEN CAST(substring(CAST(CONVERT(varchar(10), getdate(), 121) AS varchar(10)), 1, 4) AS int)
    - CAST('19' + substring(jumin, 1, 2) AS int) + 1 BETWEEN 56 AND 60 THEN '60'
    WHEN CAST(substring(CAST(CONVERT(varchar(10), getdate(), 121) AS varchar(10)), 1, 4) AS int)
    - CAST('19' + substring(jumin, 1, 2) AS int) + 1 >= 61 THEN '61'
  END AS age_name, SUBSTRING(jumin, 7, 1) as sex_name, COUNT(num) AS total_count
 FROM          회원테이블명
 GROUP BY CAST(SUBSTRING(CAST(CONVERT(varchar(10), GETDATE(), 121) AS varchar(10)), 1, 4) AS int)
   - CAST('19' + SUBSTRING(jumin, 1, 2) AS int) + 1, SUBSTRING(jumin, 7, 1)
) AS aa
GROUP BY age_name, sex_name
ORDER BY age_name

칼럼명 설명
- 주민번호 : jumin
- 성별 : sex_name (1:남자,2:여자)
- 나이대 : age_name (20:20대미만, 25:21살~25살, 30:26살~30살...)
- 회원수 : total_count

asp 페이지에서
 연령대  성별합  남자  여자
 20살미만  100,000  50,000  50,000
 21살~25살      
 26살~30살      
 31살~35살      
 ....      
 61세이상      

머 이런식으로 사용하면 됨.
참고로 각 수치옆에 (%)로 해주면 더 좋겠죠?
asp 단에서 처리할 내용이 많을듯.


Comments