Вычислить процент от общей суммы для указанного условия (SQL Server)
Я работаю над запросом, который должен возвращать набор данных, который будет использоваться в отчете. Предположим, у меня есть таблица dbo.payments с данными, как показано ниже:
Сценарий №1: я хочу написать запрос, который даст мне результаты, как показано ниже. Обратите внимание, что это процентное соотношение против SUM(Commission) за все время 240.00 независимо от предложения where.
Я новичок в SQL, и дальше всего я мог пойти :.
SELECT [TRADE DATE] AS DATE, CUSTOMER, SUM([COMMISSION]) AS TOTAL, ([COMMISSION] / (SELECT SUM([COMMISSION]) FROM DBO.PAYMENTS)) AS ‘%COMMISSION’ FROM DBO.PAYMENTS GROUP BY [TRADE DATE], CUSTOMER, COMMISSION
Как добиться желаемого результата независимо от предложения WHERE: т.е. WHERE [Trade Date] BETWEEN ’21/01/2018′ AND ’24/01/2018′ , что уменьшает общее количество до 170.00
SQL
Функции (аналитические)
Вы используете аналитические функции для определения значений, основанных на группах значений. Например, вы можете использовать этот тип функции для определения текущих итогов, процентов или верхнего результата в группе.
Синтаксис
- FIRST_VALUE (скалярное выражение) OVER ([partition_by_clause] order_by_clause [rows_range_clause])
- LAST_VALUE (скалярное выражение) OVER ([partition_by_clause] order_by_clause [rows_range_clause])
- LAG (scalar_expression [, offset] [, default]) OVER ([partition_by_clause] order_by_clause)
- LEAD (scalar_expression [, offset], [default]) OVER ([partition_by_clause] order_by_clause)
- PERCENT_RANK () OVER ([partition_by_clause] order_by_clause)
- CUME_DIST () OVER ([partition_by_clause] order_by_clause)
- PERCENTILE_DISC (numeric_literal) WITHIN GROUP (ORDER BY order_by_expression [ASC | DESC]) OVER ([<partition_by_clause>])
- PERCENTILE_CONT (numeric_literal) WITHIN GROUP (ORDER BY order_by_expression [ASC | DESC]) OVER ([<partition_by_clause>])
FIRST_VALUE
Вы используете функцию FIRST_VALUE для определения первого значения в упорядоченном наборе результатов, который вы определяете с помощью скалярного выражения.
В этом примере функция FIRST_VALUE используется для возврата ID штата или провинции с самой низкой ставкой налога. Предложение OVER используется для заказа ставок налога для получения самой низкой ставки.
| StateProvinceID | название | Ставка налога | FirstValue |
|---|---|---|---|
| 74 | Налог с продаж штата Юта | 5,00 | 74 |
| 36 | Государственный налог с продаж в Миннесоте | 6,75 | 74 |
| 30 | Государственный налог с продаж в штате Массачусетс | 7,00 | 74 |
| 1 | Канадский GST | 7,00 | 74 |
| 57 | Канадский GST | 7,00 | 74 |
| 63 | Канадский GST | 7,00 | 74 |
LAST_VALUE
Функция LAST_VALUE предоставляет последнее значение в упорядоченном наборе результатов, который вы определяете с помощью скалярного выражения.
В этом примере функция LAST_VALUE возвращает последнее значение для каждого набора строк в упорядоченных значениях.
| TerritoryID | Дата начала | BusinessEntityID | LastValue |
|---|---|---|---|
| 1 | 2005-07-01 00.00.00.000 | 280 | 283 |
| 1 | 2006-11-01 00.00.00.000 | 284 | 283 |
| 1 | 2005-07-01 00.00.00.000 | 283 | 283 |
| 2 | 2007-01-01 00.00.00.000 | 277 | 275 |
| 2 | 2005-07-01 00.00.00.000 | 275 | 275 |
| 3 | 2007-01-01 00.00.00.000 | 275 | 277 |
LAG и LEAD
Функция LAG предоставляет данные о строках перед текущей строкой в том же наборе результатов. Например, в SELECT вы можете сравнивать значения в текущей строке со значениями в предыдущей строке.
Вы используете скалярное выражение для указания значений, которые следует сравнивать. Параметр offset — это количество строк перед текущей строкой, которые будут использоваться при сравнении. Если вы не укажете количество строк, используется значение по умолчанию для одной строки.
Параметр по умолчанию указывает значение, которое должно быть возвращено, когда выражение со смещением имеет значение NULL . Если вы не укажете значение, возвращается значение NULL .
Функция LEAD предоставляет данные о строках после текущей строки в наборе строк. Например, в SELECT вы можете сравнивать значения в текущей строке со значениями в следующей строке.
Вы указываете значения, которые следует сравнивать с помощью скалярного выражения. Параметр offset — это количество строк после текущей строки, которая будет использоваться при сравнении.
Вы указываете значение, которое должно быть возвращено, когда выражение со смещением имеет значение NULL используя параметр по умолчанию. Если вы не укажете эти параметры, используется значение по умолчанию для одной строки и возвращается значение NULL .
В этом примере используются функции LEAD и LAG для сравнения значений продаж для каждого сотрудника на сегодняшний день с показателями сотрудников, перечисленных выше и ниже, с записями, упорядоченными на основе столбца BusinessEntityID.
| BusinessEntityID | SalesYTD | Значение свинца | Значение запаса |
|---|---|---|---|
| 274 | 559697.5639 | 3763178.1787 | 0,0000 |
| 275 | 3763178.1787 | 4251368.5497 | 559697.5639 |
| 276 | 4251368.5497 | 3189418.3662 | 3763178.1787 |
| 277 | 3189418.3662 | 1453719.4653 | 4251368.5497 |
| 278 | 1453719.4653 | 2315185.6110 | 3189418.3662 |
| 279 | 2315185.6110 | 1352577.1325 | 1453719.4653 |
PERCENT_RANK и CUME_DIST
Функция PERCENT_RANK вычисляет ранжирование строки относительно набора строк. Процент основан на количестве строк в группе, которые имеют меньшее значение, чем текущая строка.
Первое значение в наборе результатов всегда имеет процентный ранг нуля. Значение для наивысшего ранжированного или последнего значения в наборе всегда одно.
Функция CUME_DIST вычисляет относительное положение заданного значения в группе значений, определяя процент значений, меньших или равных этому значению. Это называется кумулятивным распределением.
В этом примере вы используете предложение ORDER для разделения или группировки — строки, полученные SELECT на основе названий рабочих мест сотрудников, при этом результаты в каждой группе сортируются в зависимости от количества часов отпуска по болезни, которые использовались сотрудниками.
| BusinessEntityID | Должность | SickLeaveHours | Процент Ранга | Кумулятивное распределение |
|---|---|---|---|---|
| 267 | Специалист по применению | 57 | 0 | 0,25 |
| 268 | Специалист по применению | 56 | +0,333333333333333 | 0,75 |
| 269 | Специалист по применению | 56 | +0,333333333333333 | 0,75 |
| 272 | Специалист по применению | 55 | 1 | 1 |
| 262 | Помощник финансового директора Cheif | 48 | 0 | 1 |
| 239 | Специалист по преимуществам | 45 | 0 | 1 |
| 252 | Покупатель | 50 | 0 | +0,111111111111111 |
| 251 | Покупатель | 49 | 0,125 | +0,333333333333333 |
| 256 | Покупатель | 49 | 0,125 | +0,333333333333333 |
| 253 | Покупатель | 48 | 0,375 | +0,555555555555555 |
| 254 | Покупатель | 48 | 0,375 | +0,555555555555555 |
Функция PERCENT_RANK оценивает записи внутри каждой группы. Для каждой записи возвращается процент записей в той же группе, которые имеют более низкие значения.
Функция CUME_DIST аналогична, за исключением того, что она возвращает процент значений, меньших или равных текущему значению.
PERCENTILE_DISC и PERCENTILE_CONT
Функция PERCENTILE_DISC отображает значение первой записи, где кумулятивное распределение выше, чем процентиль, который вы предоставляете, используя параметр numeric_literal .
Значения сгруппированы по набору строк или разделов, как указано в предложении WITHIN GROUP .
Функция PERCENTILE_CONT аналогична функции PERCENTILE_DISC , но возвращает среднее значение суммы первой соответствующей записи и следующей записи.
Чтобы найти точное значение из строки, которая соответствует или превышает 0,5 процентиля, вы передаете процентиль в виде числового литерала в функции PERCENTILE_DISC . Столбец Percentile Discreet в результирующем наборе перечисляет значение строки, в которой кумулятивное распределение выше указанного процентиля.
| BusinessEntityID | Должность | SickLeaveHours | Кумулятивное распределение | Percentile Discreet |
|---|---|---|---|---|
| 272 | Специалист по применению | 55 | 0,25 | 56 |
| 268 | Специалист по применению | 56 | 0,75 | 56 |
| 269 | Специалист по применению | 56 | 0,75 | 56 |
| 267 | Специалист по применению | 57 | 1 | 56 |
Чтобы основывать вычисления на наборе значений, вы используете функцию PERCENTILE_CONT . Столбец «Percentile Continuous» в результатах отображает среднее значение суммы значения результата и следующего максимального значения соответствия.