Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
 SQL2005: популярные задачи форума и CTE  [new]
iap
Member

Откуда: Москва
Сообщений: 47001
Здравствуйте, коллеги!
Несколько дней назад на форуме был вопрос как задвоить или затроить записи в запросе из таблицы?
Возникла мысль, а как такая задача решается в SQL2005 (у кого есть такой сервер, разумеется). По ходу дела получилось очень компактно
решить ещё несколько задач, которые то и дело возникают на нашем форуме. Короче, хочу поделиться этими решениями с интересующимися. Вполне вероятно, что многое из написанного ниже Вам хорошо известно.
Наверняка кое-что описано в каких-нибудь книжках, а, может, и на нашем форуме. Прошу прощения - искал, но не нашёл.
Во всех случаях предлагается использовать так называемое CTE (Common Table Expression), а проще говоря, конструкцию вида
WITH <CTE_Name>(<FieldList>) AS (SELECT ... UNION SELECT ...) SELECT ... FROM <CTE_Name> ...
Коротко говоря, в последнем SELECTе можно использовать имя <CTE_Name> так, как будто существует такая таблица, заполненная
SELECTом в круглых скобках. Самое интересное, что в SELECTах UNIONа, начиная со второго, тоже допустимо применять имя <CTE_Name>.
Это неизбежно приводит к появлению рекурсии. В этом случае первый SELECT UNIONа внутри скобок задаёт начальные записи перед
последующими рекурсивными итерациями, которые получаются в результате выполнения второго и последующего SELECTов UNIONа.
<CTE_Name> в них означает записи, полученные на предыдущем шаге итерации. Применение CTE для получения узлов дерева подробно
описано в BOL. Рассмотрим некоторые другие популярные задачи.
Итак:
Задача №1. Генерирование последовательности чисел, дат и т.п.
Обычно на нашем форуме предлагают создать специальную таблицу и заполнить её в цикле подходящими значениями. Если этих значений
достаточно много (например, миллион), то эту таблицу можно будет использовать в большинстве случаев и в будущем. Другой подход -
использовать уже существующую таблицу master..spt_values where type='P'. Но там только 2048 значений от 0 до 2047.
Причём это целые числа, а не, например, даты. Наконец, если последовательных значений надо немного, их можно получить в виде
подзапроса (SELECT 1 UNION SELECT 2 UNION ...). В MSSQL2005 возможно такое решение:
DECLARE @Cardinality INT, @Seed INT, @Increment INT;
SELECT @Cardinality = 10, @Seed = 5, @Increment = 3;
WITH T(ID,N) AS
(
 SELECT 1, @Seed WHERE @Cardinality >= 1
 UNION ALL
 SELECT ID + 1, N + @Increment FROM T WHERE ID < @Cardinality
)
SELECT ID, N FROM T ORDER BY ID
OPTION (MAXRECURSION 0);
Данный запрос возвращает последовательность ID с 1 до @Cardinality с шагом 1 и последовательность из @Cardinality чисел N,
начиная с @Seed и с шагом @Increment. Если в запросе, использующем CTE, не задать MAXRECURSION, то без ошибок выполнятся
только 100 итераций. MAXRECURSION n разрешает выполнить без сообщения об ошибке n итераций. MAXRECURSION 0 снимает
ограничения на уровень вложенности рекурсии. В данном случае количество итераций ограничивается условием второго SELECTа UNIONа.
Аналогично можно решить и проблему со списком дат (см., например, Нарастающая дата в запросе).
Вот запрос, возвращающий список всех дат между @FromDate и @ToDate:
DECLARE @FromDate SMALLDATETIME, @ToDate SMALLDATETIME;
SELECT @FromDate = '20050813', @ToDate = '20070121';
WITH Days(D) AS
(
 SELECT @FromDate WHERE @FromDate <= @ToDate
 UNION ALL
 SELECT DATEADD(DAY,1,D) FROM Days WHERE D < @ToDate
)
SELECT D FROM Days ORDER BY D
OPTION (MAXRECURSION 0);
Задача №2. "Размножение" записи некоторой таблицы.
Имея таблицу, про которую упоминалось в задаче №1, эту задачу решить очень просто, например, с помощью CROSS JOIN.
Соответственно, в MSSQL2005 это можно сделать без посторонних таблиц:
USE tempdb;
GO
SET NOCOUNT ON;
GO
/*Тестовая таблица, записи которой надо "размножить"*/
IF OBJECT_ID('T') IS NOT NULL DROP TABLE T;
GO
CREATE TABLE T(ID INT NOT NULL IDENTITY, S VARCHAR(100));
GO
/*Заполнение тестовой таблицы*/
DECLARE @I INT;
SET @I=0;
WHILE @I<10
BEGIN
 INSERT T(S) SELECT 'Text'+CAST(IDENT_CURRENT('T')AS VARCHAR(100));
 SET @I=@I+1;
END;
GO
/*"Размножение" записей на @Cardinality экземпляров*/
DECLARE @Cardinality INT;
SELECT @Cardinality = 3;
WITH Splitter(ID) AS
(
 SELECT 1
 UNION ALL
 SELECT ID + 1 FROM Splitter WHERE ID < @Cardinality
)
SELECT S.ID [S_ID], T.ID [T_ID], T.S
FROM Splitter S CROSS JOIN T
ORDER BY T.ID, S.ID
OPTION (MAXRECURSION 0);
GO
DROP TABLE T;
Задача №3. Получить список дат, отсутствующих в некоторой таблице.
Такой запрос требовался, например, здесь: Как выбрать отсутствующие даты?
Имея решение задачи №1, нетрудно решить и эту:
USE tempdb;
GO
SET NOCOUNT ON;
GO
/*Тестовая таблица, записи которой надо "размножить"*/
IF OBJECT_ID('T') IS NOT NULL DROP TABLE T;
GO
CREATE TABLE T(D SMALLDATETIME NOT NULL);
GO
/*Заполнение тестовой таблицы*/
DECLARE @D SMALLDATETIME;
SET @D='20060101';
WHILE @D<='20061231'
BEGIN
 IF @D NOT IN('20060118','20060119','20060228','20060301','20060313','20060722','20060724','20060725','20060726','20061222','20061231')
  INSERT T(D) VALUES(@D);
 SET @D=DATEADD(DAY,1,@D);
END;
GO
/*Получение списка отсутствующих дат*/
DECLARE @FromDate SMALLDATETIME, @ToDate SMALLDATETIME;
SELECT @FromDate = '20060101', @ToDate = '20061231';
WITH Days(D) AS
(
 SELECT @FromDate WHERE @FromDate <= @ToDate
 UNION ALL
 SELECT DATEADD(DAY,1,D) FROM Days WHERE D < @ToDate
)
SELECT D
FROM Days
WHERE D NOT IN(SELECT D FROM T)
ORDER BY D
OPTION (MAXRECURSION 0);
GO
DROP TABLE T;
Задача №4. Объединить значения строкового столбца в одну строку.
Обычно применяется недокументированный приём накопления значений в строковой переменной
(SET @S='' SELECT @S=@S+FieldName FROM TableName WHERE ... ORDER BY...)
Вариант, основанный на TSQL2005 CTE:
USE tempdb;
GO
SET NOCOUNT ON;
GO
IF OBJECT_ID('T') IS NOT NULL DROP TABLE T;
GO
/*Тестовая таблица*/
CREATE TABLE T(ID INT NOT NULL, S VARCHAR(100) NULL);
GO
/*Заполнение тестовыми данными*/
INSERT T(ID,S) VALUES(22,'мыла');
INSERT T(ID,S) VALUES(33,'раму');
INSERT T(ID,S) VALUES(11,'мама');
GO
/*Получение строки "мама мыла раму"*/
WITH Q(I,S) AS
(
 SELECT 1, CAST(S AS VARCHAR(200)) FROM (SELECT ROW_NUMBER() OVER(ORDER BY ID)[ID],S FROM T) T WHERE ID=1
 UNION ALL
 SELECT I+1, CAST(Q.S+' '+T.S AS VARCHAR(200))
 FROM Q JOIN (SELECT ROW_NUMBER() OVER(ORDER BY ID)[ID],S FROM T) T ON Q.I=T.ID-1
)
SELECT TOP 1 S FROM Q ORDER BY I DESC
OPTION (MAXRECURSION 0);
GO
DROP TABLE T;
GO
РЕЗУЛЬТАТ:
S
---------------
мама мыла раму
Небольшие пояснения. В определении CTE (внутри скобок) запрещено применять GROUP BY, ORDER BY, TOP N, агрегатные функции.
Поэтому пришлось использовать функцию ROW_NUMBER(), нумерующую строки запроса с 1, с шагом 1, в порядке, определённом в
конструкции OVER. Может, можно ещё чего-нибудь придумать?
Задача №5. Объединить значения строкового столбца в строки, соответствующие некоторому условию группировки.
Обычно в подобном случае думают о новой возможности SQL2005 - созданию пользовательской агрегатной функции конкатенации
строк на C#. Однако можно обойтись и без неё:
USE tempdb;
GO
SET NOCOUNT ON;
GO
IF OBJECT_ID('T') IS NOT NULL DROP TABLE T;
GO
/*Тестовая таблица*/
CREATE TABLE T(ID INT NOT NULL, G INT NOT NULL, S VARCHAR(100) NULL, CONSTRAINT uT UNIQUE(ID));
GO
/*Заполнение тестовыми данными*/
INSERT T(ID,G,S) VALUES(22,1,'мыла');
INSERT T(ID,G,S) VALUES(33,1,'раму');
INSERT T(ID,G,S) VALUES(11,1,'мама');
INSERT T(ID,G,S) VALUES(145,2,'у');
INSERT T(ID,G,S) VALUES(215,2,'Клары');
INSERT T(ID,G,S) VALUES(1,2,'Карл');
INSERT T(ID,G,S) VALUES(400,2,'кораллы');
INSERT T(ID,G,S) VALUES(333,2,'украл');
GO
/*Получение строк "мама мыла раму" и "Карл у Клары украл кораллы"*/
WITH Q(I,G,S) AS
(
 SELECT ID,G, CAST(S AS VARCHAR(200)) FROM (SELECT ROW_NUMBER() OVER(PARTITION BY G ORDER BY ID)[ID],G,S FROM T) T WHERE ID=1
 UNION ALL
 SELECT Q.I+1,Q.G, CAST(Q.S+' '+T.S AS VARCHAR(200))
 FROM Q JOIN (SELECT ROW_NUMBER() OVER(PARTITION BY G ORDER BY ID)[ID],G,S FROM T) [T] ON Q.G=T.G AND Q.I=T.ID-1
)
SELECT G, S FROM Q WHERE I IN (SELECT MAX(QQ.I) FROM Q QQ WHERE QQ.G=Q.G)
ORDER BY G,I
OPTION (MAXRECURSION 0);
GO
DROP TABLE T;
GO
РЕЗУЛЬТАТ:
G           S
----------- ---------------------------
1           мама мыла раму
2           Карл у Клары украл кораллы
Здесь применяется конструкция PARTITION BY, которая сбрасывает нумерацию строк в 1, как только изменяются значения перечисленных
после PARTITION BY полей.
Задача №6. Получить список позиций заданного символа в заданной строке.
DECLARE @S VARCHAR(100), @C CHAR;
SET @S='hjnfd453ijkfZguu65378setsetsetu6rsig;ipouj ghhre754we   lisetysetset75t68o7lkgyfi754reiesd46';
SET @C='i';
WITH T(P) AS
(
 SELECT CHARINDEX(@C,@S) WHERE CHARINDEX(@C,@S)>0
 UNION ALL
 SELECT CHARINDEX(@C,@S,P+1) FROM T WHERE CHARINDEX(@C,@S,P+1)>0
)
SELECT P FROM T
ORDER BY P
OPTION (MAXRECURSION 0);
GO
РЕЗУЛЬТАТ:
          P
-----------
          9
         35
         38
         58
         81
         87
Более нагляден результат немного переделанного варианта только что приведённого запроса:
DECLARE @S VARCHAR(100), @C CHAR;
SET @S='hjnfd453ijkfguu65378setsetsetu6rsig;ipouj ghhre754we   lisetysetset75t68o7lkgyfi754reiesd46';
SET @C='i';
WITH T(I,P,S,SS) AS
(
 SELECT 0, 0, @S, CAST(''AS VARCHAR(200))
 UNION ALL
 SELECT I+1, CHARINDEX(@C,S,P+1), S, CAST(SS+SPACE(CHARINDEX(@C,S,P+1)-P-1)+'^'AS VARCHAR(200))
 FROM T
 WHERE CHARINDEX(@C,S,P+1) > 0
)
SELECT S[Позиции символа 'i' в тестовой строке] FROM
(
 SELECT I,S FROM T WHERE I=0
 UNION ALL
 SELECT I,SS FROM T WHERE I>0 AND I=(SELECT MAX(I) FROM T WHERE I>0)
) T
ORDER BY I
OPTION (MAXRECURSION 0);
GO
РЕЗУЛЬТАТ:
Позиции символа 'i' в тестовой строке
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
hjnfd453ijkfguu65378setsetsetu6rsig;ipouj ghhre754we   lisetysetset75t68o7lkgyfi754reiesd46
        ^                        ^  ^                   ^                      ^     ^
Задача №7. Получить список позиций заданного строкового фрагмента в заданной строке.
DECLARE @S VARCHAR(100), @SS VARCHAR(100);
SET @S='hjnабракадабракадабракадабраfd453ij5378setsetsetu6rsig;ipoабракадабраабракадабраuj ghhre754we  абракадабра lsd46';
SET @SS='абракадабра';
WITH T(P,S) AS
(
 SELECT PATINDEX('%'+@SS+'%',@S), STUFF(@S,1, PATINDEX('%'+@SS+'%',@S),'') WHERE PATINDEX('%'+@SS+'%',@S)>0
 UNION ALL
 SELECT P+PATINDEX('%'+@SS+'%',S), STUFF(S,1, PATINDEX('%'+@SS+'%',S),'') FROM T WHERE PATINDEX('%'+@SS+'%',S)>0
)
SELECT P FROM T ORDER BY P
OPTION (MAXRECURSION 0);
GO
РЕЗУЛЬТАТ:
P
-----------
4
11
18
59
70
Как и в предыдущем случае, можно наглядно наблюдать расположение искомого фрагмента в строке:
DECLARE @S VARCHAR(MAX), @SS VARCHAR(100);
SET @S='hjnабракадабракадабракадабраfd453ij5378setsetsetu6rsig;ipoабракадабраабракадабраuj ghhre754we  абракадабра lsd46';
SET @SS='абракадабра';
WITH T(I,S,SS) AS
(
 SELECT 0, @S, CAST(''AS VARCHAR(MAX))
 UNION ALL
 SELECT I+1, STUFF(S,1, PATINDEX('%'+@SS+'%',S),''), SS+SPACE(PATINDEX('%'+@SS+'%',S)-1)+'^' FROM T WHERE PATINDEX('%'+@SS+'%',S)>0
)
SELECT S[Позиции фрагмента 'абракадабра' в тестовой строке] FROM
(
 SELECT I,S FROM T WHERE I=0
 UNION ALL
 SELECT I,SS FROM T WHERE I>0 AND I=(SELECT MAX(I) FROM T WHERE I>0)
) T
ORDER BY I
OPTION (MAXRECURSION 0);
GO
РЕЗУЛЬТАТ:
Позиции фрагмента 'абракадабра' в тестовой строке
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
hjnабракадабракадабракадабраfd453ij5378setsetsetu6rsig;ipoабракадабраабракадабраuj ghhre754we  абракадабра lsd46
   ^      ^      ^                                        ^          ^                         ^
Похожая задача обсуждалась, например, здесь (требовалось подсчитать количество вхождений фрагмента в строку).
Предложенные в указанной теме алгоритмы не могли корректно обработать строку, если несколько последних символов в конце фрагмента
совпадали с несколькими символами начала следующего фрагмента. Как показывает последний пример, такая ситуация ('абракадабракадабра')
обрабатывается правильно.
Задача №8. Получить список позиций заданного строкового фрагмента во всех строках заданного столбца таблицы.
USE tempdb;
GO
SET NOCOUNT ON;
GO
IF OBJECT_ID('T') IS NOT NULL DROP TABLE T;
GO
/*Тестовая таблица*/
CREATE TABLE T(ID INT NOT NULL, S VARCHAR(MAX) NOT NULL);
GO
/*Заполнение тестовыми данными*/
INSERT T(ID,S)
          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,'Там на неведомых дорожках'
UNION ALL SELECT 10,'Следы невиданных зверей;'
UNION ALL SELECT 11,'Избушка там на курьих ножках'
UNION ALL SELECT 12,'Стоит без окон, без дверей;'
UNION ALL SELECT 13,'Там лес и дол видений полны;'
UNION ALL SELECT 14,'Там о заре прихлынут волны'
UNION ALL SELECT 15,'На брег песчаный и пустой,'
UNION ALL SELECT 16,'И тридцать витязей прекрасных'
UNION ALL SELECT 17,'Чредой из вод выходят ясных,'
UNION ALL SELECT 18,'И с ними дядька их морской;'
UNION ALL SELECT 19,'Там королевич мимоходом'
UNION ALL SELECT 20,'Пленяет грозного царя;'
UNION ALL SELECT 21,'Там в облаках перед народом'
UNION ALL SELECT 22,'Через леса, через моря'
UNION ALL SELECT 23,'Колдун несёт богатыря;'
UNION ALL SELECT 24,'В темнице там царевна тужит,'
UNION ALL SELECT 25,'А бурый волк ей верно служит;'
UNION ALL SELECT 26,'Там ступа с Бабою Ягой'
UNION ALL SELECT 27,'Идёт, бредёт сама собой,'
UNION ALL SELECT 28,'Там царь Кащей над златом чахнет;'
UNION ALL SELECT 29,'Там русский дух... там Русью пахнет!';
GO
DECLARE @S VARCHAR(100);
SET @S='[тТ]ам';
WITH CTE(ID,Q,S,P) AS
(
 SELECT ID, 0, S, CAST(''AS VARCHAR(MAX)) FROM T
 UNION ALL
 SELECT ID, Q+1, STUFF(S,1, PATINDEX('%'+@S+'%',S),''), P+SPACE(PATINDEX('%'+@S+'%',S)-1)+'^' FROM CTE WHERE PATINDEX('%'+@S+'%',S)>0
)
SELECT ID, Q, S FROM CTE WHERE Q=0
UNION ALL
SELECT ID, Q, P FROM CTE WHERE Q=(SELECT MAX(T.Q) FROM CTE T WHERE T.ID=CTE.ID AND T.Q>0)
ORDER BY ID,Q
OPTION (MAXRECURSION 0);
GO
DROP TABLE T;
GO
РЕЗУЛЬТАТ:
ID          Q           S
----------- ----------- -------------------------------
1           0           У лукоморья дуб зелёный;
2           0           Златая цепь на дубе том:
3           0           И днём и ночью кот учёный
4           0           Всё ходит по цепи кругом;
5           0           Идёт направо - песнь заводит,
6           0           Налево - сказку говорит.
7           0           Там чудеса: там леший бродит,
7           2           ^           ^
8           0           Русалка на ветвях сидит;
9           0           Там на неведомых дорожках
9           1           ^
10          0           Следы невиданных зверей;
11          0           Избушка там на курьих ножках
11          1                   ^
12          0           Стоит без окон, без дверей;
13          0           Там лес и дол видений полны;
13          1           ^
14          0           Там о заре прихлынут волны
14          1           ^
15          0           На брег песчаный и пустой,
16          0           И тридцать витязей прекрасных
17          0           Чредой из вод выходят ясных,
18          0           И с ними дядька их морской;
19          0           Там королевич мимоходом
19          1           ^
20          0           Пленяет грозного царя;
21          0           Там в облаках перед народом
21          1           ^
22          0           Через леса, через моря
23          0           Колдун несёт богатыря;
24          0           В темнице там царевна тужит,
24          1                     ^
25          0           А бурый волк ей верно служит;
26          0           Там ступа с Бабою Ягой
26          1           ^
27          0           Идёт, бредёт сама собой,
28          0           Там царь Кащей над златом чахнет;
28          1           ^
29          0           Там русский дух... там Русью пахнет!
29          2           ^                  ^
Понятно, что в приведённых примерах поиска символа или фрагмента в строке просто решается и задача подсчёта количества их
вхождений в строку. Поскольку в CTE формируется строка на каждое такое вхождение, то достаточно в SELECTах использовать COUNT(*).
Задача №9. Получить строку, изображающую заданное целое число в виде числа в заданной системе счисления.
DECLARE @Decimal BIGINT, @Radix INT;
SET @Decimal=1876557579;
SET @Radix=16;
WITH T(D,H) AS
(
 SELECT @Decimal, CAST(''AS VARCHAR(100))
 UNION ALL
 SELECT D/@Radix, CAST(CASE WHEN D%@Radix>9 THEN CHAR(ASCII('A')+D%@Radix-10) ELSE CAST(D%@Radix AS VARCHAR(1)) END+H AS VARCHAR(100)) FROM T WHERE D>0
)
SELECT @Radix[Radix],(SELECT TOP 1 D FROM T ORDER BY D DESC)[Decimal],(SELECT TOP 1 H FROM T ORDER BY D)[Result];
GO
РЕЗУЛЬТАТ:
Radix       Decimal              Result
----------- -------------------- ---------
16          1876557579           6FD9FF0B
19 июл 06, 08:35    [2897808]     Ответить | Цитировать Сообщить модератору
 Re: SQL2005: популярные задачи форума и CTE  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31444
iap
Возникла мысль, а как такая задача решается в SQL2005 (у кого есть такой сервер, разумеется).
Знать возможности SQL2005 нужно и полезно, как и применять эти возможности.

Но есть опасность, что их будуть применять, "потому что красиво" и "потому что я об это щас прочитал" :-(

В РСУБД применение рекурсии вредно и некрасиво (для того, кто "чувствует" принципы СРУБД)

iap
Итак:
Задача №1. Генерирование последовательности чисел, дат и т.п.
Для этой задачи, как и для 3-х следующих, предлагается отказаться от таблицы с номерами, и использовать CTE.

Смотрим, к чему это приводит для 1000 записей (скрипт могу привести):
CTE: CPU:32 Reads:8003
Номера: CPU:0 Reads:2

Разница огромна - в десятки, сотни, тысячи раз. Вы готовы ради использования "красивого" CTE заменить однопроцессорный сервер на 8-ми процессорный, к примеру?

Для случая с деревом нужно сначала подумать, а не эффективнее-ли использовать более подходящие структуры?

Я для дерева использую метод селко, а вот вычисляю его с помощью рекурсивного CTE.
Разумеется, разница при чтении тоже в сотни-тысячи раз.
19 июл 06, 10:18    [2898150]     Ответить | Цитировать Сообщить модератору
 Re: SQL2005: популярные задачи форума и CTE  [new]
ChA
Member

Откуда: Москва
Сообщений: 11051
alexeyvg
В РСУБД применение рекурсии вредно и некрасиво
IMHO, рекурсия - это красиво, даже, изыскано, но, как правило, она очень прожорлива.
19 июл 06, 13:11    [2899327]     Ответить | Цитировать Сообщить модератору
 Re: SQL2005: популярные задачи форума и CTE  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31444
ChA
alexeyvg
В РСУБД применение рекурсии вредно и некрасиво
IMHO, рекурсия - это красиво, даже, изыскано, но, как правило, она очень прожорлива.
Я говорил - некрасиво для того, кто "чувствует" принципы СРУБД :-) А так, вообще, о красоте, как и о вкусах, не спорят.

РСУБД - это операции над множествами, это здесь первично.

Процедурные расширения, такие, как контроль последовательности выполнения - это уже некрасивость, хотя и неизбежность.

Если задача решается в терминах РСУБД, неужели её красиво переделать в цикл, пусть и в виде рекурсии (ведь рекурсия не что иное как цикл)?

Вот, для примера: Задача №3. Получить список дат, отсутствующих в некоторой таблице.

Что красивее - сделать цикл (в виде CTE) для получения списка дат и нахождения отсутствующих, или получить разницу пересечения двух множеств?

Или задачи с деревьями - сложные выборки будут красиво смотреться для структуры селко и убого и ужасно сложно для CTE.
19 июл 06, 15:09    [2900275]     Ответить | Цитировать Сообщить модератору
 Re: SQL2005: популярные задачи форума и CTE  [new]
ura
Member [заблокирован]

Откуда: Киев
Сообщений: 932
Я думаю данный пост смело можно помещать в статьи.

2 alexeyvg - автор нигде и не утверждает, например, что лучше использовать CTE вместо таблицы, наоборот, он перечисляет все возможные варианты, а какой из них выбирать - это дело разработчика. И знать нужно обо всех возможных методах решения
19 июл 06, 17:30    [2901244]     Ответить | Цитировать Сообщить модератору
 Re: SQL2005: популярные задачи форума и CTE  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31444
ura
какой из них выбирать - это дело разработчика. И знать нужно обо всех возможных методах решения
С этим я согласен.

И в статьи нужно поместить - хорошая иллюстрация для понимания работы и области применения CTE получилась.
20 июл 06, 10:26    [2903221]     Ответить | Цитировать Сообщить модератору
 Re: SQL2005: популярные задачи форума и CTE  [new]
AAron
Member

Откуда: Москва
Сообщений: 4324
автору - респект!
надо только обязательно указать, что решение может быть ресурсоемким.
20 июл 06, 10:55    [2903447]     Ответить | Цитировать Сообщить модератору
 Re: SQL2005: популярные задачи форума и CTE  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
AAron
автору - респект!
надо только обязательно указать, что решение может быть ресурсоемким.
Не просто указать, а и привести результаты тестирования предлагаемого варианта и "альтернативного".
20 июл 06, 12:10    [2904019]     Ответить | Цитировать Сообщить модератору
 Re: SQL2005: популярные задачи форума и CTE  [new]
sysadm2000
Member

Откуда: Москва
Сообщений: 1212
Хочу сказать спасибо IAP за этот букварь по CTE.
13 сен 06, 20:48    [3132847]     Ответить | Цитировать Сообщить модератору
 Re: SQL2005: популярные задачи форума и CTE  [new]
LR
Member

Откуда: 8P8C
Сообщений: 2423
iap
Небольшие пояснения. В определении CTE (внутри скобок) запрещено применять GROUP BY, ORDER BY, TOP N, агрегатные функции.
Поэтому пришлось использовать функцию ROW_NUMBER(), нумерующую строки запроса с 1, с шагом 1, в порядке, определённом в
конструкции OVER. Может, можно ещё чего-нибудь придумать?

Попробовал применить для этой цели оператор ANY, основательно не тестировал, но запустил два запроса (из примера "мама мыла раму")
WITH Q(I,S) AS
(
 select id,cast(s as varchar(300)) from t where id<=all(select id from t)
 union all
 select t.id, cast(q.s+' '+t.s as varchar(300)) from q join t on t.id>q.i and t.id<=all(select id from t where id>q.i)
)
SELECT TOP 1 S FROM Q ORDER BY I DESC
OPTION (MAXRECURSION 0);
WITH Q(I,S) AS
(
 SELECT 1, CAST(S AS VARCHAR(200)) FROM (SELECT ROW_NUMBER() OVER(ORDER BY ID)[ID],S FROM T) T WHERE ID=1
 UNION ALL
 SELECT I+1, CAST(Q.S+' '+T.S AS VARCHAR(200))
 FROM Q JOIN (SELECT ROW_NUMBER() OVER(ORDER BY ID)[ID],S FROM T) T ON Q.I=T.ID-1
)
SELECT TOP 1 S FROM Q ORDER BY I DESC
OPTION (MAXRECURSION 0);
в одном батче, так соотношение стоимостей у меня получилось 21 - 79, может взаправду так будет эффективней...:)

P.S. C большим уважением к трудам iap :))
20 сен 06, 15:45    [3162468]     Ответить | Цитировать Сообщить модератору
 Re: SQL2005: популярные задачи форума и CTE  [new]
LR
Member

Откуда: 8P8C
Сообщений: 2423
LR
оператор ANY

Опечатка, очевидно, что ALL :)
20 сен 06, 15:55    [3162540]     Ответить | Цитировать Сообщить модератору
 Re: SQL2005: популярные задачи форума и CTE  [new]
LR
Member

Откуда: 8P8C
Сообщений: 2423
Хе-хе...
-- 1. Query cost (relative to the batch): 7%, но выполняется около 26 сек
WITH Q(I,S) AS
(
 select id, cast([name] as nvarchar(max)) from master..sysobjects where id<=all(select id from master..sysobjects)
 union all
 select t.id, cast(q.s+', '+t.[name] as nvarchar(max)) from q join master..sysobjects t on t.id>q.i and t.id<=all(select id from master..sysobjects where id>q.i)
)
SELECT TOP 1 S FROM Q ORDER BY I DESC
OPTION (MAXRECURSION 0);
-- 2. Query cost (relative to the batch): 93%, но выполняется около 4 сек
WITH Q(I,S) AS
(
 SELECT 1, CAST([name] AS NVARCHAR(MAX)) FROM (SELECT ROW_NUMBER() OVER(ORDER BY ID)[ID],[name] FROM master..sysobjects) T WHERE ID=1
 UNION ALL
 SELECT I+1, CAST(Q.S+' '+T.[name] AS NVARCHAR(MAX))
 FROM Q JOIN (SELECT ROW_NUMBER() OVER(ORDER BY ID)[ID],[name] FROM master..sysobjects) T ON Q.I=T.ID-1
)
SELECT TOP 1 S FROM Q ORDER BY I DESC
OPTION (MAXRECURSION 0);
Относительная стоимость запроса не коррелирует с временем выполнения?

Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86) 
Apr 14 2006 01:12:25
20 сен 06, 16:46    [3162995]     Ответить | Цитировать Сообщить модератору
 Re: SQL2005: популярные задачи форума и CTE  [new]
Roman S. Golubin
Member

Откуда: 140002
Сообщений: 11541
Задача 4 без CTE:
USE tempdb;
GO
SET NOCOUNT ON;
GO
IF OBJECT_ID('T') IS NOT NULL DROP TABLE T;
GO
/*Тестовая таблица*/
CREATE TABLE T(ID INT NOT NULL, S VARCHAR(100) NULL);
GO
/*Заполнение тестовыми данными*/
INSERT T(ID,S) VALUES(22,'мыла');
INSERT T(ID,S) VALUES(33,'раму');
INSERT T(ID,S) VALUES(11,'мама');
GO
/*Получение строки "мама мыла раму"*/
select S = (select S 'data()'
            from T
            order by ID
            for xml path (''))
GO
DROP TABLE T;
GO
--
WBR, Roman S. Golubin
10 ноя 06, 12:25    [3381659]     Ответить | Цитировать Сообщить модератору
 Re: SQL2005: популярные задачи форума и CTE  [new]
Roman S. Golubin
Member

Откуда: 140002
Сообщений: 11541
Аналогично для 5-й задачи:

/*Получение строк "мама мыла раму" и "Карл у Клары украл кораллы"*/
select S = (select S 'data()'
            from T
            where G = t.G
            order by ID
            for xml path (''))
from T t
group by G
order by G
10 ноя 06, 12:30    [3381732]     Ответить | Цитировать Сообщить модератору
 Re: SQL2005: популярные задачи форума и CTE  [new]
Max-xaM
Member

Откуда: Гусь-Хрустальный
Сообщений: 556
Roman S. Golubin
Аналогично для 5-й задачи:

/*Получение строк "мама мыла раму" и "Карл у Клары украл кораллы"*/
select S = (select S 'data()'
            from T
            where G = t.G
            order by ID
            for xml path (''))
from T t
group by G
order by G


Отлично. А как аналогичное для 2000 секала?
25 янв 07, 16:09    [3694474]     Ответить | Цитировать Сообщить модератору
 Re: SQL2005: популярные задачи форума и CTE  [new]
Roman S. Golubin
Member

Откуда: 140002
Сообщений: 11541

С 2000-м не так просто - читайте FAQ, там есть примеры :)


Posted via ActualForum NNTP Server 1.3

25 янв 07, 16:46    [3694783]     Ответить | Цитировать Сообщить модератору
 Re: SQL2005: популярные задачи форума и CTE  [new]
Luchkin Dmitry
Member

Откуда: Новосибирск -> Ангарск -> Братск -> Мск
Сообщений: 1921
с птицами (^) в абракадабре фигня какая-то... или у меня глюки, или они не показывают, куда надо, кроме первой...
25 янв 07, 18:12    [3695420]     Ответить | Цитировать Сообщить модератору
 Re: SQL2005: популярные задачи форума и CTE  [new]
Max-xaM
Member

Откуда: Гусь-Хрустальный
Сообщений: 556
Roman S. Golubin

С 2000-м не так просто - читайте FAQ, там есть примеры :)


Posted via ActualForum NNTP Server 1.3


НЕТ ТАМ ТАКОГО ПРИМЕРА
13 фев 07, 12:41    [3777313]     Ответить | Цитировать Сообщить модератору
 Re: SQL2005: популярные задачи форума и CTE  [new]
Glory
Member

Откуда:
Сообщений: 104760
Max-xaM
Roman S. Golubin

С 2000-м не так просто - читайте FAQ, там есть примеры :)


Posted via ActualForum NNTP Server 1.3


НЕТ ТАМ ТАКОГО ПРИМЕРА

Смотрю в книгу - вижу фигу, да ?
13 фев 07, 12:47    [3777357]     Ответить | Цитировать Сообщить модератору
 Re: SQL2005: популярные задачи форума и CTE  [new]
Guest1988
Guest
хотелось бы еще вот такую задачку разобрать
Для заданного n без использования курсоров и циклов постройте таблицу, содержащую n записей
Пользуясь рекурсией (с глубиной вложенности не более 32) и алгоритмом умного умножения, решите задачу для n до 232. Попробуйте решить задачу с использованием табличных выражений.
13 ноя 07, 22:22    [4912174]     Ответить | Цитировать Сообщить модератору
 Re: SQL2005: популярные задачи форума и CTE  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10234
Блог
Guest1988
Попробуйте решить задачу с использованием табличных выражений.


так и не понял, что нужно, но может это:


declare @n int
set @n=232
;
WITH CTE (val)
AS
(
select 1 
union all
select Val+1 from CTE where Val<@n
)

select * from cte option (maxrecursion 0)
14 ноя 07, 06:18    [4912570]     Ответить | Цитировать Сообщить модератору
 Re: SQL2005: популярные задачи форума и CTE  [new]
nergal
Member

Откуда: Екатеринбург
Сообщений: 1015
поместите задачи в ФАК
14 ноя 07, 06:49    [4912591]     Ответить | Цитировать Сообщить модератору
 Re: SQL2005: популярные задачи форума и CTE  [new]
москит
Guest
Рекурсия - зло.
Все задачи, кроме 4-й, решаются через таблицу с последовательностью чисел, или через master..spt_values.
Да, по моему, и код понятнее получается. Например, для 3-й задачи (получение списка отсутствующих дат)
вместо
WITH Days(D) AS
(
 SELECT @FromDate WHERE @FromDate <= @ToDate
 UNION ALL
 SELECT DATEADD(DAY,1,D) FROM Days WHERE D < @ToDate
)
SELECT D
FROM Days
WHERE D NOT IN(SELECT D FROM T)
ORDER BY D
OPTION (MAXRECURSION 0);
можно написать
SELECT DATEADD(DD, n.Number, @FromDate)
FROM ManyNumbers n -- ряд целых чисел (от нуля)
WHERE n.Number BETWEEN 0 AND DATEDIFF(DD, @FromDate, @ToDate)
AND DATEADD(DD, n.Number, @FromDate) NOT IN(SELECT D FROM T)
ORDER BY 1;
14 ноя 07, 08:58    [4912726]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: SQL2005: популярные задачи форума и CTE  [new]
vehfl
Member

Откуда:
Сообщений: 397
Roman S. Golubin
Аналогично для 5-й задачи:

/*Получение строк "мама мыла раму" и "Карл у Клары украл кораллы"*/
select S = (select S 'data()'
            from T
            where G = t.G
            order by ID
            for xml path (''))
from T t
group by G
order by G

а это только у меня этот запрос возвращает
автор

Карл мама мыла раму у Клары украл кораллы
Карл мама мыла раму у Клары украл кораллы
9 сен 09, 16:19    [7638044]     Ответить | Цитировать Сообщить модератору
 Re: SQL2005: популярные задачи форума и CTE  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
А как по мне, можно смело добавлять в FAQ
PS рекурсия - божественна, ничего личного :)
PSPS iap - большая умница, па добраму. Честно решил все задачи через свои любимые CTE и не поленился ,все ето оформить и грамотно задокументировать. Понятно,что половину задач можно да и решалось всегда другими методами,но ето вовсе не говорит о том, что вот нужно дружно доказыватьчто можно по другому.
Спасибо.
-------------------------------------
Jedem Das Seine
9 сен 09, 16:59    [7638304]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить