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

Откуда:
Сообщений: 5151
Привет!

Подскажите, в Denali не поправили известную проблему с аналитикой в рекурсивной части СTE? (J. Using analytical functions in a recursive CTE).

В этом случае все ожидаемо:
with t (id, summ, part, level) as
(select cast(1 as float) id, cast(0 as float), cast(null as float), 0
union all select 2, 0, null, 0
union all select 3, 0, null, 0),
-- another CTE
t1 as
(select id, sum(id) over (partition by part) summ, part, level
from
(select id, floor(id/2) part, level + 1 level from t) t0
where level <= 1)
select *
from t1
order by level, id
id                     summ                   part                   level
---------------------- ---------------------- ---------------------- -----------
1                      1                      0                      1
2                      5                      1                      1
3                      5                      1                      1

(3 row(s) affected)
А вот здесь имеем несколько не ожидаемый результат, но описанный в документации:
with t (id, summ, part, level) as
(select cast(1 as float) id, cast(0 as float), cast(null as float), 0
union all select 2, 0, null, 0
union all select 3, 0, null, 0
-- recursive component
union all
select id, sum(id) over (partition by part) summ, part, level
from
(select id, floor(id/2) part, level + 1 level from t) t0
where level <= 1)
select *
from t
where level = 1
order by level, id
id                     summ                   part                   level
---------------------- ---------------------- ---------------------- -----------
1                      1                      0                      1
2                      2                      1                      1
3                      3                      1                      1

(3 row(s) affected)
Не совсем понятно зачем разрешать использовать аналитику, если она не имеет никакого смысла.
В Denali какие-то изменения на этот счет имеются?
24 июл 11, 23:21    [11020432]     Ответить | Цитировать Сообщить модератору
 Re: Denali: Аналитику в CTE поправили?  [new]
alexejs
Member

Откуда:
Сообщений: 147
По существу. В Денали так же.
По, скажем так, философствовать. Как должна вести себя сумма в рекурсии?
25 июл 11, 21:37    [11025373]     Ответить | Цитировать Сообщить модератору
 Re: Denali: Аналитику в CTE поправили?  [new]
dbms_photoshop
Member

Откуда:
Сообщений: 5151
2Модераторы: Я могу периодически проводить аналогии с Ораклом. Это не потому что я преследую цель сравнения двух СУБД, а только дабы яснее изложить свою мысль.

Итак,
+ в Оракле для аналитическая sum может быть либо нарастающая либо нет в зависимости от того есть сортировка в окне или нет
SQL> with t as
  2  (select 1 p, 1 id from dual union all select 1 p, 10 id from dual)
  3  select t.*, sum(id) over (partition by p) summ from t;
 
         P         ID       SUMM
---------- ---------- ----------
         1         10         11
         1          1         11
SQL> with t as
  2  (select 1 p, 1 id from dual union all select 1 p, 10 id from dual)
  3  select t.*, sum(id) over (partition by p order by id) summ from t;
 
         P         ID       SUMM
---------- ---------- ----------
         1          1          1
         1         10         11
Соответственно зная, что хоть изменения в SQL/T-SQL и не считаются ключевыми нововведениями в Denali, но тем не менее присутствуют множество изменений в плане программирования, в частности в аналитических функциях - это было одной из причин по которой я спросил.

Поскольку для 2008 R2 в сумме нельзя указывать сортировку в окне, то рассмотрим более простой случай, когда сортировка отсутствует:
alexejs
По, скажем так, философствовать. Как должна вести себя сумма в рекурсии?
Так, как если бы функция применялась ко всему набору данных, получаемому на конкретном уровне рекурсии из "from". Соответственно для примера выше если партиция не меняется то и сумма по партиции должна быть постоянной и равной 5.
Но как я уже заметил в доке указано:
http://technet.microsoft.com/en-us/library/ms175972.aspx
because only the subset of data for that recursion level is passed to ROWNUMBER. For each of the iterations of the recursive part of the query, only one row is passed to ROWNUMBER
Мне не понятен смысл такого поведения и вообще мне сложно придумать полезный пример применения аналитической функции в рекурсивном элементе при таких особенностях.

Вторая причина по которой я это спрашиваю - это исследование возможности решения недавней задачи исключительно средствами SQL: Кохонен на SQL.
Для решения на МССКЛ я написал такую функцию (исключительно в демонстрационных целях):
+
create type tt_k_means as table (id float, x float, y float, cid float, cx float, cy float)
CREATE FUNCTION f_k_means
(
	@p TT_K_MEANS readonly
)
RETURNS @result TABLE (id FLOAT, x FLOAT, y FLOAT, cid FLOAT, cx FLOAT, cy FLOAT)
AS
BEGIN
  DECLARE @tmp        TABLE (id FLOAT, x FLOAT, y FLOAT, cid FLOAT, cx FLOAT, cy FLOAT, depth INT)
  DECLARE @l_clusters INT = 2
  DECLARE @l_cnt      INT
  DECLARE @l_cnt_elem INT = (SELECT Count(*) FROM @p)
  DECLARE @depth INT = 0

  INSERT @tmp
  SELECT id,
         x,
         y,
         cid,
         Avg(x) OVER(PARTITION BY cid),
         Avg(y) OVER(PARTITION BY cid),
         0
    FROM (SELECT m.id,
                 m.x,
                 m.y,
                 c.cid,
                 Row_number() OVER(PARTITION BY m.id ORDER BY Sqrt((m.x - c.x) * (m.x - c.x) + (m.y - c.y) * (m.y - c.y))) rn
            FROM @p m,
                 (SELECT id cid, x, y FROM @p WHERE id <= @l_clusters) c) sub
   WHERE rn = 1

  WHILE 1=1 BEGIN
    INSERT @tmp
    SELECT id,
           x,
           y,
           cid,
           Avg(x) OVER(PARTITION BY cid),
           Avg(y) OVER(PARTITION BY cid),
           depth
      FROM (SELECT id,
                   x,
                   y,
                   t.cid,
                   Row_number() OVER(PARTITION BY id ORDER BY Sqrt((x - t.cx) * (x - t.cx) + (y - t.cy) * (y - t.cy))) rn,
                   depth + 1 depth
              FROM (SELECT * FROM @tmp WHERE depth = @depth) z,
                   (SELECT DISTINCT cid, cx, cy FROM @tmp WHERE depth = @depth) t) sub
     WHERE rn = 1
     
    SELECT @l_cnt = Count(*)-@l_cnt_elem
      FROM @tmp t1, @tmp t2
     WHERE t1.id = t2.id
       AND t1.cid = t2.cid
       AND t1.depth = @depth+1
       AND t2.depth = @depth
  
    IF @l_cnt = 0
      BREAK
      ELSE
    SET @depth = @depth + 1
  END
  
  INSERT @result SELECT depth, x, y, cid, cx, cy FROM @tmp WHERE depth = @depth

  RETURN
  
END
На тестовом наборе данных функция возвращает желаемый результат
declare @l tt_k_means
insert into @l(id,x,y) values (1,1,1),(2,2,1),(3,4,3),(4,5,4)
select * from f_k_means(@l)
(4 row(s) affected)
id                     x                      y                      cid                    cx                     cy
---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ----------------------
1                      1                      1                      1                      1,5                    1
1                      2                      1                      1                      1,5                    1
1                      4                      3                      2                      4,5                    3,5
1                      5                      4                      2                      4,5                    3,5

(4 row(s) affected)
Суть функции в том, что она делает кластеризацию исходного множества методом k-means. Образно говоря на каждом этапе элементы перемещаются между кластерами до тех пор пока этот процесс не стабилизируется. То есть грубо говоря идет рекурсивный процесс изменения входного множества. Это наводит на мысли, что можно задействовать рекурсивный CTE и соответственно функцию сделать Inline Table-Valued Function.

В МССКЛ развитие рекурсивного CTE зашло достаточно далеко, но вот работа аналитики оказалась для меня досадной неожиданностью.
Я упомянул все эти детали, просто чтоб описать когда может возникнуть необходимость в аналитике в рекурсивном элементе, хотя бы в исследовательских целях. :)

А если говорить на перспективу о том чего бы хотелось: то хотелось бы чтоб в будущем CTE достигло такой мощи, чтоб подобные ограничения были сняты :):
автор
# Specifying more than one WITH clause in a CTE is not allowed. For example, if a CTE_query_definition contains a subquery, that subquery cannot contain
a nested WITH clause that defines another CTE.
# The following clauses cannot be used in the CTE_query_definition:

* COMPUTE or COMPUTE BY
* ORDER BY (except when a TOP clause is specified)
* INTO
* OPTION clause with query hints
* FOR XML
* FOR BROWSE
Я не говорю что это остро необходимо, но бывает с появлением мощных инструментов возникают все более интересные идеи по поводу их применения.
+ 2 Александр Гладченко
Насколько мне известно вы имеете определенное отношение к переводу документации.
Я понимаю, что в английском варианте написано масло масляное:
http://technet.microsoft.com/en-us/library/ms175972.aspx
Analytic and aggregate functions in the recursive part of the CTE are applied to the set for the current recursion level and not to the set for the CTE. Functions like ROW_NUMBER operate only on the subset of data passed to them by the current recursion level and not the entire set of data pased to the recursive part of the CTE.
Но русский перевод имхо явно не соответствует тому, что написано:
http://technet.microsoft.com/ru-ru/library/ms175972.aspx
Аналитические и агрегатные функции в рекурсивной части обобщенных табличных выражений применяются для задания текущего уровня рекурсии, а не для задания обобщенных табличных выражений. Такие функции, как ROW_NUMBER, работают только с подмножествами данных, которые передаются им текущим уровнем рекурсии, но не со всем множеством данных, которые передаются в рекурсивную часть ОТВ.
Да и если б так было "Functions like ROW_NUMBER operate only on the subset of data passed to them by the current recursion level". Было бы замечательно. Но по факту аналитика применяется только к одной строке.
27 июл 11, 02:38    [11031678]     Ответить | Цитировать Сообщить модератору
 Re: Denali: Аналитику в CTE поправили?  [new]
dbms_photoshop
Member

Откуда:
Сообщений: 5151
dbms_photoshop
автор
# Specifying more than one WITH clause in a CTE is not allowed. For example, if a CTE_query_definition contains a subquery, that subquery cannot contain
a nested WITH clause that defines another CTE.
# The following clauses cannot be used in the CTE_query_definition:

* COMPUTE or COMPUTE BY
* ORDER BY (except when a TOP clause is specified)
* INTO
* OPTION clause with query hints
* FOR XML
* FOR BROWSE
Совершенно не то скописастил. Имелось в виду:
автор
# The FROM clause of a recursive member must refer only one time to the CTE expression_name.
# The following items are not allowed in the CTE_query_definition of a recursive member:

* SELECT DISTINCT
* GROUP BY
* HAVING
* Scalar aggregation
* TOP
* LEFT, RIGHT, OUTER JOIN (INNER JOIN is allowed)
* Subqueries
* A hint applied to a recursive reference to a CTE inside a CTE_query_definition.
27 июл 11, 02:41    [11031681]     Ответить | Цитировать Сообщить модератору
 Re: Denali: Аналитику в CTE поправили?  [new]
alexejs
Member

Откуда:
Сообщений: 147
Вы пишете: "Так, как если бы функция применялась ко всему набору данных, получаемому на конкретном уровне рекурсии из "from". Соответственно для примера выше если партиция не меняется то и сумма по партиции должна быть постоянной и равной 5".

Совершенно с Вами согласен. По умолчанию партиция считается от своей первой записи до текущей. Поэтому, например,

declare @t table (id int identity, grouping_id int, s int)
insert @t(grouping_id, s) values (1, 1), (1, 1), (1, 1), (2, 1), (2, 1)
select *, sum(s) over (partition by grouping_id order by id) from @t

даст нашу любимую сумму нарастающим итогом:

id grouping_id s (No column name)
1 1 1 1
2 1 1 2
3 1 1 3
4 2 1 1
5 2 1 2

Это очевидно. Для того, чтобы посчитать ее по всей партиции, надо указать, что окно считается не по текущую, а по последнюю запись в партиции:

select *, sum(s) over (partition by grouping_id order by id rows between unbounded preceding and unbounded following) from @t

id grouping_id s (No column name)
1 1 1 3
2 1 1 3
3 1 1 3
4 2 1 2
5 2 1 2

Однако, когда мы находимся в рекурсии, партиция полностью еще не оформилась. Она прибавляется с каждым шагом рекурсии. Поэтому оговаривать всю партицию или по дефолту - роли не играет. Все равно получится по тек.запись.
27 июл 11, 10:20    [11032257]     Ответить | Цитировать Сообщить модератору
 Re: Denali: Аналитику в CTE поправили?  [new]
ВопросPlus
Guest
alexejs
id grouping_id s (No column name)
1 1 1 3
2 1 1 3
3 1 1 3
4 2 1 2
5 2 1 2

Однако, когда мы находимся в рекурсии, партиция полностью еще не оформилась. Она прибавляется с каждым шагом рекурсии. Поэтому оговаривать всю партицию или по дефолту - роли не играет. Все равно получится по тек.запись.


Denali под рукой нет, но такой же результат даст старый синтаксис sum

select *, sum(s) over (partition by grouping_id) from @t

При каких данных результаты с
select *, sum(s) over (partition by grouping_id order by id rows between unbounded preceding and unbounded following) from @t 

будут разные???
27 июл 11, 11:25    [11032682]     Ответить | Цитировать Сообщить модератору
 Re: Denali: Аналитику в CTE поправили?  [new]
alexejs
Member

Откуда:
Сообщений: 147
Если речь идет о сумме нарастающим итогом, то у Вас потерялся order by в первом selecte.
27 июл 11, 12:44    [11033293]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить