Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / NEWID() не работает в коррелирующем запросе / 13 сообщений из 13, страница 1 из 1
08.02.2018, 13:17
    #39598920
Cammomile
Cammomile
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
NEWID() не работает в коррелирующем запросе
Заранее прошу прощения за подготовительную простыню, это у меня сразу практическая задачка которую я хотел решить на досуге по одной занимательной карточной игре. Мне нужно было сэмулировать пару мильонов стартовых рук по 7 случайных карт и подсчитать кое-какую статистику.

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


Под спойлером временная таблица с данными:
 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
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
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
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

Код: sql
1.
2.
SELECT TOP 10 object_id, LAMEID = ( SELECT LameID = NEWID())   from sys.all_objects



Все работает корректно.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.02.2018, 13:24
    #39598933
aleks222
aleks222
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
NEWID() не работает в коррелирующем запросе
1. Чисто праздный интерес: а какой прок в этой статистике? Статистика NewID() и статистика карточной игры ваще нигде не связаны.

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

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

3. И фсе.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.02.2018, 13:44
    #39598958
Cammomile
Cammomile
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
NEWID() не работает в коррелирующем запросе
автор2. Не проще ли вставить карты с NewID() в таблицу. Чать пара миллионов - это ерунда со всех точек зрения.
3. Построить кластерный индекс и выбирать по-порядку семь штук.
Перед игрой колода тщательно перемешивается. Я не знаю в какой последовательности идут карты. Стартовая рука - семь случаных карт.

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

Соответственно мне надо сделать несколько лямов прогонов чтоб посмотреть сколько земель приходит в стартовую руку в зависимости от вышеуказанного соотношения.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.02.2018, 13:46
    #39598960
Дедушка
Дедушка
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
NEWID() не работает в коррелирующем запросе
Cammomile,
не совсем понятно, что вы тут называете "коррелирующим запросом"...

опять же вот пример
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
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() внутри разные.
или я вас не понял?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.02.2018, 13:48
    #39598963
Cammomile
Cammomile
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
NEWID() не работает в коррелирующем запросе
ДедушкаCammomile,
не совсем понятно, что вы тут называете "коррелирующим запросом"...

(SELECT TOP (7) FROM ORDER BY NewID() ) который должен быть выполнен для КАЖДОЙ строки входной таблицы типа dbo.Numbers
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.02.2018, 13:50
    #39598965
Дедушка
Дедушка
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
NEWID() не работает в коррелирующем запросе
Cammomile,

тот пример, что я выше привёл соответствует?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.02.2018, 13:53
    #39598971
Cammomile
Cammomile
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
NEWID() не работает в коррелирующем запросе
ДедушкаCammomile,

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

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

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
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 одинакова, хотя должна быть разная
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.02.2018, 14:00
    #39598984
iap
iap
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
NEWID() не работает в коррелирующем запросе
CammomileДедушкаCammomile,

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

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

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
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.
Значит, может выполниться один раз? Если бы оптимизатором был я, то так бы и сделал!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.02.2018, 14:10
    #39599000
Дедушка
Дедушка
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
NEWID() не работает в коррелирующем запросе
если посмотреть план то видно что сначала делается агрегат а затем только джоин к внешней таблице
выносите суммирование наружу
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
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
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.02.2018, 14:17
    #39599005
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
NEWID() не работает в коррелирующем запросе
CammomileИ, как видите, W одинакова, хотя должна быть разнаяЭто вам хочется, чтобы была одинакова.
На самом деле подзапрос не кореллированный, ибо инвариантен относительно t3.
В общем, сравнивайте:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
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;
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.02.2018, 14:30
    #39599015
Cammomile
Cammomile
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
NEWID() не работает в коррелирующем запросе
авторЭто вам хочется, чтобы была одинакова.
На самом деле подзапрос не кореллированный, ибо инвариантен относительно t3.
В общем, сравнивайте:
Ну я изначально написал КРОСС АПЛАЙ, в котором вроде как явно написано что он будет применен НА КАЖДУЮ СТРОКУ входного набора.

Но ваши слова просто в точку! Когда я явно заказал использование "входной" таблицы, все взлетело как надо.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.02.2018, 14:38
    #39599025
Cammomile
Cammomile
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
NEWID() не работает в коррелирующем запросе
ХОТЯ остается вопрос. Почему к такому поведению приводит именно использование SUM() ?

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


Где, заметьте , LameID тоже не связан с sys.all_objects оптимизатор, все же, считает новый айди на КАЖДУЮ строку.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.02.2018, 16:37
    #39599241
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
NEWID() не работает в коррелирующем запросе
CammomileНу я изначально написал КРОСС АПЛАЙ, в котором вроде как явно написано что он будет применен НА КАЖДУЮ СТРОКУ входного набора.Ну так "применен" не эквивалентно "выполнен".
CammomileВедь если написать просто
Код: sql
1.
SELECT TOP 10 object_id, LameID= ( SELECT LameID = NEWID())   from sys.all_objects

А тут нет подзапроса. ( SELECT LameID = NEWID()) преобразуется в Compute Scalar.

Вообще, результат зависит от порядка соединения таблиц. Сравните
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
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);


Ваш же исходный запрос для этого примера написан таким образом, что изменить порядок соединения можно только сделав подзапрос зависимым от таблицы.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / NEWID() не работает в коррелирующем запросе / 13 сообщений из 13, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]