Генерация диапазонов чисел и дат на T-SQL

добавлено: 23 ноя 11
понравилось:0
просмотров: 4026
комментов: 10

теги:

Автор: Алексей Дружинин

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

Соответственно, задался вопросом - как это правильно сделать-то, не закладываясь ни на какие таблицы. Быстрее всего, понятное дело, отработает CLR-ная функция. А если по правилам игры CLR использовать нельзя?

В итоге получилась вот такая штука.

CREATE FUNCTION dbo.GetIntRange ( @StartNum int, @EndNum int)
RETURNS TABLE
AS
	RETURN 
		WITH NumberRange AS (
			SELECT 1 AS Num
			UNION ALL
			SELECT
			  nr.Num * 2 + t.RecType  
			FROM NumberRange nr
			CROSS JOIN (
				SELECT 0 AS RecType
				UNION ALL 
				SELECT 1
			) t
			WHERE nr.Num * 2 + t.RecType <= @EndNum - @StartNum + 1
		)
		SELECT @StartNum + Num  - 1 AS Num
		FROM NumberRange;
GO

CREATE FUNCTION dbo.GetDateRange ( @StartDate date, @EndDate date )
RETURNS TABLE
AS
	RETURN
		SELECT DATEADD(DAY, gsr.Num - 1, @StartDate) AS [dDate]
		FROM dbo.GetIntRange(1, DATEDIFF(DAY, @StartDate, @EndDate)) gsr

GO

SELECT * 
FROM dbo.GetDateRange('19000101',SYSDATETIME())
ORDER BY dDate 

Для не особо больших диапазонов даже довольно быстро работает.

UPD. Если вдруг случилось, что этот пост - первое, что нашлось по генерации последовательностей, то настоятельно советую прочитать как минимум первые шесть комментариев.

UPD2. Если после прочтения комментариев желания или возможности делать отдельную таблицу всё ж не появилось, а запрос упирается в быстродействие, то для небольших диапазонов (примерно до 20000 значений) слегка поправить ситуацию может использование вот такой конструкции:

DECLARE @StartNum int = 10, @EndNum int = 20000;

WITH NumXML AS (
	SELECT CONVERT(xml,REPLICATE ('<r/>', 1000)) AS x
),
GenRows AS (
	SELECT 1 AS t
	FROM NumXML n
	CROSS APPLY x.nodes('r') AS r(nn)
)
SELECT TOP(@EndNum - @StartNum + 1) 
	ROW_NUMBER() OVER (ORDER BY @StartNum) + @StartNum - 1
FROM GenRows gr1
CROSS JOIN GenRows gr2;

(будет работать примерно в 6-10 раз быстрее варианта с CTE)

Комментарии


  • 23 ноября 2011, 21:55 SomewhereSomehow

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

  • 23 ноября 2011, 22:05 Алексей Дружинин

    Ничуть не сомневаюсь - сам проверял :)
    Просто бывают разовые задачи, специально для которых заводить таблицу чисел или дней - слишком лихо.
    А если требуется ось времени в частых и/или тяжёлых запросах - абсолютно с Вами согласен.

    Насчёт известности способа - плохо гуглил, видимо :(

  • 23 ноября 2011, 22:11 Алексей Дружинин

    Посмотрел ещё. Всё, что нашёл - это CTE-шки, в которых каждая итерация прибавляет по одному дню и выставлена MAXRECURSION 0.

  • 23 ноября 2011, 23:39 SomewhereSomehow

    Вот я вам нашел
    http://www.sql.ru/forum/actualutils.aspx?action=gotomsg&tid=851526&msg=11328917
    Обратите внимание на предыдущее сообщение и на тему в целом. Если у вас возникают сомнения в компетентности iljy, то заверю вас, это может быть самый грамотный человек на форуме (лично у меня иногда возникает ощущение, что у него под рукой исходники сиквела =)), но это только ощущение, можете почитать его месаджи - как энциклопедию можно=)) ) Тема с последовательностями кстати поднимается чуть ли не несколько раз в неделю, и все советуют заюзать заготовленную таблицу.
    Однако за наглядный пример рекурсивного СТЕ спасибо, наверняка кто-то найдет и воспользуется (только добавьте комментарий что это не самый быстрый способ)!

    Что касается известности способов - тут треба то, что называется rocket science, мало кто этим может похвастаться, в основном западные MVP, да и то не все. Я знаю Делайни, Ицика Бен Гана, Пола Рэндала, Стива Каса и еще может с пяток если вспомню. Остальные, так или иначе, или основывают свои исследования на их, или дополняют, хотя полно и исключений, хотя бы Алекс Кузнецов из редгейта с его книгой. Но в основном все-таки мало о чем действительно революционном можно написать =( Если обстоятельно погуглить, наверняка найдется инфа уже. Но как гласиться в посте одного из перечисленных MVP - главное при подаче информации иметь свой twist! =) так что удачи!
    Хотя

  • 23 ноября 2011, 23:40 SomewhereSomehow

    *Хотя - лишнее =))) удалите плиз!

  • 24 ноября 2011, 01:55 Алексей Дружинин

    Не удаляется - чужие комментарии не получается редактировать :)

    Спасибо за ссылку! Всё прочёл.
    ИМХО просто у каждого решения - своя область применения, и спорить особо не о чем.
    Например, если у разработчика есть только какой-нибудь db_datareader на боевой базе, и вдруг срочно понадобилась такая вот выгрузка, то, понятное дело, ни о каком создании таблицы пока речь не идёт.
    Её можно будет сделать, включить в следующий релиз базы и т. д. А отчёт нужен, скажем, через 10 минут. Тут-то CTE и пригодится, если достаточно большой таблицы в базе не сыскалось или просто выгоднее перенести нагрузку на tempdb.

    Хотя и штуки вроде

    DECLARE @N int = 1000000;

    SELECT TOP(@N) ROW_NUMBER() OVER (ORDER BY @N)
    FROM master.dbo.spt_values v1 WITH(NOLOCK)
    CROSS JOIN master.dbo.spt_values v2 WITH(NOLOCK)
    ...

    никто, понятное дело, не отменял. Были бы, опять же, права на системные таблицы.


    Бен Ган и Рендал читал. Ну это глыбищи, конечно!
    Нет, у меня, конечно, цели сильно скромнее - привести решения (от начала до конца, без отсылок к msdn, другим темам и т. п.) задач на SQL и SSIS, попадающихся в каждодневной практике, но как бы и не совсем тривиальных.

    Спасибо за комментарии по-любому! Надеюсь, следующие посты у меня получатся чуть менее очевидными :)

  • 24 ноября 2011, 15:07 SomewhereSomehow

    Оригинальный способ генерирования строк, по крайней мере я не натыкался на такой. Спасибо, за идею!

  • Такой вопрос, возможно наивный.
    Почему в СУБД не предусмотрена псевдотаблица для генерации последовательностей? Например select * from SEQ(10) даст 10 строк со значениями от 1 до 10.
    Возможно такой винигретный подход характерен для MySQL, но ведь задача действительно частая?

  • <a href="http://kovriufa.ru"> Уличные и тамбурные напольные покрытия </a> http://kovriufa.ru/

  • Отличный вариант
    _______________________________

    <a href="http://taxisatyrn.ru/group/index.php?page=6167">такси поехали официальный сайт</a>
    <a href="http://taxisatyrn.ru/group/index.php?page=12298">рассчитать поездку такси казань</a>
    <a href="http://taxisatyrn.ru/group/index.php?page=11325">слушать музыку из фильма такси 2</a>



Необходимо войти на сайт, чтобы оставлять комментарии