[SQL] 테이블 생성 확인

#테이블 생성할 곳 지정

Use WorkDB

Go

#생성할 테이블명 및 생성할 테이블 지정

select     cast(LogTime as date) 'date', UserNo, LogType, OS, max(LV) 'LV' , count(*) 'CNT', sum(playtime) 'PlayTime'

into     dailyUV_20161229

FROM    [database].[Catalog].[dbo].[table] with (nolock)

where

     logtime between '2016-11-01 00:00:00' and '2016-12-28 23:59:59' 

group by

     cast(LogTime as date), UserNo, LogType, OStype

#클러스터인덱스 생성

create clustered index cidx_dailyUV_20161229  on 

[dbo].[dailyUV_20161229] (Userno , date )

#뽑을 쿼리 생성

select aa.nDate, count(distinct aa.Userno) 

from

(

        select  Date, Userno

        from    dbo.dailyUV_20161229a with(nolock) 

) aa

where   aa.Userno in ( select  UserNo 

                                           from    dbo.dailyUV_20161229 with(nolock)

                                           where   Date between dateadd(dd,-7, aa.Date) and dateadd(dd,-1,aa.Date)

                                          )          

group by    aa.nDate

order by     aa.nDate