Добро пожаловать в форум, Guest >> Войти | Регистрация | Поиск | Правила | | В избранное | Подписаться | ||
Все форумы / Microsoft SQL Server |
![]() ![]() |
Cammomile Member Откуда: Сообщений: 1214 |
Заранее прошу прощения за подготовительную простыню, это у меня сразу практическая задачка которую я хотел решить на досуге по одной занимательной карточной игре. Мне нужно было сэмулировать пару мильонов стартовых рук по 7 случайных карт и подсчитать кое-какую статистику. Для гарантированной случайности я выбрал старый-добрый ORDER BY NewID() и тут оказалось, что он попросту не работает в коррелирующем подзапросе и кросс аплае. Под спойлером временная таблица с данными:
Обратите внимаение на столбец 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] Ответить | Цитировать Сообщить модератору |
aleks222
Guest |
1. Чисто праздный интерес: а какой прок в этой статистике? Статистика NewID() и статистика карточной игры ваще нигде не связаны. 2. Не проще ли вставить карты с NewID() в таблицу. Чать пара миллионов - это ерунда со всех точек зрения. 3. Построить кластерный индекс и выбирать по-порядку семь штук. 3. И фсе. |
8 фев 18, 13:24 [21176133] Ответить | Цитировать Сообщить модератору |
Cammomile Member Откуда: Сообщений: 1214 |
Перед игрой колода тщательно перемешивается. Я не знаю в какой последовательности идут карты. Стартовая рука - семь случаных карт. Тут мы подходим к вопросу
Колода в данной игре состоит из разных типов карт. Основной момет, важный для игрок- количество карт с типом ЗЕМЛЯ в стартовой руке. В профессиональной среде идут споры по поводу в каком соотношении нужно класть ЗЕМЛИ и другие карты в колоду, для максимальной эффективности стартовой руки: 18-42 , 19-41, 20-40. Соответственно мне надо сделать несколько лямов прогонов чтоб посмотреть сколько земель приходит в стартовую руку в зависимости от вышеуказанного соотношения. |
||||
8 фев 18, 13:44 [21176226] Ответить | Цитировать Сообщить модератору |
Дедушка Member Откуда: Город трёх революций Сообщений: 5114 |
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] Ответить | Цитировать Сообщить модератору |
Cammomile Member Откуда: Сообщений: 1214 |
(SELECT TOP (7) FROM ORDER BY NewID() ) который должен быть выполнен для КАЖДОЙ строки входной таблицы типа dbo.Numbers |
||
8 фев 18, 13:48 [21176240] Ответить | Цитировать Сообщить модератору |
Дедушка Member Откуда: Город трёх революций Сообщений: 5114 |
Cammomile, тот пример, что я выше привёл соответствует? |
8 фев 18, 13:50 [21176248] Ответить | Цитировать Сообщить модератору |
Cammomile Member Откуда: Сообщений: 1214 |
Нет в моем случае вот так 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] Ответить | Цитировать Сообщить модератору |
iap Member Откуда: Москва Сообщений: 47052 |
Он же независим от внешнего запроса FROM @t3. Значит, может выполниться один раз? Если бы оптимизатором был я, то так бы и сделал! |
||||
8 фев 18, 14:00 [21176297] Ответить | Цитировать Сообщить модератору |
Дедушка Member Откуда: Город трёх революций Сообщений: 5114 |
если посмотреть план то видно что сначала делается агрегат а затем только джоин к внешней таблице выносите суммирование наружу 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] Ответить | Цитировать Сообщить модератору |
invm Member Откуда: Москва Сообщений: 9644 |
На самом деле подзапрос не кореллированный, ибо инвариантен относительно 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] Ответить | Цитировать Сообщить модератору |
Cammomile Member Откуда: Сообщений: 1214 |
Ну я изначально написал КРОСС АПЛАЙ, в котором вроде как явно написано что он будет применен НА КАЖДУЮ СТРОКУ входного набора. Но ваши слова просто в точку! Когда я явно заказал использование "входной" таблицы, все взлетело как надо. |
||
8 фев 18, 14:30 [21176417] Ответить | Цитировать Сообщить модератору |
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] Ответить | Цитировать Сообщить модератору |
invm Member Откуда: Москва Сообщений: 9644 |
Вообще, результат зависит от порядка соединения таблиц. Сравните 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 | ![]() |