Медиана во времени - SQL Server
У меня есть данные (если быть точным, статистика билетов), которые я пытаюсь получить медианой со временем.
Прямо сейчас у меня есть запрос, который вычисляет разницу между датой, когда билет был открыт, и датой его закрытия. Я беру эти данные и вычисляю среднее число дней для закрытия в течение нескольких месяцев и лет, используя SQL Server, встроенные в функцию AVG. Это хорошо работает, однако я нахожу, что метрики искажены из-за выбросов в данных.
Что я действительно хочу, так это медиана данных, развернутых за месяцы и годы. У меня проблемы с достижением того, чего я добиваюсь, и я не уверен, что это вообще возможно.
У меня сейчас есть запрос с использованием функции AVG:
SELECT 'Support - Days To Close Escalation', *
FROM
(
SELECT
DATEDIFF(HOUR, e.CreatedDate, e.Escalation_Close_Date_Time__c) AS DaysToCloseEscalation,
LEFT(CONVERT(CHAR(10), e.CreatedDate,126), 7) AS EscalationCreateDate
FROM [dbo].[Escalations] AS e WITH(NOLOCK)
LEFT JOIN [dbo].[Case] AS c WITH(NOLOCK)
ON e.Case__c = c.Id
WHERE e.Escalation_Queue__c IN ('PM 10 Tier 2 Support', 'PM 11 Tier 2 Support')
AND e.CreatedDate BETWEEN '2017-04-01 00:00:00.000' AND '2018-04-01 00:00:00.000'
AND e.Escalation_Close_Date_Time__c IS NOT NULL
) AS SupportEscalationVolume
PIVOT
(
AVG(SupportEscalationVolume.DaysToCloseEscalation) FOR SupportEscalationVolume.EscalationCreateDate IN ([2017-04],[2017-05],[2017-06],[2017-07],[2017-08],[2017-09],[2017-10],[2017-11],[2017-12],[2018-01],[2018-02],[2018-03])
) AS SupportEscalationVolumePivot
Результатом этого запроса является что-то вроде (кроме всего в одной строке, так как данные поворачиваются):
StatDescription | Support - Days To Close Escalation
----------------------------------------------------
2017-04 | 107
2017-05 | 52
2017-06 | 101
2017-07 | 106
2017-08 | 69
2017-09 | 54
2017-10 | 49
2017-11 | 42
2017-12 | 51
2018-01 | 31
2018-02 | 23
2018-03 | 15
После некоторых исследований о том, как получить медиану в SQL, я прибегнул к использованию DENSE_RANK(), как показано в запросе ниже. Я начал с ROW_NUMBER(), но это дало мне счетчик для ВСЕХ записей, где то, что я действительно хочу, - это среднее время закрытия билета для каждого месяца / года.
;
WITH SupportDaysToClose(HoursToCloseEscalation, EscalationCreateDate, RowNumber)
AS
(
SELECT
DATEDIFF(HOUR, e.CreatedDate, e.Escalation_Close_Date_Time__c) AS HoursToCloseEscalation,
LEFT(CONVERT(CHAR(10), e.CreatedDate,126), 7) AS EscalationCreateDate,
DENSE_RANK() OVER(ORDER BY LEFT(CONVERT(CHAR(10), e.CreatedDate,126), 7) ASC) AS RowNumber
FROM [dbo].[Escalations] AS e WITH(NOLOCK)
LEFT JOIN [dbo].[Case] AS c WITH(NOLOCK)
ON e.Case__c = c.Id
WHERE e.Escalation_Queue__c IN ('PM 10 Tier 2 Support', 'PM 11 Tier 2 Support')
AND e.CreatedDate BETWEEN '2017-04-01 00:00:00.000' AND '2018-04-01 00:00:00.000'
AND e.Escalation_Close_Date_Time__c IS NOT NULL
)
SELECT *
FROM SupportDaysToClose
ORDER BY RowNumber,HoursToCloseEscalation
Образец этих данных выглядит как
HoursToClose|CreateDate|RowNumber
---------------------------------
0 |2017-04 |1
7 |2017-08 |5
27 |2017-12 |9
Каждый RowNumber соотносится с данным месяцем и годом, максимум - 12.
На данный момент, я не совсем уверен, куда идти.
Кто-нибудь когда-либо делал что-то подобное раньше? Я не уверен, что я на правильном пути или мне нужно переосмыслить всю стратегию. Я заранее прошу прощения, если синтаксис трудно следовать.