SQL-запрос для сравнения показателей торгового представителя со средним квартилем, полученным в другой таблице
Я написал SQL-запрос, в котором он вычисляет квартили продаж за последние три месяца для всех торговых представителей и фиксируется во временной таблице в хранимой процедуре следующим образом:
Квартильное значение всех торговых представителей за последние три месяца:
Date 25th% 50th% 75th% 100th%
10/2020 88.89 90.00 95.00 100.00
11/2020 85.63 91.00 96.00 100.00
12/2020 70.00 80 .00 90.00 100.00
Теперь в моем другом CTE у меня есть фактические значения торгового представителя следующим образом:
SalesRepId Month salesvalue
101 10/2020 77
101 11/2020 90
101 12/2020 100
Когда я выполняю объединение как cte, так и временной таблицы, производительность запроса плохая, как лучше всего найти во временной таблице значение продаж и назначить квартиль моему торговому представителю?
В основном на 10/2020 значение продаж 77 меньше, чем 25-й квартиль, тогда торговый представитель должен получить 25-й квартиль, назначенный на октябрь.
Спасибо
Ответы 2
Это именно то, что percentile_disc() и percentile_cont(). К сожалению, это не функции агрегации, а один метод:
select distinct month,
percentile_disc(0.25) over (partition by month order by salesvalue) as value_25,
percentile_disc(0.50) over (partition by month order by salesvalue) as value_50,
percentile_disc(0.75) over (partition by month order by salesvalue) as value_75
from sales;
Если вы хотите рассчитать квартиль, самый простой способ ntile():
select s.*,
ntile(4) over (partition by month order by sales)
from sales s;
Вам не нужно рассчитывать перерывы. Единственное предостережение ntile() заключается в том, что плитки должны быть как можно ближе по размеру. Это означает, что галстуки могут быть в разных тайлах. Чтобы решить эту проблему, просто выполните расчет вручную:
select s.*,
ceiling(rank() over (partition by month order by sales) * 4.0 /
count(*) over (partition by month)
) as quartile
В настоящее время я присоединяюсь к обеим таблицам по дате и использую оператор case для сравнения значения, но запрос очень медленный, когда мне это нравится.
@КартикДути. . . Есть более прямые методы решения этой проблемы. Я исправил ответ.
Квартиль уже рассчитан и записан в табличную переменную. Теперь моя проблема заключается в сравнении значения со значениями в таблице квартилей, как я могу этого добиться?
Представьте, что у вас есть два отдельных cte, один со значениями квартиля, другой с фактическими значениями, как мне сравнить фактическое значение со значением квартиля и получить квартиль продажи?
Я обновил свой запрос, чтобы сохранить данные во временной таблице вместо CTE. Теперь, когда я присоединяюсь к временной таблице, это очень просто.
https://www.brentozar.com/archive/2019/06/whats-better-ctes-or-temp-tables/
Я использовал эти методы, чтобы получить значения для своих квартилей, теперь мне нужно сравнить отдельные значения с этим значением квартилей, чтобы решить, к какому квартилю они относятся.