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

Откуда:
Сообщений: 101
Добрый день.
Есть таблица Stocks (ItemId (ключ) int, SectorNo (сектор) int, Level (уровень) int, Plase (место) int, InUse (занято/свободно) bit),в которой хранятся адреса ячеек склада и флаг, занята ячейка или нет.

ItemId/SectorNo/ Level/Plase /InUse
1 /1 /1 /1 /0
2 /1 /1 /2 /0
3 /1 /1 /3 /0
4 /1 /1 /4 /1
5 /1 /2 /1 /1
6 /1 /2 /2 /1
7 /1 /2 /3 /0
8 /1 /2 /4 /0
9 /1 /2 /5 /0
10 /1 /2 /6 /0


Не могу сообразить как написать такой запрос: надо выбрать, например, первые 3 находящиеся рядом свободные ячейки. Результат строки 1,2,3. Если, первые 4, то строки 7,8,9,10.

Поискала по поиску, не нашла… хотя может не правильно задала параметры поиска ….
15 июл 13, 10:39    [14565750]     Ответить | Цитировать Сообщить модератору
 Re: выбрать первые n свободные ячейки  [new]
StarikNavy
Member

Откуда: Москва
Сообщений: 2395
Rose_Tree,

https://www.sql.ru/forum/127456/rekomendacii-po-oformleniu-soobshheniy-v-forume
15 июл 13, 10:47    [14565802]     Ответить | Цитировать Сообщить модератору
 Re: выбрать первые n свободные ячейки  [new]
StarikNavy
Member

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

select top 3
15 июл 13, 10:48    [14565809]     Ответить | Цитировать Сообщить модератору
 Re: выбрать первые n свободные ячейки  [new]
iap
Member

Откуда: Москва
Сообщений: 47052
CREATE TABLE Stocks(ItemId int, SectorNo int, Level int, Plase int, InUse bit);
INSERT Stocks(ItemId,SectorNo,Level,Plase,InUse) VALUES
 (1 ,1 ,1 ,1 ,0)
,(2 ,1 ,1 ,2 ,0)
,(3 ,1 ,1 ,3 ,0)
,(4 ,1 ,1 ,4 ,1)
,(5 ,1 ,2 ,1 ,1)
,(6 ,1 ,2 ,2 ,1)
,(7 ,1 ,2 ,3 ,0)
,(8 ,1 ,2 ,4 ,0)
,(9 ,1 ,2 ,5 ,0)
,(10 ,1 ,2 ,6 ,0);

SELECT TOP 1 WITH TIES * FROM
(
 SELECT *,C=COUNT(*)OVER(PARTITION BY N,InUse)
 FROM(SELECT N=ROW_NUMBER()OVER(ORDER BY ItemId)-ROW_NUMBER()OVER(PARTITION BY InUse ORDER BY ItemId),* FROM Stocks)T
)T
WHERE InUse=0 AND C=3
ORDER BY N;
15 июл 13, 10:55    [14565842]     Ответить | Цитировать Сообщить модератору
 Re: выбрать первые n свободные ячейки  [new]
wdwdwdwd
Guest
Rose_Tree,

with d(ItemId, SectorNo, [Level], Place, InUse) as
(
	select 1, 1, 1, 1, 0 union all
	select 2, 1, 1, 2, 0 union all
	select 3, 1, 1, 3, 0 union all
	select 4, 1, 1, 4, 1 union all
	select 5, 1, 2, 1, 1 union all
	select 6, 1, 2, 2, 1 union all
	select 7, 1, 2, 3, 0 union all
	select 8, 1, 2, 4, 0 union all
	select 9, 1, 2, 5, 0 union all
	select 10, 1, 2, 6, 0
)
select *
from
(
	select *, count(case when InUse = 0 then 1 else null end) over (partition by SectorNo, [Level], g) as free_cnt
	from
	(
		select *, (row_number() over (partition by SectorNo, [Level] order by Place) - 
			row_number() over (partition by SectorNo, [Level], InUse order by Place)) as g
		from d
	) z
) q
where q.free_cnt = 3 --4
15 июл 13, 11:01    [14565896]     Ответить | Цитировать Сообщить модератору
 Re: выбрать первые n свободные ячейки  [new]
qwfeqwef
Guest
wdwdwdwd,

with d(ItemId, SectorNo, [Level], Place, InUse) as
(
	select 1, 1, 1, 1, 0 union all
	select 2, 1, 1, 2, 0 union all
	select 3, 1, 1, 3, 0 union all
	select 4, 1, 1, 4, 1 union all
	select 5, 1, 2, 1, 1 union all
	select 6, 1, 2, 2, 1 union all
	select 7, 1, 2, 3, 0 union all
	select 8, 1, 2, 4, 0 union all
	select 9, 1, 2, 5, 0 union all
	select 10, 1, 2, 6, 0
)
select stuff(
				(select top(4 /* тут параметр */) ',' + cast(ItemId as varchar)
				from
				(
					select *, count(case when InUse = 0 then 1 else null end) over (partition by SectorNo, [Level], g) as free_cnt
					from
					(
						select *, (row_number() over (partition by SectorNo, [Level] order by Place) - 
							row_number() over (partition by SectorNo, [Level], InUse order by Place)) as g
						from d
					) z
				) q
				where q.free_cnt >=4 /* тут параметр */
				order by g, SectorNo, [Level], Place
				for xml path('')), 1, 1, ''
			)

пофиксено под задачу
15 июл 13, 11:15    [14566053]     Ответить | Цитировать Сообщить модератору
 Re: выбрать первые n свободные ячейки  [new]
Rose_Tree
Member

Откуда:
Сообщений: 101
iap , qwfeqwef, wdwdwdwd
СПАСИБО ВАМ БОЛЬШОЕ ЗА ПОМОЩЬ!!!
15 июл 13, 11:19    [14566102]     Ответить | Цитировать Сообщить модератору
 Re: выбрать первые n свободные ячейки  [new]
iap
Member

Откуда: Москва
Сообщений: 47052
Rose_Tree
Добрый день.
Есть таблица Stocks (ItemId (ключ) int, SectorNo (сектор) int, Level (уровень) int, Plase (место) int, InUse (занято/свободно) bit),в которой хранятся адреса ячеек склада и флаг, занята ячейка или нет.

ItemId/SectorNo/ Level/Plase /InUse
1 /1 /1 /1 /0
2 /1 /1 /2 /0
3 /1 /1 /3 /0
4 /1 /1 /4 /1
5 /1 /2 /1 /1
6 /1 /2 /2 /1
7 /1 /2 /3 /0
8 /1 /2 /4 /0
9 /1 /2 /5 /0
10 /1 /2 /6 /0


Не могу сообразить как написать такой запрос: надо выбрать, например, первые 3 находящиеся рядом свободные ячейки. Результат строки 1,2,3. Если, первые 4, то строки 7,8,9,10.

Поискала по поиску, не нашла… хотя может не правильно задала параметры поиска ….
А для таких данных что должно получиться?
ItemIdSectorNo LevelPlase InUse
1 1 1 1 0
2 1 1 2 0
3 1 1 3 0
4 1 1 4 0
5 1 2 1 1
6 1 2 2 1
7 1 2 3 1
8 1 2 4 0
9 1 2 5 0
10 1 2 6 0
15 июл 13, 11:25    [14566159]     Ответить | Цитировать Сообщить модератору
 Re: выбрать первые n свободные ячейки  [new]
Rose_Tree
Member

Откуда:
Сообщений: 101
iap, для n=3 подойдет как 1,2,3, так и 2,3,4 и 8,9,10 Тут пока строгого ограничения нет, нужны пустые рядом находящиеся ячейки. Хотя я думаю потом встанет задача по оптимизации данного вопроса, но как поставят задачу я не знаю.
15 июл 13, 11:40    [14566297]     Ответить | Цитировать Сообщить модератору
 Re: выбрать первые n свободные ячейки  [new]
qiqiqiqi
Guest
у меня вот тоже вопрос, ячейки подряд идущие в таблице при сортировке скажем по SectorNo, [Level], Place считаются подряд идущими если у них разный Levevl или SectorNo... исходил из того, что нет.
15 июл 13, 11:42    [14566324]     Ответить | Цитировать Сообщить модератору
 Re: выбрать первые n свободные ячейки  [new]
йцауйцайцуа
Guest
Rose_Tree,

тот запрос что выше писал, выдает первые подряд идущие N (параметр), причем если есть группа именно такого размера, то отдается она, а не часть группы большего размера. чтобы оптимальнее распределять места. хотя это от логики зависит, может для вашего случая это и не оптимально. хз.
15 июл 13, 11:45    [14566353]     Ответить | Цитировать Сообщить модератору
 Re: выбрать первые n свободные ячейки  [new]
Rose_Tree
Member

Откуда:
Сообщений: 101
йцауйцайцуа, я это понимаю.
15 июл 13, 11:57    [14566470]     Ответить | Цитировать Сообщить модератору
 Re: выбрать первые n свободные ячейки  [new]
iap
Member

Откуда: Москва
Сообщений: 47052
Rose_Tree
iap, для n=3 подойдет как 1,2,3, так и 2,3,4 и 8,9,10 Тут пока строгого ограничения нет, нужны пустые рядом находящиеся ячейки. Хотя я думаю потом встанет задача по оптимизации данного вопроса, но как поставят задачу я не знаю.
Тогда мой ответ неверен. Ибо выдаёт группу, в которой ровно заданное количество строк.
Ни больше и не меньше.
Если "на коленке" поправить, то:
USE tempdb;
IF OBJECT_ID(N'Stocks',U) IS NOT NULL DROP TABLE Stocks;
CREATE TABLE Stocks(ItemId int, SectorNo int, Level int, Plase int, InUse bit);
INSERT Stocks(ItemId,SectorNo,Level,Plase,InUse) VALUES
 (1 ,1 ,1 ,1 ,0)
,(2 ,1 ,1 ,2 ,0)
,(3 ,1 ,1 ,3 ,0)
,(4 ,1 ,1 ,4 ,0)
,(5 ,1 ,2 ,1 ,1)
,(6 ,1 ,2 ,2 ,1)
,(7 ,1 ,2 ,3 ,1)
,(8 ,1 ,2 ,4 ,0)
,(9 ,1 ,2 ,5 ,0)
,(10 ,1 ,2 ,6 ,0);

SELECT TOP(3) * FROM
(
 SELECT TOP(1) WITH TIES * FROM
 (
  SELECT *,C=COUNT(*)OVER(PARTITION BY N,InUse)
  FROM(SELECT N=ROW_NUMBER()OVER(ORDER BY ItemId)-ROW_NUMBER()OVER(PARTITION BY InUse ORDER BY ItemId),* FROM Stocks)T
 )T
 WHERE InUse=0 AND C>=3
 ORDER BY N
)T
ORDER BY ItemId;
15 июл 13, 11:58    [14566486]     Ответить | Цитировать Сообщить модератору
 Re: выбрать первые n свободные ячейки  [new]
SevDEV
Member

Откуда:
Сообщений: 2
iap
Если "на коленке" поправить, то:

не работает на следующих данных:
INSERT Stocks(ItemId,SectorNo,Level,Plase,InUse) VALUES
(1, 1, 1, 10, 0)
,(2, 1, 1, 12, 0)
,(3, 1, 1, 13, 0)


Своё вроде бы работающее решение "в лоб", но наверно можно сделать и попроще:
declare @size int

set @size = 4

; with t1 as (
select	s.ItemId,
		s.SectorNo,
		s.Level,
		s.Plase place,
		s.InUse,
		ROW_NUMBER() over (partition by s.sectorno, level order by plase) rn_place		
from Stocks s
where InUse = 0)
,t2 as (
select SectorNo, Level, MIN(place) place, COUNT(*) cnt
from t1
group by place - rn_place, sectorno, level
having COUNT(*) >= @size )
select top (@size) s.*
from t2
	inner join  Stocks s
		on t2.SectorNo = s.SectorNo
		 and t2.Level = s.Level
where s.Plase between t2.place and t2.place + @size - 1 
order by t2.SectorNo, t2.Level
15 июл 13, 17:20    [14569313]     Ответить | Цитировать Сообщить модератору
 Re: выбрать первые n свободные ячейки  [new]
iap
Member

Откуда: Москва
Сообщений: 47052
SevDEV
iap
Если "на коленке" поправить, то:

не работает на следующих данных:
INSERT Stocks(ItemId,SectorNo,Level,Plase,InUse) VALUES
(1, 1, 1, 10, 0)
,(2, 1, 1, 12, 0)
,(3, 1, 1, 13, 0)
А почему?
Попробовал - вроде всё как заказывали
15 июл 13, 17:25    [14569347]     Ответить | Цитировать Сообщить модератору
 Re: выбрать первые n свободные ячейки  [new]
iap
Member

Откуда: Москва
Сообщений: 47052
iap
SevDEV
пропущено...

не работает на следующих данных:
INSERT Stocks(ItemId,SectorNo,Level,Plase,InUse) VALUES
(1, 1, 1, 10, 0)
,(2, 1, 1, 12, 0)
,(3, 1, 1, 13, 0)

А почему?
Попробовал - вроде всё как заказывали
Или имеются в виду проигнорированные мной поля SectorNo,Level,Plase?
Я не вникал в их смысл. И вообще их не использовал.
Просто показал принцип, и всё.
15 июл 13, 17:28    [14569375]     Ответить | Цитировать Сообщить модератору
 Re: выбрать первые n свободные ячейки  [new]
SevDEV
Member

Откуда:
Сообщений: 2
iap
А почему?
Попробовал - вроде всё как заказывали


надо выбрать, например, первые 3 находящиеся рядом свободные ячейки.

Рядом стоящие - это скорее всего идущие подряд места (Place) в одном секторе на одном уровне.
15 июл 13, 17:42    [14569484]     Ответить | Цитировать Сообщить модератору
 Re: выбрать первые n свободные ячейки  [new]
wewergweg
Guest
SevDEV
iap
А почему?
Попробовал - вроде всё как заказывали


надо выбрать, например, первые 3 находящиеся рядом свободные ячейки.

Рядом стоящие - это скорее всего идущие подряд места (Place) в одном секторе на одном уровне.


А что, это не работает, как требуется по условию? 14566053

только заменить

order by g, SectorNo, [Level], Place

на

order by free_cnt, SectorNo, [Level], Place

опечатка была.
15 июл 13, 19:34    [14569951]     Ответить | Цитировать Сообщить модератору
 Re: выбрать первые n свободные ячейки  [new]
wergwergewrg
Guest
SevDEV
iap
Если "на коленке" поправить, то:

не работает на следующих данных:
INSERT Stocks(ItemId,SectorNo,Level,Plase,InUse) VALUES
(1, 1, 1, 10, 0)
,(2, 1, 1, 12, 0)
,(3, 1, 1, 13, 0)



типа дырки в нумерации учитывать?

пофиксено для такого набора :)

with d(ItemId, SectorNo, [Level], Place, InUse) as
(
	select 1, 1, 1, 1, 0 union all
	select 2, 1, 1, 2, 0 union all
	select 3, 1, 1, 3, 0 union all
	select 4, 1, 1, 4, 1 union all
	select 5, 1, 2, 1, 1 union all
	select 6, 1, 2, 2, 1 union all
	select 7, 1, 2, 3, 0 union all
	select 8, 1, 2, 4, 0 union all
	select 9, 1, 2, 5, 0 union all
	select 10, 1, 2, 6, 0
)

select stuff(
				(select top(3 /* тут параметр */) ',' + cast(ItemId as varchar)
				from
				(
					select *, count(case when InUse = 0 then 1 else null end) over (partition by SectorNo, [Level], g) as free_cnt
					from
					(
						select *, (row_number() over (partition by SectorNo, [Level] order by Place) - 
							row_number() over (partition by SectorNo, [Level], place_iseland, InUse order by Place)) as g
						from
						(
							select *, Place - row_number() over(partition by SectORNo, [Level] order by Place) as place_iseland
							from d
						) m
					) z
				) q
				where q.free_cnt >=3 /* тут параметр */
				order by free_cnt, SectorNo, [Level], Place
				for xml path('')), 1, 1, ''
			)
15 июл 13, 20:27    [14570105]     Ответить | Цитировать Сообщить модератору
 Re: выбрать первые n свободные ячейки  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
Поскольку речь идет о накопительном итоге - вот вариант с однократным чтением таблицы (предполагается, что пропусков в нумерации мест нет)
Одновременно желающие могут убедиться, что на представленных данных только один из предложенных ранее вариантов дает правильный результат, а именно: места идут подряд в одном секторе, на одном уровне, и все они свободны :)
declare @Stocks table (ItemId int, SectorNo int, [Level] int, Place int, InUse bit)
insert @Stocks values
	(1, 1, 1, 1, 1),(2, 1, 1, 2, 1),(3, 1, 1, 3, 0),(4, 1, 1, 4, 0),
	(5, 1, 2, 1, 0),(6, 1, 2, 2, 1),(7, 1, 2, 3, 0),(8, 1, 2, 4, 0),
	(9, 1, 2, 5, 0),(10,1, 2, 6, 0)

declare @SectorNo int, @Level int, @Place int, @InUse bit, @cnt int=0
declare @SectorNo2 int=0, @Level2 int=0, @Place2 int=0, @InUse2 bit=1

declare cStack cursor fast_forward for
select SectorNo, Level, Place, InUse from @Stocks order by SectorNo, Level, Place
open cStack
while 1=1 begin
   fetch next from cStack into @SectorNo, @Level, @Place, @InUse
if @@FETCH_STATUS!=0 break;
   if @SectorNo=@SectorNo2 and @Level=@Level2 and @InUse=0 /*and @Place=@Place2+1*/ begin
      select @cnt+=1 /*, @Place2=@Place */
      if @cnt>2 begin
         select * from @Stocks where SectorNo=@SectorNo and Level=@Level and Place in (@Place-2, @Place-1, @Place)
         break
      end
   end
   else
      select @cnt=0,@SectorNo2=@SectorNo, @Level2=@Level, @Place2=@Place
end
close cStack
deallocate cStack
15 июл 13, 22:14    [14570382]     Ответить | Цитировать Сообщить модератору
 Re: выбрать первые n свободные ячейки  [new]
qefqwefqwef
Guest
Cygapb-007
Одновременно желающие могут убедиться, что на представленных данных только один из предложенных ранее вариантов дает правильный результат, а именно: места идут подряд в одном секторе, на одном уровне, и все они свободны :)


Этот вариант считает правильно 14570105. Так же учитывает дырки в нумерации мест. От count избавиться не удалось. Сканирует этот запрос таблицу в один проход, правда из-за count появляется table spool.
15 июл 13, 23:02    [14570478]     Ответить | Цитировать Сообщить модератору
 Re: выбрать первые n свободные ячейки  [new]
qefqwefqwef
Guest
Избавился таки от count, запилив в один проход по таблице. В реале, индексы надо правильно создать, чтобы избавиться от сортировок дополнительно

with d(ItemId, SectorNo, [Level], Place, InUse) as
(
	select 1, 1, 1, 1, 0 union all
	select 2, 1, 1, 2, 0 union all
	select 3, 1, 1, 3, 0 union all
	select 4, 1, 1, 4, 1 union all
	select 5, 1, 2, 1, 1 union all
	select 6, 1, 2, 2, 1 union all
	select 7, 1, 2, 3, 0 union all
	select 8, 1, 2, 4, 0 union all
	select 9, 1, 2, 5, 0 union all
	select 10, 1, 2, 6, 0
)
select stuff(
				(select top(3 /* тут параметр */) ',' + cast(ItemId as varchar)
				from
				(
					select
						*,
						row_number() over(partition by SectorNo, [Level], g order by Place) +
							row_number() over(partition by SectorNo, [Level], g order by Place desc) - 1 as free_cnt

					from
					(
						select *, (row_number() over (partition by SectorNo, [Level] order by Place) - 
							row_number() over (partition by SectorNo, [Level], place_iseland, InUse order by Place)) as g
						from
						(
							select *, Place - row_number() over(partition by SectORNo, [Level] order by Place) as place_iseland
							from d
						) m
					) z
				) q
				where q.free_cnt >=3 /* тут параметр */
				order by free_cnt, SectorNo, [Level], Place
				for xml path('')), 1, 1, '')
15 июл 13, 23:32    [14570545]     Ответить | Цитировать Сообщить модератору
 Re: выбрать первые n свободные ячейки  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
qefqwefqwef, в последнем варианте (14570545) на данных
with d as
(select * from (values
   (1, 1, 1, 1, 1),
   (2, 1, 1, 2, 1),
   (3, 1, 1, 3, 0),
   (4, 1, 1, 4, 0),
   (5, 1, 2, 1, 0),
   (6, 1, 2, 2, 1),
   (7, 1, 2, 3, 0),
   (8, 1, 2, 4, 0),
   (9, 1, 2, 5, 0),
   (10,1, 2, 6, 0)
   )d(ItemId, SectorNo, [Level], Place, InUse))
возвращает 6,7,8, хотя для 6 - InUse=1
16 июл 13, 00:05    [14570595]     Ответить | Цитировать Сообщить модератору
 Re: выбрать первые n свободные ячейки  [new]
qefqwefqwef
Guest
но производительность унылое Г на 10 000 000 строк по сравнению с курсором, поскольку запрос сканирует все даже если уже давно встретил подходящие данные.

для практики курсор
для фетиша row_number
16 июл 13, 00:06    [14570597]     Ответить | Цитировать Сообщить модератору
 Re: выбрать первые n свободные ячейки  [new]
asdfasdfasd
Guest
Cygapb-007
qefqwefqwef, в последнем варианте (14570545) на данных
with d as
(select * from (values
   (1, 1, 1, 1, 1),
   (2, 1, 1, 2, 1),
   (3, 1, 1, 3, 0),
   (4, 1, 1, 4, 0),
   (5, 1, 2, 1, 0),
   (6, 1, 2, 2, 1),
   (7, 1, 2, 3, 0),
   (8, 1, 2, 4, 0),
   (9, 1, 2, 5, 0),
   (10,1, 2, 6, 0)
   )d(ItemId, SectorNo, [Level], Place, InUse))
возвращает 6,7,8, хотя для 6 - InUse=1


поправил

with d as
(select * from (values
   (1, 1, 1, 1, 1),
   (2, 1, 1, 2, 1),
   (3, 1, 1, 3, 0),
   (4, 1, 1, 4, 0),
   (5, 1, 2, 1, 0),
   (6, 1, 2, 2, 1),
   (7, 1, 2, 3, 0),
   (8, 1, 2, 4, 0),
   (9, 1, 2, 5, 0),
   (10,1, 2, 6, 0)
   )d(ItemId, SectorNo, [Level], Place, InUse))
					
select stuff(
(
	select top(3 /* тут параметр */) ',' + cast(ItemId as varchar)
	from
	(
		select
			*,
			row_number() over(partition by SectorNo, [Level], InUse, g order by Place) +
				row_number() over(partition by SectorNo, [Level], InUse, g order by Place desc) - 1 as free_cnt

		from
		(
			select *, (row_number() over (partition by SectorNo, [Level] order by Place) - 
				row_number() over (partition by SectorNo, [Level], place_iseland, InUse order by Place)) as g
			from
			(
				select *, Place - row_number() over(partition by SectORNo, [Level] order by Place) as place_iseland
				from d
			) m
		) z
	) q
	where q.free_cnt >=3 and InUse = 0 /* тут параметр */
	order by free_cnt, SectorNo, [Level], Place
	for xml path(''))
	
	, 1, 1, ''
)
16 июл 13, 00:38    [14570646]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить