LocksStat


Наиболее часто встречающиеся слова в статье:

[LocksLeaderBySum] [GETDATE] [CountBlockedSession] [SumBlockedSession] [DATEPART] [BlockCount] [DATEADD] [DB_TEMP] [DECLARE] [varchar]


Статья:

DECLARE @tab table (
    [Date] date
    , [hour] int
    , [CountBlockedSession] int
    , [SumBlockedSession] int
    , [LocksLeaderBySum] varchar(max)
    )
 
DECLARE @T2 table (
    [Date] date
    , [hour] int
    , [Query] varchar(max)
    )
 
INSERT INTO @tab
SELECT 
    DATEADD(day, -1, CAST(GETDATE() AS DATE)) as [Date]
    , DATEPART(HOUR, [Date]) as hou
    , count(*) as cou
    , sum(ISNULL([BlockCount], 0)) as summ
    , NULL
FROM 
    [DB_TEMP].[dbo].[Locks]
WHERE 
    [Date] > DATEADD(day, -1, CAST(GETDATE() AS DATE)) AND [Date] < CAST(GETDATE() AS DATE)
    AND BlockCount > 0
GROUP BY DATEPART(HOUR, [Date])
--ORDER BY hou 
 
;WITH CTE 
AS
(
SELECT 
    DATEADD(day, -1, CAST(GETDATE() AS DATE)) as [Date]
    , [Запрос]
    , DATEPART(HOUR, [Date]) as hou
--    , count(*) as cou
    , sum(ISNULL([BlockCount], 0)) as summ
    , ROW_NUMBER() OVER (PARTITION BY DATEPART(HOUR, [Date]) ORDER BY sum([BlockCount]) DESC) as rn
FROM 
    [DB_TEMP].[dbo].[Locks]
WHERE 
    [Date] > DATEADD(day, -1, CAST(GETDATE() AS DATE)) AND [Date] < CAST(GETDATE() AS DATE)
    AND [Описание] = ''Блокирующий''
    AND BlockCount > 0
GROUP BY DATEPART(HOUR, [Date]), [Запрос]
)
 
INSERT INTO @T2
SELECT [Date], hou, [Запрос] FROM CTE WHERE rn = 1
 
UPDATE T
SET [LocksLeaderBySum] = CTEQ.Query
FROM 
    @T2 CTEQ
    INNER JOIN @tab T ON (CTEQ.Date = T.Date AND CTEQ.hour = T.hour)
 
INSERT INTO [dbo].[LocksStat] ([Date], [hour], [CountBlockedSession], [SumBlockedSession], [LocksLeaderBySum])
SELECT [Date], [hour], [CountBlockedSession], [SumBlockedSession], [LocksLeaderBySum] FROM @tab
 
INSERT INTO [MDMDBX].[DB_TEMP].[dbo].[LocksStat] ([Date], [hour], [CountBlockedSession], [SumBlockedSession], [LocksLeaderBySum])
SELECT [Date], [hour], [CountBlockedSession], [SumBlockedSession], [LocksLeaderBySum] FROM @tab