Комментарии 49
Подзапросы и джойны в общем случае невзаимозаменяемы.
Наиболее близким функциональным аналогом подзапроса является кросс-апплай, который, как и все апплаи, имеет в области видимости уже подвыбранные данные, но при этом: а) как бы сплющивает запрос по количеству уровней вложенности, и главное б) даёт возможность выбрать сразу несколько скаляров одним подзапросом
Приведённые примеры не эквивалентны с точки зрения вариантов использования — все, что требует T-SQL-конструкций, нельзя использовать внутри SQL-статементов (например, во вьюхах, инлайн-TVF итд)
И ещё — накладные расходы могут быть существенно нелинейными — надо понимать, какой метод лучше для 10, 1000, 1000000 записей — лидеры и аутсайдеры на разных размерах могут коренным образом поменяться (вплоть до полного зависания)
Спасибо, хорошая справочная подборка.
Только, насколько я знаю cte есть только в sql server, а 4 практически эквивалентно 3.
Wikipedia утверждает следующее:
Common table expressions are supported by Teradata, DB2, Firebird, Microsoft SQL Server, Oracle (with recursion since 11g release 2), PostgreSQL (since 8.4), MariaDB (since 10.2), MySQL (since 8.0), SQLite (since 3.8.3), HyperSQL and H2 (experimental).
CTE в PostgreSQL точно есть:
https://postgrespro.ru/docs/postgrespro/9.5/queries-with
и в MySQL тоже есть:
https://dev.mysql.com/doc/refman/8.0/en/with.html
Необходимый инструмент – оконные функцииВы же сами ниже показываете, что оконные функции не являются необходимым инструментом.
А вариант, названный «Декартово произведение», не является таковым, в нем просто условие соединения таблиц перенесено в секцию WHERE, что для большинства СУБД никак не влияет на выполнение запроса. Декартовым произведением это было бы, если бы условия соединения таблиц не было совсем или оно было постоянно истинным (например, 1=1).
Добавлю, что решение с переменными в MySQL является документированным и применяется уже давным давно.
Оконные функции и cte есть в SQL server 2005.
Нарастающий (накопительный) итог долго считался одним из вызовов SQL.
Что удивительно, поскольку Кодд в рамках «Великого спора» 1974 года победил Бахмана как раз на всяких бухгалтерских задачках (где-то у Дейта есть эта история).
Но я однажды написал расчет нарастающего итога на SPARQL и сам удивился, как все просто. Требуются только базовые конструкции, нет даже подзапросов.
INSERT DATA {
ex:st ex:order 1 ;
ex:value 1 .
ex:nd ex:order 2 ;
ex:value 1 .
ex:rd ex:order 3 ;
ex:value 2 .
}SELECT (SAMPLE(?v1) AS ?v) (SUM(?v2) AS ?acc) {
?e1 ex:order ?o1 ; ex:value ?v1 .
?e2 ex:order ?o2 ; ex:value ?v2 .
FILTER (?o1 >= ?o2)
} GROUP BY ?e1 ORDER BY ASC(SAMPLE(?o1))Или так:
INSERT DATA {
ex:data ex:values (1 1 2)
}SELECT (SAMPLE(?v2) AS ?v) (SUM(?v1) AS ?acc) {
ex:data ex:values/rdf:rest* ?e1 .
?e1 rdf:first ?v1 .
?e1 rdf:rest* ?e2 .
?e2 rdf:first ?v2 .
} GROUP BY (?e2) ORDER BY(COUNT(?e1))До чего приятный язык. Некоторые, впрочем, оконные функции в него все же впиливают.
Было бы любопытно увидеть расчет нарастающего итога на Cypher или Gremlin. Спасибо вам за статью.
Что удивительно, поскольку Кодд в рамках «Великого спора» 1974 года победил Бахмана как раз на всяких бухгалтерских задачках (где-то у Дейта есть эта история).
Не уверен что для бухгалтеров важен нарастающий итог. Скорее он использовался в ручном варианте для облегчения расчетов и используется в Excel чтобы проверить что не промахнулся в диапазоне ячеек для итогов.
Что касается вызова — полностью согласен, что нарастающий итог чисто синтаксически легко реализуется подзапросом даже без дополнительных фич на чистом sql. Хотя это будет очень непроизводительно.
Если говорить о том что не решено в sql и важно для бухгалтера — 1) быстрое получение оборотов и остатков без перелопачивания всех данных от 1970 года и по сегодняшний день (есть же индексы, почему по аналогии не сделать сумматоры?:
2) для наименований товаров, материалов обычно есть назовем его "основное" наименование, такое как оно прописывается в технической документации или в стандартах, и назовем его "текущее" название — которое указано в документах: договорах, накладных и т.п. Так вот бухгалтеру важны оба наименования, а sql по правилам нормализации стремиться их свести к одному
3) данные с "историей" — цены, нормы расхода материалов, трудовые нормативы обычно изменяются во времени
Конечно, все это можно раелизовать на уровне приложения, однако такая реализация резко увеличивает (в разы) структуру данных. Поэтому я с большим уважением отношусь к 1с, которая за кадром реализовала как раз механизмы нужные для бухгалтера (регистры, исторические данные)
2. Никто же не заставляет нормализировать в край. Да и да — если два поля не сводятся к одному то SQL не будет к этому стремиться. Или я неправильно понял задачу?
3. В чём проблема? )
ОК, спасибо, интересно!
Я же, возможно, что-то напутал, и как «бухгалтерская» задача мне запомнилась вот эта:
The sample data base includes information about persons (an identification number, name, birth-date, and salary), medical histories (identification number of person, absent-from-date, absent-to-date, disease, and comment), education (identification number of person, degree, name of university, start-date at university, stop-date at university), jobs (job number, identification number of person who did the job, actual start-date, actual stop-date, and performance rating), machines (machine number, machine type), schedules (job number, identification number of person assigned, scheduled start-date, scheduled stop-date), skills (skill number, skill description), the possession of skills by persons (identification number of person, skill number), the alternative skills needed to operate machines (machine number, skill number).
The sample application can be stated as follows: given machine X, a job number Y, the desired start-date A for the job, and the desired stop-date B, find the identification number of a person who has a skill appropriate for the operation of machine X, and who is not scheduled at all between date A and data B; schedule this person, if one is located.
Предложенное Коддом решение есть в статье Interactive support for non-programmers (сама задача не его). Пересказ статьи есть в шестой главе недавно вышедшей книги Дейта Codd and Relational Theory.
…
я бы добавил ещё «данные вводятся вручную» (соответственно не всегда консистентные) и «забирать данные надо из трёх дублирующих систем», проверяя валидность другими системами.
Вспоминается пословица англичан что у кого в руках молоток все проблемы воспринимает как гвозди. От исходных условий так и веет готовой реляционной структурой. А постановка задачи противоположна реальной практике. Когда на интересует не кто свободен для загрузки оборудования а как составить расписание да и ещё оптимальное. Что касается существа вопроса, хватит ли реляционных данных для назначения рабочего на операцию. С точки зрения далёкого от производства человека хватит. Смотришь на профессию рабочего и операцию а также чтобы разряд работы был равен разряду рабочего и все отлично. Но реальной практике назначение рабочего на операцию может иметь больше переменных. Например если рабочий делал операцию или аналогичную раньше то его предпочтительно назначить на эту операцию повторно. Это конечно совершенно не умаляет значение Дейта и sql.
Основная проблема всех этих решений в низкой производительности. Это не велика беда, если мы материализуем таблицу с результатом (но ведь всё равно хочется большей скорости!). Дальнейшие методы куда более эффективны
От синтаксиса в декларативном языке не так уж много зависит. Оконные функции — это вариант синтаксиса. Почему вы думаете тут возможна значительная разница в производительности? Сдается мне планы выполнения должны быть выбраны оптимизатором очень похожие.
Производительность запроса определяется узкими местами базы, и в основном самым тяжелым — поднятием данных с хранилища. Как бы вы не написали свой запрос, коррелированными подзапросами ли, оконными функциями ли, сте и тп, table scan/seek будет первой и наиболее дорогостоящей операцией, стоимость которой мало зависит от синтаксиса, а в основном от грамотной фильтрации, индексов и прочей структуры хранения данных.
К сожалению, выполняющий энджин имеет конечную прозорливость, поэтому способ, которым декларируется множество данных, подлежащих выборке или изменению, все ещё имеет значение (и часто решающее)
И весь смысл, обычно, сводится к тому, что бы как можно меньше обращаться к таблицам, как можно больше использовать данные из индексов и как можно ближе писать запросы к тому, что система сможет понять и правильно интерпретировать. =)
Так переходи уже на PL\SQL и используй циклы (loop, for, if), если до курсоров дошёл
Не уверен насчёт долей секунд для SQL: сложные аналитические запросы вполне могут выполняться долго. Это тоже нужно.
Во первых, большинство серьезных проектов на Экселе очень тяжено переносятся в базы данных. По причине их ограниченности. «Красивое представление данных» в экселе — это самое не очевидное его использование. Вы просто не знаете эксель. Он позволяет объёдинить данные и бизнес-логику и озбавиться от самого тормозного, дорогого и бесполезного звена в разработке — программиста.
Во-вторых, мастодонты типа Микрософт зачем-то выпиливают сводные таблицы из баз данных (Аксес) и делают продукты типа Пауэер БИ, которые объёдиняют язык запросов, курсоры, формулы экселя и сводные таблицы. Это опять же чтобы отдать обработку данных пользователям, которые понимают что данные из себя представляют и зачем они нужны и избавиться от программистов (в виду их крайней бесполезности).
В третьх, делал итоги наростающим итогом джойнами. Все хорошо, ктроме одного — на нормальной выборке запрос зависает, слишком много данных. А вот в 1С, где тоже самое, скорее всего, сделано на курсорах и хранении остатков по каждой записи, действительно работает за доли секунды
Решения 2 — либо как в 1С, проставлять вручную (что может позволить только маленькое предприятие, на больших вменяемых эту возможность отключают), либо пересчет итогов по каждой операции при каких-либо изменениях (а это связка тригеров и курсора, что многие считают богомерзкой ересью).
А вот запросом — только академический интерес, как в этой статье. В рабочей базе, где группировка не одна, а минимум две: контаргент, договор + документ операции, запрос зависает наглухо.
Почему не рассмотрели lag? С помощью этого как раз и можно элегантно решить вопрос. По факту, рассмотрели все, кроме оптимального варианта...
select dt, val, @total:= val + @total total from test_simple, (select @total:=0)t;
MODEL в Oracle.
from functools import reduce
vals = [6, 3, 3, 4, 2, 4, 8, 0, 6, 0, 8, 8, 0, 2, 8, 7]
result = reduce(lambda x, y: x + y, vals)я победил?
где приз забирать?
functools:vals = [6, 3, 3, 4, 2, 4, 8, 0, 6, 0, 8, 8, 0, 2, 8, 7]
total = [vals[0]]; [total.append(total[i] + val) for i, val in enumerate(vals[1:])]А приз выдадут довольные клиенты (когда увидят perform вычислений на уровне приложения)
> where t2.dt < s.dt
Нельзя назвать надёжным, и полностью зависит от данных. Собственно такой подход работать не будет если есть данные с двумя одинаковыми датами.
Про это вы написали в 6м пункте.
В том же пункте есть «вариант решения проблемы»:
> Тем не менее, это решение может быть своего рода proof of concept: ведь на практике может быть поле, заменяющее номера строк (id записи).
Ну и здесь таже картина (мы делаем какое то предположение о данных, которое не всегда будет верным), ид полностью независим от даты. Представьте что вам надо добавить в таблицу какие то данные за прошлую неделю… В результате будет какой-то кошмар.
Такая постановка вопроса противоречит принципам аналитики, и не зависит от технологии — будь то sql, excel или листок бумаги.
Очевидно, что надо чтобы данные были либо сгруппированы по идентификатору (дате, в данном случае), либо нужны дополнительные параметры для сортировки.
Накопительную сумму в Экселе можно посчитать и одной формулой — протянуть формулу с закреплённой ссылкой на первую ячейку диапазона:
=СУММ($A$1:A1)
select tg.*,
coalesce(sum(tg.val) over (partition by tg.grp order by tg.dt
rows between unbounded preceding and current row),
0) as total
from test_groups tg
order by tg.grp, tg.dt;
rows between unbounded preceding and current row
Не обязателен — оконная функция суммы с order by будет показывать нарастающий итог по текущее значение по умолчанию.
10. Обновление через локальную переменную (SQL Server)
За это респект. Редкое знание но самое быстрое из перечисленных решений, в случае если нужно не просто накопительную сумму искать, а, к примеру, переходы через нули или более сложные условные накопительные изменения.
Но есть нюанс — UPDATE таблицы может происходить (строго говоря) в любом порядке, из-за чего накопительный итог не будет иметь смысла.
Поэтому рекомендуется заливать данные во временную таблицу (SELECT * INTO #TMP FROM ....), а потом построить по ней кластерный индекс по необходимой сортировке (CREATE CLUSTERED INDEX IX_SOME_INDEX ON #TMP1 (Sort_field).
В этом случае последовательность нарастающего итога гарантированно пойдёт по сортировке кластерного индекса.
Если это условие не дописать, то получится одинаковое значение для всех рядов (или для всего окна по партишну, как в данном случае).
dev.mysql.com/doc/refman/8.0/en/window-functions-frames.html
In the absence of a frame clause, the default frame depends on whether an ORDER BY clause is present:
With ORDER BY: The default frame includes rows from the partition start through the current row, including all peers of the current row (rows equal to the current row according to the ORDER BY clause). The default is equivalent to this frame specification:
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
Without ORDER BY: The default frame includes all partition rows (because, without ORDER BY, all partition rows are peers). The default is equivalent to this frame specification:
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
docs.oracle.com/cd/E11882_01/server.112/e41084/functions004.htm#SQLRF06174
If you omit the windowing_clause entirely, then the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
Oracle: http://www.sqlfiddle.com/#!4/73b847/1
PostgreSQL: http://www.sqlfiddle.com/#!17/73b84/1
MS SQL Server: http://www.sqlfiddle.com/#!18/73b847/1
|| rows between unbounded preceding and current row
Лучше всегда описывать явно, яркий пример функции first_value и last_value, поведение по умолчанию различное, несмотря на кажущийся одинаковым смысл по названию.
У Oracle есть явная рекомендация всегда использовать явное указание границ окна.
Если вам интересно можно можно протестировать эти функции онлайн https://sqlize.online/s/J0
Нарастающий итог в SQL