Как в sql посчитать разницу между суммами

Вычитание 2-х сумм в SQL

table Есть 2 селект запроса: мне нужно посчитать :

Нужно вычесть обе этих суммы — этот результат должен инсертнуться в поле другой таблицы Я чет не понимаю как это по-SQL’ному написать.

Все определенно просто:

Нажимая «Принять все файлы cookie» вы соглашаетесь, что Stack Exchange может хранить файлы cookie на вашем устройстве и раскрывать информацию в соответствии с нашей Политикой в отношении файлов cookie.

Три способа вычитания в языке SQL на простом примере

Структурированный язык запросов (SQL) во всех своих реализациях в первую очередь декларативен. Клиент (веб-сервер, клиентское приложение или та же среда разработки) дает серверу задачу, описывая, ЧТО он хочет получить, а уже оптимизатор запросов на сервере выбирает, КАК это сделать. Прелесть языка состоит в том, что в нем много избыточности и даже откровенного «синтаксического сахара» — все для сладкой жизни разработчиков. Многие конструкции языка введены для упрощения кода в стандартных и нестандартных ситуациях, и многие вещи, получается, можно выразить совершенно по-разному. От знания этих конструкций и умения ими пользоваться будет зависеть стиль кода, удобство его чтения и сопровождения, а в отдельных случаях и производительность работы системы, ведь разные «фразы» могут транслироваться в разные планы выполнения!

Рассмотрим несложный пример запроса на выборку информации из двух таблиц, связанных отношением «один-ко-многим». Каждому Клиенту соответствует 0, 1 или несколько Заказов, и некоторые Заказы были отменены. Требуется написать запрос, выдающий «хороших» Клиентов, чьи Заказы никогда не отменялись. Я использую в СУБД MS SQL Server, но в этих примерах Transact-SQL практически неотличим от стандарта SQL.

Client

id	Name
1	Штирлиц
2	Мюллер
3	Борман
4	Шлаг
5	Кэт

Order

id	id_client	date	status
1	2	12-02-1945	Finished
2	1	14-02-1945	Ready
3	2	14-02-1945	Cancelled
4	3	15-02-1945	Cancelled
5	5	15-02-1945	Processing
6	2	01-03-1945	Ready
7	1	03-03-1945	Ready
8	1	12-03-1945	Finished
9	1	20-03-1945	Processing

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

Favorite_Clients

id	Name
1	Штирлиц
4	Шлаг
5	Кэт

Кстати, а КАК мы придем к такому результату? Ведь мы мыслим по-разному, и для такой задачки могут быть разные алгоритмы. Особенно, если строк будет побольше. В данном случае, мой продвинутый эвристический мозг сразу охватил всю таблицу Order, обнаружил, что выборка отмененных Заказов мала по сравнению с генеральной совокупностью, сохранил номера Клиентов из этих строк в «черный список», а потом механически скопировал таблицу Client и удалил из нее строки с номерами из «черного списка». Уф, писать это намного дольше, чем сделать! Зато сразу становится понятно, в каком направлении можно решать задачку на SQL: нам бы нужно вычесть что-то из всего множества строк таблицы Client. Итак, приступим.

«Если у вас есть только молоток, то все вокруг кажутся гвоздями»

Первое, чему обычно учатся в SQL, это JOIN (соединение таблиц), точнее INNER JOIN (внутреннее соединение). При виде отношения «один-ко-многим», он так и просится сюда

SELECT Client.id, Name, status
FROM Client INNER JOIN Order
ON Client.id = Order.id_client

Вот только что с этим дальше делать? WHERE, очевидно, не поможет, ведь у одного Клиента могут быть и успешные, и отмененные Заказы, да и GROUP BY с HAVING, на первый взгляд, не дают полезных ответов. А раз так, то и попытки использовать LEFT, RIGHT или FULL OUTER JOIN (внешние соединения) тоже лучше отложить.

«Черный список»

Зайдем с другой стороны, и запросим номера Клиентов, у которых Заказы отменялись

SELECT id_client FROM Order WHERE status = ‘Cancelled’

Вот эти плохиши:

Пора переходить к разным способам вычитания

Except и его недостатки

В реляционной алгебре вычитание относится к теоретико-множественным операциям, и напрямую реализовано инструкцией EXCEPT языка SQL

SELECT id FROM Client
EXCEPT
SELECT id_client FROM Order WHERE status = ‘Cancelled’

Очень просто, наглядно, читабельно и даже верно. Примерно так действует и человек! Вот только выражение SELECT до и после EXCEPT должны совпадать по количеству и порядку типов столбцов. Именно поэтому мы получаем только id наших Клиентов, а в постановке задачи подразумевались и имена тоже. Похоже, что без JOIN все-таки не обойтись:

SELECT Client.id, Name
FROM Client JOIN (
SELECT id FROM Client
EXCEPT
SELECT id_client FROM Order
WHERE status = ‘Cancelled’
) AS t ON Client.id=t.id

Ну вот, мне перестал нравиться этот запрос: тут и скобки, и аж 3 слова SELECT, и, главное, целых 2 раза сканируется таблица Client, а это угрожает производительности запроса.

Можно переписать покомпактнее

SELECT id, Name FROM Client
EXCEPT
SELECT Client.id, Name, status
FROM Client INNER JOIN Order
ON Client.id = Order.id_client
WHERE status = ‘Cancelled’

Но, скорее всего, производительность будет не лучше. Зависит от того, каких Клиентов больше – «хороших» или «плохих».

NOT IN – использование несвязанного вложенного запроса

К каждому значению в строке из некоего множества можно применить условие фильтрации IN или NOT IN, проверяющее наличие этого значение в другом множестве. В нашем случае, удобно написать:

SELECT id, Name FROM Client
WHERE id NOT IN (
SELECT id_client FROM Order
WHERE status = ‘Cancelled’ )

Заметим, что это практически идеальное решение и по производительности, и по читаемости/понятности для человека: «Выбрать таких Клиентов, чей id отсутствует в списке номеров клиентов отмененных заказов», или даже «Выбрать Клиентов, чей id отсутствует в Черном списке» (если пользоваться представлением).

NOT EXISTS – использование связанного вложенного запроса

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

SELECT Client.id, Name FROM Client
WHERE NOT EXISTS (
SELECT * FROM Order
WHERE status = ‘Cancelled’
AND Order.id_client = Client.id )

Очень полезный во многих задачках механизм, но здесь он смотрится чуточку громоздко и менее очевидно. А вот по производительности надо смотреть конкретные планы выполнения. Дело в том, что «как написано» он требует вложенного цикла по всем строкам таблицы Client с поиском в Order, но оптимизатор запросов, в зависимости от используемой СУБД, имеющихся индексов и конкретной статистики может выбрать более оптимальный вариант, например, идентичный предыдущему варианту с NOT IN.

Итак, мы рассмотрели 3 достаточно очевидных способа решения задачи, использующие чисто декларативные конструкции SQL. На практике могут возникнуть потребности сделать что-то похожее императивно – например с помощью серверного курсора, или даже реализовать в клиентском коде на императивном ЯВУ. У таких решений будут свои плюсы и минусы, рассмотрение которых выходит за рамки нашего краткого экскурса. Иногда причина использования императивного подхода – банальное незнание базовых, в общем, декларативных средств.

рассчитать разницу между двумя строками в SQL

У меня есть таблица SQL, одна строка — это доход в конкретный день, и я хочу добавить новый столбец в таблицу, значение — прирост (может быть положительный или отрицательный) доход между конкретным днем и предыдущий день и интересно, как реализовать SQL?

новая таблица (добавить инкрементный столбец в конце и для первого столбца, может назначить ноль),

Я использую MySQL/MySQL Workbench.

спасибо заранее, Lin

3 ответа

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

Если вы согласны с переупорядочиванием столбцов немного, что-то вроде этого довольно просто понять:

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

Обратите внимание, что это зависит от правильности заказа, поскольку вычисления выполняются во время возвращения строк, поэтому вам нужно убедиться, что у вас есть предложение ORDER BY, которое возвращает дни в правильном порядке.

Агрегатные функции SQL — SUM, MIN, MAX, AVG, COUNT

Будем учиться подводить итоги. Нет, это ещё не итоги изучения SQL, а итоги значений столбцов таблиц базы данных. Агрегатные функции SQL действуют в отношении значений столбца с целью получения единого результирующего значения. Наиболее часто применяются агрегатные функции SQL SUM, MIN, MAX, AVG и COUNT. Следует различать два случая применения агрегатных функций. Первый: агрегатные функции используются сами по себе и возвращают одно результирующее значение. Второй: агрегатные функции используются с оператором SQL GROUP BY, то есть с группировкой по полям (столбцам) для получения результирующих значений в каждой группе. Рассмотрим сначала случаи использования агрегатных функций без группировки.

Функция SQL SUM

Функция SQL SUM возвращает сумму значений столбца таблицы базы данных. Она может применяться только к столбцам, значениями которых являются числа. Запросы SQL для получения результирующей суммы начинаются так:

После этого выражения следует FROM (ИМЯ_ТАБЛИЦЫ), а далее с помощью конструкции WHERE может быть задано условие. Кроме того, перед именем столбца может быть указано DISTINCT, и это означает, что учитываться будут только уникальные значения. По умолчанию же учитываются все значения (для этого можно особо указать не DISTINCT, а ALL, но слово ALL не является обязательным).

Если вы хотите выполнить запросы к базе данных из этого урока на MS SQL Server, но эта СУБД не установлена на вашем компьютере, то ее можно установить, пользуясь инструкцией по этой ссылке .

Сначала работать будем с базой данных фирмы — Company1. Скрипт для создания этой базы данных, её таблиц и заполения таблиц данными — в файле по этой ссылке .

Пример 1. Есть база данных фирмы с данными о её подразделениях и сотрудниках. Таблица Staff помимо всего имеет столбец с данными о заработной плате сотрудников. Выборка из таблицы имеет следующий вид (для увеличения картинки щёлкнуть по ней левой кнопкой мыши):

Для получения суммы размеров всех заработных плат используем следующий запрос (на MS SQL Server — с предваряющей конструкцией USE company1;):

Этот запрос вернёт значение 287664,63.

А теперь упражнение для самостоятельного решения. В упражнениях уже начинаем усложнять задания, приближая их к тем, что встречаются на практике.

Пример 2. Вывести сумму комиссионных, получаемых всеми сотрудниками с должностью Clerk.

Функция SQL MIN

Функция SQL MIN также действует в отношении столбцов, значениями которых являются числа и возвращает минимальное среди всех значений столбца. Эта функция имеет синтаксис аналогичный синтаксису функции SUM.

Пример 3. База данных и таблица — те же, что и в примере 1.

Требуется узнать минимальную заработную плату сотрудников отдела с номером 42. Для этого пишем следующий запрос (на MS SQL Server — с предваряющей конструкцией USE company1;):

Запрос вернёт значение 10505,90.

И вновь упражнение для самостоятельного решения. В этом и некоторых других упражнениях потребуется уже не только таблица Staff, но и таблица Org, содержащая данные о подразделениях фирмы:

Пример 4. К таблице Staff добавляется таблица Org, содержащая данные о подразделениях фирмы. Вывести минимальное количество лет, проработанных одним сотрудником в отделе, расположенном в Бостоне.

Функция SQL MAX

Аналогично работает и имеет аналогичный синтаксис функция SQL MAX, которая применяется, когда требуется определить максимальное значение среди всех значений столбца.

Пример 5. База данных и таблица — те же, что и в предыдущих примерах.

Требуется узнать максимальную заработную плату сотрудников отдела с номером 42. Для этого пишем следующий запрос (на MS SQL Server — с предваряющей конструкцией USE company1;):

Запрос вернёт значение 18352,80

Пришло время упражнения для самостоятельного решения.

Пример 6. Вновь работаем с двумя таблицами — Staff и Org. Вывести название отдела и максимальное значение комиссионных, получаемых одним сотрудником в отделе, относящемуся к группе отделов (Division) Eastern. Использовать JOIN (соединение таблиц).