SQL, Вспомогательная таблица чисел

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

Каков оптимальный способ создания такой функции?

 JeffO25 февр. 2013 г., 21:54
Не все приложения DBA и / или сторонние приложения позволяют добавлять постоянную таблицу.
 jammus14 авг. 2008 г., 11:39
Не могли бы вы объяснить, почему вы это делаете, а не используете таблицу, предварительно заполненную цифрами?
 vzczc27 мая 2009 г., 12:57
Для заполнения такой таблицы, например.

Ответы на вопрос(7)

Эта статья дает 14 различных возможных решений с обсуждением каждого. Важным моментом является то, что:

suggestions regarding efficiency and performance are often subjective. Regardless of how a query is being used, the physical implementation determines the efficiency of a query. Therefore, rather than relying on biased guidelines, it is imperative that you test the query and determine which one performs better.

Мне лично понравилось:

WITH Nbrs ( n ) AS (
    SELECT 1 UNION ALL
    SELECT 1 + n FROM Nbrs WHERE n < 500 )
SELECT n FROM Nbrs
OPTION ( MAXRECURSION 500 )
 18 июн. 2013 г., 12:17
Доказано неправильно принятым ответом? Это не «оптимально», хотя выглядит красиво.

од. CTE (не касается базовых таблиц, чтобы получить объем строк):

--===== Hans CROSS JOINED CTE method
WITH Numbers_CTE (Digit)
AS
(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9)
SELECT HundredThousand.Digit * 100000 + TenThousand.Digit * 10000 + Thousand.Digit * 1000 + Hundred.Digit * 100 + Ten.Digit * 10 + One.Digit AS Number
INTO #Tally5
FROM Numbers_CTE AS One CROSS JOIN Numbers_CTE AS Ten CROSS JOIN Numbers_CTE AS Hundred CROSS JOIN Numbers_CTE AS Thousand CROSS JOIN Numbers_CTE AS TenThousand CROSS JOIN Numbers_CTE AS HundredThousand

Этот CTE выполняет больше операций чтения, чем CTE Ицик, но меньше, чем традиционный CTE. However, it consistently performs less WRITES then the other queries. Как вы знаете, Writes значительно дороже, чем Reads.

Продолжительность сильно зависит от количества ядер (MAXDOP), но на моем 8-ядерном процессоре выполняется стабильно быстрее (меньше длительности в мс), чем другие запросы.

Я использую:

Microsoft SQL Server 2012 - 11.0.5058.0 (X64) 
May 14 2014 18:34:29 
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: )

на Windows Server 2012 R2, 32 ГБ, Xeon X3450 @ 2,67 ГГц, 4-ядерный HT включен.

Ответ Джеффа Модена великолепен ... но я обнаружил на Postgres, что метод Ицик не работает, если вы не удалите строку E32.

Немного быстрее на postgres (40 мс против 100 мс) - еще один метод, который я нашел наВот адаптировано для postgres:

WITH 
    E00 (N) AS ( 
        SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
        SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ),
    E01 (N) AS (SELECT a.N FROM E00 a CROSS JOIN E00 b),
    E02 (N) AS (SELECT a.N FROM E01 a CROSS JOIN E01 b ),
    E03 (N) AS (SELECT a.N FROM E02 a CROSS JOIN E02 b 
        LIMIT 11000  -- end record  11,000 good for 30 yrs dates
    ), -- max is 100,000,000, starts slowing e.g. 1 million 1.5 secs, 2 mil 2.5 secs, 3 mill 4 secs
    Tally (N) as (SELECT row_number() OVER (ORDER BY a.N) FROM E03 a)

SELECT N
FROM Tally

Когда я перехожу с SQL Server на мир Postgres, возможно, упустил лучший способ составления таблиц на этой платформе ... INTEGER ()? ПОСЛЕДОВАТЕЛЬНОСТЬ()?

 08 сент. 2018 г., 02:31
@Conrad Frix, хе ... у вас уже есть настроенная программа, и вы не можете потратить 5 минут, чтобы проверить свои собственные претензии к производительности. NP. Двигаясь дальше,
 05 сент. 2018 г., 20:02
@Conrad Frix, извиняюсь за очень поздний вопрос (более чем на 5 лет позже), но провели ли вы какое-либо тестирование производительности, чтобы сравнить этот замечательный встроенный инструмент с другими методами?
 05 сент. 2018 г., 22:06
@JeffModen Извините, нет, но это легко проверить. Возьмите запрос Раскина и сравните его с вызовом, чтобы сгенерировать серию.
 24 июн. 2013 г., 16:35
спасибо Конрад ... ошибка новичка.
 24 янв. 2013 г., 22:07
may have missed a better way to do tally tables on [postgres]  Да, ты сделалgenerate_series
Решение Вопроса

я так поздно отвечаю на старый пост. И да, я должен был ответить, потому что самый популярный ответ (в то время, ответ Recursive CTE со ссылкой на 14 различных методов) в этом потоке, ммм ... производительность ставится под сомнение в лучшем случае.

Во-первых, статья с 14 различными решениями хороша для того, чтобы увидеть различные методы создания таблицы Numbers / Tally на лету, но, как указано в статье и в цитируемой теме, естьvery важная цитата ...

"suggestions regarding efficiency and performance are often subjective. Regardless of how a query is being used, the physical implementation determines the efficiency of a query. Therefore, rather than relying on biased guidelines, it is imperative that you test the query and determine which one performs better."

По иронии судьбы, сама статья содержит много субъективных утверждений и «предвзятых указаний». такие как"a recursive CTE can generate a number listing pretty efficiently" а также"This is an efficient method of using WHILE loop from a newsgroup posting by Itzik Ben-Gen" (который, я уверен, он опубликовал только для сравнения). C'mon monks ... Простое упоминание доброго имени Ицик может привести к тому, что какой-то плохой человек действительно использует этот ужасный метод. Автору следует практиковать то, что он проповедует, и проводить небольшое тестирование производительности, прежде чем делать такие смехотворно неверные утверждения, особенно перед лицом любой масштабируемости.

Мысль о том, чтобы на самом деле провести некоторое тестирование, прежде чем делать какие-либо субъективные заявления о том, что делает какой-либо код или что кому-то «нравится», вот код, с которым вы можете провести свое собственное тестирование. Настройте профилировщик для идентификатора SPID, с которого вы запускаете тест, и проверьте его сами ... просто выполните "Поиск" и "Заменить". на номер 1000000 для вашего "фаворита" номер и посмотри ...

--===== Test for 1000000 rows ==================================
GO
--===== Traditional RECURSIVE CTE method
   WITH Tally (N) AS 
        ( 
         SELECT 1 UNION ALL 
         SELECT 1 + N FROM Tally WHERE N < 1000000 
        ) 
 SELECT N 
   INTO #Tally1 
   FROM Tally 
 OPTION (MAXRECURSION 0);
GO
--===== Traditional WHILE LOOP method
 CREATE TABLE #Tally2 (N INT);
    SET NOCOUNT ON;
DECLARE @Index INT;
    SET @Index = 1;
  WHILE @Index <= 1000000 
  BEGIN 
         INSERT #Tally2 (N) 
         VALUES (@Index);
            SET @Index = @Index + 1;
    END;
GO
--===== Traditional CROSS JOIN table method
 SELECT TOP (1000000)
        ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS N
   INTO #Tally3
   FROM Master.sys.All_Columns ac1
  CROSS JOIN Master.sys.ALL_Columns ac2;
GO
--===== Itzik's CROSS JOINED CTE method
   WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1),
        E02(N) AS (SELECT 1 FROM E00 a, E00 b),
        E04(N) AS (SELECT 1 FROM E02 a, E02 b),
        E08(N) AS (SELECT 1 FROM E04 a, E04 b),
        E16(N) AS (SELECT 1 FROM E08 a, E08 b),
        E32(N) AS (SELECT 1 FROM E16 a, E16 b),
   cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E32)
 SELECT N
   INTO #Tally4
   FROM cteTally
  WHERE N <= 1000000;
GO
--===== Housekeeping
   DROP TABLE #Tally1, #Tally2, #Tally3, #Tally4;
GO

Пока мы здесь, вот числа, которые я получаю от SQL Profiler для значений 100, 1000, 10000, 100000 и 1000000 ...

SPID TextData                                 Dur(ms) CPU   Reads   Writes
---- ---------------------------------------- ------- ----- ------- ------
  51 --===== Test for 100 rows ==============       8     0       0      0
  51 --===== Traditional RECURSIVE CTE method      16     0     868      0
  51 --===== Traditional WHILE LOOP method CR      73    16     175      2
  51 --===== Traditional CROSS JOIN table met      11     0      80      0
  51 --===== Itzik's CROSS JOINED CTE method        6     0      63      0
  51 --===== Housekeeping   DROP TABLE #Tally      35    31     401      0

  51 --===== Test for 1000 rows =============       0     0       0      0
  51 --===== Traditional RECURSIVE CTE method      47    47    8074      0
  51 --===== Traditional WHILE LOOP method CR      80    78    1085      0
  51 --===== Traditional CROSS JOIN table met       5     0      98      0
  51 --===== Itzik's CROSS JOINED CTE method        2     0      83      0
  51 --===== Housekeeping   DROP TABLE #Tally       6    15     426      0

  51 --===== Test for 10000 rows ============       0     0       0      0
  51 --===== Traditional RECURSIVE CTE method     434   344   80230     10
  51 --===== Traditional WHILE LOOP method CR     671   563   10240      9
  51 --===== Traditional CROSS JOIN table met      25    31     302     15
  51 --===== Itzik's CROSS JOINED CTE method       24     0     192     15
  51 --===== Housekeeping   DROP TABLE #Tally       7    15     531      0

  51 --===== Test for 100000 rows ===========       0     0       0      0
  51 --===== Traditional RECURSIVE CTE method    4143  3813  800260    154
  51 --===== Traditional WHILE LOOP method CR    5820  5547  101380    161
  51 --===== Traditional CROSS JOIN table met     160   140     479    211
  51 --===== Itzik's CROSS JOINED CTE method      153   141     276    204
  51 --===== Housekeeping   DROP TABLE #Tally      10    15     761      0

  51 --===== Test for 1000000 rows ==========       0     0       0      0
  51 --===== Traditional RECURSIVE CTE method   41349 37437 8001048   1601
  51 --===== Traditional WHILE LOOP method CR   59138 56141 1012785   1682
  51 --===== Traditional CROSS JOIN table met    1224  1219    2429   2101
  51 --===== Itzik's CROSS JOINED CTE method     1448  1328    1217   2095
  51 --===== Housekeeping   DROP TABLE #Tally       8     0     415      0

Как вы видете,the Recursive CTE method is the second worst only to the While Loop for Duration and CPU and has 8 times the memory pressure in the form of logical reads than the While Loop, Это RBAR на стероидах, и его следует любой ценой избегать при любых вычислениях в одну строку, так же как следует избегать цикла «Пока».There are places where recursion is quite valuable but this ISN'T one of them.

В качестве боковой панели, мистер Денни абсолютно на ... постоянный номер правильных размеров или стол Tally - подход для большинства вещей. Что означает правильный размер? Ну, большинство людей используют таблицу Tally для генерации дат или разбивки на VARCHAR (8000). Если вы создадите таблицу Tally из 11 000 строк с правильным кластеризованным индексом «N», у вас будет достаточно строк для создания дат на более чем 30 лет (я работаю с ипотечным кредитом, поэтому 30 лет - это ключевое число для меня) и, конечно, достаточно, чтобы справиться с разделением VARCHAR (8000). Почему & quot; правильный размер & quot; очень важно? Если таблица Tally используется часто, она легко помещается в кэш, что делает ее невероятно быстрой без какого-либо большого давления на память.

И последнее, но не менее важное: каждый знает, что если вы создаете постоянную таблицу Tally, то не имеет большого значения, какой метод вы используете для ее построения, потому что 1) она будет сделана только один раз и 2) если что-то будет как и таблица из 11 000 строк, все методы будут запускаться «достаточно хорошо».So why all the indigination on my part about which method to use???

Ответ заключается в том, что какой-то бедный парень / девчонка, который не знает ничего лучше и просто нуждается в выполнении своей работы, может увидеть что-то вроде метода рекурсивного CTE и решит использовать его для чего-то гораздо большего и гораздо более часто используемого, чем создание. постоянный стол подсчета, и я пытаюсьprotect those people, the servers their code runs on, and the company that owns the data on those servers, Да ... это большое дело. Так должно быть и для всех остальных. Обучите правильному способу делать что-то вместо «достаточно хорошего». Проведите некоторое тестирование, прежде чем публиковать или использовать что-то из поста или книги ... жизнь, которую вы спасете, может, на самом деле, быть вашей, особенно если вы думаете, что рекурсивный CTE - это путь для чего-то подобного. ;-)

Спасибо за внимание...

 23 июл. 2017 г., 02:31
Спасибо за очень добрый отзыв, Андре.
 16 июл. 2017 г., 05:09
Я действительно очень хочу, чтобы больше людей почувствовали социальную ответственность. Сказали, что, кроме того, один раз нужно заполнить таблицу чисел для всех видов вещей, если это необходимо по какой-то причине,it seems SELECT INTO w/ IDENTITY is faster than CTE.

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

 18 июн. 2013 г., 12:19
Я думаю, что это зависит от вашей ситуации. Между двумя наиболее эффективными вариантами вы можете торговать между IO и стоимостью процессора, в зависимости от того, что для вас дороже.
 14 февр. 2019 г., 17:31
@rmalayter, за исключением случаев, когда таблица используется достаточно часто, чтобы мы могли о ней позаботиться, она почти наверняка будет в памяти, а память дешевле в обновлении и, как правило, не влияет на лицензирование, как добавление ядер процессора. Выпуск SQL Server Enterprise будет состоять из 5 цифр на ядро, т. Е. Добавление ядер, вероятно, будет стоить вам дороже только в рамках лицензирования, чем вся стоимость за счет добавления большего количества оперативной памяти на сервере.
 18 июн. 2013 г., 13:53
IO почти всегда будет дешевле, чем CPU, тем более что эта таблица будет маленькой и, вероятно, уже в budferpool.
 21 нояб. 2017 г., 05:25
@mrdenny I / O всегдаway дороже и медленнее, чем процессор. SSD несколько изменили это в последние годы, но в большинстве производственных архитектур эти SSD имеют сетевое соединение между ними и процессорами. Единственные базы данных, которые я вижу, которые действительно связаны с процессором, - это запуск ненастроенных приложений только для ORM или тяжелое машинное обучение.

int ценности.

CREATE VIEW dbo.Numbers
WITH SCHEMABINDING
AS
    WITH Int1(z) AS (SELECT 0 UNION ALL SELECT 0)
    , Int2(z) AS (SELECT 0 FROM Int1 a CROSS JOIN Int1 b)
    , Int4(z) AS (SELECT 0 FROM Int2 a CROSS JOIN Int2 b)
    , Int8(z) AS (SELECT 0 FROM Int4 a CROSS JOIN Int4 b)
    , Int16(z) AS (SELECT 0 FROM Int8 a CROSS JOIN Int8 b)
    , Int32(z) AS (SELECT TOP 2147483647 0 FROM Int16 a CROSS JOIN Int16 b)
    SELECT ROW_NUMBER() OVER (ORDER BY z) AS n
    FROM Int32
GO
 18 июн. 2013 г., 12:22
Интересно, может ли @AnthonyFaull подтвердить это некоторыми измерениями.
 27 февр. 2012 г., 23:12
Любая конкретная причина, чтобы добавитьWITH SCHEMABINDING в представлении?
 28 февр. 2012 г., 09:07
Добавление "С SCHEMABINDING" может делать запросы быстрее. Это помогает оптимизатору узнать, что данные не доступны. (Увидетьblogs.msdn.com/b/sqlprogrammability/archive/2006/05/12/…)
 04 июл. 2011 г., 23:02
0 часто полезно. И я бы, вероятно, преобразовать последний столбец вint, Также вы должны знать, что в основном метод включен в принятый ответ (без0 или преобразование вint либо) по имениItzik's CROSS JOINED CTE method.

SQL Server 2016+ создать таблицу чисел, которую вы могли бы использоватьOPENJSON :

-- range from 0 to @max - 1
DECLARE @max INT = 40000;

SELECT rn = CAST([key] AS INT) 
FROM OPENJSON(CONCAT('[1', REPLICATE(CAST(',1' AS VARCHAR(MAX)),@max-1),']'));

LiveDemo

Идея взята изHow can we use OPENJSON to generate series of numbers?

 05 сент. 2018 г., 20:01
Извините за поздний комментарий, но этот код использует в 11,4 раза больше ЦП и бесконечно больше логических чтений (2 000 023), чем каскадный метод CTE Итика.
 02 мая 2016 г., 20:06
Ницца. Я думаю, можно было бы использовать XML аналогично этому, еслиposition() полностью поддерживается в SQL Server XQuery.

Ваш ответ на вопрос