데이터베이스를 사랑하는 사람들의 모임 데이터베이스 사랑넷

MS-SQL Q&A 6890 게시물 읽기

No. 6890

일자별 호텔방문 손님 수
작성자	양현지(question)	작성일	2015-03-10 12:00	조회수	7,442

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 조건으로 일자 걸어주시면..

됩니다..

--쿼리 시작

                      --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 )

(

SELECT ROW_NUMBER() OVER ( ORDER BY ( SELECT NULL ) )

FROM sys.all_columns

)

, conDays ( aDay )

(

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.	제목	작성자	작성일	조회
6893	Procedure 실행시 속도 저하 해결법? [2]	박창수	2015-03-17	11671
6892	sql 쿼리 질의 드립니다~ [1]	하메스	2015-03-15	5484
6891	밑에글 작성잔데요. 현재 호텔에 머무르고 있는 손님 수 구하려해요. [10]	ㅁㅁ	2015-03-12	6654
6890	일자별 호텔방문 손님 수 [5]	양현지	2015-03-10	7442
6889	mssql DB 질문드립니다. [1]	듀기파파	2015-03-09	5173
6888	select count 문의 [1]	신상우	2015-03-03	5376
6887	[질문]2건씩 구분된 세로 데이터 가로로 나타내는 query [1]	안동석	2015-02-25	5615