Блог

Главная - Вопросы по программированию - SQL-запрос для сравнения показателей торгового представителя со средним значением по квартилю, полученным в другой таблице

SQL-запрос для сравнения показателей торгового представителя со средним значением по квартилю, полученным в другой таблице

#sql #sql-server

#sql #sql-server

Вопрос:

Я написал 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, так и временной таблицы, производительность запроса низкая, каков наилучший способ поиска значения продаж во временной таблице и присвоения квартиля моему salesrepid?

В основном для 10/2020 значение salesvalue 77 меньше 25-го квартиля, тогда salesrep должен получить 25-й квартиль, назначенный на октябрь месяц.

Спасибо

Ответ №1:

Это именно то, что 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

Комментарии:

1. Я использовал эти методы, чтобы вывести значения для моих квартилей, теперь мне нужно сравнить отдельные значения со значением этого квартиля, чтобы решить, к какому квартилю они относятся

2. В настоящее время я объединяю обе таблицы по дате и использую оператор case для сравнения значения, но запрос выполняется очень медленно, когда мне это нравится

3. @KarthikDooty . , , Есть более прямые методы решения этой проблемы. Я внес изменения в ответ.

4. Квартиль уже вычислен и записан в табличной переменной. Теперь моя проблема заключается в сравнении значения со значениями в таблице квартилей, как я могу этого добиться?

5. Представьте, что у вас есть два отдельных cte, один со значениями квартиля, другой с фактическими значениями, как мне сравнить фактическое значение со значением квартиля и получить квартиль продажи?

Ответ №2:

Я обновил свой запрос, чтобы я мог сохранять данные во временной таблице вместо CTE. Теперь, когда я присоединяюсь к временной таблице, это легко.

https://www.brentozar.com/archive/2019/06/whats-better-ctes-or-temp-tables/