Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Среднее кол-во запросов  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4727
Господа, есть набор диапазонов (StartDate DATETIME, EndDate DateTime).

кто предложит лучший способ построить гистограмму

выбирается период в 1 сек, 5 сек, 10 сек и т.д показать среднее кол-во ОДНОВРЕМЕННО ИСПОЛНЯЕМЫХ ЗАПРОСОВ.

Например, 1 запрос с 1 по 3 секунду. второй запрос со 2 по 3 секунду, третий запрос со 3 по 4 секунду.

результат для 1 секунды.

1 - 1
2 - 2
3 - 3
4 - 1

Для 5 секунд AVG (1,2,3,1,0) = 1.4

Пример интервалов за 1 минуту. Нужно, чтобы можно было перенести на периоды минута, час, день. данных будет сотни тысяч.

+
IF OBJECT_ID ('tempdb..#D') IS NOT NULL 
DROP TABLE #D 

CREATE TABLE #D (StartDate DATETIME, EndDate DateTime)

INSERT INTO #D VALUES 
(('2019-11-07 06:46:04.720'),('2019-11-07 06:46:18.497')),
(('2019-11-07 06:46:16.210'),('2019-11-07 06:46:18.720')),
(('2019-11-07 06:46:19.850'),('2019-11-07 06:46:51.087')),
(('2019-11-07 06:46:49.633'),('2019-11-07 06:46:51.900')),
(('2019-11-07 06:46:21.870'),('2019-11-07 06:46:55.847')),
(('2019-11-07 06:46:46.630'),('2019-11-07 06:47:01.753')),
(('2019-11-07 06:46:50.757'),('2019-11-07 06:47:15.093')),
(('2019-11-07 06:46:50.077'),('2019-11-07 06:47:15.100')),
(('2019-11-07 06:46:58.453'),('2019-11-07 06:50:08.967')),
(('2019-11-07 06:46:04.113'),('2019-11-07 06:51:06.770')),
(('2019-11-07 06:46:11.150'),('2019-11-07 06:46:20.050')),
(('2019-11-07 06:46:16.633'),('2019-11-07 06:46:21.200')),
(('2019-11-07 06:46:21.013'),('2019-11-07 06:46:21.570')),
(('2019-11-07 06:46:14.613'),('2019-11-07 06:46:23.237')),
(('2019-11-07 06:46:10.857'),('2019-11-07 06:46:13.490')),
(('2019-11-07 06:46:12.630'),('2019-11-07 06:46:13.917')),
(('2019-11-07 06:46:11.163'),('2019-11-07 06:46:15.107')),
(('2019-11-07 06:46:15.197'),('2019-11-07 06:46:15.893')),
(('2019-11-07 06:46:23.400'),('2019-11-07 06:46:24.360')),
(('2019-11-07 06:46:24.520'),('2019-11-07 06:46:25.423')),
(('2019-11-07 06:46:25.523'),('2019-11-07 06:46:27.353')),
(('2019-11-07 06:46:22.177'),('2019-11-07 06:46:27.777')),
(('2019-11-07 06:46:27.477'),('2019-11-07 06:46:28.633')),
(('2019-11-07 06:46:19.300'),('2019-11-07 06:46:28.830')),
(('2019-11-07 06:46:28.233'),('2019-11-07 06:46:29.667')),
(('2019-11-07 06:46:31.117'),('2019-11-07 06:46:32.167')),
(('2019-11-07 06:46:33.127'),('2019-11-07 06:46:33.907')),
(('2019-11-07 06:46:33.587'),('2019-11-07 06:46:34.500')),
(('2019-11-07 06:46:28.393'),('2019-11-07 06:46:34.697')),
(('2019-11-07 06:46:19.600'),('2019-11-07 06:46:35.847')),
(('2019-11-07 06:46:29.767'),('2019-11-07 06:46:36.610')),
(('2019-11-07 06:46:00.463'),('2019-11-07 06:46:38.570')),
(('2019-11-07 06:46:35.927'),('2019-11-07 06:46:38.983')),
(('2019-11-07 06:46:35.020'),('2019-11-07 06:46:38.987')),
(('2019-11-07 06:46:36.230'),('2019-11-07 06:46:40.660')),
(('2019-11-07 06:46:39.550'),('2019-11-07 06:46:40.883')),
(('2019-11-07 06:46:39.357'),('2019-11-07 06:46:41.787')),
(('2019-11-07 06:46:42.020'),('2019-11-07 06:46:43.007')),
(('2019-11-07 06:46:35.860'),('2019-11-07 06:46:46.627')),
(('2019-11-07 06:46:39.363'),('2019-11-07 06:46:47.227')),
(('2019-11-07 06:46:46.657'),('2019-11-07 06:46:47.267')),
(('2019-11-07 06:46:47.377'),('2019-11-07 06:46:47.907')),
(('2019-11-07 06:46:48.190'),('2019-11-07 06:46:48.927')),
(('2019-11-07 06:46:37.353'),('2019-11-07 06:46:49.050')),
(('2019-11-07 06:46:21.273'),('2019-11-07 06:46:50.717')),
(('2019-11-07 06:46:02.330'),('2019-11-07 06:46:03.987')),
(('2019-11-07 06:46:02.123'),('2019-11-07 06:46:04.087')),
(('2019-11-07 06:46:02.697'),('2019-11-07 06:46:04.607')),
(('2019-11-07 06:46:03.403'),('2019-11-07 06:46:05.483')),
(('2019-11-07 06:46:04.233'),('2019-11-07 06:46:05.640')),
(('2019-11-07 06:46:03.190'),('2019-11-07 06:46:06.827')),
(('2019-11-07 06:46:06.620'),('2019-11-07 06:46:07.677')),
(('2019-11-07 06:46:07.027'),('2019-11-07 06:46:08.277')),
(('2019-11-07 06:46:08.710'),('2019-11-07 06:46:10.167')),
(('2019-11-07 06:46:06.087'),('2019-11-07 06:46:10.320')),
(('2019-11-07 06:46:10.073'),('2019-11-07 06:46:10.707')),
(('2019-11-07 06:45:59.430'),('2019-11-07 06:46:01.953')),
(('2019-11-07 06:45:59.507'),('2019-11-07 06:46:02.117')),
(('2019-11-07 06:45:54.523'),('2019-11-07 06:46:02.137')),
(('2019-11-07 06:45:20.827'),('2019-11-07 06:46:02.960')),
(('2019-11-07 06:45:19.653'),('2019-11-07 06:46:03.013')),
(('2019-11-07 06:45:55.473'),('2019-11-07 06:46:23.773')),
(('2019-11-07 06:45:49.597'),('2019-11-07 06:46:12.267')),
(('2019-11-07 06:45:19.437'),('2019-11-07 06:46:19.607')),
(('2019-11-07 06:45:12.790'),('2019-11-07 06:46:50.757')),
(('2019-11-07 06:45:37.317'),('2019-11-07 06:46:17.113'))

SELECT * FROM #D


Сообщение было отредактировано: 29 ноя 19, 12:18
29 ноя 19, 12:17    [22028661]     Ответить | Цитировать Сообщить модератору
 Re: Среднее кол-во запросов  [new]
Remind
Member

Откуда: UK
Сообщений: 523
a_voronin,

DECLARE
  @DateFrom datetime,
  @DateTo datetime,
  @PeriodInSeconds int = 60;

SELECT
  @DateFrom = MIN(StartDate),
  @DateTo   = MAX(EndDate)
FROM #D;

WITH Seconds AS
(
  SELECT @DateFrom AS SecondFrom, DATEADD(SECOND, 1, @DateFrom) AS SecondTo
  UNION ALL
  SELECT DATEADD(SECOND, 1, SecondFrom), DATEADD(SECOND, 1, SecondTo)
  FROM Seconds
  WHERE SecondTo < @DateTo

)
, QueriesPerSecond AS
(
  SELECT t.SecondFrom, t.SecondTo, COUNT(*) AS QueriesCount,
    ROW_NUMBER() OVER (ORDER BY t.SecondFrom) AS rn
  FROM #D d
    JOIN Seconds t
      ON t.SecondFrom <= d.EndDate AND t.SecondTo >= d.StartDate
  GROUP BY t.SecondFrom, t.SecondTo
)
SELECT MIN(SecondFrom) AS PeriodFrom, MAX(SecondTo) AS PeriodTo, ROUND(AVG(CONVERT(FLOAT, QueriesCount)), 2) AS AvgQueriesCount
FROM QueriesPerSecond
GROUP BY (rn - 1) / @PeriodInSeconds
OPTION (MAXRECURSION 0);


Сообщение было отредактировано: 29 ноя 19, 14:51
29 ноя 19, 14:44    [22028936]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить