MSSQL : Как использовать row_number() в фильтре where

Надеюсь меня не лишат моего маленького звания MSTS....

Сегодня пока решал проблему отправки сообщений, наткнулся на то что row_number() нельзя напрямую использовать в фильтре where.

Честно говоря, когда мною использовалась нумерация, она обычно лежала где-нибудь в подзапросах или CTE, а тут хотел все и сразу, на что MS SQL сказал "неа....".

Собственно если вы попытаетесь выполнить такой код:

1. USE AdventureWorks
2. GO
3.  
4. SELECT ROW_NUMBER() OVER (
5.         ORDER BY addressline1
6.         ) AS rowNum
7.     ,addressline1
8.     ,city
9. FROM person.address
10. WHERE rowNum > 3;

* This source code was highlighted with Source Code Highlighter.

То получите такую ошибку:

Server: Msg 207, Level 16, State 1, Line
6 Invalid column name 'rowNum'

А решать проблему будем по старинке.

Вариант первый (подзапрос):

1. SELECT *
2. FROM (
3.     SELECT ROW_NUMBER() OVER (
4.             ORDER BY addressline1
5.             ) AS rowNum
6.         ,addressline1
7.         ,city
8.     FROM person.address
9.     ) AS x
10. WHERE rowNum > 3;

* This source code was highlighted with Source Code Highlighter.

Вариант второй (CTE):

1. WITH x (
2.     rowNum
3.     ,addressline1
4.     ,city
5.     )
6. AS (
7.     SELECT ROW_NUMBER() OVER (
8.             ORDER BY addressline1
9.             ) AS rowNum
10.         ,addressline1
11.         ,city
12.     FROM person.address
13.     )
14. SELECT *
15. FROM X
16. WHERE rowNum > 3;

* This source code was highlighted with Source Code Highlighter.

MSSQL : Расчет нарастающим итогом, без курсора

Обожаю классические задачи, вот и сегодня наткнулся на одну из них.

Суть была такова:

Сейчас переписываем систему оповещений о результатах выполнения процедур.

Результат кладется в таблицы и с использованием sp_send_dbmail рассылается адресатам.

Но есть одна проблема, рассылаемый объем не может превышать 1000000 байт (ограничение MS SQL).

Ладно ок, SQL не хочет, тогда надо вводить ограничение, а как?

Имперический метод себя не оправдает, так как объем вычисляется по суммарной длине строк подзапроса (про запрос см. MSDN) и ограничение по ширине строки тоже, тогда нам необходимо заранее рассчитать: а сколько байт будет наше сообщение.

Обратившись к статистике и финансовому учету (ага забавно как эти дисциплины помогают решать проблемы), можно вспомнить о таком термине как "нарастающий итог" (google расскажет о методе :) ).

Так давайте решим задачу "нарастающего итога"

Пусть у нас есть таблица только с одной ячейкой самого сообщения, например:

1. CREATE TABLE msgbodytosend
2.  (
3.      mgsstring VARCHAR(max)
4.  );

* This source code was highlighted with Source Code Highlighter.

Первый вариант - использование одного запроса.

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

Дальше используя произведение (INNER JOIN) таблиц и суммирование длин, получаем необходимый результат.

Подзапросы я предпочитаю оборачивать в CTE выражения. На выходе получаем следующий код:

1. WITH
2. cte_msgbody AS (
3.     SELECT row_number() (ORDER BY msgstring) row
4.         , msgstring
5.         , len(msgstring) msgstring_len
6.     )
7. SELECT
8.     mb_l.row
9. ,  mb_l.msgstring
10. ,  sum(mb_r.msgstring_len) Running_Total
11. FROM cte_msgbody mb_l
12. INNER JOIN cte_msgbody mb_r ON mb_l >= mb_r
13. GROUP BY
14. mb_l.row
15.     , mb_l.msgstring
16. HAVING sum(mb_r.msgstring_len) < 1000000

* This source code was highlighted with Source Code Highlighter.

Второй вариант разбить решение на составные части.

В данном случае мы убираем CTE выражение и делаем предварительный расчет во временной таблице.

1. -- Удалим временную таблицу если вдруг она использовалась ранее в тексте
2. IF object_id('tempdb..#msgbody')
3.     DROP TABLE #msgbody;
4.  
5. -- И создадим ее за ново.
6. CREATE TABLE #msgbody (
7.     row INT identity(1, 1) PRIMARY KEY NOT NULL
8.     , msgstring VARCHAR(max) NULL
9.     , msgstring_len INT NULL
10.     , running_total INT NULL
11.     );
12.  
13. -- Заполняем таблицу
14. INSERT INTO #msgbody (
15.     msgstring
16.     , msgstring_len
17.     )
18. SELECT msgstring
19.     , len(msgstring)
20. FROM msgbodytosend
21.  
22. -- Теперь сделаем расчет
23. UPDATE mb
24. SET running_total = (
25.         SELECT sum(msgstring_len)
26.         FROM #msgbody mb_i
27.         WHERE mb_i.row <= mb.row
28.         )
29. FROM #msgbody mb
30. -- Добавим небольшое ограничение, этот кусок можно улучшить
31. WHERE (
32.         SELECT sum(msgstring_len)
33.         FROM #msgbody mb_i
34.         WHERE mb_i.row <= mb.row
35.         ) < 1000000

* This source code was highlighted with Source Code Highlighter.

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

MSSQL : Удаление не цифровых символов в строке

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

1. CREATE FUNCTION dbo.RemoveChars(@Input varchar(1000))
2.     RETURNS VARCHAR(1000)
3. BEGIN
4.     DECLARE @pos INT
5.     SET @Pos = PATINDEX('%[^0-9]%',@Input)
6.     WHILE @Pos > 0
7.     BEGIN
8.         SET @Input = STUFF(@Input,@pos,1,'')
9.         SET @Pos = PATINDEX('%[^0-9]%',@Input)
10.     END
11.     RETURN @Input
12. END

Но, так как мы находимся в среде, SQL в моем случае MSSQL, то было найти решение не использующее скалярную функцию. Такое решение было найдено тут (http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/06eb934c-4082-4d12-ab9b-5fbe68855061/) Вот текст изначального примера:

1. DECLARE @test TABLE(x VARCHAR(20));
2. INSERT INTO @test (x)
3.     SELECT '+91 (876) 098 6789' UNION ALL
4.     SELECT '1-567-987-7655' UNION ALL
5.     SELECT 'FR5-5105';
7. WITH
8.     Num1 (n) AS (SELECT 1 UNION ALL SELECT 1),
9.     Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
10.     Num3 (n) AS (SELECT 1 FROM Num2 AS X, Num2 AS Y),
11.     Num4 (n) AS (SELECT 1 FROM Num3 AS X, Num3 AS Y),
12.     Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num4),
13.     Cleaner AS (
14.         SELECT x
15.         ,   (SELECT
16.                 CASE
17.                     WHEN SUBSTRING(x, n, 1) NOT LIKE '[^0-9]'
18.                         THEN SUBSTRING(x, n, 1)
19.                     ELSE ''
20.                 END + ''
21.             FROM Nums
22.             WHERE n <= LEN(x)
23.             FOR XML PATH('')) AS x_clean
24.         FROM @test)
25. SELECT x, x_clean
26. FROM Cleaner;

1. /*
2. x x_clean
3. -------------------- -------------
4. +91 (876) 098 6789 918760986789
5. 1-567-987-7655 15679877655
6. FR5-5105 55105
7. */

На мой взгляд просто замечательный подход, но что нам делать если такая задача будет повторяться?! Не переписывать же под каждую задачу этот кусок (мне надо было 8 полей очистить в одном единственном запросе) И тут на помощь приходят табличные функции

1. CREATE FUNCTION fnt_RemoveNonNumericChars (@x varchar(50))
2.     RETURN TABLE
3. AS
4. RETURN (
5.     WITH
6.         Num1 (n) AS (SELECT 1 UNION ALL SELECT 1),
7.         Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
8.         Num3 (n) AS (SELECT 1 FROM Num2 AS X, Num2 AS Y),
9.         Num4 (n) AS (SELECT 1 FROM Num3 AS X, Num3 AS Y),
10.         Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num4),
11.         Cleaner AS (
12. SELECT @x [x]
13. ,    (SELECT
14.         CASE
15.             WHEN SUBSTRING(@x, n, 1) NOT LIKE '[^0-9]'
16.                 THEN SUBSTRING(@x, n, 1)
17.             ELSE ''
18.         END + ''
19.     FROM Nums
20.     WHERE n <= LEN(@x)
21.     FOR XML PATH('')) AS x_clean
22.     )
23. SELECT x, x_clean
24. FROM Cleaner;
25. )

И вот собственно вариант использования:

1. DECLARE @test TABLE(x VARCHAR(20));
3. INSERT INTO @test (x)
4.     SELECT '+91 (876) 098 6789' UNION ALL
5.     SELECT '1-567-987-7655' UNION ALL
6.     SELECT 'FR5-5105';
8. SELECT
9.     t.x
10. ,   vv.x
11. ,   vv.x_clean
12. FROM @test t
13.     CROSS APPLY fnt_RemoveNonNumericChars(t.x) vv

И результат выполнения:

1. t.x vv.x vv.x_clean
2. -------------------- -------------------- -------------
3. +91 (876) 098 6789 +91 (876) 098 6789 918760986789
4. 1-567-987-7655 1-567-987-7655 15679877655
5. FR5-5105 FR5-5105 55105

ВАЖНО!!! Если будете использовать вариант с XML имейте в виду, порядок цифр может перемешаться, наткнулся на эту проблему буквально на следующий день.
В принципе мне не очень нравится текущий вариант нумерации, но это уже лирика, ее легко можно поменять, это позволит сэкономить еще несколько тактов, особенно на больших числах.