DBMS/MS-SQL

[MS-SQL][Query] 데이터베이스 일별 집계 테이블 채크 쿼리 (Daily Table Check Query)

Fly_Mir 2018. 6. 1. 16:41

Daily Data가 쌓이는 Table에 빠진 데이터가 있는지 확인해보는 쿼리를 만들어보자.

※ fnStringToTable() 이라는 함수를 이용한다. 해당 함수의 자세한 내용은 아래 관련글 링크를 찾아가 확인 가능하다.

    -- 동적으로 최근 날짜별,시간별 데이터 채크 로직 --
    ------------------------------------------ 설정값
    DECLARE @ViewDay Integer = 1 -- 몇일전까지 보고 싶은지
    DECLARE @ViewTable Varchar(max) = 'DB1.dbo.TABLE1, DB2.dbo.TABLE2'  -- 보고싶은 테이블 리스트
    DECLARE @DateColumn Varchar(max) = 'updateTime' -- 날짜,시간 채크 컬럼명 
                                                    -- (DateTime 형이 아닐경우 아래 query문 수정필요)
    -----------------------------------------------------------
    DECLARE @Cnt Integer
    DECLARE @PivString Varchar(max) = ''
    DECLARE @ColString Varchar(max) = ''
    DECLARE @StartDate Datetime = DATEADD(DD, DATEDIFF(DD, 0, GETDATE() - @ViewDay), 0)
    DECLARE @Sql Varchar(max) = ''
    DECLARE @Title Varchar(max) = ''
    DECLARE @TableList Table (
    Title Varchar(max)
    ,Idx Int IDENTITY(1,1) )
     
    --------------------------------------------------------------------------------------------------------
    ----------------------------------------------- 00시부터 24시까지 Column String 구하기 (Pivot String 포함)
    --------------------------------------------------------------------------------------------------------
    SET @Cnt = 0
    WHILE @Cnt < 24
    BEGIN
    SET @PivString = @PivString + '[' + Right('00' + Convert(Varchar, @Cnt), 2) + '],'
    SET @ColString = @ColString + '[' + Right('00' + Convert(Varchar, @Cnt), 2) + '] [' 
                                      + Right('00' + Convert(Varchar, @Cnt), 2) + '],'
    SET @Cnt = @Cnt + 1
    END
    -- 마지막 ',' 빼주기 ---
    SET @PivString = Left(@PivString, LEN(@PivString) - 1 )
    SET @ColString = Left(@ColString, LEN(@ColString) - 1 )
    --------------------------------------------------------------------------------------------------------
    --------------------------------------------- String to Table Function 이용하여 Table List Table 가져오기
    --------------------------------------------------------------------------------------------------------
    INSERT INTO @TableList
    SELECT col AS Title FROM dbo.fnStringToTable(@ViewTable, ',')
    --------------------------------------------------------------------------------------------------------
    --------------------------------------------------------------------------------------------- Query 생성
    --------------------------------------------------------------------------------------------------------
    SET @cnt = 1
     
    WHILE @Cnt <= (SELECT Count(*) FROM @TableList)
    BEGIN
    SET @Title = (SELECT Title FROM @TableList WHERE Idx = @Cnt)
    SET @Sql = @Sql + N'
    SELECT Title
        ,Date
        ,' + @ColString + '
    FROM (
        SELECT Right(Convert(CHAR(13), ' + @DateColumn + ', 20),2) Hour
                ,''o'' Value
                ,''' + @Title + ''' Title
                ,Convert(CHAR(8),' + @DateColumn + ', 112) Date
            FROM (
            SELECT DISTINCT ' + @DateColumn + '
                    FROM ' + @Title + '
                    WHERE ' + @DateColumn + ' >= ''' + Convert(Varchar(10), @StartDate , 121) + '''
                ) a
        ) a
    PIVOT ( Max(Value) FOR Hour in ( ' + @PivString + ' ) ) PV
    '
    SET @Cnt = @Cnt + 1
    IF @Cnt <= (SELECT Count(*) FROM @TableList) SET @Sql = @Sql + N'
    UNION ALL
    '
    END
    ------------------------------------------------------------------------------------------------------
    ------------------------------------------------------------------------------------------- Query 실행
    ------------------------------------------------------------------------------------------------------ 
    PRINT @Sql
    EXEC (@Sql)

결과는 아래와 같이 표시 된다. (데이터가 없는 부분은 null로 표시된다.)

※현재시간 포함 이후부터는 null이여야 정상이다.

Title 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19
DB1.dbo.TABLE1 o o o o o o o o o o o o o o null
DB2.dbo.TABLE2 o o o o o o o o o o o o o o null
DB3.dbo.TABLE3 o o o null o o o o o o o o o o null

날다의 운영환경
본체DeskTop
O SWindows7 Ultimate K
ApplicationMicrosoft SQL Server Management Studio 2012
MS-SQLMicorsoft SQL Server 2008 R2 (10.50.1765.0)