#sql #sql-server-2008 #count
#sql #sql-server-2008 #подсчет
Вопрос:
Мне нужно, чтобы вы помогли мне написать два запроса в SQL Server 2008, которые отображают следующую информацию на основе item master:
- С точки зрения бренда рассчитывайте на мастера клиента плюс клиента, который приобрел бренд
- Количество основных элементов клиента плюс клиент, который приобрел товар
Вот ссылка, которая показывает информацию о таблице и запрос, который я пробовал.
Нажмите здесь, чтобы просмотреть таблицу в SQL Fiddle
SELECT
brandname,
division,
route,
DivisionTotalCustomersCount = MAX(DivisionTotalCustomersCount),
RouteTotalCustomersCount = MAX(RouteTotalCustomersCount),
PurchasedCustomersCount = SUM(PurchasedCustomersCount)
FROM
(SELECT
i.brandname,
c.division,
c.route,
DivisionTotalCustomersCount =
(SELECT COUNT(distinct x.CustomerID)
FROM CustomerMaster x
WHERE x.division = c.division),
RouteTotalCustomersCount =
(SELECT COUNT(distinct x.CustomerID)
FROM CustomerMaster x
WHERE x.Route = c.route),
PurchasedCustomersCount = count(distinct C.CustomerID)
FROM CustomerMaster c
LEFT OUTER JOIN SalesData s on c.CustomerID = s.CustomerID
right outer join ItemMaster i on s.item = i.itemcode
GROUP BY i.brandname, c.division, c.route) A
GROUP BY
brandname, division, route
ORDER BY 1
Результат должен быть таким, как показано ниже
Комментарии:
1. Почему ваш пример не сработал?
2. У меня есть несколько клиентов, которые не приобрели ни одного элемента. В моем запросе количество клиентов в маршруте, а деление равно null, что неверно.
3. Итак, вы тогда хотите исключить бренд, у которого никто не приобрел бренд? В противном случае у вас будет null для этих полей, потому что они поступают из главной таблицы customer и будут там только в том случае, если клиент приобрел что-то у этого бренда?
4. Нет, отчет основан на бренде из главного элемента и количестве клиентов из главного клиента. Что я хочу видеть, так это список брендов и показать доступного клиента в моей базе данных на основе маршрута, подразделения, а также показать, сколько клиентов из главного списка формы покупают эти бренды.
5. кроме того, в моем отчете, если бренд не был приобретен ни одним клиентом, но все же я могу видеть, сколько потенциальных клиентов доступно.
Ответ №1:
Я думаю, вам нужно пересмотреть отчет и, возможно, разделить его на несколько отчетов.
Не имеет смысла иметь количество маршрутов, а также количество подразделений, если они подсчитывают данные на разных уровнях агрегирования. Итак, имейте отчет о количестве маршрутов и количестве разделений.
В любом случае, разделение и маршрут будут равны нулю для 100PLUS, потому что для этого бренда нет клиентов, что означает, что нет доступной информации о маршруте или разделе.
--Division Count
SELECT BrandName, Division, COUNT(CustomerMaster.CustomerID) [Customer Count]
FROM ItemMaster LEFT OUTER JOIN
SalesData ON ItemMaster.BrandName = SalesData.Brand LEFT OUTER JOIN
CustomerMaster ON SalesData.CustomerID = CustomerMaster.CustomerID
GROUP BY BrandName, Division
--Route Count
SELECT BrandName, Route, Division, COUNT(CustomerMaster.CustomerID) [Customer Count]
FROM ItemMaster LEFT OUTER JOIN
SalesData ON ItemMaster.BrandName = SalesData.Brand LEFT OUTER JOIN
CustomerMaster ON SalesData.CustomerID = CustomerMaster.CustomerID
GROUP BY BrandName, Route, Division
Комментарии:
1. клиент доступен в customer master, и мы можем их подсчитать, поскольку у клиента есть маршрут и подразделение, если бренд не был приобретен ни одним клиентом, мы можем показать ноль. Пожалуйста, проверьте
2. Тогда это именно то, что вы получаете со вторым запросом. Это показывает нулевое количество клиентов, потому что бренд равен нулю?
3. Проблема в том, что у меня нет количества доступных клиентов для каждого подразделения и маршрута
4. Каково определение доступного клиента?
5. Все клиенты в главном клиенте
Ответ №2:
Используя ваши данные sqlfiddle, имеется 25 записей о продажах и 18 отдельных записей о бренде / подразделении / маршруте / клиентах, и нет продаж, превышающих 100PLUS
select
B.BrandName
, V.Division
, coalesce(Brand_count,0) as Brand_count
, coalesce(Division_count,0) as Division_count
from (select distinct BrandName from ItemMaster) as B
cross join (select distinct Division from CustomerMaster) as V
left join (
select
Brand
, Division
, sum(cust_count) over (partition by Brand) as Brand_count
, sum(cust_count) over (partition by Division) as Division_count
from (
select
S.Brand
, C.Division
, count(distinct S.CustomerID) cust_count
from salesdata as S
inner join CustomerMaster as C
on S.CustomerID = C.CustomerID
inner join ItemMaster as I
on S.item = I.ItemCode
group by
S.Brand
, C.Division
) as S
) as D
on B.BrandName = D.Brand
and V.Division = D.Division
order by
B.BrandName
, V.Division
;
| BRANDNAME | DIVISION | BRAND_COUNT | DIVISION_COUNT |
|-----------|----------|-------------|----------------|
| 100PLUS | Dubai | 0 | 0 |
| 100PLUS | RAK | 0 | 0 |
| KITCO | Dubai | 9 | 11 |
| KITCO | RAK | 9 | 7 |
| Red Bull | Dubai | 9 | 11 |
| Red Bull | RAK | 9 | 7 |
Комментарии:
1. Мне нужно увидеть все бренды из item master, ваш результат покажет только товар, у которого есть продажи
2. Все, что вам нужно, это список всех брендов, затем используйте его в качестве вашего из «таблицы», а затем присоединитесь к запросу, который я предложил. В конечном итоге, конечно, вы получите нули, поэтому я оставляю вам использовать ISNULL или COALESCE для решения этих проблем.
3. мой друг, количество брендов неверно, оно отображается как null. вы должны подсчитать клиента, используя customer master и sales_count, чтобы отобразить как ноль
4. это моя проблема, ничем не отличается от моего результата и вашего, и моя первоначальная проблема остается прежней. Это то, что я хочу видеть ссылку
5. Ну, макет в этой электронной таблице содержит результаты, которые не могут быть получены из выборочных данных в sqlfiddle. например, нет ни одной продажи с участием бренда 100PLUS, поэтому я не знаю, как достичь ожидаемого результата, который содержит цифры, которые я просто не могу получить из предоставленных необработанных данных. но я взгляну еще раз, скоро вернусь.
Ответ №3:
Вся заслуга @kevriley
select
A.BrandName,
B.Division,
B.Route,
B.DivisionTotalCustomers,
B.RouteTotalCustomers,
isnull(C.PurchasedCustomersCount,0) as PurchasedCustomersCount
from
(
select distinct
BrandName, Route, Division
from dbo.ItemMaster
cross join dbo.CustomerMaster
) A
join
(
select distinct
Division,
Route,
DENSE_RANK() over (partition by Division order by c.CustomerID asc) DENSE_RANK() over (partition by Division order by c.CustomerID desc) - 1 as DivisionTotalCustomers ,
DENSE_RANK() over (partition by ROUTE order by c.CustomerID asc) DENSE_RANK() over (partition by ROUTE order by c.CustomerID desc) - 1 as RouteTotalCustomers
from CustomerMaster c
left join SalesData s on c.CustomerID = s.CustomerID
) B on B.Division = A.Division and B.Route = A.Route
left join
(
select
s.brand,
c.division,
c.route,
PurchasedCustomersCount = count(distinct C.CustomerID)
FROM CustomerMaster c
JOIN SalesData s on c.CustomerID = s.CustomerID
--join ItemMaster i on s.item = i.itemcode
GROUP by s.brand, c.division, c.route
) C on A.Brandname = C.Brand and C.Division = A.Division and C.Route = A.Route
Смотрите то же самое на SQL Fiddle
Ответ №4:
Select B.Brandname,B.Division,C AS DivisionTotalCustomerCount,
ISNULL(T.Count,0) AS PURCHASEDCUSTOMERSCOUNT
from
(
Select CM.Division,M.BrandName,COUNT(distinct CM.CustomerID) AS C
from dbo.CustomerMaster CM
CROSS JOIN ItemMaster M
GROUP BY CM.Division,M.BrandName
)B
LEFT JOIN
(Select Division,Brand,COUNT(Distinct C.CustomerID) As Count from CustomerMaster C
JOIN salesdata D
On C.CustomerID=D.CustomerID
where D.Brand='Red Bull'
GROUP BY Division,Brand
)T
ON B.Brandname=T.Brand
and B.Division=T.Division
Order by 1,2