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

Откуда: Moscow
Сообщений: 1836
Доброго времени суток коллеги.

Что то туплю в вот каком вопросе:
хочу выстроить список псевдослучайных чисел на произвольную таблицу для этого написал табличную функцию возвращающую данный список.

При применении оператора outer apply почему то функция вызывается единственный раз для каждой строки левой таблицы хотя я предполагал что "список случайных чисел" будет дергать функцию каждый раз когда будет обрабатывать последующую строку.

собственно код:
CREATE TABLE tbl_Numbers (Value BIGINT, CONSTRAINT PK_tbl_Numbers PRIMARY KEY(Value)) --таблица чисел от 0 до 100000

;WITH X (Value) AS (
     SELECT 0 AS Value
       UNION ALL
     SELECT Value + 1 FROM X WHERE X.Value < 100000
)
INSERT INTO tbl_Numbers (Value)
  SELECT Value FROM X OPTION (MAXRECURSION 0)
GO

CREATE VIEW v_GetRandomDoubleList --представление списка псевдослучайных чисел
WITH SCHEMABINDING
AS
SELECT X.Value
FROM (
      SELECT
            ABS(CAST(NEWID() AS binary(6)) % 1000) + 1 AS Value
      FROM dbo.tbl_Numbers
     ) X
GROUP BY X.Value
GO

CREATE FUNCTION fn_GetRandomDouble(
      @min FLOAT,
      @max FLOAT
)
RETURNS TABLE
AS
RETURN (
        SELECT TOP 1 CAST(CAST(i.value AS VARCHAR(3)) + '.' + CAST(n.value AS VARCHAR(2))  AS FLOAT) AS Value
        FROM dbo.v_GetRandomDoubleList i
            CROSS JOIN dbo.v_GetRandomDoubleList n
        WHERE i.Value >= @min AND i.Value <= @max
          AND n.Value >= 0 AND n.Value <= 99
       )
GO


DECLARE @tbl TABLE (ID INT)
INSERT INTO @tbl VALUES (1), (2), (3), (4), (5)

SELECT * FROM @tbl t
          OUTER APPLY dbo.fn_GetRandomDouble(100.00, 300.00) F

/* Вывод:
ID          Value
----------- ----------------------
1           261,23
2           261,23
3           261,23
4           261,23
5           261,23
*/


возможно я неправильно понимаю принцип работы APPLY?
9 фев 16, 14:45    [18793600]     Ответить | Цитировать Сообщить модератору
 Re: OUTER APPLY  [new]
Glory
Member

Откуда:
Сообщений: 104751
felix_ff
будет дергать функцию каждый раз когда будет обрабатывать последующую

И зачем это делать, если у вас в вызове константы ?
9 фев 16, 14:46    [18793613]     Ответить | Цитировать Сообщить модератору
 Re: OUTER APPLY  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1836
судя по плану запроса насколько я понимаю, оптимизатор рассчитывает табличную функцию раньше чем производит операцию соединения по APPLY в связи с чем и получаю такой результат.

возможно я вообще изначально выбрал не лучший вариант решения задачи. будут другие предложения?

К сообщению приложен файл (plan_09022016.sqlplan - 60Kb) cкачать
9 фев 16, 14:50    [18793645]     Ответить | Цитировать Сообщить модератору
 Re: OUTER APPLY  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
Зачем такие сложности с функциями? Честно не понял...

SELECT TOP(5) number
FROM [master].dbo.spt_values
WHERE [type] = 'P'
ORDER BY NEWID()

Замените на Вашу таблицу и добавьте ограничения, которые необходимы.
9 фев 16, 14:52    [18793670]     Ответить | Цитировать Сообщить модератору
 Re: OUTER APPLY  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1836
AlanDenton,

плохо сегодня у меня с восприятием, согласен список случайных чисел данный подход дает, далее требуется для таблицы

DECLARE @tbl TABLE (ID INT)
INSERT INTO @tbl VALUES (1), (2), (3), (4), (5)


получить результат вида к примеру:
1, 241.32
2, 100.86
3, 140.00
4, 299.01
5, 188.27

соответственно не декартово множество:
1, 241.32
2, 241.32
3, 241.32
4, 241.32
5, 241.32
1, 100.86
2, 100.86
3, 100.86
4, 100.86
5, 100.86
и.т.д

я туплю в моменте как отфильтровать тогда получившийся набор данных.
ну точнее можно погемороиться с подзапросами к нему, но хотелось бы написать менее емко.
9 фев 16, 15:13    [18793872]     Ответить | Цитировать Сообщить модератору
 Re: OUTER APPLY  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1836
хотя пожалуй такой вариант меня устроит:

DECLARE @tbl TABLE (rowID INT IDENTITY(1, 1), ID INT)
INSERT INTO @tbl VALUES (10), (20), (30), (40), (50)

SELECT * FROM @tbl t
;WITH CTE (rowID, Value) AS ( 
SELECT TOP(5)
      ROW_NUMBER() OVER (ORDER BY NEWID()) AS rowID,
      CAST(CAST(i.value AS VARCHAR(3)) + '.' + CAST(m.value AS VARCHAR(3))  AS FLOAT) AS Value
FROM dbo.tbl_Numbers i
    CROSS APPLY (SELECT n.Value FROM dbo.tbl_Numbers n WHERE n.Value > 0 AND n.Value <= 99) m
WHERE i.Value >= 100 AND i.Value <= 300
)
SELECT * FROM @tbl t
    JOIN CTE ON t.rowId = CTE.rowID
9 фев 16, 15:19    [18793917]     Ответить | Цитировать Сообщить модератору
 Re: OUTER APPLY  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
Пытаюсь понять суть, но что-то не получается... Ведь можно так сделать:

SELECT ROW_NUMBER() OVER (ORDER BY 1/0), *
FROM (
	SELECT TOP(5) number
	FROM [master].dbo.spt_values
	WHERE [type] = 'P'
	ORDER BY NEWID()
) t

и регулировать кол-во записей значением переменной в конструкции TOP(@cnt)
9 фев 16, 15:20    [18793929]     Ответить | Цитировать Сообщить модератору
 Re: OUTER APPLY  [new]
invm
Member

Откуда: Москва
Сообщений: 9830
felix_ff
я предполагал что "список случайных чисел" будет дергать функцию каждый раз когда будет обрабатывать последующую строку.
У вас константы в вызове. Поэтому функция вызывается один раз для первой строки внешней части NL, результат кешируется в Table Spool. Для остальных строк уже используются данные из Table Spool.
Если запудрите оптимизатору мозг, то получите желаемое:
SELECT * FROM @tbl t
          OUTER APPLY dbo.fn_GetRandomDouble(100.00 + t.ID - t.ID, 300.00) F
:)
9 фев 16, 15:21    [18793938]     Ответить | Цитировать Сообщить модератору
 Re: OUTER APPLY  [new]
invm
Member

Откуда: Москва
Сообщений: 9830
felix_ff
будут другие предложения?
create view dbo.vRandValue
as
select rand(checksum(newid())) as Value;
go

create function dbo.fnGetRandomDouble
(
 @min float,
 @max float
)
returns table
as
return (
 select @min + Value * (@max - @min) as Value from dbo.vRandValue
);
go

DECLARE @tbl TABLE (ID INT)
INSERT INTO @tbl VALUES (1), (2), (3), (4), (5)

SELECT * FROM @tbl t
          CROSS APPLY dbo.fnGetRandomDouble(100.00, 300.00) F
9 фев 16, 15:25    [18793974]     Ответить | Цитировать Сообщить модератору
 Re: OUTER APPLY  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1836
invm,

спасибо. то что нужно :)


AlanDenton, Вам тоже
9 фев 16, 15:30    [18794013]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить