Какой запрос строится не sql когда мы обращаемся к таблице остатков

Хрусталева Е. Ю. Язык запросов 1С-Предприятия 8 (2013)

В данном запросе на список движений регистра бухгалтерии накладывается отбор по полю Регистратор . Выбранный документ-регистратор Операция или Приходная накладная передается как значение параметра &Регистратор .

Для документа Операция №3 мы получим следующий результат (рис. 3.58).

Рис. 3.58. Отбор движений регистра бухгалтерии по регистратору

Для построения бухгалтерских отчетов и анализа данных в большинстве случаев используются виртуальные таблицы регистров бухгалтерии. Эти таблицы предназначены для решения конкретных задач.

Для получения остатков по счетам в разрезе субконто и измерений используется таблица остатков Остатки() . Агрегация данных производится по счетам, а также измерениям и субконто, указанным в запросе. Итоги ресурсов получаются только по активным записям.

Виртуальная таблица Остатки имеет следующий состав полей:

<Имя измерения> – поле, содержащее значения измерения регистра с именем, заданным в конфигурации. Количество таких полей равно количеству измерений, определенных для регистра как объекта конфигурации; <Имя общего реквизита> – поле, содержащее значения общего реквизита с именем,

заданным в конфигурации. Такие поля создаются для общих реквизитов, являющихся разделителями (режим разделения данных – Разделять ) с режимом использования разделяемых данных НезависимоИСовместно , в которых участвует данный регистр; <Имя ресурса>Остаток – поле, содержащее остаток ресурса регистра по именам ресурсов, как они заданы в конфигураторе, с добавлением слова Остаток . Содержит абсолютный остаток без учета вида счета – дебетовый остаток показывается положительным числом, кредитовый – отрицательным числом; <Имя ресурса>ОстатокДт – поле, содержащее дебетовый остаток ресурса

регистра по именам ресурсов, как они заданы в конфигураторе, с добавлением слова ОстатокДт . Содержит дебетовый остаток с учетом вида счета. Если счет пассивный, значение этого поля всегда равно нулю. Если счет активный, значение поля равно значению поля Остаток . Если счет активно-пассивный, значение поля равно значению поля Остаток , если Остаток больше или равен нулю. Если Остаток меньше нуля, значит – ноль; <Имя ресурса>ОстатокКт – поле, содержащее кредитовый остаток ресурса

регистра по именам ресурсов, как они заданы в конфигураторе, с добавлением слова ОстатокКт . Содержит кредитовый остаток с учетом вида счета. Если счет активный, значение этого поля всегда равно нулю. Если счет пассивный, равно —

Остаток . Если счет активно-пассивный, значение поля равно нулю, если значение поля Остаток больше или равно нулю. Если значение поля Остаток меньше нуля, значение этого поля равно -Остаток ;

<Имя ресурса>РазвернутыйОстатокДт – содержит развернутый дебетовый остаток, который считается развернуто по всем измерениям, указанным в запросе. Имеет смысл только при использовании в запросе итогов по измерениям. Для детальной записи значение этого поля равно значению поля <Имя ресурса>ОстатокДт . Для итоговой записи равно сумме дебетовых остатков всех детальных записей;

<Имя ресурса>РазвернутыйОстатокКт – содержит развернутый кредитовый остаток, который считается развернуто по всем измерениям, указанным в запросе. Имеет смысл только при использовании в запросе итогов по измерениям. Для детальной записи значение этого поля равно значению поля <Имя ресурса>ОстатокКт . Для итоговой записи равно сумме кредитовых остатков всех детальных записей;

Субконто<Номер субконто> – имеет тип Характеристика.<имя> . Содержит значение субконто. Количество полей Субконто зависит от максимального количества субконто на счете плана счетов. Номер субконто начинается с 1 . Набор и порядок этих полей определяются параметром Субконто , переданным в виртуальную таблицу; Счет – имеет тип ПланСчетовСсылка.<имя> . Позволяет получить остатки, сгруппированные по счетам.

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

Период – имеет тип Дата , МоментВремени или Граница . Момент времени, на который нужно посчитать остатки. Если параметр не задан, то будут получены актуальные остатки, включающие движения последнего проведенного документа; УсловиеСчета – содержит конструкцию языка запросов. Позволяет установить фильтр по счету или счетам. Как правило, содержит следующие условия: Счет = (В

ИЕРАРХИИ, В) &Счет ;

Субконто – в этот параметр передается ссылка, или массив ссылок, или фиксированный массив ссылок, или список значений, содержащий ссылки, на виды субконто. Задает набор и порядок субконто, которыми можно оперировать в запросе. А также служит для отбора записей регистра по видам субконто. Если параметр задан, то будут выбираться данные только по тем счетам, у которых определены все указанные виды субконто. Если параметр не задан, то ограничений по видам субконто нет. Субконто определяются позиционно по соответствующему счету; Условие – содержит конструкцию языка запросов. Позволяет устанавливать отбор данных виртуальной таблицей по значениям субконто и измерений регистра бухгалтерии.

Рассмотрим примеры построения запросов к таблице остатков регистра бухгалтерии.

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

Листинг 3.49. Вывод остатка заданного товара со счета товаров

В данном запросе в параметр УсловиеСчета , используемый при построении виртуальной таблицы остатков, мы передаем ссылку на счет товаров (ссылку для предопределенного счета можно получить по имени, например: СчетТоваров =

ПланыСчетов.ОсновнойПланСчетов.Товары ). В параметр Условие передаем условие отбора конкретного товара по значению субконто, т. е. ссылку на элемент справочника Номенклатура , который используется на счете товаров как первый вид субконто. Результат запроса будет содержать одну строку в одном поле: абсолютный остаток по ресурсу регистра Количество .

По аналогии с помощью следующего запроса можно получить стоимостной остаток материалов на определенном складе на счете материалов. Для этого нужно выполнить следующий запрос (листинг 3.50).

Листинг 3.50. Вывод остатка материалов на заданном складе на счете материалов

В данном запросе в виртуальную таблицу остатков мы передаем ссылку на счет материалов и условие отбора конкретного склада по значению субконто, т. е. ссылку на элемент справочника Склады , который используется на счете материалов как первый вид субконто.

Результат запроса будет содержать одну строку в одном поле: абсолютный остаток по ресурсу регистра Сумма (рис. 3.59).

Рис. 3.59. Вывод остатка материалов на заданном складе на счете материалов

Следующий запрос позволяет отобрать остатки в разрезе валют по всем счетам, на

которых ведется валютный учет (листинг 3.51).

Листинг 3.51. Вывод остатков на валютных счетах

Результат выполнения данного запроса представлен на рис. 3.60.

Рис. 3.60. Вывод остатков на валютных счетах

В данном запросе в параметр УсловиеСчета передается условие отбора по счетам с признаком учета Валютный . Остатки подсчитываются в разрезе счетов и валют (значения измерения Валюта ).

На примере таблицы остатков рассмотрим особенности третьего параметра Субконто , используемого при построении виртуальной таблицы остатков. Все нижесказанное актуально также для виртуальных таблиц оборотов (имена параметров Субконто , КорСубконто ), остатков и оборотов, оборотов ДтКт (имена параметров СубконтоДт ,

Все перечисленные параметры могут принимать значения

ПланыВидовХарактеристикСсылка.<имя> или содержать массив, состоящий из значений указанного типа данных. То есть, точнее говоря, в параметре Субконто можно передать не значение субконто, а значение вида субконто или массив видов субконто. А для отбора по значению субконто используется параметр Условие (см. листинги 3.49, 3.50).

Рассмотрим два аспекта использования параметра Субконто . Прежде всего, это отбор итогов по виду субконто.

Продемонстрируем это на примере нашей демонстрационной конфигурации. Предположим, нам необходимо получить отчет по остаткам товарно-материальных запасов. Для этого мы должны получить остатки со счетов учета материальных ценностей. Таких счетов два: предопределенный счет Товары , на котором первым

субконто учитывается Номенклатура , а вторым – Склады . И предопределенный счет Материалы , на котором первым субконто учитываются Склады , а вторым –

Выведем стоимостные остатки со счетов учета материальных ценностей в разрезе субконто Номенклатура с помощью следующего запроса (листинг 3.52).

Листинг 3.52. Вывод остатков товарно-материальных ценностей в разрезе субконто «Номенклатура»

В консоли запросов установим значение параметра ВидСубконто как субконто

Номенклатура типа СправочникСсылка.Номенклатура , значения параметров СчетМатериалов и СчетТоваров как ссылки на предопределенные счета Материалы и Товары соответственно. Выполним запрос (рис. 3.61).

Рис. 3.61. Вывод остатков товарно-материальных ценностей в разрезе субконто «Номенклатура»

В результате итоговые остатки товарно-материальных ценностей на обоих счетах ( Товары и Материалы ) подсчитаны в разрезе номенклатуры, которая учитывается как аналитика на этих счетах.

Теперь рассмотрим второй аспект использования параметра Субконто – для определения набора и порядка следования субконто в результате запроса.

В нашем примере проблема в том, что субконто Номенклатура прикреплено первым субконто на счете Товары и вторым – на счете Материалы . А обращаемся мы к ним в запросе именно по номеру ( Субконто1 , Субконто2 ).

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

Если при этом не указать параметр Субконто (листинг 3.53), то поле Субконто1 будет содержать и товары для счета товаров, и склады для счета материалов. Поле Субконто2 будет содержать склады для счета товаров и товары для счета материалов

Листинг 3.53. Вывод остатков товарно-материальных ценностей в разрезе субконто «Номенклатура» и «Склады»

Рис. 3.62. Вывод остатков товарно-материальных ценностей в разрезе субконто «Номенклатура» и «Склады»

В итоге мы видим строку результата запроса – Филиал Паркер 1000 , где Филиал – это склад (но находится в колонке Товар ), а Паркер – товар (но находится в колонке Склад ).

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

Листинг 3.54. Вывод остатков товарно-материальных ценностей в разрезе субконто «Номенклатура» и «Склады»

В результате мы получим нужные итоги (рис. 3.63).

Рис. 3.63. Вывод остатков товарно-материальных ценностей в разрезе субконто «Номенклатура» и «Склады»

Таким образом, теперь независимо от счета первым субконто в нашем запросе является Номенклатура , вторым – Склады . Причем если будет нужно изменить последовательность группировок в запросе (сначала группировать по складам, потом внутри склада – по номенклатурным позициям), нам достаточно изменить порядок следования элементов массива, который передается в параметр виртуальной таблицы

Этот пример можно посмотреть в демонстрационной конфигурации «Бухгалтерский учет», прилагающейся к книге, в обработке Субконто .

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

Продемонстрируем эту особенность на следующем примере. В нашей демонстрационной конфигурации по счету Материалы существует всего одна проводка. В дебет счета начисляется 1000 , значение первого субконто – Паркер (товар), второго – Филиал (склад).

Изменим аналитику на счете материалов. В конфигураторе удалим для предопределенного счета Материалы второй вид субконто, а первый вид субконто изменим на субконто Номенклатура . Для счета Товары аналитику оставим без изменений. Первым субконто на нем учитывается Номенклатура , вторым – Склады . В документе Операция №8 изменим значение первого субконто на Паркер (товар), а второго субконто на счете материалов теперь нет.

После этого выполним команду Остатки товарно-материальных ценностей в

обработке Субконто (рис. 3.64).

Рис. 3.64. Вывод остатков товарно-материальных ценностей в разрезе субконто «Номенклатура» и «Склады»

Если сравнить результат с правильным вариантом (см. рис. 3.63), то мы видим, что пропала как раз строка результата запроса – Паркер Филиал 1000 , т. к. в параметре Субконо передается массив из двух видов субконто ( Номенклатура , Склады ), а у счета материалов теперь один вид субконто – Номенклатура .

Ситуацию можно исправить путем объединения запросов к каждому счету ( Товары и

Материалы ) отдельно (листинг 3.55).

Листинг 3.55. Вывод остатков товарно-материальных ценностей в разрезе субконто «Номенклатура» и «Склады»

В запросе используется объединение данных запроса к счету товаров и запроса к счету материалов. Параметр Субконто в этих запросах не задан, т. к. в этом случае виды субконто и их последовательность определяются позиционно по соответствующему счету. Для счета товаров – это Номенклатура и Склады , для счета материалов – Номенклатура . Затем для результата объединения этих запросов рассчитываются общие итоги и итоги по полю Товар .

Как посчитать остатки и обороты на складе, накладные товара и услуг вместе?

Помогите, пожалуйста, найти оптимальное решение.
У меня есть рабочая программа (Сервер — Intebase 2009, Среда-Delphi XE6), которая работает уже несколько лет. Сначала база была спроэктирована для аптеки, но постепенно возникла потребность:

1) усовершенствовать бизнес-процесс и сделать программой для учета не только лекарств, но и других тмц и, кроме этого, оказания услуг.
2) переходить на Firebird 2.5, поскольку из-за отсутствия в Intebase 2009 derived tables и CTE мне было трудно или невозможно написать какие-то запросы.

Новая база будет содержать около 60 таблиц, но пока в Firebird-е создана только основная часть, «скелет», на который хочу обратить ваше внимание (это таблицы накладных (master и несколько datails, в которых по отдельности содержится записи приходов, расходов, списания, перемещения и услуг)) и некоторые справочники.

Приведу эту часть диаграммы:

alt text

1) Как лучше посчитать остатки?

Я думаю о 2 вариантах:
а) создать дополнительные таблицы (operations, rests, storages, nomenkl), для остатков на конкретный момент времени сохранить в rests остатки, например, на начало месяца или квартала и потом пересчитать до «сегодня», как написано здесь (особенно 4-5 вариант). Но при нескольких складах отдельно для них хранить остатки конца каждого месяца значит, что около при 5000 наименований в этой таблице через год будет 5000.12.количество складов записей! А через 5-10 лет.
б) создать «хранимые агрегаты», как прочитал в темах на форуме sql.ru. Но «хранимые агрегаты» создаются триггером, а он сработает на before(after) insert, delete, update, то есть при каждом изменении таблиц документов происходит обновление записей в агрегате, и так я получу текущие остатки. А для остатков двухнедельной давности «идти назад»? Или чего-то не понимаю? «хранимые агрегаты»

2) насколько оправдано то, что я загнал накладные товародвижения и услуг вместе, в одну таблицу? В услуге же будут свои, специфические поля, которые останутся пустыми для тмц? Не лучше ли ТМЦ и услуги (master tables) в отдельных таблицах?

3) Насколько оправдано собирать вместе накладные («шапки») приходов, расходов, перемещения и списания (на диаграмме не видно) в одну таблицу,а сами datails -то есть записи- в отдельных таблицах? Или лучше так, как тут (III-V вариант)? Какие от этого могут быть «последствия» в будущем? Ведь у всех этих накладных будут свои, специфические поля, а это значит избыточость данных и денормализация! Тем более, что постепенно появятся «типичные» документы и других типов, например, «обслуживание клиента своим материалом», или «обслуживание материалом предприятия», или «документы инвентаризации», «кассовые операции прихода», «кассовые операции расхода » и т.д.? И у всех будут свои дополнительные свойства, и постепенно избыточность увеличится!
По-вашему, даст собрание в одной таблице экономию времени во время запросов об остатках и оборотов? Или есть еще другая причина за? Прощу ответьте аргументами.

С другой стороны, постепенно не соберутся ли много разных типов документов, и поэтому написать запросы и собирать данные из многих datails таблиц не станет ли труднее, чем из одного? Кстати, в старой базе и «шапки», и datails у меня были в отдельных таблицах, и на sql.ru и другие тоже посоветовали, что поскольку движение товара одна сущность, зачем его делить на части. Смотрите ссылку и что там Сибиряков советует:
Я пишу: «Если можно, еще один вопрос: что вы имели в виду, когда писали: «проще будет объединить эти две таблицы в одну»? Я всегда думал, что в целях нормализации БД sales и incomes обязательно должны быть в отдельных таблицах!»
Ответ: «И какую же нормальную форму ты пытаешься воплотить в жизнь, разделяя одну сущность «движение средств» на две таблицы. «

Сейчас уже запутался, не пойму, как поступить.

Создать дополнительные таблицы. Аккумулировать в них данные регламентом, т.е. по кнопке. Раз в день, в месяц, в квартал, год. Называется свертка базы.
Ни к чему. Экономия на таблицах = неудобство в выборках: дополнительное поле для фильтра, как следствие дополнительное поле в индексе, как следствие тормоза при вставках, объем базы. все это в худшую сторону.
Тоже ни к чему. Отделите сущности. То, что в будущем в программе появится кассовый учет, не значит, что вы должны его впихнуть в ту же структуру данных. Будет ПКО, создадите для него свою таблицу.

То, что у вас реализуется количественный учет — это хорошо. Количественные остатки — это просто разность прихода и расхода. Скорость выборки с группировкой по полям индекса даст в принципе нормальную скорость и без таблицы остатков. ну, например.

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

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

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

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

Все это нас приводит к тому, что таблиц с движениями (с определенным набором данных), вам потребуется довольно много. И смысл в них такой, что остатки, собираться должны по ним, а не по документам. И вот эти таблицы как раз и надо будет каким-то образом партиционировать.

Например: таблицы архивов по годам, таблицы остатков на начало года, таблицы архивов кварталов текущего года, остатки на начала кварталов, текущие движения. Это один из вариантов, логика получения данных должна быть и гибкой в зависимости от потребностей и без избытка. В частности, бывают организации, которые раз в год-три делают свертку, и начинают новую базу вести, просто перенеся туда остатки. И никакие партиционирования и тригеры по генерации остатков не нужны.

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

UPD 1 Речь идет о срезах по остаткам. Т.е. на определенные даты у вас будет храниться срез по всем остаткам. Все верно, при изменениях задним числом вся прелесть рассчитанных остатков на начало месяца/квартала/года пропадает и возникает необходимость пересчета.

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

С другой стороны, если все-таки делать пересчет, вся сложность будет перенесена на интеллект системы пересчета. Т.е. возникает необходимость определять что конкретно надо пересчитывать и какие последствия (в частности для партионного учета) такого пересчета будут. Допускает ли система списания несуществующих партий (пересортица), или пока вся история не будет выправлена актуальных остатков не получить. Идеальный и быстрый механизм пересчета правок задним числом — Святой Грааль 🙂 , мне такой не встречался, то есть обычно тупо идет откат актуальных остатков к дате измененного документа, а остатки позже даты актуальности просто работают с отключением контроля остатков (минусы на остатках могут быть легко).

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

UPD 2 Да, речь о партиях. Но не только. Любое товародвижение внутреннее или внешнее порождает несколько видов движений. Количественные остатки, партии, ГТД. И собирать по документам эти данные для дальнейших расчетов в расходах неудобно и нецелесообразно в плане того, что дергать придется тучу таблиц, связывать их union’ом, я бы все-таки остановился на регистрах, где в удобном виде будет храниться только нужная информация по движениям документов и работал бы только с ними, а документы не нужны.
Ну вот смотрите, для расхода вам придется собирать данные: по приходам, внутренним перемещениям, списаниям, расходам — это если не брать еще комплектацию, производство и прочие причуды. Зачем бегать по таблицам документов если можно обойтись одной таблицей для каждого вида движения. ИМХО, это гораздо удобнее.

Как получить N строк для каждой группы в SQL

Давайте предположим, что вы разрабатываете главную страницу в интернет-магазине. На этой странице должны отображаться категории товаров с 10 товарами в каждой. Как сделать запрос к базе данных? Какие индексы нужно создать, чтобы ускорить выполнение запроса?

В начале давайте создадим таблицу products и заполним ее данными:

Таблица содержит 1 миллион товаров, разделенные на 5 категорий.

Первое, о чем вы можете подумать – это использовать LIMIT с GROUP BY , но это не сработает, т.к. в начале каждая группа будет схлопнута в одну строку, а после этого будет применен LIMIT . Чтобы все строки из одной группы могли схлопнуться в одну строку, для всех остальных колонок нужно использовать агрегатную функцию ( COUNT , SUM , AVG и др), иначе мы получим ошибку.

Это определенно не то, что мы хотим.

Существует 2 способа решить данную проблему. Давайте разберем оба варианта, поймем какие индексы необходимы для ускорения запросов и определим какой SQL-запрос самый быстрый.

Способ 1. Использование оконной функции

Оконная функция позволяет выполнять определенные действия над строками из каждой группы, также так же, как это делают агрегатные функции ( COUNT , SUM , AVG и др), но строки из одной и той же группы не схлопываются в одну строку.

Чтобы было понятнее, давайте добавим новую таблицу deals и выполним агрегатную фукнцию и оконную функцию.

В таблице 7 сделок. У первого пользователя 3 сделки, у второго 4 сделки.

В начале воспользуемся агрегатной функцией COUNT .

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

Теперь давайте рассмотрим оконную функцию COUNT . Чтобы ей воспользоваться, нужно после функции написать OVER() .

Как вы можете увидеть, было также вычислено количество всех строк, но в отличии от SELECT COUNT(*) FROM deals это количество выведено в каждой строке, а не в одной строке. Если OVER указан без параметров, то в 1 группу (окно) будут входить все строки из таблицы.

Чтобы вывести количество сделок по пользователю, нужно окном сделать не всю таблицу, а группу, используя PARTITION BY .

В качестве оконных функций можно использовать как любые агрегатные, так и специализированные оконные.

Давайте воспользуемся оконной функцией ROW_NUMBER , которая выводит номер текущей строки в группе.

У нас есть порядковый номер сделки по каждому пользователю. Теперь дополнительным запросом можно отфильтровать все строки, которые имеют порядковый номер меньше или равный 2. В итоге мы получим по 2 сделки на каждого пользователя.

Можно также задать порядок, в котором будут обрабатываться строки оконной функцией, используя ORDER BY внутри OVER .

Строки внутри каждой группы теперь отсортированы по колонке id .

Давайте вернемся к таблице products с 1 миллионом строк и посмотрим сколько времени будет выполняться подобный запрос.

Запрос выполнялся почти 4 сек.

Как вы можете увидеть, в начале был выполнен вложенный запрос. Для этого была просканирована вся таблица products , отсортирована по колонке id по убыванию и выполнена оконная фукнция, которая пронумеровала строки внутри каждой группы. Затем был выполнен основной запрос, который отфильтровал строки с порядковым номером меньше либо равным 10.

Чтобы ускорить этот запрос, давайте добавим B-Tree индекс по 2 колонкам: category_id и id , чтобы из таблицы products строки выбирались сразу в отсортированном порядке.

Еще раз выполним запрос.

Теперь запрос выполнился за 687 мс.

Если, как в моем случае, вам необходимо группировать не по 1 колонке, а по нескольким, то после PARTITION BY вы можете указать несколько колонок. Например, PARTITION BY record_id, field_id .

Если используется пагинация (постраничный вывод) для вывода категорий (если категорий много), то во внутреннем запросе нужно добавить условие WHERE category_id IN (1,2,4) , где 1,2,4 — это id категорий, которые нужно показать на данной странице (предположим, что категория с была удалена). В этом случае запрос выполнится еще быстрее.

Главный недостаток данного подхода заключается в том, что на первом этапе (в подзапросе) необходимо выбрать все строки, которые находятся в указанных группах (категориях товаров). Эти строки нумеруются оконной функцией и после этого происходит фильтрация первых N строк в каждой группе.

Можно ли сразу выбрать N строк для каждой группы, чтобы не тратить время на чтение всех строк и фильтрацию?

Способ 2. Использование JOIN LATERAL

Идеальным решением нашей проблемы было бы выполнение только 2 шагов:

Взять все группы. В нашем примере это категории товаров.

Для каждой группы взять только N результатов и объеденить в одну таблицу.

Давайте в начале выберем все категории товаров, которые используются в таблице products . Для этого воспользуемся DISTINCT , который возьмет из таблицы только строки с уникальным значением category_id . Подробнее об этом тут.

Теперь, используя JOIN LATERAL , для каждой группы возьем N результатов и объеденим это в одну таблицу. LATERAL позволяет в JOIN запросе ссылаться на уже объявленные ранее таблицы. Тем самым мы можем в начале получить все категории, а затем для каждой категории взять N товаров.

Запрос выполнился за 226 мс, что быстрее в 3 раза, чем запрос, в котором использовалась оконная функция. Запрос выполняется следующим образом:

Выбираются все категории, которые используются в таблице products и сортируются по возрастанию.

Для каждой категории, которые были выбраны в первом подзапросе, выбираются 2 последние товара. ON true означает, что все полученные строки будут объеденены со строками из таблицы с категориями.

Выбираются только колонки из JOIN-таблицы, чтобы избежать дублирования колонки category_id .

Для ускорения запроса необходим тот же индекс, который мы уже создали ранее:

Для выполнения первого подзапроса было произведено последовательное сканирование. Если выбрать, только часть из всех категорий (напр, 2 из 5), то будет использоваться индекс.

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

Скорее всего, у вас уже есть отдельная таблица с категориями товаров.

А значит нет смысла сканировать всю таблицу products для поиска этих категорий. Давайте возьмем эти категории сразу из таблицы categories .

Запрос выполнился за 0.2 мс, в 1000 раз быстрее предыдущего запроса с DISTINCT и в 3000 раз быстрее запроса с оконной функцией.

Выводы

Чтобы получить N строк в каждой группе лучше использовать запрос с JOIN LATERAL , который выполняется гораздо быстрее, чем запрос с оконной функцией. Это связано с тем, что при использовании оконной функции на первом этапе выбираются все строки в каждой группе, а уже после этого отсекаются ненужные строки. JOIN LATERAL позволяет сослаться на уже выбранные группы и для каждой группы сразу взять N строк.

Какой запрос строится не sql когда мы обращаемся к таблице остатков

Шекхар Шаши, Чаула Санжей
Издательство «КУДИЦ-ОБРАЗ»

Часть 3

3.1. СТАНДАРТНЫЕ ЯЗЫКИ ЗАПРОСОВ К БАЗАМ ДАННЫХ

3.1.1. База данных World

3.2.1. Операции выборки и проекции
3.2.2. Операции над множествами
3.2.3. Операция соединения

3.3.1. Язык описания данных
3.3.2. Язык модификации данных
3.3.3. Основная форма запроса на языке SQL
3.3.4. Примеры запросов на языке SQL
3.3.5. Реляционная алгебра и язык SQL: резюме

3.4.1. Стандарт OGIS как расширение языка SQL
3.4.2. Ограничения стандарта

3.6.1. Взгляд на SQL3
3.6.2. Объектно-реляционная схема
3.6.3. Примеры запросов

Библиографические Заметки

3.8.1. Примеры запросов на языке реляционной алгебры

Язык запросов, главное средство взаимодействия с базой данных, – это основное необходимое условие существования СУБД. Популярным коммерческим языком запросов к реляционным системам управления базами данных (RDBMS, relational database management system) является SQL. Отчасти он основан на формальном языке запросов, реляционной алгебре (РА), его отличают простота использования, интуитивность и универсальный характер. СУПБД представляют собой частный случай расширяемых СУБД и работают как с пространственными, так и с непространственными данными, поэтому вполне естественно попытаться найти расширение SQL, позволяющее обращаться к пространственным данным.

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

Конструкции объектно-ориентированного программирования, такие, как пользовательские типы, а также наследование данных и функций, нашли непосредственное применение в создании моделей сложных данных. Повсеместное использование реляционной модели и языка SQL в приложениях, включающих простые типы данных, в сочетании с функциональностью объектно-ориентированной модели привело к рождению новой «гибридной» парадигмы систем управления базами данных, именуемой ОР-СУБД (OR-DBMS).

Результатом интереса, проявленного к ОР-СУБД, стало желание расширить SQL путем введения объектной функциональности. Практическим следствием этой попытки стал новый стандарт SQL, рассчитанный на ОР-СУБД и носящий название SQL3. Работая с пространственными данными, мы изучим пространственные расширения и библиотеки SQL3.

Уникальная особенность пространственных данных состоит в том, что «естественная» среда взаимодействия с пользователем является наглядной, графической, а не текстовой. Поэтому любой язык пространственных запросов должен поддерживать сложный компонент графической визуализации. Показав это, мы сосредоточимся далее на неграфических пространственных расширениях SQL. В разделе 3.1 будет описана база данных World, которая послужит основой всех примеров запросов в этой главе. Разделы 3.2 и 3.3 соответственно содержат краткий обзор РА и языка SQL. Раздел 3.4 посвящен обсуждению требований, предъявляемых к пространственным расширениям SQL. Кроме того, мы расскажем о стандарте консорциума OGIS, предназначенном для расширения SQL применительно к географическим данным. В разделе 3.5 будет показано, как часто встречающиеся пространственные запросы можно сформулировать при помощи OGIS-расширения SQL. В разделе 3.6 вы познакомитесь с языком SQL3 и реализацией подмножества этого языка, предлагаемой фирмой Oracle.

Пользователи взаимодействуют с данными, находящимися в СУБД, посредством языка запросов. В отличие от традиционных языков программирования языки запросов к базам данных относительно просты в изучении и использовании. В этом разделе мы обсудим два таких языка запросов. Первый из них, РА, является более формальным, нежели второй, и обычно не реализуется в коммерческих базах данных. Говоря о важности РА, следует помнить, что она образует ядро SQL – самого популярного и имеющего множество реализаций языка запросов к базам данных.

С РА и языком SQL мы познакомимся на примере учебной базы данных. Новая учебная база данных требуется нам здесь для того, чтобы придать разнообразие примерам и упражнениям. База данных World (Мир) состоит из трех сущностей: Country (Страны), City (Города) и River (Реки). Расширенная путем введения пиктограмм диаграмма «сущность-связь» этой базы данных, а также примеры таблиц приведены соответственно на рис. 3.1 и в таблице 3.1. Схема базы данных показана ниже. Заметим, что подчеркнутые атрибуты являются первичными ключами. Например, Name является первичным ключом таблиц Country, City и River.

Рис. 3.1. Диаграмма «сущность-связь» базы данных World

Таблица 3.1. Таблицы базы данных World с примерами записей

Сущность Country имеет шесть атрибутов. Название страны (Name) и континент (Cont), где она находится, хранятся как символьные строки, максимальная длина которых равна 35. Население (Pop) и валовый внутренний продукт (GDP) имеют целый тип. ВВП – это совокупная стоимость товаров и услуг, произведенных в стране за один финансовый год. Атрибут Life-Exp отражает выраженную в годах (округленную до ближайшего целого) среднюю продолжительность жизни граждан страны. Атрибут очертания (Shape) требует некоторых пояснений. Геометрические очертания страны представлены столбцом Shape таблицы 3.1. В реляционных базах данных, где типы данных ограничены, атрибут Shape является внешним ключом к таблице очертаний. В объектно-реляционных или объектно-ориентированных базах данных атрибут Shape имеет абстрактный тип данных (АТД) «многоугольник». Поскольку в данный момент нашей целью является введение основ РА и SQL, мы не будем строить запросов по атрибуту Shape до изложения раздела 3.4.

Отношение City содержит пять атрибутов: Name, Country, Pop, Capital и Shape . Атрибут страна (Country) является внешним ключом по отношению к таблице Country. Столица (Capital) – это символьное поле длины 1 с фиксированным набором значений; город может либо быть столицей страны, либо не быть ею. Атрибут очертания (Shape) – это внешний ключ к таблице, содержащей образ, составленный из точек. Что же касается отношения Country , то мы не станем выполнять запросы к столбцу Shape до изучения типов данных OGIS в языке SQL3.

Четыре атрибута отношения River называются Name, Origin, Length и Shape . Атрибут исток (Origin) является внешним ключом отношения Country и определяет страну, где река берет свой исток. Атрибут очертания (Shape) представляет собой внешний ключ к таблице, содержащей образ, составленный из линий. Чтобы определить страну, на территории которой находится исток реки, геометрической информации, заданной атрибутом Shape , недостаточно. Перегрузку имен в таблицах можно преодолеть, используя «точечную» нотацию уточненного имени атрибута, содержащего название таблицы: таблица.атрибут. Конструкции Country.Name, city.Name и river.Name однозначно определяют атрибут Name в пределах различных таблиц. Также нам потребуется информация о направлении течения рек. В главе 7 мы обсудим пространственно-сетевые запросы, в которых важна информация о направлении.

Реляционная алгебра – это формальный язык запросов, связанный с реляционной моделью. Алгеброй называется математическая структура, состоящая из двух различных множеств ( Ωa , Ωo ). Ω a – множество операндов , Ωo – множество операций . Алгебра должна удовлетворять большому количеству аксиом, однако решающее значение имеет то, что результат операции над операндом должен входить во множество Ωa. Простым примером алгебры является множество целых чисел. Операндами являются целые числа, операциями – сложение и умножение. В главе 8 мы обсудим другие виды алгебр, связанных с растровыми объектами и изображениями.

В РА существует только один тип операндов и шесть базовых операций. Операнд – это отношение (таблица), к операциям же относятся выборка (selection), проекция (project), объединение (union), векторное произведение (cross-product), разность (difference) и пересечение (intersection). Представим некоторые из базовых операций более подробно.

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

Результат этой операции показан в таблице 3.2(а). Столбцы, возвращаемые операцией выборки σ, определяются оператором выборки, то есть оператором сравнения, который в этом примере записывается как cont = " Северная Америка ". Оператор выборки не изменяет схему входного отношения. Формально синтаксис операции выборки имеет вид:

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

Формально синтаксис операции проекции имеет вид:

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

Таблица 3.2. Результаты двух базовых операций РА: выборки и проекции

Объединение. Если R и S есть отношения, то возвращает все кортежи, которые встречаются или в R , или в S . Например, мы можем использовать операцию объединения для составления списка стран, которые либо расположены в Северной Америке, либо имеют реки, берущие исток на этом континенте.

1. R = π Name (σ Cont=СА (Country)).

2. S = π Origin (River).

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

1. R = π Name (σ Cont=СА (Country)).

2. S = π Origin (River).

1. R = π Name (σ Cont=ЮА (Country)).

2. S = π Origin (River).

Таблица 3.3. Векторное произведение отношений R и S

R	R.A	R.B
	A 1	B 1
	A 2	B 2

(а) Отношение R

S	S.C	S.D
	C 1	D 1
	C 2	D 2

(b) Отношение S

R × S	R.A	R.B	S.C	S.D
	A 1	B 1	C 1	D 1
	A 1	B 1	C 2	D 2
	A 2	B 2	C 1	D 1
	A 2	B 2	C 2	D 2

Таблица 3.4. Результаты операций над множествами

НАЗВАНИЕ

Канада

Мексика

Бразилия

Куба

США

(а) Объединение

НАЗВАНИЕ

Канада

Мексика

Куба

(б) Разность

НАЗВАНИЕ

Бразилия

3.2.3. Операция соединения

Операции выборки и проекции полезны для извлечения информации из единичного отношения. Операция соединения (join) используется для построения запросов к нескольким реляционным таблицам. Операцию соединения можно рассматривать как векторное произведение с последующей операцией выборки. Операция соединения общего вида называется условным (conditional) соединением. Важным специальным случаем условного соединения является естественное (natural) соединение.

Условные соединения

Условное соединение общего вида | c для отношений R и S выражается так:

Условие c обычно относится как к атрибутам R , так и к атрибутам S . Например, операция соединения может использоваться для формирования запроса названий тех стран, количество жителей которых превышает численность населения Мексики (см. таблицу 3.5).

1. R = π Name, Pop (Country).

2. S = R . (S – полная копия отношения R).

3. Построим векторное произведение R x S . Схема отношения R x S выглядит так:

4. Применим условие: количество жителей страны в отношении S превышает численность населения Мексики.

Естественное соединение

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

1. Переименуем отношение Country в C , отношение River – в R .

2. Вычислим векторное произведение C x R .

3. Выполним соединение обоих отношений по атрибутам C.Name и R.Origin . Области определения этих двух атрибутов идентичны:

4. При естественном соединении условие выборки не допускает двоякого толкования, поэтому отсутствует необходимость в его явной записи в качестве индекса формулы.

5. Окончательный результат получается в результате выполнения операции проекции по атрибутам Name и Pop :

Таблица 3.5. Результаты операций над множествами

SQL – коммерческий язык запросов, изначально разработанный корпорацией IBM. С момента появления он приобрел статус стандартного языка запросов к реляционным СУБД. SQL является декларативным языком, другими словами, пользователь должен описать только ответ на запрос, не указывая процедуру получения этого ответа.

Язык SQL включает, по меньшей мере, два отдельных компонента: язык описания данных (ЯОД, англ. DDL, data description language) и язык модификации данных (ЯМД, англ. DML, data modification language) 1 . ЯОД используется для создания, удаления или изменения структур описания таблиц базы данных. На ЯМД формулируются запросы, инициирующие вставку и удаление строк из таблиц, заданных средствами ЯОД. Кроме того, SQL содержит другие операторы языка управления данными. Здесь мы изложим краткое введение в SQL. Наша цель – рассказать об этом языке в той мере, насколько это необходимо, чтобы читатель смог по достоинству оценить пространственные расширения, которые мы будем обсуждать в разделе 3.4. Более подробное и полное изложение SQL можно найти в любой книге, посвященной базам данных [Elmasri and Navathe, 2000; Ullman and Widom, 1999].

Создание реляционной схемы, создание и удаление таблиц выполняются посредством одного из компонентов SQL – языка описания данных. Например, ниже на языке SQL описана схема отношения City , введенная в разделе 3.2. Таблицы Country и River описаны в таблице 3.6.

Оператор CREATE TABLE применяется для описания отношений, входящих в реляционную схему. Именем таблицы является CITY. Таблица состоит из четырех столбцов, при этом должно быть указано название каждого столбца и соответствующий тип данных. Атрибуты название (Name) и страна (Country) должны быть строками ASCII-символов длиной не более 35 символов. Атрибут население (Population) имеет целочисленный тип, а столица (Capital) – это однобуквенный атрибут, принимающий либо значение Д (да), либо значение H (нет). В языке SQL92 возможные типы данных закреплены стандартом и не могут определяться пользователем. Мы не приводим полного набора типов данных, который можно найти в любой книге по базам данных. Наконец, первичным ключом отношения служит атрибут Name . Значит, каждая строка таблицы должна иметь уникальное значение этого атрибута. Таблицы, которые более не используются, можно удалить из базы данных, применяя команду Drop Table . Еще одна важная команда ЯОД – Alter Table , она позволяет вносить изменения в схему отношения.

Таблица 3.6. Схемы таблиц Country и River на языке SQL

(а) Схема Country

(б) Схема River

После того, как таблица создана по правилам ЯОД, она готова принимать данные. Эта задача, которую часто называют «заселением таблицы» («populating the table»), выполняется средствами другого компонента SQL – языка модификации данных. К примеру, следующий оператор добавляет к таблице River одну строку:

Если заданы не все атрибуты отношения, выполняется автоматическая подстановка значений по умолчанию. Наиболее часто по умолчанию используется «пустое» значение NULL . Попытка добавить в таблицу River другую строку с Name = ‘Миссисипи’ будет отвергнута СУБД в силу ограничений по первичному ключу, которые сформулированы на ЯОД.

Основная форма оператора удаления строк из таблицы имеет такой вид:

Например, следующий оператор удаляет из таблицы River строку, которую мы только что вставили.

Когда схема базы данных описана средствами ЯОД, а таблицы заполнены данными, можно формулировать SQL-запросы на извлечение из базы нужных подмножеств данных. Основная синтаксическая конструкция запроса на языке SQL чрезвычайно проста:

Эта форма эквивалентна выражению РА, состоящему из операций π, σ и |. SQL-оператор SELECT включает большее число предложений, относящихся к агрегированию (например, GROUP BY, HAVING ), упорядочению результатов (например, ORDER BY ) и т. д. Более того, SQL позволяет составлять вложенные запросы. Проиллюстрируем сказанное рядом примеров.

Приведем несколько примеров того, как составляются различные типы SQL-запросов. Мы хотим передать дух универсальности и мощи оператора SELECT. Все таблицы, используемые в запросах, входят в учебную базу данных WORLD , представленную в разделе 3.1.1. Результаты различных запросов можно найти в таблицах 3.7 и 3.8.

1. Запрос. Составить список всех городов в таблице City и тех стран, где эти города находятся.

Примечания. Выражение SQL эквивалентно операции проекции в РА. Предложение WHERE отсутствует в SQL-выражении потому, что в этом запросе не требуется использовать конструкцию, эквивалентную реляционно-алгебраической операции выборки. Отметим также необязательное применение каскадной «точечной» нотации. Таблица CITY переименована в Ci , и для обращения к ее атрибутам используются имена Ci.Name и Ci.Country .

2. Запрос. Составить список названий столиц, перечисленных в таблице CITY .

Примечания. Это SQL-выражение эквивалентно операции выборки , введенной в РА. К сожалению, реляционно-алгебраическая операция выборки задается в языке SQL ключевым словом WHERE , а не предложением SELECT ! Знак * в предложении SELECT означает, что должны быть перечислены все атрибуты, входящие в таблицу CITY .

3. Запрос. Составить список атрибутов стран, занесенных в отношение Country , где средняя продолжительность жизни меньше 70 лет.

Примечания. В терминах РА это выражение эквивалентно π ○ σ. Проецируемые атрибуты, в нашем примере это Co.Name и Co.Life-Exp , определяются предложением SELECT . Условие выборки задается ключевым словом WHERE .

4. Запрос. Составить список столичных городов и показателей численности населения тех стран, ВВП которых превышает один триллион долларов.

Примечания. Это неявный способ выражения операции соединения . SQL2 и SQL3 также поддерживают явную операцию JOIN. В таком случае обе таблицы City и Country сопоставляются по своим общим атрибутам Ci.country и Co.name . Более того, два условия выборки из таблиц City и Country задаются независимо друг от друга. Обратите внимание на то, как каскадная «точечная» нотация устраняет потенциальный конфликт, который может возникнуть в результате использования одинаковых имен атрибутов в обоих отношениях.

5. Запрос. Как называется столица и какова численность населения страны, где начинается река Св. Лаврентия?

Примечания. Выполнение этого запроса требует соединения трех таблиц. Таблицы River и Country соединяются по атрибутам Origin и Name. Таблицы Country и City соединяются по атрибутам Name и Country. Соответственно имеются два условия выборки по таблицам River и City .

6. Запрос. Какова средняя численность населения нестоличных городов, перечисленных в таблице City ?

Примечания. AVG (среднее, англ. average) – это пример операции агрегирования, то есть расчета составного атрибута. Подобные операции отсутствуют в РА. Кроме AVG , есть и такие операции агрегирования, как COUNT, MAX, MIN и SUM . Операции агрегирования расширяют функциональность языка SQL, поскольку они позволяют производить вычисления над полученными данными.

7. Запрос. Найти средний ВВП каждого континента.

Примечания. Данный запрос иллюстрирует наиболее характерное отступление от базового формата SQL-запроса. Оно связано с наличием предложения GROUP BY , которое разбивает таблицу на основе указанного в нем атрибута. В приведенном примере Co.cont может принять одно из двух значений: СА и ЮА. Следовательно, таблица Country делится на две группы записей. Значение среднего ВВП (GDP) рассчитывается для каждой группы отдельно. Затем оно сохраняется так, как предписывает предложение SELECT , то есть в виде атрибута Continent-GDP (ВВП-континента).

8. Запрос. Для каждой страны, на территории которой расположены истоки хотя бы двух рек, определить длину самой короткой реки.

Примечания. Этот запрос подобен предыдущему. Отличие состоит в том, что предложение HAVING позволяет задавать условия выборки для различных групп, сформированных предложением GROUP BY . Таким образом, в рассмотрение принимаются только те группы, которые насчитывают более одного члена.

9. Запрос. Составить список стран, ВВП которых превосходит ВВП Канады.

Примечания. Это пример вложенного запроса. К таковым относятся запросы, содержащие другие, встроенные в них запросы. Вложенный запрос становится обязательным, когда перед расчетом запроса требуется создать промежуточную таблицу, которой не существует. Вложенный запрос обычно встречается в предложении WHERE , однако, кроме того, хотя и редко, может появляться в предложениях FROM и SELECT. ANY – оператор сравнения множеств. Полный обзор вложенных запросов вы сможете найти в любой книге по базам данных.

Таблица 3.7. Таблицы-результаты операций выборки, проекции и выборки-проекции

Название	Страна	Население (млн)	Столица	Очертания
Гавана	Куба	2,1	Д	Point
Вашингтон	США	3,2	Д	Point
Бразилиа	Бразилия	1,5	Д	Point
Оттава	Канада	0,8	Д	Point
Мехико	Мексика	14,1	Д	Point
Буэнос-Айрес	Аргентина	10,75	Д	Point

(а) Запрос 2. Выборка

Название	Страна
Гавана	Куба
Вашингтон	США
Монтеррей	Мексика
Торонто	Канада
Бразилиа	Бразилия
Росарио	Аргентина
Оттава	Канада
Мехико	Мексика
Буэнос-Айрес	Аргентина

(б) Запрос 1. Проекция

Название	Продолжительность жизни
Мексика	69,36
Бразилия	65,60

(в) Запрос 3. Выборка и проекция

Таблица 3.8. Результаты выполнения примеров запросов

Ci.Name	Co.Pop
Бразилиа	183,3
Вашингтон	270,0

(а) Запрос 4

Ci.Name	Ci.Pop
Вашингтон	3,2

(б) Запрос 5

Среднее население

2,2

(в) Запрос 6

Континент	ВВП континента
СА	2343,05
ЮА	676,1

(г) Запрос 7

Исток	Мин-длина
США	1200

(д) Запрос 8

Co.Name

Мексика

Бразилия

США

РА – формальный язык запросов к базам данных. Обычно она не реализуется в коммерческих СУБД, однако образует ядро SQL. SQL – наиболее популярный и имеющий множество реализаций язык баз данных. Он включает два компонента: ЯОД и ЯМД. Схема таблиц базы данных определяется и заселяется средствами ЯОД. Запросы к базе данных фактически составляются на ЯМД. Мы сделали краткий обзор языка SQL. Более подробная информация приведена в любой книге по базам данных.

Даже будучи мощными языками обработки запросов, РА и SQL имеют свои недостатки. Основным из них является то, что эти языки традиционно обеспечивали поддержку лишь простых типов данных, например целых чисел, дат и строк. Приложения пространственных БД должны работать со сложными типами, такими, как точки, линии и многоугольники. Производители баз данных ответили на это двояко: либо для хранения пространственной информации они стали использовать большие двоичные объекты (blobs, binary large objects), либо создали гибридные системы, в которых пространственные атрибуты сохраняются в файлах операционной системы средствами ГИС. SQL не может обрабатывать данные, хранимые как blob-атрибуты, и ответственность за обработку данных в форме больших двоичных объектов ложится на приложение [Stonebraker and Moore, 1997]. Это решение не является ни эффективным, ни эстетичным, поскольку данные зависят от кода приложения на некотором языке программирования. В гибридных системах пространственные атрибуты хранятся в виде отдельных файлов операционной системы и потому не могут пользоваться преимуществами традиционных служб базы данных, таких, как язык запросов, управление параллелизмом и поддержка индексирования.

Основное влияние на расширение возможностей СУБД в части поддержки пространственных (сложных) объектов оказали объектно-ориентированные системы. Программа, призванная обогатить реляционную базу данных объектно-ориентированными функциями, укладывается в общие рамки концепции ОР-СУБД. Ключевое свойство ОР-СУБД состоит в том, что такая система работает с SQL3/SQL99, версией языка SQL, которая, в свою очередь, поддерживает понятие пользовательских типов (как в языках Java или C++). Нашей задачей является изучение SQL3/SQL99 в той мере, насколько это необходимо для использования его как средства манипулирования пространственными данными и их выборки.

Главное требование к пространственному языку SQL лежит в плоскости обеспечения более высокого уровня абстракции в представлении пространственных данных путем введения понятий, более близких к нашему пониманию пространства [Egenhofer, 1994]. Для достижения этого вводится объектно-ориентированное понятие пользовательских АТД. АТД – это пользовательский тип и связанные с ним функции. Например, если мы располагаем информацией о земельных участках, хранящейся в базе данных в виде многоугольников, то полезным АТД могла бы стать комбинация типа « многоугольник » и нескольких связанных с ним функций (методов), скажем функции смежности adjacent . Функция adjacent может применяться по отношению к земельным участкам для того, чтобы определить, имеют ли они общие границы. Понятие « абстрактный » используется потому, что конечному пользователю нет необходимости знать детали реализации функций, связанных с этим типом. Все, что должны знать конечные пользователи, – это средства взаимодействия, то есть доступные функции и типы данных для входных параметров и конечного результата.

Консорциум OGIS был создан ведущими производителями программного обеспечения с целью выработки промышленного стандарта, регламентирующего взаимодействие ГИС. Модель пространственных данных OGIS может быть встроена в большое количество различных языков программирования, например C, Java, SQL и т. д. В этом разделе мы остановимся на встраивании этой модели в язык SQL.

Стандарт OGIS основан на модели геометрических данных, представленной на рис. 2.2. Вспомним, что эта модель данных состоит из базового класса GEOMETRY , который является абстрактным, – иначе говоря, мы не можем описать объекты как экземпляры этого класса, – однако задает пространственную систему координат, применимую ко всем производным от него классам. Четыре основных класса, порожденных от предка GEOMETRY , – это Point, Curve, Surface и GeometryCollection. С каждым из этих классов связан набор операций, выполняемых над экземплярами классов. Некоторые важные операции, а также их назначение приведены в таблице 3.9.

Операции, определенные в стандарте OGIS, делятся на три категории.

Таблица 3.9. Примеры операций, входящих в стандарт OGIS [OGIS, 1999]

Базовые функции	SpatialReference()	Возвращает базовую систему координат геометрии
	Envelope()	Возвращает минимальный ортогональный ограничивающий прямоугольник геометрии
	Export()	Возвращает альтернативное представление геометрии
	IsEmpty()	Возвращает истинное значение, если геометрия является пустым множеством
	IsSimple()	Возвращает истинное значение, если геометрия является простой (без самопересечений)
	Boundary()	Возвращает границы геометрии
Топологические операции и операции над множествами	Equal	Возвращает истинное значение, если внутренние области и границы обеих геометрий пространственно равны
	Disjoint	Возвращает истинное значение, если границы и внутренняя область не пересекаются
	Intersect	Возвращает истинное значение, если геометрии имеют общие элементы
	Touch	Возвращает истинное значение, если границы двух поверхностей пересекаются, а внутренние области – нет
	Cross	Возвращает истинное значение, если внутренняя область поверхности пересекается кривой
	Within	Возвращает истинное значение, если внутренняя область одной геометрии не пересекается с внешней областью другой геометрии
	Contains	Проверяет, содержит ли одна геометрия другую
	Overlap	Возвращает истину, если внутренние области двух геометрий имеют непустое пересечение
Пространственный анализ	Distance	Возвращает кратчайшее расстояние между двумя геометриями
	Buffer	Возвращает геометрию, содержащую все точки, лежащие на указанном или меньшем расстоянии от данной геометрии
	ConvexHull	Возвращает наименьшее выпуклое геометрическое множество, заключающее в себе данную геометрию
	Intersection	Возвращает геометрическое пересечение двух геометрий
	Union	Возвращает геометрическое объединение двух геометрий
	Difference	Возвращает фрагмент геометрии, который не пересекается с другой геометрией
	SymmDiff	Возвращает фрагменты двух геометрий, которые не пересекаются друг с другом

1. Базовые операции, применимые ко всем геометрическим типам данных. Например, SpatialReference возвращает базовую систему координат, в которой описана геометрия объекта. К числу распространенных систем координат относятся широко известная система широт и долгот , а также часто используемая система Universal Traversal Mercator (UTM).

2. Операции, выявляющие топологические отношения между пространственными объектами. Например, операция overlap проверяет, имеют ли внутренние области двух объектов (см. главу 2) непустое множество пересечений.

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

Спецификация OGIS ограничена объектной моделью пространства. Как было показано в предыдущей главе, пространственная информация иногда наиболее естественным образом отображается в модели на основе полей. Сейчас OGIS находится на стадии выработки соглашения, разрабатывая модели для полевых типов данных и операций. В главе 8 мы покажем некоторые операции, которые применимы к моделям на основе полей и могут быть включены в будущий стандарт OGIS.

Даже в рамках объектной модели операции OGIS ограничиваются простыми запросами ВЫБОРКИ – ПРОЕКЦИИ – СОЕДИНЕНИЯ . Поддержка пространственных запросов агрегирования, содержащих предложения GROUP BY и HAVING , ставит ряд проблем (см. упражнение 4). Наконец, стандарт OGIS нацелен исключительно на базовые топологические и метрические пространственные соотношения. Отсутствует поддержка целого класса метрических операций, а именно операций на основе предиката направления (например, «на север», «на юг», «левая сторона», «передняя часть»). Также не поддерживаются динамические операции, которые основаны на очертаниях и понятии видимости и обсуждались в разделе 2.1.5.

Таблица 3.10. Основные таблицы

Используя типы данных и операции стандарта OGIS, мы сформулируем SQL-запросы к базе данных World , иллюстрирующие пространственные соотношения между тремя сущностями: странами ( Country ), городами ( City ) и реками ( River ). Для начала переопределим реляционную схему, предполагая доступность в языке SQL типов данных и операций OGIS. Пересмотренная схема показана в таблице 3.10.

1. Запрос. Отыскать в таблице Country названия всех стран, которые являются соседями Соединенных Штатов.

Примечания. Предикат Touch проверяет, являются ли два любых геометрических объекта смежными без наложения. Эта операция полезна для отыскания соседних объектов. Операция Touch – один из восьми топологических предикатов, описанных в стандарте OGIS. Приятно отметить одно из свойств топологических операций – их инвариантность по отношению ко многим геометрическим преобразованиям. В частности, на результаты топологических операций не влияет выбор координатной системы базы данных World .

Топологические операции применимы к большому числу различных сочетаний геометрических типов данных. Следовательно, в идеальной ситуации эти операции должны быть определены в стиле «перегрузки». К сожалению, многие объектно-реляционные СУБД не поддерживают таких понятий объектно-ориентированной парадигмы, как наследование классов и перегрузка операций. Поэтому в практических целях для каждой комбинации применяемых геометрических типов эти операции можно определить отдельно.

2. Запрос. Для всех рек, перечисленных в таблице River , определить страны, по которым они протекают.

Примечания. Cross – еще один топологический предикат. Чаще всего он используется для проверки наличия пересечения между объектами LineString и Polygon , как в этом примере, или между парой объектов LineString .

3. Запрос. Какой город, содержащийся в таблице City , ближе других расположен ко всем рекам, перечисленным в таблице River ?

Примечания. Distance – двухместная операция вещественного типа. Используется в предложении WHERE и еще раз – в предложении SELECT подзапроса. Функция Distance описана для любых комбинаций геометрических объектов.

4. Запрос. Река Св. Лаврентия может снабжать водой города, удаленные от нее не далее чем на 300 км. Составить список городов, которые могут получать воду из реки Св. Лаврентия.

Примечания. Буфером ( Buffer ) геометрического объекта называется центрированная относительно объекта геометрическая область, размер которой определяется параметром операции Buffer . В данном примере размер буферной области определяется запросом. Операция нахождения буфера используется во многих приложениях ГИС, включая противопаводочные мероприятия, а также муниципальные правила городского и сельского районирования. Графическое изображение операции нахождения буфера показано на рис. 3.2. Города A и B здесь, вероятно, будут затронуты наводнением, тогда как город C останется за пределами зоны подтопления.

Рис. 3.2. Буфер реки и точки внутри и вне буфера

5. Запрос. Составить список названий, численности населения и площади стран, содержащихся в таблице Country .

Примечания. Этот запрос иллюстрирует применение функции Area . Данная функция применяется только для геометрических типов Polygon и MultiPolygon . Очевидно, что вычисление результата Area зависит от базовой системы координат базы данных World . Например, если очертания в кортежах таблицы Country заданы широтой и долготой, то расчету значения Area должно предшествовать промежуточное преобразование координат. То же справедливо для функций Distance и Length.

6. Запрос. Составить список длин рек в пределах каждой страны, по которой они протекают.

Примечания. Значение, возвращаемое двухместной операцией Intersection , имеет геометрический тип. Операция Intersection отличается от функции Intersects , которая играет роль топологического предиката, определяющего, пересекаются ли две геометрии. Результат операции Intersection над объектами типа LineString и Polygon может иметь тип Point или LineString . Если река пересекает территорию страны, то результат будет иметь тип LineString . В этом случае функция Length возвратит ненулевую длину реки в каждой стране, по которой та протекает.

7. Запрос. Для всех стран составить список ВВП и расстояний от столичных городов до экватора.

Примечания. Поиск неявных соотношений между наборами данных, хранящимися в базе данных, не входит в функции стандартных СУБД. Современные системы баз данных развиваются в направлении оперативной обработки транзакций (OLTP), в то время как приведенный запрос, в том виде, как он сформулирован, относится к сфере аналитической обработки (OLAP). Саму технологию OLAP относят к технологиям добычи данных, нам предстоит рассмотреть эту тему в главе 8. Лучшее, что мы можем сделать на данный момент, – перечислить все столицы и их удаленность от экватора.

Point(0,Ci.Shape.y) – это точка на экваторе, имеющая ту же долготу, что и текущая столица, представленная атрибутом Ci.Name . Результаты показаны в таблице 3.11.

8. Запрос. Составить список всех стран, упорядоченный по количеству государств-соседей.

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

9. Запрос. Перечислить страны, имеющие только одного соседа. Страна является соседней по отношению к другой стране, если их территории имеют общую сухопутную границу. В соответствии с этим определением островные государства, скажем Исландия, соседей не имеют.

Примечания. Здесь мы имеем вложенный запрос, находящийся в предложении FROM . Результатом запроса в конструкции FROM является таблица, состоящая из пар стран, которые являются соседями. Предложение GROUP BY делит новую таблицу на части в соответствии с названиями государств. Наконец, HAVING инициирует принудительное объединение результатов выборки в пары с теми странами, которые имеют только одного соседа. Ключевое слово HAVING играет ту же роль, что и WHERE, за исключением одного: HAVING должно включать такие функции агрегирования, как count, sum, max и min .

10. Запрос. Какая страна имеет наибольшее число соседей?

Примечания. Этот запрос демонстрирует применение представлений (views) с целью упрощения сложных запросов. Первый запрос (представление) вычисляет количество соседей каждой страны. Представление создает виртуальную таблицу, которая в последующих запросах может использоваться как обычная таблица. Второй запрос производит выборку страны с наибольшим количеством соседей из представления Neighbor (Соседи).

Таблица 3.11. Результаты запроса 7

Co.Name	Co.GDP	Расст-до-экватора (в км)
Гавана	16,9	2562
Вашингтон	8003	4324
Бразилиа	1004	1756
Оттава	658	5005
Мехико	694,3	2161
Буэнос-Айрес	348,2	3854

Стандарт OGIS содержит описание типов данных и связанных с ними операций, которые считаются необходимыми для таких пространственных приложений, как ГИС. Например, тип данных Point обладает важной операцией Distance , вычисляющей расстояние между двумя точками. Операция length в отношении типа Point оказывается семантически некорректной. Эти наблюдения по своему характеру напоминают мысль о том, что операция concatenation (сцепление) более осмысленна для типа данных Character , нежели, скажем, для типа Integer .

В реляционных базах данных набор типов фиксирован. В объектно-реляционных и объектно-ориентированных базах данных подобное ограничение ослаблено, и сделано это с целью поддержки пользовательских типов данных. Даже несмотря на то, что подобная возможность оборачивается явным преимуществом, особенно при работе с такими нетрадиционными приложениями баз данных, как, например, ГИС, бремя описания синтаксически и семантически корректных типов данных теперь лежит на разработчике приложения базы данных. Чтобы взять часть этого бремени на себя, производители коммерческих баз данных начали создавать «пакеты», нацеленные на конкретные приложения и предоставляющие пользователям баз данных средства «бесшовного» интерфейса. Например, корпорация Oracle предлагает ориентированный на ГИС пакет под названием Spatial Data Cartridge .

SQL3/SQL99, предложенный в качестве стандарта языка SQL для ОР-СУБД, предоставляет возможность описывать пользовательские типы данных в реляционной базе. Сейчас мы расскажем о двух особенностях стандарта SQL3, которые могут оказаться полезными при описании пользовательских пространственных типов данных.

Язык SQL3/SQL99 предлагает два основных расширения SQL2/SQL92, принятой на сегодняшний день черновой версией SQL.

1. АТД. АТД может быть описан при помощи оператора CREATE TYPE . Как и классы в объектно-ориентированной технологии, АТД состоит из атрибутов и функций-членов, предназначенных для доступа к значениям атрибутов. Функции-члены могут изменять значения атрибутов типа данных, а значит, могут изменять и состояние базы данных. АТД можно использовать как тип столбца реляционной схемы. Для доступа к значению, которое содержится в АТД, нужно вызвать функцию-член, описанную в операторе CREATE TYPE . Например, следующий код порождает тип Point и описывает одну функцию-член Distance :

Двоеточия перед u и v указывают, что это – локальные переменные.

2. Тип строки. Тип строки (row type) – это тип отношения. Тип строки задает схему отношения. Например, следующий оператор объявляет тип строки с именем Point :

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

В этой книге особое значение мы будем придавать использованию АТД, а не типов строк. Это связано с тем, что АТД в роли типа столбца естественным образом согласуется с описанием ОР-СУБД как расширенной реляционной базы данных.

Oracle8 – это ОР-СУБД, разработанная Oracle Corporation. Подобные продукты предлагаются и другими производителями баз данных, например фирмой IBM. В ОР-СУБД Oracle частично реализован стандарт SQL3. АТД носит в этой системе название «объектного типа» («object type»).

Рассмотрим, как в Oracle8 построены три базовых типа пространственных данных: Point, LineString и Polygon .

Тип Point имеет два атрибута x и y , а также одну функцию-член Distance . PRAGMA указывает на то, что функция Distance не изменяет состояние базы данных: WNDS (Write No Database State). Разумеется, в стандарте OGIS описано большое количество других операций, относящихся к типу Point , однако для простоты мы показали только одну. После создания тип Point может использоваться как тип атрибута в отношениях. Например, схему отношения City можно описать так:

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

Создание типа данных LineString несколько сложнее аналогичной процедуры для типа Point . Начнем с создания промежуточного типа LineType :

LineType – это массив переменного размера, который состоит не более чем из 500 элементов типа Point . Если тип объявлен как Varray , то функции-члены этого типа описывать запрещено. Поэтому сконструируем другой тип LineString:

Атрибут Num_of_Points (количество точек) содержит размер (число элементов типа Point ) каждого экземпляра типа LineString . Теперь мы готовы к заданию схемы таблицы River :

При добавлении данных в таблицу River нужно отслеживать различные используемые типы данных.

Тип Polygon аналогичен типу LineString . Последовательность операций описания типов, создания таблицы и добавления данных приведена в таблице 3.12.

Таблица 3.12. Последовательность операций при создании таблицы Country

1. Запрос. Перечислить все пары городов из таблицы City и расстояния между ними.

Примечания. Обратите внимание на объектно-ориентированную форму записи функции Distance в предложении SELECT . Сравните ее с обычной нотацией, использованной в разделе 3.5: Distance(C1.Shape, C2.Shape) . Условие в предложении WHERE гарантирует, что функция Distance не применяется по отношению к двум копиям одного и того же города.

2. Запрос. Проверить правильность вычисления длин рек, которые приведены в таблице River , на основе геометрической информации в атрибуте Shape .

Примечания. Этот запрос используется для проверки корректности данных. Длины рек уже содержатся в таблице River в виде атрибута Length . Используя функцию Length() , мы можем убедиться в целостности данных таблицы.

3. Запрос. Привести названия, численность населения и площадь всех стран, граничащих с США.

Примечания. Area() – естественная функция, поддерживаемая АТД Polygon . Наряду с ней запрос также использует топологический предикат Touch .

В этой главе мы обсудили языки запросов к базам данных и рассмотрели следующие темы.

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

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

Стандарт OGIS содержит рекомендуемый набор пространственных типов данных и функций, считающихся необходимыми для работы с запросами к пространственным данным.

SQL3/SQL 1999 представляет собой стандартизированную платформу объектно-реляционного расширения языка SQL. Эта платформа не ориентирована исключительно на ГИС или пространственные базы данных, ее действие распространяется на объектно-реляционные базы данных общего назначения. Самый естественный вариант ее внедрения состоит в реализации стандарта OGIS в составе подмножества языка SQL3.

3.1, 3.2, 3.3. Полное изложение реляционной алгебры и SQL можно найти в любой книге, посвященной введению в базы данных, в том числе в работах [Elmasri and Navathe, 2000; Ramakrishnan, 1998; Ullman and Widom, 1999].

3.4, 3.5. Расширения SQL для пространственных приложений изложены в [Egenhofer, 1994]. Нормативный документ консорциума OGIS [OpenGIS, 1998] представляет собой попытку согласования с языком SQL различных пространственных расширений. Примеры языков запросов для поддержки анализа пространственных данных приведены в статье [Lin and Huang, 2001].

3.6. SQL 1999/SQL3 – принятый стандарт объектно-реляционного расширения SQL. Подмножества этого стандарта уже реализованы в коммерческих продуктах, включая Oracle8 компании Oracle и DB2 корпорации IBM.

УПРАЖНЕНИЯ

Все запросы в упражнениях 1 и 2 относятся к информации, приведенной в таблице 3.1.

Найти все страны, ВВП которых выше 500 млрд дол., но ниже 1 трлн дол.
Привести среднюю продолжительность жизни в странах, на территории которых расположены истоки рек.
Найти все страны, расположенные в Южной Америке либо имеющие население менее 2 млн чел.
Составить список городов, находящихся за пределами Южной Америки.

2. Выразите на языке SQL запросы, перечисленные в упражнении 1.

Вычислить количество стран, в которых проживает менее 100 млн чел.
Найти в Северной Америке государство с наименьшим ВВП. Не использовать функцию Min . Указание: вложенный запрос.
Составить список стран, расположенных в Северной Америке или имеющих столицы с числом жителей менее 5 млн чел.
Найти страну, занимающую второе место по уровню ВВП.

4. Reclassify (см. раздел 2.1.5) – это функция агрегирования, которая объединяет пространственные геометрические объекты на основе непространственных атрибутов. Она создает новые объекты из уже существующих, в общем случае, путем удаления внутренних границ между соседними многоугольниками, в которых выбранный атрибут принимает одинаковое значение. Можно ли выразить операцию Reclassify при помощи операций OGIS и языка SQL92 с поддержкой пространственных типов данных? Поясните ответ.

5. Рассмотрим геометрическую модель данных на рис. 2.2. Принимая, что в «мировом» масштабе города представляются точечными типами данных, ответьте на вопрос: какой тип данных нужно использовать для представления стран мира? Примечание. Сингапур, Ватикан и Монако – это государства. Каковы будут результаты реализации пространственных функций, рекомендованных стандартом OGIS?

6. Автор статьи [Egenhofer, 1994] предлагает список требований к расширениям языка SQL для пространственных приложений. Эти требования перечислены ниже. Какие из рекомендаций вошли в стандарт OGIS SQL? Назовите возможные причины того, что внедрение прочих рекомендаций было отложено.

Пространственные АТД	Пространственная иерархия абстрактных типов данных и операции над ними
Графическое представление	Естественная среда взаимодействия с пространственными данными
Сочетание результатов	Сочетание результатов последовательности запросов
Контекст	Погружение результата в тот или иной контекст путем включения информации, которая не запрашивалась в явном виде
Изучение контекста	Обеспечение механизмов управления процессом построения карты
Выбор методом указания	Постановка и задание ограничений путем указания на карту
Манипуляции с изображением	Изменение графического представления пространственных объектов и их частей
Легенда	Описательная легенда
Метки	Метки для лучшего понимания изображения
Выбор масштаба карты	Построенная карта должна дать пользователю возможность применять свои навыки в интерпретации фактического размера изображенных объектов; выбор указанного масштаба визуализации
Интересующая область	Средства сужения интересующей области до конкретного географического района

7. Стандарт OGIS включает набор топологических пространственных предикатов. Как следует расширить стандарт с целью включения в него предикатов направления , таких, как восток, север, северо-восток и т. д.? Заметим, что предикаты направления могут допускать нечеткую формулировку: «Где заканчивается северо-восток и начинается восток?»

8. Это упражнение связано с исследованием пространственно-расширенной модели девяти пересечений DE-9IM (dimension-extended nine-intersection model). Она расширяет описанную в главе 2 модель девяти пересечений Эгенхофера. Матрица-шаблон модели DE-9IM показана ниже.

Ключевое различие между 9IM и DE-9IM состоит в том, что проверка каждого элемента матрицы на пустоту заменяется в модели DE-9IM требованием наличия лишь информации о размерности геометрического объекта. Размерность плоских двухмерных объектов может принимать четыре значения: –1 для пустого множества, 0 для точек, 1 для линий и 2 для объектов ненулевой площади. Во многих случаях значение элемента матрицы не имеет значения. Приведем список значений, которые могут содержаться в элементах матрицы.

T : X и Y должны пересекаться: = 0, 1, 2. X и Y – это либо внутренние, либо внешние области, либо границы A и B соответственно.

F : = –1. X и Y должны быть непересекающимися.

*: Существование пересечения не имеет значения. = <–1, 0, 1, 2>.

Ниже приведена сигнатурная матрица (signature matrix) двух равных объектов.

Какой вид имеет сигнатурная матрица (матрицы) топологических операций touch и cross ? Заметим, что сигнатурная матрица зависит от комбинации типов данных. Сигнатурная матрица комбинации точка/точка отличается от аналогичной матрицы комбинации составной многоугольник/составной многоугольник.
Какую операцию (и комбинацию типов данных) представляет следующая сигнатурная матрица?

Рис. 3.3 . Примеры объектов [Clementini and Felice, 1995]

Перечислить все города из таблицы City , находящиеся в пяти тысячах миль от Вашингтона.
Какова длина участков реки Парана в Аргентине и Бразилии?
Имеют ли Аргентина и Бразилия общую границу?
Составить список стран, которые полностью лежат к югу от экватора.

Составить список всех рек, протекающих по территории леса Итаска Стейт-Форест.
Перечислить все гудронные дороги, пересекающие Фрэнсис-Форест.
Найти все дороги, которые проходят в пределах поймы реки Монтана и могут затапливаться во время наводнения.
В двухмильной зоне от реки Ред-Ривер и в пяти милях от государственного парка «Биг-Три» запрещено городское строительство. Найти земельные участки, которые нельзя застраивать, а также округа, на территории которых эти участки находятся.
Река служит участком границы округа.

11. Изучите такие компиляторы, как YACC (Yet Another Compiler Compiler). Разработайте синтаксическую схему создания операторов описания данных на языке SQL3, пользуясь аннотированными пиктограммами на диаграммах «сущность-связь».

Река (LineString) начинается на территории округа (Polygon).
Страна (например, государство Ватикан) полностью окружена территорией одной другой страны (например, Италии).
Одна река (например, Миссури) впадает в другую (например, Миссисипи).
Лес является частью другого лесного массива.

13. Обратитесь к примерам запросов на языке РА, которые приведены в Приложении, посвященном базе данных «Государственный парк». Составьте SQL-выражения, соответствующие каждому запросу на языке РА.

14. Перечертите приведенную на рис. 3.4 диаграмму «сущность-связь», используя пиктограммы. Как в новой диаграмме следует представить атрибуты Fishing-Opener (Открытие-сезона) и Distance (Расстояние)? Создайте таблицы, преобразуя полученную в результате диаграмму «сущность-связь» с использованием конструкций SQL3/OGIS.

15. Проанализируйте проекты таблиц на рис. 1.3 и 1.4. Опишите SQL-запросы для вычисления пространственных свойств (например, площади, периметра) переписных участков, используя все способы представления. Какое представление позволяет получить более простые запросы?

16. Вернитесь к Java-программе из раздела 2.1.6. Напишите на языке Java программу для выполнения пространственных запросов, перечисленных в разделе 3.6.3. Сравните составление запросов к пространственным наборам данных на языках Java и SQL3/OGIS.

17. Определите средствами SQL3 пользовательские типы данных, предназначенные для представления геометрических типов составных данных стандарта OGIS.

18.Вернитесь к реляционной схеме «Государственного парка» в примере раздела 2.2.3. Кратко опишите SQL-операторы ЯМД для создания соответствующих таблиц при помощи пространственного типа данных OGIS.

19. Проанализируйте запросы на основе информации об очертаниях объектов – например, перечислить страны, границы которых похожи на дамский сапог, или переписные участки квадратной формы. Предложите расширение языка SQL3/OGIS для поддержки подобных запросов.

20. Проанализируйте запросы на основе информации о видимости например, перечислить объекты, которые видны (не загораживаются) при данном положении наблюдателя. Предложите набор типов данных и операций, образующих расширение SQL3/OGIS для поддержки запросов такого вида.

База данных State Park (Государственный парк) состоит из двух сущностей: Park (Парк) и Lake (Озеро). Атрибуты этих двух сущностей и связей показаны на рис. 3.4. Отображение диаграммы «сущность-связь» на реляционную схему приведено ниже. Сущности и связи между ними представлены в таблице 3.13.

Данная схема представляет три сущности: StatePark, Lake и ParkLake . Сущность StatePark представляет все государственные парки штата Миннесота, а ее атрибутами являются уникальный национальный идентификатор Sid , название парка Sname , площадь парка в квадратных километрах Area , а также расстояние от Миннеаполиса – Distance . Сущность Lake также имеет уникальный идентификатор Lid и название Lname ; средняя глубина озера хранится в атрибуте Depth , самый распространенный в озере вид рыб – в атрибуте Main-catch . Сущность ParkLake применяется для соединения запросов к обеим сущностям – StatePark и Lake . Она определяет озера, которые расположены на территории государственных парков. Ее атрибутами являются Lid, Sid , а также дата открытия сезона рыбной ловли на озере – Fishing-Opener . В данном случае мы предполагаем, что на разных озерах Открытие-Сезона происходит в разное время.

Таблица 3.13. Таблицы базы данных StatePark

Park	Sid	Sname	Area	Distance
	S1	Итаска	150,0	52
	S2	Вудбери	255,0	75
	S3	Брайтон	175,0	300

(а) Park

Lake	Lid	Lname	Depth	Main-Catch
	100	Лино	20,0	Плотва
	200	Часка	30,0	Форель
	300	Суссекс	45,0	Плотва
	400	Тодд	28,0	Окунь

(б) Lake

ParkLake	Lid	Sid	Fishing-Opener
	100	S1	15.05
	200	S1	15.05
	300	S3	01.06

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

Запрос. Найти название государственного парка , где расположено озеро с номером 100.

Примечания. Начнем с выборки множества кортежей отношения ParkLake , имеющих значение атрибута Lid , равное 100. Множество-результат подвергается естественному соединению по ключу Sid с отношением StatePark . Результат проецируется на атрибут Название отношения StatePark , то есть Spname . Пользуясь оператором переименования ρ, этот запрос можно разбить на части. Оператор переименования применяется для присваивания названия промежуточному отношению, появляющемуся в ходе вычисления сложного запроса. Также его можно использовать для переименования атрибутов отношений. Например,

переименовывает отношение СтароеНазв в НовоеНазв . Кроме того, первый атрибут отношения НовоеНазв , считая слева направо, получает имя Att1 .

Пользуясь этим соглашением об именовании отношений, можно разбить этот запрос на части:

Альтернативная формулировка запроса выглядит так:

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

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

Примечания. Здесь последовательно применяются два оператора соединения. Однако сначала мы сокращаем размер множества путем предварительной выборки всех озер , где основным уловом является форель . Затем соединяем результат по ключу Lid с отношением ParkLake . После этого следует другое соединение с отношением StatePark по ключу Sid . Наконец, мы проецируем ответ на название парка ( Spname ).

2. Запрос. Найти виды рыб, которые являются основным уловом в озерах, расположенных на территории государственного парка Итаска.

Примечание. Этот запрос очень похож на предыдущий.

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

Примечание. Соединение по атрибуту Sid создает промежуточное отношение, в котором кортежи из StatePark присоединяются к кортежам ParkLake . Затем результат проецируется на атрибут Spname.

3. Запрос. Составить список государственных парков , основной улов в озерах которых – это окунь или плотва.

Примечания. Здесь мы впервые используем оператор объединения. Сначала производится выборка озер, где основной улов составляют окунь или плотва. Затем выполняется соединение по атрибуту Lid с отношением ParkLake и по атрибуту Sid с отношением StatePark . Результат получен проекцией по атрибуту Spname .

4. Запрос. Определить названия государственных парков , в которых есть озера, где основным уловом являются и окунь, и плотва.

Примечание. Формулировка этого запроса очевидна.

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

6. Запрос. Определить идентификаторы, то есть атрибуты Sid, государственных парков , которые находятся на расстоянии не менее 50 миль от Миннеаполиса и в озерах которых основным уловом не является форель.

1 Существует и другое название этого компонента SQL – язык манипулирования данными (DML, data manipulation language). – Примеч. пер.

Какой запрос строится не sql когда мы обращаемся к таблице остатков

Хрусталева Е. Ю. Язык запросов 1С-Предприятия 8 (2013)

Как посчитать остатки и обороты на складе, накладные товара и услуг вместе?

Как получить N строк для каждой группы в SQL

Какой запрос строится не sql когда мы обращаемся к таблице остатков

Часть 3

3.2.3. Операция соединения

Условные соединения

Естественное соединение

УПРАЖНЕНИЯ

Добавить комментарий Отменить ответ