Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Агрегация строк в одну / 15 сообщений из 15, страница 1 из 1
05.02.2018, 13:43
    #39596736
X-Cite
X-Cite
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Агрегация строк в одну
Добрый день.
Помогите найти статью, в которой подробно разлаживалось почему вариант
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
DECLARE
 @A VARCHAR(MAX) = ''
  
SELECT
  @A = @A + CAST(sil.Line AS VARCHAR(MAX)) + ', '
FROM
  TableA AS sil
ORDER BY
  sil.Line DESC
  
SELECT LEFT(@A, LEN(@A) - 1)


может привести к артефактам и некорректной конкатенации и необходимо заменить это на/через XML PATH

Во первых я сам столкнулся с некорректной работой такого алгоритма, а во вторых, где-то на просторах интернета, нашел статью подробную, где рассказывалось на уровне движка ядра MSSQL почему так происходит (из-за ORDER BY)

Или если кто может подробно объяснить своими словами, если нет статьи.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.02.2018, 13:49
    #39596741
Yasha123
Yasha123
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Агрегация строк в одну
вот тут обсуждали:
SORT iterator или еще раз о "сборе в переменную"

смотрите еще и ссылку в первом посте на тему, по мотивам которой
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.02.2018, 14:13
    #39596764
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Агрегация строк в одну
X-Cite,

Код: sql
1.
2.
SELECT
  @A = @A + CAST(sil.Line AS VARCHAR(MAX)) + ', '

эквивалентно
Код: sql
1.
2.
SELECT
  @A = Expr

где Expr вычисляется в Compurte Scalar плана выполнения.
Соответственно @A = Expr будет выполнено столько раз, сколько строк в результирующем наборе.

Возьмите пример:
Код: 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.
use tempdb;
go

create table dbo.t (id int primary key, s varchar(100));
insert into dbo.t
values
 (1, 'a'), (2, 'b'), (3, 'c');
go

set statistics profile on;
go

declare @s varchar(max) = '';

select @s = @s + s from dbo.t;
/*
select @s = @s + s from dbo.t;
  |--Compute Scalar(DEFINE:([Expr1003]=[@s]+[tempdb].[dbo].[t].[s]))
       |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[t].[PK__t__3213E83F3892A83A]))
*/
select @s;

select @s = '';
select @s = @s + s from dbo.t order by cast(id as varchar(10)) + s;
/*
select @s = @s + s from dbo.t order by cast(id as varchar(10)) + s;
  |--Sort(ORDER BY:([Expr1004] ASC))
       |--Compute Scalar(DEFINE:([Expr1003]=[@s]+[tempdb].[dbo].[t].[s], [Expr1004]=CONVERT(varchar(10),[tempdb].[dbo].[t].[id],0)+[tempdb].[dbo].[t].[s]))
            |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[t].[PK__t__3213E83F3892A83A]))
*/
select @s;
go

set statistics profile off;
go

drop table dbo.t;
go


В первом случае потребителем результата Compute Scalar будет select @s = ...
Выполнятся этот Compute scalar будет 3 раза, каждый раз с новым значением @s. В результате получите агрегированную строку.

Во вотором случае потребителем результата Compute Scalar будет Sort. Sort - блокирующий оператор, т.е. выход появится только тогда, когда будет потреблен весь вход.
Т.е. Compute Scalar выполняетеся те же 3 раза, только с одним и тем же значением @s. В результате получите @s = последнему полученному значению s из таблицы.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.02.2018, 14:30
    #39596783
X-Cite
X-Cite
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Агрегация строк в одну
Да. Большое спасибо.
Надо будет в закладки добавить.
Будет весомый аргумент для новых молодых сотрудников привыкших делать как умею...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.02.2018, 15:31
    #39596840
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Агрегация строк в одну
X-Cite,

Лечится довольно легко:
Код: sql
1.
2.
3.
4.
SELECT
  @A = @A + sil.Line + ', '
FROM
  (select top (cast(0x7fffffff as int)) CAST(sil.Line AS VARCHAR(MAX)) TableA order by Line desc) AS sil

Но все равно это не повод пользоваться недокументированным способом при наличии документированного.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.02.2018, 15:33
    #39596843
iap
iap
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Агрегация строк в одну
invmX-Cite,

Лечится довольно легко:
Код: sql
1.
2.
3.
4.
SELECT
  @A = @A + sil.Line + ', '
FROM
  (select top (cast(0x7fffffff as int)) CAST(sil.Line AS VARCHAR(MAX)) TableA order by Line desc) AS sil


Но все равно это не повод пользоваться недокументированным способом при наличии документированного.Кстати, в TOP() тип BIGINT, так что можно задать побольше
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.02.2018, 15:46
    #39596856
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Агрегация строк в одну
iapКстати, в TOP() тип BIGINT, так что можно задать побольше Лень f-ы считать
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.02.2018, 15:50
    #39596863
iap
iap
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Агрегация строк в одну
invmiapКстати, в TOP() тип BIGINT, так что можно задать побольше Лень f-ы считать 9223372036854775807
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.02.2018, 16:00
    #39596873
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Агрегация строк в одну
iap9223372036854775807Емкости моей памяти недостаточно для запоминания :)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.02.2018, 16:02
    #39596876
iap
iap
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Агрегация строк в одну
invmiap9223372036854775807Емкости моей памяти недостаточно для запоминания :)Поэтому я каждый раз лезу в справку про BIGINT!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.02.2018, 16:15
    #39596889
iap
iap
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Агрегация строк в одну
Кстати, максимальное значение INT можно получить вот так: CHECKSUM(NULL*0)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.02.2018, 16:29
    #39596901
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Агрегация строк в одну
iapмаксимальное значение INT можно получить вот так: CHECKSUM(NULL*0)Забавно :)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.02.2018, 17:04
    #39596941
VGalamakh
VGalamakh
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Агрегация строк в одну
iapКстати, максимальное значение INT можно получить вот так: CHECKSUM(NULL*0)

Код: sql
1.
2.
3.
Select power(2.0, max_length*8-1) 
from sys.types
where name = 'int'
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.02.2018, 18:00
    #39597008
Руслан Дамирович
Руслан Дамирович
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Агрегация строк в одну
VGalamakh, iap, invm
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
DECLARE @base DECIMAL( 30, 0 ) = 2
SELECT
  [name], 
  [min] = CASE WHEN t.[name] = 'TINYINT' THEN 0 ELSE -ss.[limit] END,
  [max] = CASE WHEN t.[name] = 'TINYINT' THEN 2 * ss.[limit] - 1 ELSE ss.[limit] - 1 END
FROM 
  sys.types t
  CROSS APPLY (
    SELECT
      [limit] = CONVERT( BIGINT, POWER( @base, t.[max_length] * 8 - 1 ) )
  ) ss
WHERE
  t.[name] LIKE '%INT'


BIGINT округляется, как получить реальное значение?
Google, Wikipedia и MSDN не предлагать!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.02.2018, 18:18
    #39597032
Руслан Дамирович
Руслан Дамирович
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Агрегация строк в одну
Ладно, нашел на SO

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
DECLARE @base BIGINT = 2
SELECT
  [name],
  [min] = CASE WHEN t.[name] = 'TINYINT' THEN 0 ELSE -ss.[limit] - 1 END,
  [max] = CASE WHEN t.[name] = 'TINYINT' THEN 2 * ss.[limit] + 1 ELSE ss.[limit] END
FROM 
  sys.types t
  CROSS APPLY (
    SELECT
      [limit] = POWER( @base, t.[max_length] * 8 - 2 ) + ( POWER( @base, t.[max_length] * 8 - 2 ) - 1 )
  ) ss
WHERE
  t.[name] LIKE '%INT'
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Агрегация строк в одну / 15 сообщений из 15, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


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