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

Откуда:
Сообщений: 1214
 		Заранее прошу прощения за подготовительную простыню, это у меня сразу практическая задачка которую я хотел решить на досуге по одной занимательной карточной игре. Мне нужно было сэмулировать пару мильонов стартовых рук по 7 случайных карт и подсчитать кое-какую статистику.

Для гарантированной случайности я выбрал старый-добрый ORDER BY NewID() и тут оказалось, что он попросту не работает в коррелирующем подзапросе и кросс аплае.


Под спойлером временная таблица с данными:
+
CREATE TABLE #TheDeck (  TheCard varchar(40), CMC int)
INSERT INTO #TheDeck SELECT 	 'Arid Mesa'	,	0
INSERT INTO #TheDeck SELECT 	 'Arid Mesa'	,	0
INSERT INTO #TheDeck SELECT 	 'Arid Mesa'	,	0
INSERT INTO #TheDeck SELECT 	 'Arid Mesa'	,	0

INSERT INTO #TheDeck SELECT 	 	 'Bloodstained Mire'	,	0
INSERT INTO #TheDeck SELECT 	 	 'Bloodstained Mire'	,	0
INSERT INTO #TheDeck SELECT 	 	 'Bloodstained Mire'	,	0

INSERT INTO #TheDeck SELECT 	 'Inspiring Vantage'	,	0
INSERT INTO #TheDeck SELECT 	 	 'Inspiring Vantage'	,	0
INSERT INTO #TheDeck SELECT 	 	 'Inspiring Vantage'	,	0
INSERT INTO #TheDeck SELECT 	 	 'Inspiring Vantage'	,	0


INSERT INTO #TheDeck SELECT  	 'Mountain'	,	0
INSERT INTO #TheDeck SELECT  	 'Mountain'	,	0

INSERT INTO #TheDeck SELECT 	  'Sacred Foundry'	,	0
INSERT INTO #TheDeck SELECT 	  'Sacred Foundry'	,	0

INSERT INTO #TheDeck SELECT 	   'Wooded Foothills'	,	0
INSERT INTO #TheDeck SELECT 	   'Wooded Foothills'	,	0
INSERT INTO #TheDeck SELECT 	   'Wooded Foothills'	,	0
INSERT INTO #TheDeck SELECT 	   'Wooded Foothills'	,	0

INSERT INTO #TheDeck SELECT  	 'Eidolon of the Great Revel'	,	2
INSERT INTO #TheDeck SELECT  	 'Eidolon of the Great Revel'	,	2
INSERT INTO #TheDeck SELECT  	 'Eidolon of the Great Revel'	,	2
INSERT INTO #TheDeck SELECT  	 'Eidolon of the Great Revel'	,	2


INSERT INTO #TheDeck SELECT   'Goblin Guide'	,	1
INSERT INTO #TheDeck SELECT   'Goblin Guide'	,	1
INSERT INTO #TheDeck SELECT   'Goblin Guide'	,	1
INSERT INTO #TheDeck SELECT   'Goblin Guide'	,	1

INSERT INTO #TheDeck SELECT   'Grim Lavamancer'	,	1
INSERT INTO #TheDeck SELECT 	  'Monastery Swiftspear'	,	1
INSERT INTO #TheDeck SELECT 	  'Monastery Swiftspear'	,	1
INSERT INTO #TheDeck SELECT 	  'Monastery Swiftspear'	,	1
INSERT INTO #TheDeck SELECT 	  'Monastery Swiftspear'	,	1


INSERT INTO #TheDeck SELECT   'Boros Charm'	,	2
INSERT INTO #TheDeck SELECT   'Boros Charm'	,	2
INSERT INTO #TheDeck SELECT   'Boros Charm'	,	2
INSERT INTO #TheDeck SELECT   'Boros Charm'	,	2

INSERT INTO #TheDeck SELECT 	  'Lava Spike'	,	1
INSERT INTO #TheDeck SELECT 	  'Lava Spike'	,	1
INSERT INTO #TheDeck SELECT 	  'Lava Spike'	,	1
INSERT INTO #TheDeck SELECT 	  'Lava Spike'	,	1

INSERT INTO #TheDeck SELECT   'Lightning Bolt'	,	1
INSERT INTO #TheDeck SELECT   'Lightning Bolt'	,	1
INSERT INTO #TheDeck SELECT   'Lightning Bolt'	,	1
INSERT INTO #TheDeck SELECT   'Lightning Bolt'	,	1

INSERT INTO #TheDeck SELECT 	  'Lightning Helix'	,	2
INSERT INTO #TheDeck SELECT 	  'Lightning Helix'	,	2
INSERT INTO #TheDeck SELECT 	  'Lightning Helix'	,	2
INSERT INTO #TheDeck SELECT 	  'Lightning Helix'	,	2

INSERT INTO #TheDeck SELECT 	 'Rift Bolt'	,	1
INSERT INTO #TheDeck SELECT 	 'Rift Bolt'	,	1
INSERT INTO #TheDeck SELECT 	 'Rift Bolt'	,	1
INSERT INTO #TheDeck SELECT 	 'Rift Bolt'	,	1

INSERT INTO #TheDeck SELECT   'Searing Blaze'	,	2
INSERT INTO #TheDeck SELECT   'Searing Blaze'	,	2
INSERT INTO #TheDeck SELECT   'Searing Blaze'	,	2
INSERT INTO #TheDeck SELECT   'Searing Blaze'	,	2

INSERT INTO #TheDeck SELECT   'Skullcrack'	,	2
INSERT INTO #TheDeck SELECT   'Skullcrack'	,	2
INSERT INTO #TheDeck SELECT   'Skullcrack'	,	2
INSERT INTO #TheDeck SELECT   'Skullcrack'	,	2





Обратите внимаение на столбец LandsCount 
SELECT TOP 10  object_id,  

   LandsCount = ( SELECT SUM(F.TheLand) FROM 
        (
        SELECT TOP(7) Z.TheCard, Z.CMC , TheLand = CASE WHEN Z.CMC = 0 THEN 1 ELSE 0 END  
         FROM (
        SELECT   TheCard, CMC, Case When CMC = 0 THEN 1 ELSE 0 END TheLand 
         , SortID = NewID()  /*одинаково для всех строк!!!*/
        FROM #TheDeck      ) Z   ORDER BY Z.SortID
        ) F ) 

FROM sys.all_columns

В то время, как при более простом случаt

SELECT TOP 10 object_id, LAMEID = ( SELECT LameID = NEWID())   from sys.all_objects


Все работает корректно.
8 фев 18, 13:17    [21176084]     Ответить | Цитировать Сообщить модератору
 Re: NEWID() не работает в коррелирующем запросе  [new]
aleks222
Guest 		1. Чисто праздный интерес: а какой прок в этой статистике? Статистика NewID() и статистика карточной игры ваще нигде не связаны.

2. Не проще ли вставить карты с NewID() в таблицу. Чать пара миллионов - это ерунда со всех точек зрения.

3. Построить кластерный индекс и выбирать по-порядку семь штук.

3. И фсе.
8 фев 18, 13:24    [21176133]     Ответить | Цитировать Сообщить модератору
 Re: NEWID() не работает в коррелирующем запросе  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
автор
2. Не проще ли вставить карты с NewID() в таблицу. Чать пара миллионов - это ерунда со всех точек зрения.
3. Построить кластерный индекс и выбирать по-порядку семь штук.

Перед игрой колода тщательно перемешивается. Я не знаю в какой последовательности идут карты. Стартовая рука - семь случаных карт.

Тут мы подходим к вопросу
автор
1. Чисто праздный интерес: а какой прок в этой статистике?

Колода в данной игре состоит из разных типов карт. Основной момет, важный для игрок- количество карт с типом ЗЕМЛЯ в стартовой руке. В профессиональной среде идут споры по поводу в каком соотношении нужно класть ЗЕМЛИ и другие карты в колоду, для максимальной эффективности стартовой руки: 18-42 , 19-41, 20-40.

Соответственно мне надо сделать несколько лямов прогонов чтоб посмотреть сколько земель приходит в стартовую руку в зависимости от вышеуказанного соотношения.
8 фев 18, 13:44    [21176226]     Ответить | Цитировать Сообщить модератору
 Re: NEWID() не работает в коррелирующем запросе  [new]
Дедушка
Member

Откуда: Город трёх революций
Сообщений: 5111
 		Cammomile,
не совсем понятно, что вы тут называете "коррелирующим запросом"...

опять же вот пример
declare @t1 table (id1 int)
declare @t3 table (id3 int)

insert @t1 (id1) values (11),(12),(13)
insert @t3 (id3) values (31),(32),(33)

select
id3,
(
	select top (1) 
		case when q.id1 = 12 then 1 else 0 end as p 
	from 
		(select id1, newid() as id from @t1) q 
	order by q.id
) as w
from
@t3
в котором w разные, значит и newid() внутри разные.
или я вас не понял?
8 фев 18, 13:46    [21176232]     Ответить | Цитировать Сообщить модератору
 Re: NEWID() не работает в коррелирующем запросе  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
Дедушка
Cammomile,
не совсем понятно, что вы тут называете "коррелирующим запросом"...

(SELECT TOP (7) FROM ORDER BY NewID() ) который должен быть выполнен для КАЖДОЙ строки входной таблицы типа dbo.Numbers
8 фев 18, 13:48    [21176240]     Ответить | Цитировать Сообщить модератору
 Re: NEWID() не работает в коррелирующем запросе  [new]
Дедушка
Member

Откуда: Город трёх революций
Сообщений: 5111
 		Cammomile,

тот пример, что я выше привёл соответствует?
8 фев 18, 13:50    [21176248]     Ответить | Цитировать Сообщить модератору
 Re: NEWID() не работает в коррелирующем запросе  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
Дедушка
Cammomile,

тот пример, что я выше привёл соответствует?

Нет

в моем случае вот так 

declare @t1 table (id1 int)
declare @t3 table (id3 int)

insert @t1 (id1) values (11),(12),(13)
insert @t3 (id3) values (31),(32),(33)

select
id3,
(
   SELECt SUM(p) as THE_P_SUM FROM 
   (
	select top (2) 
		case when q.id1 = 12 then 1 else 0 end as p 
	from 
		(select id1, newid() as id from @t1) q 
	order by q.id 
  ) DD
) as w
from

@t3


И, как видите, W одинакова, хотя должна быть разная
8 фев 18, 13:53    [21176264]     Ответить | Цитировать Сообщить модератору
 Re: NEWID() не работает в коррелирующем запросе  [new]
iap
Member

Откуда: Москва
Сообщений: 46977
Cammomile
Дедушка
Cammomile,

тот пример, что я выше привёл соответствует?

Нет

в моем случае вот так 

declare @t1 table (id1 int)
declare @t3 table (id3 int)

insert @t1 (id1) values (11),(12),(13)
insert @t3 (id3) values (31),(32),(33)

select
id3,
(
   SELECt SUM(p) as THE_P_SUM FROM 
   (
	select top (2) 
		case when q.id1 = 12 then 1 else 0 end as p 
	from 
		(select id1, newid() as id from @t1) q 
	order by q.id 
  ) DD
) as w
from

@t3



И, как видите, W одинакова, хотя должна быть разная
По плану сколько раз выполняется подзапрос w ?
Он же независим от внешнего запроса FROM @t3.
Значит, может выполниться один раз? Если бы оптимизатором был я, то так бы и сделал!
8 фев 18, 14:00    [21176297]     Ответить | Цитировать Сообщить модератору
 Re: NEWID() не работает в коррелирующем запросе  [new]
Дедушка
Member

Откуда: Город трёх революций
Сообщений: 5111
 		если посмотреть план то видно что сначала делается агрегат а затем только джоин к внешней таблице
выносите суммирование наружу
declare @t1 table (id1 int)
declare @t3 table (id3 int)

insert @t1 (id1) values (11),(12),(13)
insert @t3 (id3) values (31),(32),(33)

select
	a.id3, sum(a.p) as sp
from
	(
	select
	id3,
	w.p
	from
	@t3
	cross apply
		(
		select top (2)
			id, 
			case when q.id1 = 12 then 1 else 0 end as p 
		from 
			(select id1, newid() as id from @t1) q 
		order by q.id 
		) as w
	) a
group by
	a.id3
8 фев 18, 14:10    [21176341]     Ответить | Цитировать Сообщить модератору
 Re: NEWID() не работает в коррелирующем запросе  [new]
invm
Member

Откуда: Москва
Сообщений: 9344
Cammomile
И, как видите, W одинакова, хотя должна быть разная
Это вам хочется, чтобы была одинакова.
На самом деле подзапрос не кореллированный, ибо инвариантен относительно t3.
В общем, сравнивайте:
declare @t1 table (id1 int);
declare @t3 table (id3 int);

insert @t1 (id1) values (11),(12),(13);
insert @t3 (id3) values (31),(32),(33);

set statistics xml on;

select
id3,
(
   SELECt SUM(p) as THE_P_SUM FROM 
   (
	select top (2) 
		case when q.id1 = 12 then 1 else 0 end as p 
	from 
		(select id1, newid() as id from @t1) q 
	order by q.id 
  ) DD
) as w
from
 @t3;

select
id3,
(
   SELECt SUM(p) as THE_P_SUM FROM 
   (
	select top (2) 
		case when t3.id3 > 0 and q.id1 = 12 then 1 else 0 end as p 
	from 
		(select id1, newid() as id from @t1) q 
	order by q.id 
  ) DD
) as w
from
 @t3 t3;

set statistics xml off;
8 фев 18, 14:17    [21176362]     Ответить | Цитировать Сообщить модератору
 Re: NEWID() не работает в коррелирующем запросе  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
автор
Это вам хочется, чтобы была одинакова.
На самом деле подзапрос не кореллированный, ибо инвариантен относительно t3.
В общем, сравнивайте:

Ну я изначально написал КРОСС АПЛАЙ, в котором вроде как явно написано что он будет применен НА КАЖДУЮ СТРОКУ входного набора.

Но ваши слова просто в точку! Когда я явно заказал использование "входной" таблицы, все взлетело как надо.
8 фев 18, 14:30    [21176417]     Ответить | Цитировать Сообщить модератору
 Re: NEWID() не работает в коррелирующем запросе  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
 		ХОТЯ остается вопрос. Почему к такому поведению приводит именно использование SUM() ?

Ведь если написать просто 
SELECT TOP 10 object_id, LameID= ( SELECT LameID = NEWID())   from sys.all_objects

Где, заметьте , LameID тоже не связан с sys.all_objects оптимизатор, все же, считает новый айди на КАЖДУЮ строку.
8 фев 18, 14:38    [21176448]     Ответить | Цитировать Сообщить модератору
 Re: NEWID() не работает в коррелирующем запросе  [new]
invm
Member

Откуда: Москва
Сообщений: 9344
Cammomile
Ну я изначально написал КРОСС АПЛАЙ, в котором вроде как явно написано что он будет применен НА КАЖДУЮ СТРОКУ входного набора.
Ну так "применен" не эквивалентно "выполнен".
Cammomile
Ведь если написать просто 
SELECT TOP 10 object_id, LameID= ( SELECT LameID = NEWID())   from sys.all_objects
А тут нет подзапроса. ( SELECT LameID = NEWID()) преобразуется в Compute Scalar.

Вообще, результат зависит от порядка соединения таблиц. Сравните
select
 id3, a.THE_P_SUM
from
 @t3 cross apply
 (
   SELECt SUM(p) as THE_P_SUM FROM 
   (
	select top (2) 
		case when q.id1 = 12 then 1 else 0 end as p 
	from 
		(select id1, newid() as id from @t1) q 
	order by q.id 
   ) DD
 ) a
option
 (force order);

select
 id3, a.THE_P_SUM
from
 (
   SELECt SUM(p) as THE_P_SUM FROM 
   (
	select top (2) 
		case when q.id1 = 12 then 1 else 0 end as p 
	from 
		(select id1, newid() as id from @t1) q 
	order by q.id 
   ) DD
 ) a cross apply
 @t3
option
 (force order);

Ваш же исходный запрос для этого примера написан таким образом, что изменить порядок соединения можно только сделав подзапрос зависимым от таблицы.
8 фев 18, 16:37    [21177259]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить