database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
PostgreSQL
Firebird
Oracle
Informix
Sybase
ㆍMS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
MS-SQL Q&A 6890 게시물 읽기
No. 6890
일자별 호텔방문 손님 수
작성자
양현지(question)
작성일
2015-03-10 12:00
조회수
6,797

visit 테이블이구여

방문번호

 이름 체크인날짜 체크아웃 날짜 회원등급
1 a 20150301 20150302 gold
2 b 20150303 20150304 silver
3 c 20150304 20150304 gold
4 d 20150301 20150303 silver
5 e 20150303 20150303 vip

 원하는 결과는

  체크인     체크아웃    
날짜 silver gold vip silver gold vip
20150301 1 1 0 0 0 0
20150302 0 0 0 0 1 0
20150303 1 0 1 1 0 1
20150304 0 1 0 1 1 0

 날짜는 매일 나와야하구 그날 체크인이나 아웃한 사람 없으면 0으로 표시돼야해요ㅠㅠ

체크인, 체크아웃 따로 해서 조인해야할까요?

select dte, isilver, igold, ivip, vsilver, vgold, vvip
from

(select '20150301', count(*) from visit where ckIn='20150301' and grade='silver') is,

(select '20150301', count(*) from visit where ckIn='20150301' and grade='gold') ig,

(select '20150301', count(*) from visit where ckIn='20150301' and grade='vip') iv,

(select '20150301', count(*) from visit where ckOut='20150301' and grade='silver') os,

(select '20150301', count(*) from visit where ckOut='20150301' and grade='gold') og,

(select '20150301', count(*) from visit where ckOut='20150301' and grade='vip') ov

 

이렇게 무식한 방법으로 햇더니 한달치는 잘 나오는데

3달씩 나와야하는데 3달 나오게하려니까

리소스 풀 'internal'에 시스템 메모리가 부족하여 이 쿼리를 실행할 수 없습니다.

라는 오류가 떳어요.

원래는 등급도 더 많고 복잡해서...

mssql쓰구요.. 어떻게 해야하죠?

이 글에 대한 댓글이 총 5건 있습니다.

Union All 과 Case 문을 활용하시면

쉽게 답이 나옵니다.

 

하기 쿼리 참조하세요..

 

---쿼리시작

With visit As
(
Select 1 As no,'a'  As name ,'20150301'  As chkin_ymd, '20150302'  As chkout_ymd, 'gold  '   As level  Union All
Select 2 As no,'b'  As name ,'20150303'  As chkin_ymd, '20150304'  As chkout_ymd, 'silver'   As level  Union All
Select 3 As no,'c'  As name ,'20150304'  As chkin_ymd, '20150304'  As chkout_ymd, 'gold  '   As level  Union All
Select 4 As no,'d'  As name ,'20150301'  As chkin_ymd, '20150303'  As chkout_ymd, 'silver'   As level  Union All
Select 5 As no,'e'  As name ,'20150303'  As chkin_ymd, '20150303'  As chkout_ymd, 'vip   '   As level
)

          Select
                 a.ymd      As ymd
                ,Sum(a.chkin_silver_cnt )  As chkin_silver_cnt
                ,Sum(a.chkin_gold_cnt   )  As chkin_gold_cnt
                ,Sum(a.chkin_vip_cnt    )  As chkin_vip_cnt
                ,Sum(a.chkout_silver_cnt)  As chkout_silver_cnt
                ,Sum(a.chkout_gold_cnt  )  As chkout_gold_cnt
                ,Sum(a.chkout_vip_cnt   )  As chkout_vip_cnt
            From
                   
                 (
                      Select
                             a.chkin_ymd   As ymd
                            ,(
                               Case a.level When 'gold' Then 1
                                  Else 0
                               End  
                             )     As chkin_gold_cnt
                            ,(
                               Case a.level When 'silver' Then 1
                                  Else 0
                               End  
                             )     As chkin_silver_cnt
                            ,(
                               Case a.level When 'vip' Then 1
                                  Else 0
                               End  
                             )     As chkin_vip_cnt
                            ,0     As chkout_gold_cnt
                            ,0     As chkout_silver_cnt
                            ,0     As chkout_vip_cnt                    
                        From visit a
                   
                   Union All
                  
                      Select
                             a.chkout_ymd   As ymd
                            ,0     As chkin_gold_cnt
                            ,0     As chkin_silver_cnt
                            ,0     As chkin_vip_cnt                    
                            ,(
                               Case a.level When 'gold' Then 1
                                  Else 0
                               End  
                             )     As chkout_gold_cnt
                            ,(
                               Case a.level When 'silver' Then 1
                                  Else 0
                               End  
                             )     As chkout_silver_cnt
                            ,(
                               Case a.level When 'vip' Then 1
                                  Else 0
                               End  
                             )     As chkout_vip_cnt
                        From visit a
                 ) a
        Group By a.ymd
        Order By a.ymd Asc

--쿼리끝

최한영(terry0515)님이 2015-03-10 12:19에 작성한 댓글입니다.

음 근데 이렇게 하면 컬럼에 없는 날짜는 안나오는 거 아닌가요ㅠㅠ?

기간을 지정해서 3달치 매일매일이 다 나와야하는데...

작성자님이 2015-03-10 13:11에 작성한 댓글입니다. Edit

일자 가져오는 부분은

마농님께서 작성하셨던 쿼리 고대로 썼습니다 ^^;;

(게시물 검색하여)

 

일자 관련 Table 이 있으면..

고걸 이용하시면 될듯하네요..

(한 10년치 일자만 넣어두면..유용하게 사용됨!!)

 

Sub 쿼리 내의 1,2,3 에 대해

Where 조건으로 일자 걸어주시면..

됩니다..

 

--쿼리 시작

With visit As
(
Select 1 As no,'a'  As name ,'20150301'  As chkin_ymd, '20150302'  As chkout_ymd, 'gold  '   As level  Union All
Select 2 As no,'b'  As name ,'20150303'  As chkin_ymd, '20150304'  As chkout_ymd, 'silver'   As level  Union All
Select 3 As no,'c'  As name ,'20150304'  As chkin_ymd, '20150304'  As chkout_ymd, 'gold  '   As level  Union All
Select 4 As no,'d'  As name ,'20150301'  As chkin_ymd, '20150303'  As chkout_ymd, 'silver'   As level  Union All
Select 5 As no,'e'  As name ,'20150303'  As chkin_ymd, '20150303'  As chkout_ymd, 'vip   '   As level
)
,t(dt) AS
(
SELECT DATEADD(mm, DATEDIFF(m, 0, GETDATE()), -70) dt
 UNION ALL
SELECT dt + 1
  FROM t
 WHERE dt + 1 < DATEADD(mm, DATEDIFF(m, 0, GETDATE()) + 1, 0)
)
,ymd As
(
Select CONVERT(VARCHAR(8), dt, 112) As ymd
  From t
)


          Select
                 a.ymd      As ymd
                ,Sum(a.chkin_silver_cnt )  As chkin_silver_cnt
                ,Sum(a.chkin_gold_cnt   )  As chkin_gold_cnt
                ,Sum(a.chkin_vip_cnt    )  As chkin_vip_cnt
                ,Sum(a.chkout_silver_cnt)  As chkout_silver_cnt
                ,Sum(a.chkout_gold_cnt  )  As chkout_gold_cnt
                ,Sum(a.chkout_vip_cnt   )  As chkout_vip_cnt
            From
                   
                 (

                      --1. 체크인 날짜 기준으로..등급별로 카운트
                      Select
                             a.chkin_ymd   As ymd
                            ,(
                               Case a.level When 'gold' Then 1
                                  Else 0
                               End  
                             )     As chkin_gold_cnt
                            ,(
                               Case a.level When 'silver' Then 1
                                  Else 0
                               End  
                             )     As chkin_silver_cnt
                            ,(
                               Case a.level When 'vip' Then 1
                                  Else 0
                               End  
                             )     As chkin_vip_cnt
                            ,0     As chkout_gold_cnt
                            ,0     As chkout_silver_cnt
                            ,0     As chkout_vip_cnt                    
                        From visit a
                   
                   Union All
 

                      --2. 체크아웃 날짜 기준으로..등급별로 카운트                   
                      Select
                             a.chkout_ymd   As ymd
                            ,0     As chkin_gold_cnt
                            ,0     As chkin_silver_cnt
                            ,0     As chkin_vip_cnt                    
                            ,(
                               Case a.level When 'gold' Then 1
                                  Else 0
                               End  
                             )     As chkout_gold_cnt
                            ,(
                               Case a.level When 'silver' Then 1
                                  Else 0
                               End  
                             )     As chkout_silver_cnt
                            ,(
                               Case a.level When 'vip' Then 1
                                  Else 0
                               End  
                             )     As chkout_vip_cnt
                        From visit a

                   Union All

                      --3. 날짜 가져오기..(등급별 카운트는 0으로 Set)
                      Select
                             a.ymd   As ymd
                            ,0     As chkin_gold_cnt
                            ,0     As chkin_silver_cnt
                            ,0     As chkin_vip_cnt                    
                            ,0     As chkout_gold_cnt
                            ,0     As chkout_silver_cnt
                            ,0     As chkout_vip_cnt
                        From ymd a
                 ) a
        Group By a.ymd
        Order By a.ymd Asc
-- 쿼리 끝       

최한영(terry0515)님이 2015-03-10 13:30에 작성한 댓글입니다.

빠른 답변 너무 감사합니다ㅠㅠ

너무 감사합니다. 정말 많은 도움이 됐어요!!! 감사합니다

작성자님이 2015-03-10 13:51에 작성한 댓글입니다.
이 댓글은 2015-03-10 14:17에 마지막으로 수정되었습니다. Edit

-- 간만에 쿼리 놀이하러 놀러왔더니..ㅋㅋ
-- 한영님 쿼리 코스트 절반짜리 살짝쿵 만들어봤.. ㅋ
With visit As

(
Select 1 As no,'a'  As name ,'2015-03-01'  As chkin_ymd, '2015-03-02'  As chkout_ymd, 'gold  '   As level  Union All
Select 2 As no,'b'  As name ,'2015-03-03'  As chkin_ymd, '2015-03-04'  As chkout_ymd, 'silver'   As level  Union All
Select 3 As no,'c'  As name ,'2015-03-04'  As chkin_ymd, '2015-03-04'  As chkout_ymd, 'gold  '   As level  Union All
Select 4 As no,'d'  As name ,'2015-03-01'  As chkin_ymd, '2015-03-03'  As chkout_ymd, 'silver'   As level  Union All
Select 5 As no,'e'  As name ,'2015-03-03'  As chkin_ymd, '2015-03-03'  As chkout_ymd, 'vip   '   As level
)
, nums ( aNum )
AS
(
SELECT ROW_NUMBER() OVER ( ORDER BY ( SELECT NULL ) )
FROM sys.all_columns
)
, conDays ( aDay )
AS
(
SELECT TOP (35) DATEADD( DAY, DATEDIFF(DAY, 0, '2015-03-01' ), aNum - 1 )
FROM nums
)
SELECT aDay, silverChkIn, goldChkIn, vipChkIn, silverChkOut, goldChkOut, vipChkOut
FROM conDays
LEFT JOIN
(
SELECT chkin_ymd, [gold  ] AS goldChkIn, [silver] AS silverChkIn, [vip   ] AS vipChkIn
FROM(
SELECT chkin_ymd, [level], COUNT(*) AS cnt
FROM visit
GROUP BY chkin_ymd, [level]
) AS grInDay
PIVOT (
SUM( cnt )
FOR [level] IN ( [gold  ], [silver], [vip   ] )
) AS pvt
) AS inDay
ON conDays.aDay = inDay.chkin_ymd
LEFT JOIN
(
SELECT chkout_ymd, [gold  ] AS goldChkOut, [silver] AS silverChkOut, [vip   ] AS vipChkOut
FROM(
SELECT chkout_ymd, [level], COUNT(*) AS cnt
FROM visit
GROUP BY chkout_ymd, [level]
) AS grOutDay
PIVOT (
SUM( cnt )
FOR [level] IN ( [gold  ], [silver], [vip   ] )
) AS pvt
) AS outDay
ON conDays.aDay = outDay.chkout_ymd
;
우욱님이 2015-03-10 14:27에 작성한 댓글입니다. Edit
[Top]
No.
제목
작성자
작성일
조회
6893Procedure 실행시 속도 저하 해결법? [2]
박창수
2015-03-17
10996
6892sql 쿼리 질의 드립니다~ [1]
하메스
2015-03-15
4965
6891밑에글 작성잔데요. 현재 호텔에 머무르고 있는 손님 수 구하려해요. [10]
ㅁㅁ
2015-03-12
6125
6890일자별 호텔방문 손님 수 [5]
양현지
2015-03-10
6797
6889mssql DB 질문드립니다. [1]
듀기파파
2015-03-09
4652
6888select count 문의 [1]
신상우
2015-03-03
4854
6887[질문]2건씩 구분된 세로 데이터 가로로 나타내는 query [1]
안동석
2015-02-25
5054
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2023 DSN, All rights reserved.
작업시간: 0.048초, 이곳 서비스는
	PostgreSQL v16.1로 자료를 관리합니다