#sql #sql-server #tsql
#sql #sql-сервер #tsql
Вопрос:
Нужна небольшая помощь:
ПРИ РЕДАКТИРОВАНИИ: я пытаюсь определить, кто мои лучшие 40% клиентов на основе продаж. Затем, удалив этих клиентов, дайте мне лучшие 30%. Затем удаление этих двух наборов данных, которые сейчас занимают 20% mytop и т. Д
Вот сценарий:. Допустим, у нас общий объем продаж составляет 1 000 000 долларов. Я хочу иметь систему ранжирования клиентов для ранжирования клиентов A, B, C и D, чтобы видеть, кто мои лучшие клиенты.
«A» клиенты получают 40% от общего дохода — 400 тыс. долларов «B» клиенты получают следующие 30% — 300 тыс. долларов «C» клиенты получают следующие 20% — 200 тыс. долларов «D» клиенты получают последние 10% от общего дохода — 100 тыс. долларов
Вот мой SQL:
declare @TotalTotal decimal(18,2)
declare @ATotalRankTotal decimal(18,2)
declare @BTotalRankTotal decimal(18,2)
declare @CTotalRankTotal decimal(18,2)
declare @DTotalRankTotal decimal(18,2)
SET @TotalTotal = 1000000
set @ATotalRankTotal = @TotalTotal * .40
set @BTotalRankTotal = @ATotalRankTotal (@TotalTotal * .30)
set @CTotalRankTotal = @BTotalRankTotal (@TotalTotal * .20)
set @DTotalRankTotal = @CTotalRankTotal (@TotalTotal * .10)
select
CustomerName
, sum(TotalSales)
, TotalCustomerRank = CASE
WHEN SUM(sum(TotalSales)) over (order by SUM(TotalSales) desc) <= @ATotalRankTotal then
'A'
WHEN SUM(sum(TotalSales)) over (order by SUM(TotalSales) desc) > @ATotalRankTotal and SUM(sum(TotalSales)) over (order by SUM(TotalSales) desc) <= @BTotalRankTotal then
'B'
WHEN SUM(sum(TotalSales)) over (order by SUM(TotalSales) desc) > @BTotalRankTotal and SUM(sum(TotalSales)) over (order by SUM(TotalSales) desc) <= @CTotalRankTotal then
'C'
else
'D'
end
from
Customers
GROUP BY
CustomerName
Проблема, с которой я столкнулся, заключается в том, что у меня может не быть клиента, объем продаж которого составил бы 400 тысяч долларов или больше, поэтому я никогда не узнаю, кто мои клиенты категории «А».
Есть идеи о том, как написать сценарий, чтобы определить, на каких клиентов приходится 40% моего дохода (мои клиенты «A»), какие клиенты занимают СЛЕДУЮЩИЕ 30% (мои клиенты «B»), какие клиенты занимают СЛЕДУЮЩИЕ 20% (мои клиенты «C») и какие клиенты занимают последние 10% (мои клиенты «D»)?
Комментарии:
1. Я не уверен, что одного образца набора данных достаточно, возможно, потребуется предоставить несколько сценариев. Я не думаю, что вы хорошо разбираетесь в том, что именно вы хотите. «на каких клиентов приходится 40% моего дохода» — это не совсем четкое описание.
2. Просто добавьте еще один столбец и отфильтруйте каждый случай, где SalesDept = «A» для 1-го случая или = «B» для 2-го случая …
3. Я отлично справляюсь с тем, что пытаюсь сделать, возможно, просто неправильно объяснил это. Кто входит в число моих лучших 40% клиентов на основе продаж. Затем удаляю тех клиентов, которые сейчас составляют мои лучшие 30%. Затем удаление этих двух наборов данных, которые сейчас составляют 20% лучших, и т.д.
4. Как сказал @GordonLinoff, образцы данных с соответствующим желаемым результатом для каждого из ваших сценариев были бы очень полезны.
5. Вы думали об использовании RANK()?
Ответ №1:
Это выполняется очень быстро на Northwind. Должно быть именно то, что вы хотите. Он фокусируется на 40% ПРОДАЖАХ, а не на 40% от числа клиентов.
DECLARE
@TotalTotal INT = 0;
DECLARE @st TABLE
(
CustomerID nchar(5),
CustTotal INT,
RunningTotal INT,
RunningPCT INT,
CustRank nchar(1)
);
DECLARE
@CustomerID nchar(5),
@CustTotal INT,
@RunningTotal INT = 0,
@RunningPCT INT = 0,
@CustRank nchar(1)= ' ';
DECLARE c CURSOR
LOCAL STATIC FORWARD_ONLY READ_ONLY
FOR
SELECT
oo.CustomerID
,SUM( od.[UnitPrice] * od.[Quantity] ) as CustTotal
FROM [Northwind].[dbo].[Order Details] as od
left join [Northwind].[dbo].[Orders] as oo
on od.OrderID = oo.OrderID
left join [Northwind].[dbo].[Products] as pd
on od.ProductID = pd.ProductID
Group by oo.CustomerID
order by SUM( od.[UnitPrice] * od.[Quantity] ) desc
;
-- get the Denominator for the Percent ranking
OPEN c;
FETCH NEXT FROM c INTO @CustomerID, @CustTotal;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @TotalTotal = @TotalTotal @CustTotal;
FETCH NEXT FROM c INTO @CustomerID, @CustTotal;
END
CLOSE c;
-- compute Rank for each customer
OPEN c;
FETCH NEXT FROM c INTO @CustomerID, @CustTotal;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @RunningTotal = @RunningTotal @CustTotal;
SET @RunningPCT = (@RunningTotal * 100 / @TotalTotal);
SET @CustRank = Case
When @RunningPCT <= 40 Then 'A'
When @RunningPCT <= 70 Then 'B'
When @RunningPCT <= 90 Then 'C'
Else 'D' End ;
INSERT @st(CustomerID, CustTotal, RunningTotal, RunningPCT, CustRank)
SELECT @CustomerID, @CustTotal, @RunningTotal, @RunningPCT, @CustRank;
FETCH NEXT FROM c INTO @CustomerID, @CustTotal;
END
CLOSE c;
DEALLOCATE c;
-- Show the results
Select @TotalTotal;
Select * From @st;
Комментарии:
1. Хотя я пытался избежать использования курсоров, это сделало свое дело. Спасибо!
Ответ №2:
Я придерживаюсь этого предложения:
Кто входит в число моих лучших 40% клиентов на основе продаж.
Другими словами, если у вас 10 клиентов, вам нужны 4 лучших, независимо от того, какой процент от общего дохода они составляют. Если это не то, что вы хотели спросить, то ваш вопрос все еще недостаточно ясен.
Вы можете получить это с помощью встроенной функции NTILE. Используя NTILE(10) OVER (ORDER BY Revenue)
для размещения ваших клиентов в сегментах по 10% от общего числа клиентов, вы можете получить A, B, C, D, проверив это значение в диапазонах.
Предположим, вы используете приведенную выше функцию NTILE для генерации столбца с именем MyNtile
, тогда вы могли бы сделать что-то вроде:
CASE WHEN MyNtile <= 4 THEN 'A'
WHEN MyNtile BETWEEN 5 AND 7 THEN 'B'
etc..