Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 SQL запрос. Точка с макс. кол-вом продаж.  [new]
LolBee
Member

Откуда: Киев
Сообщений: 45
Драсьте уважаемые))

Вынужден обратиться за помощью/советом, тк у самого мозгов не хватает к сожалению(
Есть 2 таблицы:

dbo.FCT_Test - где хранятся данные продаж по точкам:
point_id sales
1 0.50000
1 7.00000
2 4.82000
2 0.85000
2 1.20000
3 9.66000
4 10.80000
5 15.00000
5 0.20000
5 0.36000
6 0.40000
6 5.80000
7 10.00000
8 8.20000
8 1.25000
8 7.00000
3 8.50000
3 7.00000
3 4.82000

dbo.POS_Test - Справочник где точком присвоены данные расположения(Область, Район, Город)
point_id Region Area City
1 Киевская Броварской Бровары
2 Киевская Броварской Бровары
3 Киевская Броварской Бровары
4 Киевская Броварской Бровары
5 Киевская Броварской Бровары
6 Киевская Броварской Заворичи
7 Киевская Броварской Заворичи
8 Киевская Броварской Заворичи

Нужно написать запрос который выводил для каждого города точку у которой максимальное кол-во продаж!

Мой запрос:

SELECT		POS.Region,
			POS.Area,
			POS.City,
			MAX(FCT.sales) ss
FROM		(
			SELECT	point_id,
					SUM(sales) sales
			FROM	dbo.FCT_Test
			GROUP BY point_id
) FCT
			JOIN dbo.POS_Test POS ON FCT.point_id = POS.point_id
GROUP BY	POS.Region,
			POS.Area,
			POS.City


Выводит:

Region Area City ss
Киевская Броварской Бровары 29.98000
Киевская Броварской Заворичи 16.45000

Но самого главного point_id тут нет!
Как написать так чтоб выводилась id точки с макс значением?

Спасибо заранее!
Извиняюсь за такой вид данных..не нашел где тут форматирование для таблиц..(
23 ноя 15, 13:02    [18459104]     Ответить | Цитировать Сообщить модератору
 Re: SQL запрос. Точка с макс. кол-вом продаж.  [new]
Добрый Э - Эх
Guest
почитать про TOP-n запросы?
23 ноя 15, 13:14    [18459204]     Ответить | Цитировать Сообщить модератору
 Re: SQL запрос. Точка с макс. кол-вом продаж.  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8834
LolBee,
решение зависит от версии сервера. Если 2012+, то можно использовать over() для нумерации и группировки, если меньше, то поиск по ключу POS.Region, POS.Area, POS.City, FCT.sales для значений, попавших в результат отбора.
23 ноя 15, 13:18    [18459238]     Ответить | Цитировать Сообщить модератору
 Re: SQL запрос. Точка с макс. кол-вом продаж.  [new]
LolBee
Member

Откуда: Киев
Сообщений: 45
Добрый Э - Эх,

Допустим, но я не вижу здесь применения TOP-n..
Как вы видите есть как минимум 2 города, в каждом несколько точек..
Как я понимаю такой вариант выдаст мне просто первую строку сортировки по сумме продаж..А надо максимум для КАЖДОГО города.

Или я не так понимаю?
23 ноя 15, 13:22    [18459259]     Ответить | Цитировать Сообщить модератору
 Re: SQL запрос. Точка с макс. кол-вом продаж.  [new]
LolBee
Member

Откуда: Киев
Сообщений: 45
Владислав Колосов,
Версия 2008R2, вариант с ключем по POS.Region, POS.Area, POS.City, FCT.sales рассматривал, но теоретически может быть такое что для одного города будет несколько точек с одинаковы кол-вом продаж. Редкость, но теоретически возможно.
23 ноя 15, 13:25    [18459280]     Ответить | Цитировать Сообщить модератору
 Re: SQL запрос. Точка с макс. кол-вом продаж.  [new]
_djХомяГ
Guest
LolBee
Добрый Э - Эх,

Допустим, но я не вижу здесь применения TOP-n..
Как вы видите есть как минимум 2 города, в каждом несколько точек..
Как я понимаю такой вариант выдаст мне просто первую строку сортировки по сумме продаж..А надо максимум для КАЖДОГО города.

Или я не так понимаю?

а в TOP 1 WITH TIES видите?
23 ноя 15, 13:28    [18459302]     Ответить | Цитировать Сообщить модератору
 Re: SQL запрос. Точка с макс. кол-вом продаж.  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8834
LolBee,

так если у Вас несколько точек с одинаковой ценой, то какую точку выберите?
23 ноя 15, 13:53    [18459460]     Ответить | Цитировать Сообщить модератору
 Re: SQL запрос. Точка с макс. кол-вом продаж.  [new]
LolBee
Member

Откуда: Киев
Сообщений: 45
Владислав Колосов,

тогда все эти точки
по принципу TOP WITH TIES
23 ноя 15, 14:33    [18459732]     Ответить | Цитировать Сообщить модератору
 Re: SQL запрос. Точка с макс. кол-вом продаж.  [new]
LolBee
Member

Откуда: Киев
Сообщений: 45
_djХомяГ,

не совсем.. в таком варианте как мне надо не понимаю(
23 ноя 15, 14:35    [18459743]     Ответить | Цитировать Сообщить модератору
 Re: SQL запрос. Точка с макс. кол-вом продаж.  [new]
гр к
Guest
LolBee, ч-з оконку как-то так - на 100% не уверен, но смысл понятен наверное
with fct as 
(
SELECT	point_id,
SUM(sales) sales
FROM	dbo.FCT_Test
GROUP BY point_id
)
, 
c as
(
select fct.*,
max(sales) qq over (partition by POS.City)   
from fct JOIN dbo.POS_Test POS ON FCT.point_id = POS.point_id
)
select c.* from c
where sales = qq 
23 ноя 15, 14:58    [18459903]     Ответить | Цитировать Сообщить модератору
 Re: SQL запрос. Точка с макс. кол-вом продаж.  [new]
_djХомяГ
Guest
упрощенно
declare @t table (Name varchar(100),qty int)
insert into @t 
select '1',10
union all
select '1',20
union all
select '2',999
union all
select '2',1
union all
select '2',2

select tOP 1 with ties * from @t 
order by ROW_NUMBER() over (partition  by Name order by qty desc)
23 ноя 15, 15:00    [18459927]     Ответить | Цитировать Сообщить модератору
 Re: SQL запрос. Точка с макс. кол-вом продаж.  [new]
Gena928
Member

Откуда: Москва
Сообщений: 1139
asdf

-- Данные по продажам
declare @fct_test table (
	row_id int identity(1,1) primary key,
	point_id int,
	sales decimal(18,10))

-- Координаты точек
declare @pos_test table (
	point_id int primary key,
	region nvarchar(50),
	area nvarchar(50),
	city nvarchar(50))



-- Исходники....
insert into @fct_test (point_id, sales) values (1, 0.50000)
insert into @fct_test (point_id, sales) values (1, 7.00000)
insert into @fct_test (point_id, sales) values (2, 4.82000)
insert into @fct_test (point_id, sales) values (2, 0.85000)
insert into @fct_test (point_id, sales) values (2, 1.20000)
insert into @fct_test (point_id, sales) values (3, 9.66000)
insert into @fct_test (point_id, sales) values (4, 0.80000)
insert into @fct_test (point_id, sales) values (5, 5.00000)
insert into @fct_test (point_id, sales) values (5, 0.20000)
insert into @fct_test (point_id, sales) values (5, 0.36000)
insert into @fct_test (point_id, sales) values (6, 0.40000)
insert into @fct_test (point_id, sales) values (6, 5.80000)
insert into @fct_test (point_id, sales) values (7, 0.00000)
insert into @fct_test (point_id, sales) values (8, 8.20000)
insert into @fct_test (point_id, sales) values (8, 1.25000)
insert into @fct_test (point_id, sales) values (8, 7.00000)
insert into @fct_test (point_id, sales) values (3, 8.50000)
insert into @fct_test (point_id, sales) values (3, 7.00000)
insert into @fct_test (point_id, sales) values (3, 4.82000)


insert into @pos_test (point_id, region, area, city) values (1, N'Киевская', N'Броварской', N'Бровары')
insert into @pos_test (point_id, region, area, city) values (2, N'Киевская', N'Броварской', N'Бровары')
insert into @pos_test (point_id, region, area, city) values (3, N'Киевская', N'Броварской', N'Бровары')
insert into @pos_test (point_id, region, area, city) values (4, N'Киевская', N'Броварской', N'Бровары')
insert into @pos_test (point_id, region, area, city) values (5, N'Киевская', N'Броварской', N'Бровары')
insert into @pos_test (point_id, region, area, city) values (6, N'Киевская', N'Броварской', N'Заворичи')
insert into @pos_test (point_id, region, area, city) values (7, N'Киевская', N'Броварской', N'Заворичи')
insert into @pos_test (point_id, region, area, city) values (8, N'Киевская', N'Броварской', N'Заворичи')




;with tbl_main as (
	-- Соединяем обе таблицы и ранжируем
	select tbl_sales.row_id,
			tbl_sales.point_id,
			tbl_sales.sales,
			rank() over (partition by tbl_locations.region, tbl_locations.area, tbl_locations.city order by sales desc) as my_row_number,
			tbl_locations.region,
			tbl_locations.area,
			tbl_locations.city
		
	from @fct_test as tbl_sales inner join @pos_test as tbl_locations 
	on tbl_sales.point_id = tbl_locations.point_id
)

-- финальная выборка
select *
from tbl_main
where (my_row_number = 1);
23 ноя 15, 15:02    [18459943]     Ответить | Цитировать Сообщить модератору
 Re: SQL запрос. Точка с макс. кол-вом продаж.  [new]
LolBee
Member

Откуда: Киев
Сообщений: 45
Последний вариант вообще шикарный!
Спасибо огромное!
23 ноя 15, 15:44    [18460266]     Ответить | Цитировать Сообщить модератору
 Re: SQL запрос. Точка с макс. кол-вом продаж.  [new]
Макбет
Member

Откуда:
Сообщений: 72
LolBee,

попробуй
select Region, Area, City
FROM
(SELECT		POS.Region,
			POS.Area,
			POS.City,
			rank() over(partition by POS.City order by FCT.sales) rnk
FROM		(
			SELECT	point_id,
					SUM(sales) sales
			FROM	dbo.FCT_Test
			GROUP BY point_id
) FCT
			JOIN dbo.POS_Test POS ON FCT.point_id = POS.point_id) Q
			WHERE rnk=1

почему RANK - если есть точки продаж по городу с одинаковой суммой, то будут выведены все
если такая ситуация исключается - иcпользуем ROW_NUMBER
23 ноя 15, 16:02    [18460401]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить