Sum qty в таблице что это

Получение итоговых значений

Как узнать количество моделей ПК, выпускаемых тем или иным поставщиком? Как определить среднее значение цены на компьютеры, имеющие одинаковые технические характеристики? На эти и многие другие вопросы, связанные с некоторой статистической информацией, можно получить ответы при помощи итоговых (агрегатных) функций. Стандартом предусмотрены следующие агрегатные функции:

Найти минимальную и максимальную цену на персональные компьютеры:

Результатом будет единственная строка, содержащая агрегатные значения:

Найти имеющееся в наличии количество компьютеров, выпущенных производителем А

В результате получим

Найти количество имеющихся различных моделей ПК, выпускаемых производителем А.

Запрос похож на предыдущий, в котором требовалось определить общее число моделей, выпускаемых производителем А. Здесь же требуется найти число различных моделей в таблице РС (то есть имеющихся в продаже).

даст следующий результат

Источник

SelectFromDual

лучше сразу записать, чем потом забыть (заметки по Oracle)

понедельник, 26 апреля 2010 г.

Том Кайт: о суммировании, слиянии и перемещении

Вопросы к Томасу Кайту и его ответы.

Вопрос. У меня есть родительская и дочерняя таблицы. Дочерняя таблица имеет столбец ‘qty’ (количество). Я хочу просуммировать его и сохранить результат в столбце родительской таблицы ‘tot_qty’ (общее количество). Любые изменения (операторы UPDATE, DELETE, INSERT) дочернего столбца ‘qty’ должны вносится в родительский столбец ‘tot_qty’. Я знаю, что это можно сделать с помощью триггеров, но не знаю, как приступить к этому.

Ответ. Суммирование столбцов QTY для родительской записи выполняется чертовски быстро. Но прежде чем вы сделаете это, убедитесь, что вы решаете реальную проблему. СУБД родились, чтобы соединять и агрегировать данные; это – то, что они делают наилучшим образом. Кроме того, помните, с этим подходом к суммированию, вы будете сериализовать транзакции на уровне родительского записи. Каждая модификация дочерней записи будет сериализовать все транзакции на родительском уровне.

Есть два подхода. Вы можете воспользоваться подходом «сделай сам», используя триггеры, или же можете использовать материализованное представление с предложением ON COMMIT REFRESH (обновление при фиксации транзакции). Во втором варианте родительская таблица не будет иметь столбца итогов, но вы будете иметь таблицу итогов с родительским ключом и подсчетом итогов.

Сначала, для подхода «сделай сам», я предполагаю следующую структуру таблиц:
Это – родительская (P) и дочерняя (C) таблицы, и я хочу поддерживать сумму по столбцу C.QTY в столбце P.QTY. Триггер, который я использовал бы, будет похож на следующее:
Этот триггер срабатывает каждый раз, когда я модифицирую столбец QTY в таблице C или изменяю значение внешнего ключа в таблице C (перемещая, следовательно, количество из одной родительской записи в другую). Когда я выполняю операцию вставки, я только добавляю к записи QTY родительской таблицы (используя функцию NVL, чтобы сначала преобразовать какие-либо NULL-значения в нули). Когда я выполняю операцию удаления, я только вычитаю. Когда я выполняю операцию обновления, я добавляю новое значение к новой записи внешнего ключа и вычитаю старое значение из старой записи внешнего ключа.

Теперь, создав триггер, я могу выполнять операции вставки и обновления, такие, как:

И я могу проверить модификации родительской записи:
Я говорил, что есть другой способ (не использующий триггеры «сделай сам»), это –материализованные представления. (Подробно о них см. в руководстве по хранилищам данных Oracle Data Warehousing Guide.) Эквивалентное решение (предполагаем, что вы только что создали таблицы P и C и здесь нет никаких триггеров) может быть следующим:

Больше ничего нет. Таблица MV (материализованное представление создает и поддерживает физическую таблицу) содержит просуммированное значение QTY, а так же значения функций COUNT(*)и COUNT(QTY) – необходимое условие создания материализованного представления агрегатов одной таблицы с быстрым обновлением («single table aggregate fast refresh materialized view») – без них невозможно его инкрементое обновление.

Оператор MERGE – что и когда?

Вопрос. Я озадачен оператором MERGE и срабатыванием триггеров базы данных. Какие триггеры будут срабатывать при выполнении оператора MERGE?

Ответ. Оператор MERGE – забавный оператор – он может быть и оператором вставки данных INSERT, и оператором обновления UPDATE, а в сервере Oracle Database 10g он может быть и оператором удаления DELETE. Следовательно, когда вы выполняете оператор MERGE, в сервере Oracle9i Database немедленно сработают триггеры BEFORE UPDATE и BEFORE INSERT (так как в этом операторе предложения WHEN MATCHED THEN UPDATE (когда совпадают, то обновить) И WHEN NOT MATCHED THEN INSERT (когда не совпадают, то вставить) являются обязательными). В сервере Oracle Database 10g будут срабатывать триггеры BEFORE UPDATE, INSERT и/или DELETE – в зависимости от указанных в операторе MERGE операций.

Подобным образом после выполнения оператора MERGE будут срабатывать и триггеры AFTER. В сервере Oracle9i Database будут срабатывать любые триггеры AFTER UPDATE или AFTER INSERT, а в сервере Oracle Database 10g – любые триггеры AFTER.

Триггеры строк для операторов INSERT и UPDATE (а в сервере Oracle Database 10g и DELETE) будут срабатывать по мере выполнения оператором MERGE соответствующих операций.

Таблица, которую я буду использовать для демонстрации этого, очень простая:

Для этой таблицы я создам шесть триггеров, используя следующий шаблон:

То же самое я сделаю и для оставшихся пяти триггеров BEFORE UPDATE, BEFORE DELETE и AFTER UPDATE/INSERT/DELETE. Теперь я выполню:
И именно это вы увидели бы как в сервере Oracle9i Database, так и в сервере Oracle Database 10g – будут срабатывать все триггеры BEFORE INSERT/UPDATE и AFTER UPDATE/INSERT, даже при том, что в этом случае единственным выполненным действием была вставка данных (никаких совпадений не было, поскольку таблица T была пустой). Это и предполагалось, так как обновление пустой таблицы должно было инициировать срабатывание соответствующих триггеров BEFORE и AFTER. Вставка нуля строк также должна была инициировать срабатывание соответствующих триггеров. Так как при выполнении оператора MERGE могли быть выполнены операторы INSERT и UPDATE, сработать должны были все эти пары триггеров.

В сервере Oracle Database 10g каждая часть оператора MERGE является факультативной – мы не обязаны указывать опцию вставки, мы не обязаны указывать опцию обновления и у нас также есть опция удаления данных. Я могу иметь такой оператор MERGE:

Обратите внимание, что теперь сработали все шесть триггеров, поскольку могли иметь место любые из операций INSERT, UPDATE или DELETE. Если я опущу необязательное (в сервере Oracle Database 10g) предложение INSERT:
Теперь триггеры INSERT не срабатывают. В сервере Oracle Database 10g будут срабатывать только те триггеры которые могли бы сработать. Первоначальные вопросы и ответы на эту тему см. в asktom.oracle.com/

tkyte/merge_trigger.html; в этом интерактивном обсуждении имеется больше примеров, а также рассматривается срабатывание триггеров на уровне строк.

Представления и слияние /проталкивание

Вопрос. Я разбираюсь с руководством по оптимизации производительности Oracle9i Performance Tuning Guide and Reference. Я прочитал о слиянии представлений (view merging) и проталкивании предикатов (predicate pushing). Не могли бы вы растолковать их?

Ответ. Слияние представления просто означает переписывание запроса без использования представления. То есть, вместо:
На самом деле вводится текст ПРЕДСТАВЛЕНИЯ.

Я создаю первое представление следующим образом:

Это представление может быть слито. То есть, сервер Oracle Database будет в состоянии переписать оператор SELECT * FROM V2 с заменой V2 в самом запросе на текст этого представления. Таким образом, оптимизатор имеет лучший шанс выработать хороший полный план выполнения.

Я создаю последнее представление следующим образом:

Предложение ORDER BY в этом представлении не допускает общего слияния, но допускает проталкивание предиката (в отличие от примера с ROWNUM в представлении V1). Итак, как мы можем реально увидеть эти факты? Ответ: воспользуемся оператором EXPLAIN PLAN. Давайте посмотрим на запросы к каждому из наших представлений и проанализируем результаты. На листинге 1 показан запрос к представлению V1.
Появление на листинге 1 шага VIEW (Id=1) указывает, что это представление не было слито с самим запросом. Факт, что фильтр WHERE USERNAME=’FRED’ появился поздно (Id=6), показывает, что предикат не был протолкнут в представление. Сервер Oracle Database будет материализовать представление, а затем применит предикат. Это – пример представления, которое не может быть слито и не поддерживает проталкивание предиката.

Второй пример, запрос к представлению V2, показан на листинге 2.
Отсутствие на листинге 2 шага VIEW указывает, что представление было слито. Это как если бы я запросил:

Сервер Oracle Database просто переместил наш текст непосредственно в сам запрос и оптимизировал его, как будто представление даже не существует.

Последний запрос, к представлению V3, показан на листинге 3.
На листинге 3 я вижу шаг VIEW, указывающий, что представление не было слито. Я запросил упорядочение строк, поэтому я не мог слить этот шаг. Предикаты применяются к представлению, однако они были протолкнуты в запросе настолько далеко, насколько это возможно. Шаг 5 (Id=5) на плане листинга 3 показывает, что предикат WHERE USERNAME = ‘FRED’ применяется так рано, как это можно (так как он не будет модифицировать ответ, возвращаемый запросом, – в отличие от запроса первого представления, который я анализировал (листинг 1)).

Вопрос. Из-за реорганизации одного из моих файлов данных мне нужно перенести одну таблицу в другой файл данных. Я думал о двух возможностях. Одна – создать другое табличное пространство, экспортировать таблицу, удалить ее, предварительно создать таблицу в другом табличном пространстве и импортировать ее. Другая – для перемещения таблицы в другой файл данных использовать операцию ALTER TABLE MOVE.

Какой вариант лучше в терминах времени недоступности таблицы, потребления пространства и возобновляемости процесса? Тестовой системы у меня нет. Таблица – традиционная таблица размером приблизительно 3.5 ГБ с одним первичным ключом.

Ответ. Есть только два подхода, которые я мог бы рассмотреть:
Во-первых, если было бы можно использовать время простоя, проще всего воспользоваться оператором ALTER TABLE T MOVE ТАБЛИЧНОЕ_ПРОСТРАНСТВО НОВОЕ_ ТАБЛИЧНОЕ_ПРОСТРАНСТВО. Это можно сделать, если хотите, в режиме отказа от журнализации NOLOGGING (но, если вы работаете в режиме архивирования журнальных файлов, не забудьте немедленно после переноса таблицы создать резервную копию). Во время переноса запрещена модификация этой таблицы, а затем потребуется перестройка индексов. Во время переноса можно выполнять запросы, но сразу же после переноса все индексы станут неиспользуемыми (unusable), поэтому запросы начнут сбиваться до тех пор, пока вы не перестроите эти индексы. Этот подход является полностью транзакционным – в противоположность использованию утилит EXP и IMP (всегда, когда вы забираете данные из базы данных, я нервничаю, потому что вы можете потерять их). Оператор ALTER TABLE MOVE либо выполняется успешно, либо оставляет данные такими, какими они были.

Второй подход заключается в использовании пакета DBMS_REDEFINITION, доступного в сервере Oracle9i Database и более поздние версии; поищите DBMS_REDEFINITION на сайте asktom.oracle.com для получения быстрых примеров. Преимущества этого пакета состоят в том, что он поддерживает выполнение непрерывных запросов и модификаций. Однако в сервере Oracle9i Database вы несете ответственность за то, что новый объект удовлетворяет всем вашим потребностям (таким, как наличие индексов, ограничений целостности, триггеров и т.д.). Сервер Oracle Database 10g берет все это на себя – сервер базы данных может выполнить все служебные операции, обеспечивая наличие у переопределенной таблицы всех необходимых предоставлений привилегий, индексов, ограничений, триггеров и т.д.; таким образом он делает ее прекрасной копией оригинала. Этот процесс также является транзакционным – никакие данные не могут пропасть.

Для таблицы размером 3.5 ГБ любой подход, невзирая ни на что, будет довольно быстрым; в наши дни 3.5 ГБ – это не очень много. Если вы можете допустить время простоя, то использование оператора ALTER TABLE MOVE – вероятно, самый легкий подход (не забудьте перестраивать индексы!). Но если время простоя не допускается, использование пакета DBMS_REDEFINITION – это то, что надо.

Источник

Получение итоговых значений

Как узнать количество моделей ПК, выпускаемых тем или иным поставщиком? Как определить среднее значение цены на компьютеры, имеющие одинаковые технические характеристики? На эти и многие другие вопросы, связанные с некоторой статистической информацией, можно получить ответы при помощи итоговых (агрегатных) функций. Стандартом предусмотрены следующие агрегатные функции:

Все эти функции возвращают единственное значение. При этом функции COUNT, MIN и MAX применимы к любым типам данных, в то время как SUM и AVG используются только для числовых полей. Разница между функцией COUNT(*) и COUNT( ) состоит в том, что вторая при подсчете не учитывает NULL-значения.

Пример. Найти минимальную и максимальную цену на персональные компьютеры:

Результатом будет единственная строка, содержащая агрегатные значения:

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

В результате получим:

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

Совпадение результатов совершенно случайно, т.к. в базе данных количество компьютеров производителя А оказалось равным числу выпускаемых им моделей:

Пример. Найти количество имеющихся различных моделей, выпускаемых производителем А. Запрос похож на предыдущий, в котором требовалось определить общее число моделей, выпускаемых производителем А. Здесь же требуется найти число различных моделей в таблице PC (т.е. имеющихся в продаже).

Для того, чтобы при получении статистических показателей использовались только уникальные значения, при аргументе агрегатных функций можно использовать параметр DISTINCT. Другой параметр ALL используется по умолчанию и предполагает подсчет всех возвращаемых значений в столбце. Оператор,

даст следующий результат:

Если же нам требуется получить количество моделей ПК, производимых каждым производителем, то потребуется использовать предложение GROUP BY, синтаксически следующего после предложения WHERE.

Предложение GROUP BY

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

В этом запросе для каждой модели ПК определяется их количество и средняя стоимость. Все строки с одинаковыми значениями model (номер модели) образуют группу, и на выходе SELECT вычисляются количество значений и средние значения цены для каждой группы. Результатом выполнения запроса будет следующая таблица:

Если бы в SELECT присутствовал столбец с датой, то можно было бы вычислять эти показатели для каждой конкретной даты. Для этого нужно добавить дату в качестве группирующего столбца, и тогда агрегатные функции вычислялись бы для каждой комбинации значений (модель−дата).

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

Предложение HAVING

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

Заметим, что в предложении HAVING нельзя использовать псевдоним (Avg_price), используемый для именования значений агрегатной функции. Дело в том, что предложение SELECT, формирующее выходной набор запроса, выполняется предпоследним перед предложением ORDER BY.

Этот порядок не соответствует синтаксическому порядку общего формата оператора SELECT, представленному ниже:

Источник

SQL-Урок 9. Подзапросы

До сих пор мы получали данные из базы данных с помощью простых запросов и одного оператора SELECT. Однако, все же, чаще нам нужно будет выбирать данные, соответствующие многим условиям, и здесь не обойтись без расширенных запросов. Для этого в SQL существуют подзапросы или вложенные подзапросы, когда один оператор SELECT укладывается в другой.

1. Фильтрация с помощью подзапросов

Таблицы баз данных, которые используются в СУБД Access являются реляционными таблицами, т.е. все таблицы можно связать между собой по общим полям. Допустим у нас хранятся данные в двух разных таблицах и нам нужно выбрать данные в одной из них, в зависимости от того, какие данные в другой. Для этого создадим еще одну таблицу в нашей базе данных. Это будет, например, таблица Sellers с информацией о поставщиках:

SELECT City FROM Sellers WHERE Country = ‘Canada’

Теперь передадим эти данные в следующий запрос, который будет выбирать данные из таблицы Sumproduct:

SELECT SUM(Quantity) AS Qty_Canada FROM Sumproduct WHERE City IN ( ‘Montreal’,’Toronto’ )

SELECT SUM(Quantity) AS Qty_Canada FROM Sumproduct WHERE City IN ( SELECT City FROM Sellers WHERE Country = ‘Canada’ )

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

2. Использование подзапросов в качестве расчетных полей

Мы также можем использовать подзапросы в качестве расчетных полей. Отразим, например, количество реализованной продукции по каждому продавцу с помощью следующего запроса:

SELECT Seller_name, ( SELECT SUM(Quantity) FROM Sumproduct WHERE Sellers.City = Sumproduct.City ) AS Qty FROM Sellers

Также в подзапросе, предложение WHERE выполняет функцию объединения, поскольку с помощью WHERE мы соединили две таблицы по полю City, использовав полные названия столбцов (Таблиця.Поле).

Источник