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

Откуда:
Сообщений: 34
Занимаюсь созданием адаптера для MS SQL Server (сейчас есть PostgreSQL и Oracle), соответственно возникло несколько ситуаций, которые я так и не смог найти как решить в MS SQL:

1.
CREATE OR REPLACE FUNCTION notZero(anyelement) RETURNS anyelement AS
$$
    SELECT CASE WHEN $1 > -0.0005 AND $1 < 0.0005 THEN NULL ELSE $1 END;
$$ LANGUAGE 'sql' IMMUTABLE;


Своего рода generic в PostgreSQL. То есть нужна функция, которая если модуль числа меньше
какого то маленького значения возвращает NULL, а не 0. Проблема в том что тип заранее неизвестен и необходимо чтобы тип не "терялся". Конечно можно обойти, но все же...

2.
CREATE OR REPLACE FUNCTION recursion(rectable text, initial text, step text, VARIADIC params char[]) RETURNS SETOF RECORD AS
$$
    DECLARE inserted INT;
    DECLARE toggler BOOLEAN;
    DECLARE stepnext TEXT;
    DECLARE nextrectable TEXT;
    BEGIN

    nextrectable = 'nt' || rectable || 'it';
	stepnext = replace(step, rectable, nextrectable);
	EXECUTE 'CREATE TEMP TABLE ' || rectable || ' AS ' || initial USING params;
	GET DIAGNOSTICS inserted = ROW_COUNT;
	EXECUTE 'CREATE TEMP TABLE ' || nextrectable || ' AS SELECT * FROM ' || rectable || ' LIMIT 0';

	WHILE inserted > 0 LOOP
		IF toggler THEN
			RETURN QUERY EXECUTE 'SELECT * FROM ' || nextrectable;
			EXECUTE 'INSERT INTO ' || rectable || ' ' || stepnext USING params;
			GET DIAGNOSTICS inserted = ROW_COUNT;
			EXECUTE 'DELETE FROM ' || nextrectable;
			toggler = FALSE;
		ELSE
			RETURN QUERY EXECUTE 'SELECT * FROM ' || rectable;
			EXECUTE 'INSERT INTO ' || nextrectable || ' ' || step USING params;
			GET DIAGNOSTICS inserted = ROW_COUNT;
			EXECUTE 'DELETE FROM ' || rectable;
			toggler = TRUE;
		END IF;
	END LOOP;

	EXECUTE 'DROP TABLE ' || nextrectable;
	EXECUTE 'DROP TABLE ' || rectable;
    END
 $$ LANGUAGE 'plpgsql' VOLATILE COST 1000000;


Это организация рекурсивного CTE "вручную". Нужно для двух целей 1) обойти ограничение использования рекурсивной таблицы один раз (благо в отличие от Postgres, здесь можно несколько UNION ALL использовать не так критично). 2) Борьба с циклами, проблема в том что в рекурсивном CTE нельзя GROUP BY'и использовать с CTE, а значит при поиске цикла (если добавлять колонку, в которой собирать путь) на каждой итерации будет не количество вершин, а количество путей. А оно растет экспоненциально.

Соответственно тут целых 2 вопроса:

RETURNS SETOF RECORD нет конструкции (своего рода generic в таблицах). Нужно в явную указывать структуру таблицы, а значит создавать такие функции для каждой структуры таблиц :(

VARIADIC - динамическое число параметров f(int... a) тоже не нашел. (но это как раз легко обойти)
12 окт 12, 15:29    [13309115]     Ответить | Цитировать Сообщить модератору
 Re: Функциональные возможности MS SQL Server  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Bond__JamesBond,

у NULLа самого по себе типа нет. Так что
CASE WHEN ABS(@X)>=@Epsilon THEN @X END
вернёт тот же тип, что и имеет @X
12 окт 12, 15:33    [13309169]     Ответить | Цитировать Сообщить модератору
 Re: Функциональные возможности MS SQL Server  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
iap
Bond__JamesBond,

у NULLа самого по себе типа нет. Так что
CASE WHEN ABS(@X)>=@Epsilon THEN @X END
вернёт тот же тип, что и имеет @X
И есть тип
SQL_VARIANT
12 окт 12, 15:39    [13309211]     Ответить | Цитировать Сообщить модератору
 Re: Функциональные возможности MS SQL Server  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Кстати, непонятно, что мешает интерпретировать вышеприведённый CASE как разновидность функции.
Принимаем текст вызова notZero(anyelement) - заменяем его на CASE
А в другую сторону и заменять ничего не надо, ибо CASE стандартен и всеми поддерживается.
12 окт 12, 15:43    [13309257]     Ответить | Цитировать Сообщить модератору
 Re: Функциональные возможности MS SQL Server  [new]
Bond__JamesBond
Member

Откуда:
Сообщений: 34
iap,

Ну в общем-то да, но так как параметр 2 раза используется, генерируемая строка может в 2 раза вырасти...

CREATE FUNCTION notZero (@X SQL_VARIANT) RETURNS SQL_VARIANT AS
BEGIN
    RETURN CASE WHEN ABS(CAST(@X AS numeric(17,4))) > 0.0005 THEN @X END;
END


В общем-то так вроде работает, так что снимается пока...
12 окт 12, 15:50    [13309303]     Ответить | Цитировать Сообщить модератору
 Re: Функциональные возможности MS SQL Server  [new]
Bond__JamesBond
Member

Откуда:
Сообщений: 34
Bond__JamesBond,

Правда, конечно потом могут вопросы начаться в рекурсивных запросах, UNION ALL, JDBC драйверах и т.п. (когда notZero(1) будет SQL_VARIANT, а не int), но если что явный cast вставлю снаружи notZero.
12 окт 12, 15:53    [13309330]     Ответить | Цитировать Сообщить модератору
 Re: Функциональные возможности MS SQL Server  [new]
Bond__JamesBond
Member

Откуда:
Сообщений: 34
iap,

Кстати а MIN, MAX в MS SQL есть? В смысле MAX(4,2,5)..
12 окт 12, 15:58    [13309372]     Ответить | Цитировать Сообщить модератору
 Re: Функциональные возможности MS SQL Server  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Bond__JamesBond
Bond__JamesBond,

Правда, конечно потом могут вопросы начаться в рекурсивных запросах, UNION ALL, JDBC драйверах и т.п. (когда notZero(1) будет SQL_VARIANT, а не int), но если что явный cast вставлю снаружи notZero.
Явный CAST так или иначе рано или поздно понадобится для SQL_VARIANT.
12 окт 12, 16:00    [13309386]     Ответить | Цитировать Сообщить модератору
 Re: Функциональные возможности MS SQL Server  [new]
komrad
Member

Откуда:
Сообщений: 5739
Bond__JamesBond
iap,

Кстати а MIN, MAX в MS SQL есть? В смысле MAX(4,2,5)..


первоисточник
12 окт 12, 16:02    [13309410]     Ответить | Цитировать Сообщить модератору
 Re: Функциональные возможности MS SQL Server  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Только я не понял задачу.
Есть текст запроса на PostgreSQL, а надо его преобразовать в текст запроса на Transact SQL?
Тогда зачем нужна функция, а не просто CASE?
12 окт 12, 16:03    [13309414]     Ответить | Цитировать Сообщить модератору
 Re: Функциональные возможности MS SQL Server  [new]
Bond__JamesBond
Member

Откуда:
Сообщений: 34
iap,

Нет, запрос генерируется из некоторой абстрактной структуры.

Просто если есть скажем выражение CASE WHEN w1 THEN e1 WHEN w2 THEN e2 ELSE e3 END, то с notZero - это notZero(CASE WHEN w1 THEN e1 WHEN w2 THEN e2 ELSE e3 END), а без CASE ABS(CASE WHEN w1 THEN e1 WHEN w2 THEN e2 ELSE e3 END) > 0.0005 THEN (CASE WHEN w1 THEN e1 WHEN w2 THEN e2 ELSE e3 END) END, то есть в 2 раза больше
12 окт 12, 16:08    [13309465]     Ответить | Цитировать Сообщить модератору
 Re: Функциональные возможности MS SQL Server  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Bond__JamesBond
iap,

Кстати а MIN, MAX в MS SQL есть? В смысле MAX(4,2,5)..
SELECT (SELECT MAX(V) FROM(VALUES(4),(2),(5))T(V))
То, что в скобках, - это похоже на то, про что Вы говорите.
Вместо 4,2,5 могут стоять поля таблиц внешнего запроса, например.
Только FROM ещё дописать надо, разумеется.
А максимальное значение подсчитается для каждой строки этого запроса.
12 окт 12, 16:08    [13309470]     Ответить | Цитировать Сообщить модератору
 Re: Функциональные возможности MS SQL Server  [new]
Bond__JamesBond
Member

Откуда:
Сообщений: 34
komrad,

Да я там искал, нашел много странных функций, но не те что надо. Причем ISNULL скажем есть отдельной функций, хотя его тоже CASE'ом можно реализовать.
12 окт 12, 16:09    [13309481]     Ответить | Цитировать Сообщить модератору
 Re: Функциональные возможности MS SQL Server  [new]
Bond__JamesBond
Member

Откуда:
Сообщений: 34
iap,

Жестковато :) Тогда уж лучше SQL_VARIANT использовать.
12 окт 12, 16:12    [13309512]     Ответить | Цитировать Сообщить модератору
 Re: Функциональные возможности MS SQL Server  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Ещё один бесполезный универсальный монстр ©

notZero - бесполезен, как в принципе и recursion.
И скорее от непонимания места и принципов.

Устал это объяснять.
12 окт 12, 18:01    [13310343]     Ответить | Цитировать Сообщить модератору
 Re: Функциональные возможности MS SQL Server  [new]
Bond__JamesBond
Member

Откуда:
Сообщений: 34
Mnior,

Готов услышать ваш вариант поиска цикла в графе...

С notZero согласен - технический момент, но с рекурсией как раз абсолютно искусственное ограничение.
12 окт 12, 22:12    [13311639]     Ответить | Цитировать Сообщить модератору
 Re: Функциональные возможности MS SQL Server  [new]
SIMPLicity_
Member

Откуда: (((@)))
Сообщений: 8877
Объясните, как формируются подобные конструкции:

iap
...
SELECT (SELECT MAX(V) FROM(VALUES(4),(2),(5))T(V))
Вместо 4,2,5 могут стоять поля таблиц внешнего запроса, например.
Только FROM ещё дописать надо, разумеется.

...


Безусловно, йа валенок, но из синтаксиса, имхо, не могу вывести такого!... Но ведь, мать его!, работает!... Ааааааа......
13 окт 12, 00:23    [13312143]     Ответить | Цитировать Сообщить модератору
 Re: Функциональные возможности MS SQL Server  [new]
sql server values clause 2008
Guest
загугливай на sql server values clause 2008.
первое же что выпадает все такое разъясняет
примерец
13 окт 12, 01:17    [13312299]     Ответить | Цитировать Сообщить модератору
 Re: Функциональные возможности MS SQL Server  [new]
SIMPLicity_
Member

Откуда: (((@)))
Сообщений: 8877
sql server values clause 2008,

Да блин, я понимаю про селект и вэльюз... Но откуда следует такая конструкция где опущено всё то, во что вставляется продолжение (параметрами стесняюсь назвать) предиката VALUES ?!
13 окт 12, 01:23    [13312310]     Ответить | Цитировать Сообщить модератору
 Re: Функциональные возможности MS SQL Server  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
SIMPLicity_ вы чё прикалываетесь?
Bond__JamesBond
С notZero согласен - технический момент
Не знаю что вы подразумевает под этим термином.
Оно не нужно по конструктивно-идеологическим причинам.
Преобразования могут быть только линейными, а данные чистыми. А для контроля - функции тормозное решение - используются явные выражения, а рулесы выпиливают.
Т.е. если такое будет в запросах то руки точно оторвут.
Bond__JamesBond
Готов услышать ваш вариант поиска цикла в графе...
Я много могу показать недостатков этого языка.
Но форуме много приводил. Отправная точка
Если вам нужна полная функциональность и красота, то полноценно логику первого порядка поддерживает только пролог.
Скуль это сильно ограниченная его версия заточенная на больших объёмах, нет скорее на конкурентном доступе с транзакционной целостностью.
Но главное это СУБД, а не решатель всего.
Bond__JamesBond
но с рекурсией как раз абсолютно искусственное ограничение.
Не думаю. На самом деле многие ограничения стоят по нескольким причинам:
1. Возникают очень много неразрешимых ситуаций
2. Позволяет писать вещи не свойственные СУБД
3. Взаимо исключающие параграфы - которые на первый взгляд не видны
4. Долбаная обратная совместимость

Изначально в CTE было позволительно намного больше, но были выявлена дыра в модели и её вот так закрыли.

Bond__JamesBond , это не императивный язык программирования, а декларативный. И нужно чтобы в нём работали все его законы по максимому.
CTE может иногда и вывертываться на изнанку при оптимизации.
13 окт 12, 01:57    [13312360]     Ответить | Цитировать Сообщить модератору
 Re: Функциональные возможности MS SQL Server  [new]
Bond__JamesBond
Member

Откуда:
Сообщений: 34
Mnior,

Я все понимаю, но прикол в том, что описанная конструкция в Postgres работает, и работает достаточно эффективно.

И кстати на фоне MSSQL и Postgres забавно смотрится Oracle, который пытается наоборот разрешить все, даже то что плохо работает (например MATERIALIZED VIEW с FAST REFRESH в куче случаев) и как видим неплохо живет.
13 окт 12, 10:16    [13312603]     Ответить | Цитировать Сообщить модератору
 Re: Функциональные возможности MS SQL Server  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Bond__JamesBond
Я все понимаю, но прикол в том, что описанная конструкция в Postgres работает, и работает достаточно эффективно.
Эффективно?
Вы понимаете разницу между эффективно и быстро?
Быстро? Да, очень. Эффективно - нет, а для сложных запросов - просто песец.
Bond__JamesBond
забавно смотрится Oracle, который пытается наоборот разрешить все, и как видим неплохо живет.
Плохо живёт? Вы о чём?
Посмотрите вокруг, миллиарды хомячков не ведают что творят, а коровьего мяса больше человечества - венец эволюции?

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

Да, ваш каменный топор острее и надёжнее мушкета. Мне пох. Мне главное чтобы язык становился более строгим и выразительным, а компилятор делал более оптимальный план. А среды разработки наставляли на путь истинный и ставили палки в колёса императивистам. Жаль что это чертовски медленно идёт - что не удивительно, посмотрев на нас всех.
13 окт 12, 13:37    [13313035]     Ответить | Цитировать Сообщить модератору
 Re: Функциональные возможности MS SQL Server  [new]
Bond__JamesBond
Member

Откуда:
Сообщений: 34
Mnior,

Вы так и не ответили на мой вопрос. Задача классификаторов, а соответственно в том числе поиска цикла, есть в любой доменной логике. Вопрос как быстро узнать есть ли там цикл, не "эмулируя" по сути CTE, создавая временные таблицы и т.п.
13 окт 12, 15:24    [13313323]     Ответить | Цитировать Сообщить модератору
 Re: Функциональные возможности MS SQL Server  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Bond__JamesBond
Задача классификаторов, а соответственно в том числе поиска цикла, есть в любой доменной логике.
С чего вы решили что СУБД должны решать данный вопрос? Они точно не покрывают всю доменную логику.

Повторяю, можно написать запрос, при котором CTE сработает на ура, а ваша консрукция помрёт.
Скуль может логически протянуть фильтры (к примеру) по связкам и применить их в нужном месте, т.е. заранее (сделав более эффективный план). Под этим я и подразумевал - вывернуть на изнанку. Эта логика не сработает для вашей закрученной кишко-дробилке. Ибо вы заострили внимание на последовательности действий, что убивает логику (прозрачность декларативизма).
13 окт 12, 16:31    [13313501]     Ответить | Цитировать Сообщить модератору
 Re: Функциональные возможности MS SQL Server  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Кстати, вы не совсем правы на счёт того, что нельзя делать GROUP BY. Некоторые (не все) ограничения обходятся использованием функций, в которых можно делать и группировки и LEFT JOIN и т.п.
Да, неудобно, но реально.
13 окт 12, 16:44    [13313540]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить