powered by simpleCommunicator - 2.0.51     © 2025 Programmizd 02
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Нужное количество строк без использования таблицы
59 сообщений из 59, показаны все 3 страниц
Нужное количество строк без использования таблицы
    #35275035
NIIIK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Всем доброго времени суток ! :)

Уже голову сломал с тревиальнейшей задачей :(
Всё началось с задачи, когда мне в зависимости от какого-то числа (переменой или значения в таблицах) необходимо выбрать количество строк. Вот отвлечённый (не тот для которого мне нужно решение) пример.
Есть начальная дата и нам надо от неё построить таблицу чисел на 400 дней. Что может быть проще ?! берём эту дату и начинаем прибавлять к ней номер строки. Весь вопрос откуда его взять.
Есть топорный метод, когда делается выборка из таблицы с заранее не меньшим количеством строк (либо она получается перемножением), выводится нормер строки, а потом ставится условие на следующем уровне запроса. Но этот метод не наш !

В некоторых хороших СУБД можно напиать так
Код: plaintext
1.
select level from dual connect by  level <  400 

и будет счастье.

В Ms SQL Server 2005 есть понятие если не иерархического, то рекурсивного запроса, где при помощи ловкости рук можно построить иерархию. Вот и воспользуемся мы этим CTE

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
WITH derevo (
            level
            ) AS
      (
      select  1  as level

      union all

      select d.level +  1 
        from derevo d
        where d.level <  99 

      )
      select 
             d.level
        from derevo d

Но у нас возникает проблемка, когда уровень РЕКУРСИИ, по которой определяется уровень ИЕРАРХИИ становится больше 100. Сразу скажу что мне этого мало. А очередное перемножение с row_number() - это не дело.

Как обойти эту проблему или как убрать ограничение на уровень рекурсии ???
Поиском пользовался. Толком ничего не нашёл к сожалению :(
Буду благодарен за помощь.
...
Рейтинг: 0 / 0
Нужное количество строк без использования таблицы
    #35275045
iap
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
NIIIKВсем доброго времени суток ! :)

Уже голову сломал с тревиальнейшей задачей :(
Всё началось с задачи, когда мне в зависимости от какого-то числа (переменой или значения в таблицах) необходимо выбрать количество строк. Вот отвлечённый (не тот для которого мне нужно решение) пример.
Есть начальная дата и нам надо от неё построить таблицу чисел на 400 дней. Что может быть проще ?! берём эту дату и начинаем прибавлять к ней номер строки. Весь вопрос откуда его взять.
Есть топорный метод, когда делается выборка из таблицы с заранее не меньшим количеством строк (либо она получается перемножением), выводится нормер строки, а потом ставится условие на следующем уровне запроса. Но этот метод не наш !

В некоторых хороших СУБД можно напиать так
Код: plaintext
1.
select level from dual connect by  level <  400 

и будет счастье.

В Ms SQL Server 2005 есть понятие если не иерархического, то рекурсивного запроса, где при помощи ловкости рук можно построить иерархию. Вот и воспользуемся мы этим CTE

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
WITH derevo (
            level
            ) AS
      (
      select  1  as level

      union all

      select d.level +  1 
        from derevo d
        where d.level <  99 

      )
      select 
             d.level
        from derevo d

Но у нас возникает проблемка, когда уровень РЕКУРСИИ, по которой определяется уровень ИЕРАРХИИ становится больше 100. Сразу скажу что мне этого мало. А очередное перемножение с row_number() - это не дело.

Как обойти эту проблему или как убрать ограничение на уровень рекурсии ???
Поиском пользовался. Толком ничего не нашёл к сожалению :(
Буду благодарен за помощь.
Код: plaintext
OPTION(MAXRECURSION  0 )
...
Рейтинг: 0 / 0
Нужное количество строк без использования таблицы
    #35275046
Glory
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
NIIIK
Но у нас возникает проблемка, когда уровень РЕКУРСИИ, по которой определяется уровень ИЕРАРХИИ становится больше 100.

И какая же это проблемка ?
...
Рейтинг: 0 / 0
Нужное количество строк без использования таблицы
    #35275096
NIIIK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Спасибо за столь быстрый ответ !
И что я не подумал что про CT в БОЛ можно почитать :(
...
Рейтинг: 0 / 0
Нужное количество строк без использования таблицы
    #35275140
NIIIK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Не получается

Код: plaintext
OPTION(MAXRECURSION  0 )

Использовать в табличной функции.
...
Рейтинг: 0 / 0
Нужное количество строк без использования таблицы
    #35275183
NIIIK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Извеняюсь, в неоторых надо писать
Код: plaintext
1.
2.
3.
4.
5.
select t.fn_level
  from (select level as fn_level 
          from dual
        connect by level <=  1000 ) t

...
Рейтинг: 0 / 0
Нужное количество строк без использования таблицы
    #35275208
Фотография daw
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
>Не получается
>OPTION(MAXRECURSION 0)
>Использовать в табличной функции.

в multi-statement можно. в inline непосредственно в функции не получится. надо
указывать maxrecursion в запросе из функции.

Posted via ActualForum NNTP Server 1.4
...
Рейтинг: 0 / 0
Нужное количество строк без использования таблицы
    #35275211
iap
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
NIIIKНе получается

Код: plaintext
OPTION(MAXRECURSION  0 )

Использовать в табличной функции.Внутри inline функции и view действительно нельзя. А в multistatement function - можно. Если нельзя внутри, то всё равно можно - в SELECTе из этих объектов. Неудобно, конечно
...
Рейтинг: 0 / 0
Нужное количество строк без использования таблицы
    #35275272
pkarklin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
авторSELECT TOP 400
number
FROM
master..spt_values
WHERE
type = 'P'

Там таких записей 2048. Большее число - получается декартовым произведением. Наконец в некоторых хорошихъ базах данных всегда присутсвет табличка Number с числоми от ... и до...
...
Рейтинг: 0 / 0
Нужное количество строк без использования таблицы
    #35279409
NIIIK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
pkarklin авторSELECT TOP 400
number
FROM
master..spt_values
WHERE
type = 'P'

Там таких записей 2048. Большее число - получается декартовым произведением. Наконец в некоторых хорошихъ базах данных всегда присутсвет табличка Number с числоми от ... и до...

по моему вас не понятен вопрос.
1) вы предлагаете изначально кривой метод;
2) вы не можете управлять количаством строк через TOP, а можете изначально выбрать много записей, а потом поставить where ПОЛЕ < @Переменная, что так же кривой метод.

Сейчас же вопрос стоит как написать табличную функцию, потому что:
1) хинт OPTION(MAXRECURSION 0) не работает в функциях
2) процедуру нельзя использовать функции (потому что процедура совсем не процедура, а кусок скрипта с входными/выходными параметрами)

Нужна именно табличная функция, потому что:
1) используется в других функциях
2) не хочу писать ни этот кусок запроса везде где мне нужна подобная конструкция, ни объявлять таблицчную переменную потом инсёртить в неё данные (что работает так же только в процедурах)
...
Рейтинг: 0 / 0
Нужное количество строк без использования таблицы
    #35279552
iap
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Не понимаю, а почему Вы не можете использовать конструкцию
Код: plaintext
SELECT TOP(Expression) * FROM ...
если надо выбрать рассчитанное количество строк из таблицы? Причем Expression - это в частном случае может быть даже подзапрос!
...
Рейтинг: 0 / 0
Нужное количество строк без использования таблицы
    #35279557
pkarklin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
авторпо моему вас не понятен вопрос.
1) вы предлагаете изначально кривой метод;

Да что Вы говорите?!

автор2) вы не можете управлять количаством строк через TOP, а можете изначально выбрать много записей, а потом поставить where ПОЛЕ < @Переменная, что так же кривой метод.

Не знаю как Вы, я могу:

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
DECLARE @N int

SET @N =  100 

SELECT TOP (@N)
  number
FROM 
  master..spt_values 
WHERE
  type = 'P'

Более того, с таким поздапросом (постоянной таблицей Number) обычно JOINятся по условию

Код: plaintext
1.
...
Number <= @var

авторНужна именно табличная функция, потому что:
1) используется в других функциях
2) не хочу писать ни этот кусок запроса везде где мне нужна подобная конструкция, ни объявлять таблицчную переменную потом инсёртить в неё данные (что работает так же только в процедурах)

Да ради бога, уже давно пора было написать, использовав за основу приведенный мною запрос.
...
Рейтинг: 0 / 0
Нужное количество строк без использования таблицы
    #35279561
Glory
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
NIIIK

Нужна именно табличная функция, потому что:
1) используется в других функциях
2) не хочу писать ни этот кусок запроса везде где мне нужна подобная конструкция, ни объявлять таблицчную переменную потом инсёртить в неё данные (что работает так же только в процедурах)
И что мешает в функции в цикле добавить в табличную переменную нужное число записей ?
...
Рейтинг: 0 / 0
Нужное количество строк без использования таблицы
    #35279988
NIIIK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
[quot pkarklin]

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
DECLARE @N int

SET @N =  100 

SELECT TOP (@N)
  number
FROM 
  master..spt_values 
WHERE
  type = 'P'

Извеняюсь. Хе, действительно синтаксис хрен угадаешь хоть и аналогичен exec (@SQL).
Но выбирать из левой таблицы надеюсь всё равно не прийдётся.
...
Рейтинг: 0 / 0
Нужное количество строк без использования таблицы
    #35280078
iap
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
NIIIK[quot pkarklin]

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
DECLARE @N int

SET @N =  100 

SELECT TOP (@N)
  number
FROM 
  master..spt_values 
WHERE
  type = 'P'

Извеняюсь. Хе, действительно синтаксис хрен угадаешь хоть и аналогичен exec (@SQL).
Но выбирать из левой таблицы надеюсь всё равно не прийдётся.Под "левой" таблицей имеется в виду master..spt_values ? А кто мешает создать свою таблицу с числами, "правую"?
...
Рейтинг: 0 / 0
Нужное количество строк без использования таблицы
    #35284171
NIIIK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
iapПод "левой" таблицей имеется в виду master..spt_values ? А кто мешает создать свою таблицу с числами, "правую"?

Да потому что не нужна мне таблица с которой я не собираюсь брать данные. Потому что это не подход, а фмнт ушами. Я и так знаю что можно использовать запрос с таблицы, но меня от этой мысли протсо коробит. Как это вообще терпеть, что в запросе юзается таблица, которая тебе вообще не нужна. Тем более что в ней всё равно конечное количество записей. Перемножением получать всё равно глупо. Держать таблицу и заполнять её тоже не хочу. Ну должен жи быть метод более красивый ??? Я всё ещё надеюсь что эта функия будет переписана без использования РЕЛЬНОЙ таблицы, данные с которой НЕ ИСПОЛЬЗУЮТСЯ в запросе. Вопрос ведь изначально в этом. На этом заострено внимание.
...
Рейтинг: 0 / 0
Нужное количество строк без использования таблицы
    #35284175
Glory
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
NIIIK iapПод "левой" таблицей имеется в виду master..spt_values ? А кто мешает создать свою таблицу с числами, "правую"?

Да потому что не нужна мне таблица с которой я не собираюсь брать данные. Потому что это не подход, а фмнт ушами. Я и так знаю что можно использовать запрос с таблицы, но меня от этой мысли протсо коробит.
Как же она "не нужна", если вы собрались из нее выбираит записи ?
...
Рейтинг: 0 / 0
Нужное количество строк без использования таблицы
    #35284812
NIIIK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Glory NIIIK iapПод "левой" таблицей имеется в виду master..spt_values ? А кто мешает создать свою таблицу с числами, "правую"?

Да потому что не нужна мне таблица с которой я не собираюсь брать данные. Потому что это не подход, а фмнт ушами. Я и так знаю что можно использовать запрос с таблицы, но меня от этой мысли протсо коробит.
Как же она "не нужна", если вы собрались из нее выбираит записи ?

Вот точно так же как было в первом посте. Физической таблицы нет. Мне это как подход нужен. Ну не правильно это ориентироваться на количество записей в таблице. Это просто обход.
...
Рейтинг: 0 / 0
Нужное количество строк без использования таблицы
    #35284898
Glory
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
NIIIK
Вот точно так же как было в первом посте. Физической таблицы нет. Мне это как подход нужен. Ну не правильно это ориентироваться на количество записей в таблице. Это просто обход.
По-моему, вы занимаетесь тофтологией. Все равно в результате действий вы получите объект, который будет занимать ресурсы. Если он заранее не занимает места в базе, значит он будет занимать больше процессорного времени при формировании и наоборот. А вы, по-моему, просто ищите метод с наименьшим программным кодом
...
Рейтинг: 0 / 0
Нужное количество строк без использования таблицы
    #35284917
NIIIKЯ и так знаю что можно использовать запрос с таблицы, но меня от этой мысли протсо коробит.
Хм, да, в общем-то, весь язык SQL и T-SQL, в частности, созданы и заточены именно для запросов из таблиц. Вы средой разработки не ошиблись?
Как это вообще терпеть, что в запросе юзается таблица, которая тебе вообще не нужна. Тем более что в ней всё равно конечное количество записей. Перемножением получать всё равно глупо. Держать таблицу и заполнять её тоже не хочу. Ну должен жи быть метод более красивый ??? Я всё ещё надеюсь что эта функия будет переписана без использования РЕЛЬНОЙ таблицы, данные с которой НЕ ИСПОЛЬЗУЮТСЯ в запросе. Вопрос ведь изначально в этом. На этом заострено внимание.
Понятия красоты, глупости и т.п. сильно отличаются в разных языках. Вы точно про SQL говорите? Или Вы хотите со своим уставом в чужой монастырь залезть?
...
Рейтинг: 0 / 0
Нужное количество строк без использования таблицы
    #35307167
NIIIK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Извиняюсь, совсем забыл что окончательное решение не выложено. Вспомнил случайно.
Отдельное спасибо jap, daw и тем кот понял вопрос и дал ответы про

Код: plaintext
1.
OPTION(MAXRECURSION  0 )


в multi-statement можно. в inline непосредственно в функции не получится. надо
указывать maxrecursion в запросе из функции.


Код: plaintext
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.
create function get_sequence
(
	@pMaxOrder integer
)
returns 
@Result table 
(
	level integer
)
AS
BEGIN
   
	with derevo
         (
            level
         ) AS
         (
          select  1  as level
          union all
          select d.level +  1 
            from derevo d
           where d.level < @pMaxOrder
         )
		 insert 
           into @Result
         select d.level
           from derevo d OPTION (MAXRECURSION  0 )	
	return 
end
...
Рейтинг: 0 / 0
Период между сообщениями больше года.
Нужное количество строк без использования таблицы
    #40095784
NIIIK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
На данный момент я стараюсь использовать in-line функцию и посторяю maxrecursion опцию каждый раз.
Но у функций стоит "жирный" комментарий.
...
Рейтинг: 0 / 0
Нужное количество строк без использования таблицы
    #40095845
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
NIIIK,

авторберём эту дату и начинаем прибавлять к ней номер строки. Весь вопрос откуда его взять.
Из таблицы чисел, разумеется. Вы же работаете с базами данных, а не с императивными языками.
...
Рейтинг: 0 / 0
Нужное количество строк без использования таблицы
    #40095872
uaggster
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Хренасе флэшбэки через, без малого, 15 лет!
:-)
Я думал, эта тема до конца уже обсосана.
Ну, если master..spt_values по каким то причинам брезгаете пользоваться, и длина генерируемой последовательности чем то ограничена (миллионом, миллиардом, етц.), то можно так:
Код: 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.
Set statistics time on;
Set statistics io on;

Declare @n int = 199999

;WITH derevo (
            level
            ) AS
      (
      select 0 as level

      union all

      select d.level + 1
        from derevo d
        where d.level < @n-1

      )
      select 
             d.level
        from derevo d
Option(maxrecursion 0)

;With a as (
Select t.n from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n)
)
Select top(@n)
a0.n + isNull(a1.n,0)*10 + isNull(a2.n,0)*100 + isNull(a3.n,0)*1000 + isNull(a4.n,0)*10000 + isNull(a5.n,0)*100000 N
from a a0
cross join a a1
cross join a a2
cross join a a3
cross join a a4
cross join a a5
Order by 1 ASC


Тем более что:

SQL Server parse and compile time:
CPU time = 11 ms, elapsed time = 11 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

(199999 rows affected)
Table 'Worktable'. Scan count 2, logical reads 1199995, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 1032 ms, elapsed time = 1171 ms.

(199999 rows affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 489, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 312 ms, elapsed time = 367 ms.

Completion time: 2021-09-08T10:46:47.6679671+04:00
...
Рейтинг: 0 / 0
Нужное количество строк без использования таблицы
    #40095881
Фотография HandKot
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
uaggster
Хренасе флэшбэки через, без малого, 15 лет!
:-)

видел на просторах инета еще такой вариант
Код: sql
1.
2.
select row_number() over (order by (select 1/0)) n
from string_split(REPLICATE(cast(' ' as varchar(max)), @n-1), ' ') x



Код: plaintext
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.
Время синтаксического анализа и компиляции SQL Server: 
 время ЦП = 12 мс, истекшее время = 12 мс.

 Время работы SQL Server:
   Время ЦП = 0 мс, затраченное время = 0 мс.

 Время работы SQL Server:
   Время ЦП = 0 мс, затраченное время = 0 мс.

 Время работы SQL Server:
   Время ЦП = 0 мс, затраченное время = 0 мс.

(199999 rows affected)
Таблица "Worktable". Число просмотров 2, логических чтений 1199995, физических чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.

 Время работы SQL Server:
   Время ЦП = 1141 мс, затраченное время = 1248 мс.

(199999 rows affected)
Таблица "Worktable". Число просмотров 0, логических чтений 0, физических чтений 0, упреждающих чтений 857, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.

 Время работы SQL Server:
   Время ЦП = 485 мс, затраченное время = 150 мс.

(199999 rows affected)

 Время работы SQL Server:
   Время ЦП = 172 мс, затраченное время = 188 мс.
...
Рейтинг: 0 / 0
Нужное количество строк без использования таблицы
    #40095892
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Код: 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.
declare @v int, @n int = 1000000;

set statistics time on;

;With a as (
Select t.n from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n)
)
Select top(@n)
@v = a0.n + isNull(a1.n,0)*10 + isNull(a2.n,0)*100 + isNull(a3.n,0)*1000 + isNull(a4.n,0)*10000 + isNull(a5.n,0)*100000
from a a0
cross join a a1
cross join a a2
cross join a a3
cross join a a4
cross join a a5

;With a as (
Select t.n from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n)
)
Select top(@n)
@v = row_number() over (order by 1/0)
from a a0
cross join a a1
cross join a a2
cross join a a3
cross join a a4
cross join a a5

set statistics time off;



Код: plaintext
1.
2.
3.
4.
5.
 SQL Server Execution Times:
   CPU time = 531 ms,  elapsed time = 536 ms.

 SQL Server Execution Times:
   CPU time = 266 ms,  elapsed time = 259 ms.
...
Рейтинг: 0 / 0
Нужное количество строк без использования таблицы
    #40095898
Oleg_SQL
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
[quot HandKot#22369108]
uaggster
Хренасе флэшбэки через, без малого, 15 лет!
:-)

видел на просторах инета еще такой вариант
Код: sql
1.
2.
select row_number() over (order by (select 1/0)) n
from string_split(REPLICATE(cast(' ' as varchar(max)), @n-1), ' ') x



еще для краткости кода (select 1/0) можно заменить на 1/0
...
Рейтинг: 0 / 0
Нужное количество строк без использования таблицы
    #40095899
uaggster
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
HandKot, класс какой!
Возьму в копилку.
У меня время немного другое получилось

(199999 rows affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

SQL Server Execution Times:
CPU time = 440 ms, elapsed time = 983 ms.

(199999 rows affected)

SQL Server Execution Times:
CPU time = 438 ms, elapsed time = 1187 ms.

Но всё равно - огонь. Жаль, работает с 2017, тогда, кажется STRING_SPLIT появилось.

Кстати, возможно это самый быстрый и наименее ресурсоемкий вариант генерации последовательности 1...N
...
Рейтинг: 0 / 0
Нужное количество строк без использования таблицы
    #40095901
uaggster
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
invm, там, кстати, и isNull лишние. И я даже знаю, откуда они взялись :-)
Изначально там так было:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
;With a as (
Select t.n from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n)
)
Select top(@n)
a0.n + isNull(a1.n,0)*10 + isNull(a2.n,0)*100 + isNull(a3.n,0)*1000 + isNull(a4.n,0)*10000 + isNull(a5.n,0)*100000 N
from a a0
left join a a1 on @n / 10 > 0
left join a a2 on @n / 100 > 0
left join a a3 on @n / 1000 > 0
left join a a4 on @n / 10000 > 0
left join a a5 on @n / 100000 > 0
Order by 1 ASC



Но "улучшение", очевидно, бессмысленное, о чем я в свое время, и сказал разрабам. Низ поправили, а верх - нет :-)
...
Рейтинг: 0 / 0
Нужное количество строк без использования таблицы
    #40095903
uaggster
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Кстати, если их убрать - получается не все так однозначно:

Код: 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.
declare @n int = 1000000;

set statistics time on;

;With a as (
Select t.n from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n)
)
Select top(@n) a0.n + a1.n*10 + a2.n*100 + a3.n*1000 + a4.n*10000 + a5.n*100000
from a a0
cross join a a1
cross join a a2
cross join a a3
cross join a a4
cross join a a5
Order by 1 asc

;With a as (
Select t.n from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n)
)
Select top(@n) row_number() over (order by 1/0)
from a a0
cross join a a1
cross join a a2
cross join a a3
cross join a a4
cross join a a5

set statistics time off;


(1000000 rows affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

SQL Server Execution Times:
CPU time = 1077 ms, elapsed time = 4423 ms.

(1000000 rows affected)

SQL Server Execution Times:
CPU time = 625 ms, elapsed time = 5521 ms.
...
Рейтинг: 0 / 0
Нужное количество строк без использования таблицы
    #40095904
Oleg_SQL
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
uaggster


Кстати, возможно это самый быстрый и наименее ресурсоемкий вариант генерации последовательности 1...N


Примерно до 1 млн, а далее намного быстрее ваш код
...
Рейтинг: 0 / 0
Нужное количество строк без использования таблицы
    #40095911
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
uaggster
получается не все так однозначно
Потому что данные клиенту передаются.
Я не просто так в переменную складываю.
...
Рейтинг: 0 / 0
Нужное количество строк без использования таблицы
    #40095921
Uridian
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Все придумано до нас.
...
Рейтинг: 0 / 0
Нужное количество строк без использования таблицы
    #40095933
NIIIK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Владислав Колосов,

Ага и каждый раз, для каждого пользователя/запроса запущенного ещё добавить операций чтения (а может ещё и записи в случае вставки в табличную переменную мульти-стейтмент функции). А если ещё эта функция вызывала в

Код: sql
1.
2.
3.
4.
5.
select
  from ... t1
 ...
 outer
 apply dbo.fnSequenceGet ( t2.Val)



или в чём-то подобном. Или если эта функция использована ещё в чём (в той же функции возврата списка дат по периоду) ...
...
Рейтинг: 0 / 0
Нужное количество строк без использования таблицы
    #40095936
NIIIK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
uaggster,

Думаете надо было дать "настояться" до 15ти :) ?!

Просто очередной холивар по работе возник сейчас. Оппонент как раз за мой старый вариант с мульти-стейтмент функцией и вставкой во временную таблицу топит по мотивам "что бы options не надо было писать". А я предпочитаю даже перед вызовом функции каждый раз коммент поставить.

Так и в обычном CTE разрабочики могут забывать/не знать/ про это ограничение.

Было бы лучше если бы МС дало бы встроенную (и не CLR) функцию для этого дела.

На фоне остальных вариантов предпочитаю именно рекурсивный CTE (с учётом быстродействия, чтений-записей и т. п.).
...
Рейтинг: 0 / 0
Нужное количество строк без использования таблицы
    #40095939
NIIIK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
[quot Oleg_SQL#22369138]
HandKot
пропущено...

видел на просторах инета еще такой вариант
Код: sql
1.
2.
select row_number() over (order by (select 1/0)) n
from string_split(REPLICATE(cast(' ' as varchar(max)), @n-1), ' ') x



еще для краткости кода (select 1/0) можно заменить на 1/0


Ну и СплитСтринш не всегда был (даже .nodes для XML раньше появился вроде), но сомнительно что это быстрее.

(select 1/0) вряд ли убрать получится из оконной функции, скорее можно просто написать (select 1)
...
Рейтинг: 0 / 0
Нужное количество строк без использования таблицы
    #40095950
DaniilSeryi
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
NIIIK,

А вариант с циклом Вам не подойдёт?

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
  declare @d date='2021-01-01' --начальная дата, первая строка в создаваемой таблице
  declare @t table(i int,d date) --временная таблица, куда сохраняем записи
  declare @i integer=0 --счётчик строк
  while @i<400 --цикл
  begin
	insert into @t(i,d) 
	select @i+1, DATEADD(DAY,@i,@d) -- плюс 1, чтобы номера строк начинались с 1, а не с 0
	set @i=@i+1
  end
--выводим записи
  select * 
  from @t
  order by i
...
Рейтинг: 0 / 0
Нужное количество строк без использования таблицы
    #40095959
NIIIK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Oleg_SQL,

Отличный вариант, спасибо, по чтениям и записям и CPU выигрывает

Чуть код отформатировал

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
alter function [scratch].[fnSequenceGet_StringSplit](@MaxVal INT)
RETURNS TABLE
RETURN
(
select row_number() over (order by (t.f)) RowNum
  from (select 1 f) t 
  cross
  apply string_split(replicate(cast(' ' as varchar(max)), @MaxVal - 1), ' ') s
)
...
Рейтинг: 0 / 0
Нужное количество строк без использования таблицы
    #40096002
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
NIIIK,

производительность запроса не оценивается количеством чтений.
В случае использования таблицы чисел получить список дат очень легко:

авторавторselect dateadd(day, numbers.nn, @StartDate) dd from dbo.numbers

при том таблица числе может быть сформирована как угодно - неделя, месяц, квартал и так далее.
...
Рейтинг: 0 / 0
Нужное количество строк без использования таблицы
    #40096012
DaniilSeryi
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Владислав Колосов
NIIIK,

производительность запроса не оценивается количеством чтений.
В случае использования таблицы чисел получить список дат очень легко:

авторпропущено...


при том таблица числе может быть сформирована как угодно - неделя, месяц, квартал и так далее.

Владислав, этому посту 13 лет!
...
Рейтинг: 0 / 0
Нужное количество строк без использования таблицы
    #40096015
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
NIIIK
по чтениям и записям и CPU выигрывает
Даже стало интересно, где тут чтение и запись?
И какова методика тестирования, показавшая выигрыш по CPU?
...
Рейтинг: 0 / 0
Нужное количество строк без использования таблицы
    #40096022
NIIIK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
invm,

https://gyazo.com/30ccc507f43addcdcf874ee23c137020
Такая устроит?

А если хотите видеть запись сделайте мульти-стейтмент.
...
Рейтинг: 0 / 0
Нужное количество строк без использования таблицы
    #40096024
NIIIK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Владислав Колосов,

Производительность запроса оценивается операциями I/O, особенно если борешься именно с этим у убираешь циклы на Т-SQL и пишешь один запрос и легаси код меняешь из кучи вложенных multi-statement функций которые на каждый чих делают вставку в табличную переменную.
...
Рейтинг: 0 / 0
Нужное количество строк без использования таблицы
    #40096026
NIIIK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Кстати,
бесит MS, уже бы сделал готовую функцию, как тот же string_split сделал (не прошло и 10+++ лет).

Новый метод по производительности хорош, однако "в недрах string_split" всё равно идёт создание отельной строки для каждого случая которые могут быть ограничены внешними условиями и из 10к вернуться могут пару (или ноль) строк.
Но всё равно этот костыль лучший, даже "продвинутый протез", просто надо иметь маленькую последовательность вызовов встроенных функций (а не постоянные итерации в рекурсии).
...
Рейтинг: 0 / 0
Нужное количество строк без использования таблицы
    #40096060
aleks222
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
NIIIK
Кстати,
бесит MS, уже бы сделал готовую функцию, как тот же string_split сделал (не прошло и 10+++ лет).


В "правильной" базе такая функция не нужна. Равно как и string_split.
...
Рейтинг: 0 / 0
Нужное количество строк без использования таблицы
    #40096065
NIIIK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
aleks222,

В правильной базе нет операций, часто связанных с внешними данными:

- когда надо сгенерить отдельную запись для каждой даты из периода (причём для каждой записи отдельно)
- когда надо заменить цикл на Т-SQL на обычный запрос
- ....

Вот, пример "от балды", стоит у вас, например, какая-то настройка у пользователя/продукта/... в духе "по умолчанию создавать 10 записей при таком-то действии" - вы как, одним insert-оператором вставляющем 10 строк напишите?
Или будите в T-SQL цикле крутить?
Или вообще, в цикле на клиенте?
...
Рейтинг: 0 / 0
Нужное количество строк без использования таблицы
    #40096075
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
NIIIK
Такая устроит?
Замечательный пример как выбрать наилучший вариант из худших возможных.
...
Рейтинг: 0 / 0
Нужное количество строк без использования таблицы
    #40096079
aleks222
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
NIIIK
aleks222,

В правильной базе нет операций, часто связанных с внешними данными:

- 1) когда надо сгенерить отдельную запись для каждой даты из периода (причём для каждой записи отдельно)
- когда надо заменить цикл на Т-SQL на обычный запрос
- ....

Вот, пример "от балды", стоит у вас, например, какая-то настройка у пользователя/продукта/... в духе "по умолчанию создавать 10 записей при таком-то действии" - вы как, одним insert-оператором вставляющем 2) 10 строк напишите?
Или будите в T-SQL цикле крутить?
Или вообще, в цикле на клиенте?


1) 100 лет = 30000 и, в большинстве случаев (101% таковых), не нужно,
2) про 10 я помолчу.

Это не стоит функции - таблица справляется лучше и проще.
...
Рейтинг: 0 / 0
Нужное количество строк без использования таблицы
    #40096091
NIIIK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
invm,

ага, набросил на вентилятор без внятного ответа и всё... я уже и начал забывать типичный СКЛ.Ру
...
Рейтинг: 0 / 0
Нужное количество строк без использования таблицы
    #40096094
NIIIK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
aleks222,

какие ещё 100лет ?!
Вы к тест-кейсу с 10к значений?
Так это простой тест-кейс. А объёмы берутся когда у тебя ЕТЛ-операции да и обычные запросы пользовательские по списку каких-то сущностей и для каждой из них до сделать какое-то перемножение.

Чем "лучше" и "проще" использовать таблицу фейковую тупо ради строк ?!

Вот надо вам будет расписание для списка каких-то сотрудников вывести (естественно колонки для ГУИ будут строками в вашем дата-сете) - вы что бы получить нужное количество строк будите делать выборки из реальной таблицы?
А если такие расписания просматриваются одновременно многими пользователями в ГУИ ?
...
Рейтинг: 0 / 0
Нужное количество строк без использования таблицы
    #40096105
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
NIIIK
без внятного ответа и всё...
Ответы уже были даны ранее. Надо было читать все, а не выборочно.
Код: 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.
use tempdb;
set ansi_nulls, quoted_identifier, xact_abort on;
go

drop table if exists dbo.N;
create table dbo.N (n int primary key);
insert into dbo.N
 (n)
 select top (2000000)
  row_number() over (order by 1/0)
 from
  master.dbo.spt_values a cross join
  master.dbo.spt_values b;
go

declare @v int, @n int = 1000000;

set statistics time on;

select @v = n from dbo.N where n <= @n;

;With a as (
Select t.n from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n)
)
Select top(@n)
@v = a0.n + isNull(a1.n,0)*10 + isNull(a2.n,0)*100 + isNull(a3.n,0)*1000 + isNull(a4.n,0)*10000 + isNull(a5.n,0)*100000
from a a0
cross join a a1
cross join a a2
cross join a a3
cross join a a4
cross join a a5

;With a as (
Select t.n from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n)
)
Select top(@n)
@v = row_number() over (order by 1/0)
from a a0
cross join a a1
cross join a a2
cross join a a3
cross join a a4
cross join a a5

select @v = row_number() over (order by (select 1/0))
from string_split(REPLICATE(cast(' ' as varchar(max)), @n-1), ' ') x

set statistics time off;


Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
(2000000 rows affected)

 SQL Server Execution Times:
   CPU time = 94 ms,  elapsed time = 93 ms.

 SQL Server Execution Times:
   CPU time = 141 ms,  elapsed time = 146 ms.

 SQL Server Execution Times:
   CPU time = 109 ms,  elapsed time = 96 ms.

 SQL Server Execution Times:
   CPU time = 3672 ms,  elapsed time = 3693 ms.
...
Рейтинг: 0 / 0
Нужное количество строк без использования таблицы
    #40096107
spenov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
а расскажите, что за магия в запросе "1/0"? как это работает?
...
Рейтинг: 0 / 0
Нужное количество строк без использования таблицы
    #40096122
aleks222
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
NIIIK
aleks222,

какие ещё 100лет ?!
Вы к тест-кейсу с 10к значений?
Так это простой тест-кейс. А объёмы берутся когда у тебя ЕТЛ-операции да и обычные запросы пользовательские по списку каких-то сущностей и для каждой из них до сделать какое-то перемножение.

Чем "лучше" и "проще" использовать таблицу фейковую тупо ради строк ?!

Вот надо вам будет расписание для списка каких-то сотрудников вывести (естественно колонки для ГУИ будут строками в вашем дата-сете) - вы что бы получить нужное количество строк будите делать выборки из реальной таблицы?
А если такие расписания просматриваются одновременно многими пользователями в ГУИ ?


Вы не нервничайте - пользуйтесь.
Если по-другому не умеете.

Я для неокрепших умов, шобы не сбивались на скользкий путь.

ЗЫ. 30000 int = 15 страниц в памяти. Можно сколько угодно вопить, но это лучше, чем грузить процессор.
...
Рейтинг: 0 / 0
Нужное количество строк без использования таблицы
    #40096134
NIIIK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
invm,

Ох, дух Глори живёт на СКЛ.ру (может он ещё и сам живёт).

Где был дан ответ на моё сравнение варианты с рекурсивным ЦТЕ и новым c string_split ?
...
Рейтинг: 0 / 0
Нужное количество строк без использования таблицы
    #40096136
NIIIK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
spenov
а расскажите, что за магия в запросе "1/0"? как это работает?


Да никакой магнии, просто оконной функции надо что-то для сортировки, константу туда пихать не получается, чаще (select 1) вкорячивают, он тоже НЕ выполняется и в процессе выполнения нет ошибки.
...
Рейтинг: 0 / 0
Нужное количество строк без использования таблицы
    #40096137
NIIIK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
aleks222,

О, опять ражигатель набрасывающий на вентилятор...

У меня ум "окреп" ещё 13+ лет назад когда эта тема мной же создавалась.
Что в то время это был костылище, что сейчас костыль, но получше, после появления функции парсящей строку.

Только "по другому не умеете" - это скорее вам. Тот код с "with derevo" я создал вроде как сам, причём я тогда контролировал новый проект на МсСКЛе, хотя изначально пришёл в мир БД с Оракла. И как бы решил задачу :) А разработка тогда не была основной моей задачей.

Но функции парсящей строку на тот момент в принципе не было. И мне как-то вариант новый больше нравится, чем козлячие методы с запросами реальных таблиц, перемножениями и т. п.

Ещё больше бы понравилось бы, если бы МС дело сделал (а не тролей подкармливал защищающих его).
...
Рейтинг: 0 / 0
Нужное количество строк без использования таблицы
    #40096140
aleks222
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
NIIIK


У меня ум "окреп" ещё 13+ лет назад когда эта тема мной же создавалась.
Ещё больше бы понравилось бы, если бы МС дело сделал (а не тролей подкармливал защищающих его).


Шо, померимся у кого длиннее?
Помогите написать функцию
17 лет.
Просто я уж не полез ТОГДА в ЭТУ тему, ибо очевидное было фуфло.
Сколько таких тем...
...
Рейтинг: 0 / 0
Нужное количество строк без использования таблицы
    #40096553
NIIIK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
aleks222,

Ну это ваша нездоровая идея, а я сказал что уже в 2008ом у меня всё окрепло.

Ох... опять превращаете полезный форум в СКЛсРУ
...
Рейтинг: 0 / 0
Нужное количество строк без использования таблицы
    #40096600
aleks222
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
NIIIK
aleks222,

Ну это ваша нездоровая идея, а я сказал что уже в 2008ом у меня всё окрепло.

Ох... опять превращаете полезный форум в СКЛсРУ


Пойми, страдалец, то, что тебе мерещится - не является истиной.

Ибо
1. Генерация чисел занимает память (причем память НА КАЖДЫЙ вызов) и процессор НА КАЖДЫЙ вызов.
2. Чтение чисел из таблицы занимает ТОЛЬКО память. Причем одну и ту же. Т.е. 1 раз на всех.
...
Рейтинг: 0 / 0
59 сообщений из 59, показаны все 3 страниц
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Нужное количество строк без использования таблицы
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


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