учебники, программирование, основы, введение в,

 

Группировка и условия раздела HAVING, порождаемые и соединенные таблицы

Введение
В предыдущих двух лекциях мы обсудили допускаемые в стандарте SQL виды ссылок на таблицы в разделе FROM оператора SELECT и подробно, с многочисленными примерами, рассмотрели возможные способы построения условных выражений раздела WHERE. Данную лекцию мы начинаем с анализа возможностей и целесообразности использования в запросах разделов GROUP BY и HAVING. Соответствующий раздел "Агрегатные функции, группировка и условия раздела HAVING" формально похож на раздел "Логические выражения раздела WHERE" лекции 14: обсуждаются виды предикатов, которые можно использовать в условных выражениях раздела HAVING, и приводятся иллюстрирующие примеры. Но в действительности мы преследуем большую цель: показать, что во многих случаях разделы GROUP BY и HAVING являются избыточными; запрос можно сформулировать более понятным образом без их использования. Применение разделов GROUP BY и HAVING оказывается действительно полезным, а иногда и необходимым, в тех случаях, когда в запросе присутствует несколько вызовов агрегатных функций на группах строк.
После обсуждения разделов GROUP BY и HAVING можно будет считать, что мы полностью рассмотрели базовые конструкции оператора выборки (раздел ORDER BY не заслуживает дополнительного обсуждения). Поэтому в разделах "Ссылки на порождаемые таблицы в разделе FROM" и "Более сложные конструкции оператора выборки" мы возвращаемся к отложенным в лекции 13 темам порождаемых таблиц, соединенных таблиц и порождаемых таблиц с горизонтальной связью.
В обычных порождаемых таблицах SQL нет ничего особенного. По всей видимости, возможность указывать в разделе FROM выражения запросов, а не только ссылки на базовые или представляемые таблицы, была введена в SQL на основе следующих естественных соображений. Результатом вычисления выражения запросов в SQL является таблица. Следовательно, в любой конструкции языка, где может присутствовать ссылка на таблицу SQL, следует допустить присутствие выражения запросов. Одновременное наличие возможностей определения представляемых таблиц, указания именованного выражения запросов в разделе WITH и указания выражения запросов порождаемой таблицы непосредственно в списке раздела FROM, очевидно, является избыточным.
Соединенные таблицы появились еще в стандарте SQL/92, и внедрение в стандарт SQL этой возможности было действительно обоснованным. В соответствии с традиционной общей семантикой оператора SELECT в нем вообще не предусматривалось явных средств для выражения потребности в соединении двух или более таблиц. Наличие возможности указывать несколько ссылок на таблицы в разделе FROM и спецификации произвольного логического выражения в разделе WHERE для ограничения расширенного декартова произведения этих таблиц позволяет выражать с помощью традиционных средств SQL соединение общего вида в смысле Кодда, и до поры до времени это считалось достаточным.
Внешние соединения
Но имеются два важных частных случая соединений, которые выражаются с помощью традиционных средств SQL излишне громоздко,- это естественные и внешние соединения. При наличии возможности определения внешних ключей таблицы кажется достаточно странной потребность всякий раз явно указывать в запросах условие естественного соединения. Например, во многих примерах запросов в лекции 14 присутствует условие соединения EMP.DEPT_NO = DEPT.DEPT_NO в тех случаях, когда в действительности нам требовался результат операции EMP NATURAL JOIN DEPT.
Внешние соединения были введены еще Эдгаром Коддом в 1979 г. В целом, основная идея этой разновидности операции соединения состояла в том, что, с одной стороны, результат операции обычного соединения двух отношений повышает информационный уровень данных, поскольку в результате операции мы имеем информационно связанные данные. Но, с другой стороны, в результирующем отношении мы теряем информацию об исходных объектах, которые оказались несвязанными и не вошли в результат соединения. Кодд придумал, как, используя неопределенные значения, определить обобщенную операцию, которая будет обладать достоинствами обычной операции соединения, не приводя к потере исходной информации. Вернее, он предложил три операции: левое внешнее соединение, правое внешнее соединение и полное (симметричное) внешнее соединение. Приведем их определения (в реляционных терминах данного курса).
Пусть имеются отношения r1 и r2, совместимые относительно операции взятия расширенного декартова произведения. Пусть s является результатом операции r1 LEFT OUTER JOUN r2 WHERE comp (левое внешнее соединение r1 и r2 по условию comp). Тогда Hs = Hr1 union Hr2. Пусть tr1 Br1 и tr2 Br2. Тогда tr1 union tr2 Bs в том и только в том случае, когда comp (tr1 union tr2) = true. Если имеется кортеж tr1 Br1, для которого нет ни одного кортежа tr2 r2, такого, что comp (tr1 union tr2) = true, то tr1 union tr2null Bs, где tr2null - кортеж, соответствующий Hr2, все значения которого являются неопределенными.
Пусть s является результатом операции r1 RIGHT OUTER JOUN r2 WHERE comp (правое внешнее соединение r1 и r2 по условию comp). Тогда Hs = Hr1 union Hr2. Пусть tr1 Br1 и tr2 Br2. Тогда tr1 union tr2 Bs в том и только в том случае, когда comp (tr1 union tr2) = true. Если имеется кортеж tr2 Br2, для которого нет ни одного такого кортежа tr1 Br1, что comp (tr1 union tr2) = true, то tr1null union tr2 Bs, где tr1null - кортеж, соответствующий Hr1, все значения которого являются неопределенными.
Наконец, пусть s является результатом операции r1 FULL OUTER JOUN r2 WHERE comp (полное внешнее соединение r1 и r2 по условию comp). Тогда Hs = Hr1 union Hr2. Пусть tr1 Br1 и tr2 Br2. Тогда tr1 union tr2 Bs в том и только в том случае, когда comp (tr1 union tr2) = true. Если имеется кортеж tr1 Br1, для которого нет ни одного кортежа tr2 Br2, такого, что comp (tr1 union tr2) = true, то tr1 union tr2null Bs, где tr2null - кортеж, соответствующий Hr2, все значения которого являются неопределенными. Если имеется кортеж tr2 Br2, для которого нет ни одного кортежа tr1 Br1, такого, что comp (tr1 union tr2) = true, то tr1null union tr2 Bs, где tr1null - кортеж, соответствующий Hr1, все значения которого являются неопределенными.
Понятно, что традиционными средствами SQL можно выразить все виды внешних соединений (например, с использованием переключателей), но такие запросы будут очень громоздкими. Компании-производители SQL-ориентированных СУБД пытались обеспечивать выразительные средства внешних соединений путем расширения системы обозначений для операций сравнения. Этот подход был не слишком удачным и не обеспечивал общего решения.
В стандарте языка SQL специфицирован отдельный специализированный подъязык для формирования выражений соединения таблиц. Такие выражения называются соединенными таблицами, и их можно использовать в качестве ссылок на таблицы в списке раздела FROM. Разработчики стандарта SQL не любят мельчить - в языке допускается 14 видов соединений:

  • прямое соединение;
  • внутреннее соединение по условию;
  • внутреннее соединение по совпадению значений указанных одноименных столбцов;
  • естественное внутреннее соединение;
  • левое внешнее соединение по условию;
  • правое внешнее соединение по условию;
  • полное внешнее соединение по условию;
  • левое внешнее соединение по совпадению значений указанных одноименных столбцов;
  • правое внешнее соединение по совпадению значений указанных одноименных столбцов;
  • полное внешнее соединение по совпадению значений указанных одноименных столбцов;
  • естественное левое внешнее соединение;
  • естественное правое внешнее соединение;
  • естественное полное внешнее соединение;
  • соединение объединением.

Во всех этих операциях нет ничего сложного, но их неформальное описание исключительно громоздко. Поэтому в разделе "Более сложные конструкции оператора выборки" мы определяем операции на формальном уровне, а потом иллюстрируем их на примерах.
Наконец, последняя тема этой лекции относится к еще одному типу ссылок на таблицу, допускаемых в разделе FROM: порождаемым таблицам с горизонтальной связью. Фактически порождаемая таблица с горизонтальной связью представляет собой выражение запросов, в котором может присутствовать корреляция со строками таблиц, специфицированных в списке раздела FROM слева от данной порождаемой таблицы с горизонтальной связью. Наличие порождаемых таблиц с горизонтальной связью требует некоторого уточнения семантики выполнения раздела FROM оператора SELECT. По нашему мнению, это средство является полностью избыточным, хотя и не вредным, поскольку его реализация не должна вызывать затруднений и/или снижать эффективность системы.

Агрегатные функции, группировка и условия раздела HAVING
В этом разделе мы систематически обсудим все аспекты группировки таблиц и вычисления агрегатных функций. Некоторые темы уже затрагивались на неформальном уровне в предыдущих лекциях.
Семантика агрегатных функций
Агрегатные функции (в стандарте SQL они называются функциями над множествами) определяются следующими синтаксическими правилами:
<set_function_specification> ::=
     COUNT(*)
   | set_function_type ([DISTINCT | ALL ] value_expression)
   | GROUPING (column_reference)
<set_function_type> ::=
   { AVG | MAX | MIN | SUM | EVERY | ANY | SOME | COUNT }
Как видно из этих правил, в стандарте SQL:1999 определены пять стандартных агрегатных функций: COUNT - число строк или значений, MAX - максимальное значение, MIN - минимальное значение, SUM - суммарное значение и AVG - среднее значение, а также две "кванторные" функции EVERY и SOME (ANY). В последних двух случаях выражение должно иметь булевский тип. Обсуждение функции GROUPING мы отложим до следующей лекции.
Агрегатные функции предназначены для того, чтобы вычислять некоторое значение для заданного мультимножества строк. Таким мультимножеством строк может быть группа строк, если агрегатная функция применяется к сгруппированной таблице, или (в вырожденных случаях) вся таблица. Для всех агрегатных функций, кроме COUNT(*), фактический (т. е. требуемый семантикой) порядок вычислений состоит в следующем. На основании параметров агрегатной функции из заданного мультимножества строк производится список значений. Затем по этому списку значений производится вычисление функции. Если список оказался пустым, то значением функции COUNT для него является 0, значением функции SOME - false, значением функции ALL - true, а значением всех остальных функций - NULL.
Пусть T обозначает тип значений из этого списка (вернее, "наименьший общий" тип, см. раздел "Скалярные выражения" лекции 13). Типы значений агрегатных функций определяются следующими правилами.

  • Результат вычисления функции COUNT - это точное число с точностью и шкалой, которые определяются в реализации.
  • Тип результата значений функций MAX и MIN совпадает с T. При вычислении функций SUM и AVG тип T не должен быть типом символьных строк.
    • Если T представляет собой тип точных чисел, то и типом результата функции является тип точных чисел с определяемыми в реализации точностью и шкалой.
    • Если T представляет собой тип приблизительных чисел, то и типом результата функции является тип приблизительных чисел с определяемыми в реализации точностью.
  • Для функций EVERY и SOME T является булевским типом.
    • Первая функция принимает значение true в том и только в том случае, когда вычисление выражения-аргумента дает значение true для каждой строки из заданного набора строк; false - в том и только в том случае, когда значение выражения-аргумента есть false хотя бы для одной строки из заданного набора строк и uknown - во всех остальных случаях.
    • Функция SOME принимает значение false в том и только в том случае, когда значение выражения-аргумента есть false для каждой строки из заданного набора строк; true - в том и только в том случае, когда значение выражения-аргумента есть true хотя бы для одной строки из заданного набора строк; uknown - во всех остальных случаях.

Вычисление функции COUNT(*) производится путем подсчета числа строк в заданном мультимножестве. Все строки считаются различными, даже если они состоят из одного столбца со значением null во всех строках.
Если "арифметическая" (AVG, MAX, MIN, SUM, COUNT) агрегатная функция специфицирована с ключевым словом DISTINCT, то множество значений, на котором она вычисляется, строится из значений указанного выражения, вычисляемого для каждой строки заданной группы строк. Затем из этого мультимножества удаляются неопределенные значения, и в нем устраняются значения-дубликаты (т. е. образуется множество). После этого вычисляется указанная функция.
Если агрегатная функция специфицирована без ключевого слова DISTINCT (или с ключевым словом ALL), то мультимножество значений формируется из значений выражения, вычисляемого для каждой строки заданной группы строк. Затем из этого мультимножества удаляются неопределенные значения, и производится вычисление агрегатной функции.
Результаты запросов и агрегатные функции
Об использовании агрегатных функций в разделах HAVING и SELECT оператора выборки упоминалось в разделе "Общие синтаксические правила построения скалярных выражений" лекции 13. В данном подразделе уместно повторить и уточнить этот материал.
Агрегатные функции можно разумным образом использовать в списке выборки (при построении выражений, являющихся элементами выборки) и в логическом выражении раздела HAVING (вернее, в выражениях, входящих в простые условия). Рассмотрим разные случаи применения агрегатных функций в списке выборки в зависимости от вида табличного выражения.
Если результат табличного выражения R не является сгруппированной таблицей (т. е. в табличном выражении отсутствуют разделы GROUP BY и HAVING), то появление в списке выборки хотя бы одного вызова агрегатной функции от (мульти) множества строк R приводит к тому, что R неявно рассматривается как сгруппированная таблица, состоящая из одной (или нуля, если R пусто) групп с отсутствующими столбцами группирования. Поэтому в данном случае в выражениях списка выборки не допускается прямое использование имен столбцов R: все они должны находиться внутри спецификаций вызова агрегатных функций. Результатом запроса является таблица, состоящая не более чем из одной строки, значения столбцов которой получены путем применения агрегатных функций к R.
Аналогично обстоит дело в том случае, когда R представляет собой сгруппированную таблицу, но табличное выражение не содержит раздела GROUP BY (и, следовательно, содержит раздел HAVING). В этом случае считается, что результат табличного выражения явно объявлен сгруппированной таблицей, состоящей из одной группы, и результат запроса можно формировать только путем применения агрегатных функций к данной группе строк. Опять результатом запроса является таблица, состоящая не более чем из одной строки, значения столбцов которой получены путем применения агрегатных функций к R.
Наконец, рассмотрим случай, когда R представляет собой "настоящую" сгруппированную таблицу, т. е. табличное выражение содержит раздел GROUP BY, и, следовательно, определен по крайней мере один столбец группирования (т. е. имеется хотя бы один такой столбец, что для любой группы его значения одинаковы во всех строках группы). В этом случае правила формирования списка выборки полностью соответствуют правилам формирования условия выборки раздела HAVING. Другими словами, в выражениях, являющихся элементами списка выборки, допускается прямое использование имен столбцов группирования, а спецификации остальных столбцов R могут появляться только внутри спецификаций агрегатных функций. Результатом запроса является таблица, число строк в которой равно числу групп в R. Значения столбцов каждой строки формируются на основе значений столбцов группирования и вызовов агрегатных функций для соответствующей группы.

Логические выражения раздела HAVING

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

Предикаты сравнения
SELECT DEPT_NO
FROM EMP
WHERE DEPT_NO IS NOT NULL
GROUP BY DEPT_NO
HAVING COUNT(*) = 30;

Конечно, этот запрос можно сформулировать и без использования разделов GROUP BY и HAVING. Например, возможна следующая формулировка:

SELECT DISTINCT DEPT_NO
FROM EMP
WHERE (SELECT COUNT (*)
   FROM EMP EMP1
   WHERE EMP1.DEPT_NO = EMP.DEPT_NO) = 30;

Обратите внимание, что в формулировке  отдельная проверка условия DEPT_NO IS NOT NULL не требуется.

SELECT DEPT_NO
FROM EMP
WHERE DEPT_NO IS NOT NULL
GROUP BY DEPT_NO
HAVING AVG(EMP_SAL) > 12000.00;

Очевидно, что и в этом случае возможна формулировка запроса без использования разделов GROUP BY и HAVING

SELECT DISTINCT DEPT_NO
FROM EMP
WHERE (SELECT AVG(EMP1.EMP_SAL)
   FROM EMP EMP1
   WHERE EMP1.DEPT_NO = EMP.DEPT_NO) > 12000.00;

Немного задержимся на этих примерах и обсудим, что означает различие в формулировках запросов. В соответствии с семантикой оператора SELECT, при выполнении запросов  для каждой строки таблицы EMP в цикле просмотра внешнего запроса будет выполняться подзапрос, который в случае наших примеров выберет из таблицы EMP (EMP1) все строки со значением столбца DEPT_NO, равным значению этого столбца в текущей строке внешнего цикла. Другими словами, для каждой строки внешнего цикла образуется группа, для нее проверяется условие выборки, и в списке выборки используется имя столбца этой неявной группировки. Из-за того, что группа образуется и оценивается для каждой строки таблицы EMP, мы вынуждены указать в разделе SELECT спецификацию DISTINCT.
Формулировки  и  обеспечивают более четкие указания для выполнения запроса. Нужно сразу сгруппировать таблицу EMP в соответствии со значениями столбца DEPT_NO, отобрать нужные группы, и для каждой отобранной группы вычислить значения выражений списка выборки. В этом случае семантика выполнения запроса не предписывает выполнения лишних действий. Конечно, в развитой реализации SQL компилятор должен суметь понять, что формулировки  и  эквивалентны формулировкам  и  соответственно, и избежать выполнения лишних действий.

SELECT DEPT_NO
FROM EMP
WHERE DEPT_NO IS NOT NULL
GROUP BY DEPT_NO
HAVING SUM(EMP_SAL) < (SELECT SUM(EMP1.EMP_SAL)
   FROM EMP EMP1, DEPT
   WHERE EMP1.EMP_NO = DEPT_MNG); 

И в этом случае возможна формулировка без использования разделов GROUP BY и HAVING. Эта формулировка является более сложной, чем в случае двух предыдущих примеров, но и к ней применимы приведенные выше замечания.

SELECT DISTINCT DEPT_NO
FROM EMP
WHERE (SELECT SUM(EMP1.EMP_SAL)
   FROM EMP EMP1
   WHERE EMP1.DEPT_NO = EMP.DEPT_NO) < 
   (SELECT SUM(EMP1.EMP_SAL)
   FROM EMP EMP1, DEPT
   WHERE EMP1.EMP_NO = DEPT_MNG);
SELECT DEPT.DEPT_NO, EMP.EMP_NAME, COUNT(*), 
   MIN(EMP1.EMP_SAL), MAX(EMP1.EMP_SAL), AVG(EMP1.EMP_SAL)
FROM DEPT, EMP, EMP EMP1
WHERE DEPT.DEPT_NO = EMP1.DEPT_NO
GROUP BY DEPT.DEPT_NO, DEPT.DEPT_MNG, EMP.EMP_NO, EMP.EMP_NAME
HAVING DEPT.DEPT_MNG = EMP.EMP_NO;

Этот запрос иллюстрирует несколько интересных особенностей языка SQL. Во-первых, это первый пример запроса с соединениями, в котором присутствуют разделы GROUP BY и HAVING. Во-вторых, одно условие соединения находится в разделе WHERE, а другое - в разделе HAVING. На самом деле, можно было бы перенести в раздел WHERE и второе условие соединения, и, скорее всего, на практике использовалась бы формулировка, приведенная в:

SELECT DEPT.DEPT_NO, EMP.EMP_NAME, COUNT(*), 
   MIN(EMP1.EMP_SAL), MAX(EMP1.EMP_SAL), AVG(EMP1.EMP_SAL)
FROM DEPT, EMP, EMP EMP1
WHERE DEPT.DEPT_NO = EMP1.DEPT_NO 
   AND DEPT.DEPT_MNG = EMP.EMP_NO
GROUP BY DEPT.DEPT_NO, EMP.EMP_NAME;

Но первая формулировка тоже верна, поскольку второе условие соединения определено на столбцах группировки.
Наконец, легко видеть, что по существу группировка производится по значениям столбца DEPT.DEPT_NO. Остальные столбцы, указанные в списке столбцов группировки, функционально определяются столбцом DEPT.DEPT_NO. Тем не менее, в первой формулировке мы включили в этот список столбцы DEPT.DEPT_MNG и EMP.EMP_NO, чтобы их имена можно было использовать в условии раздела HAVING, и столбец EMP.EMP_NAME, чтобы можно было использовать его имя в списке выборки раздела SELECT. Другими словами, мы вынуждены расширять запрос избыточными данными, чтобы выполнить формальные синтаксические требования языка. Как видно, во второй формулировке мы смогли удалить из списка группировки два столбца. Кстати, не следует думать, что многословие первой формулировки помешает СУБД выполнить запрос настолько же эффективно, как запрос во второй формулировке. Грамотно построенный оптимизатор SQL сам приведет первую формулировку ко второй.
Наконец, и этот запрос можно сформулировать без использования раздела GROUP BY за счет использования подзапросов в списке раздела SELECT):

SELECT DEPT.DEPT_NO, EMP.EMP_NAME, 
   (SELECT COUNT(*)
   FROM EMP
   WHERE EMP.DEPT_NO = DEPT.DEPT_NO),
   (SELECT MIN(EMP_SAL)
   FROM EMP
   WHERE EMP.DEPT_NO = DEPT.DEPT_NO),
   (SELECT MAX(EMP_SAL)
   FROM EMP
   WHERE EMP.DEPT_NO = DEPT.DEPT_NO),
   (SELECT AVG(EMP_SAL)
   FROM EMP
   WHERE EMP.DEPT_NO = DEPT.DEPT_NO)
FROM DEPT, EMP
WHERE DEPT.DEPT_MNG = EMP.EMP_NO;

Здесь мы снова имеем замаскированную группировку строк по значениям столбца DEPT.DEPT_NO и вычисление агрегатных функций для каждой группы. Формально группа строится каждый раз заново при вызове каждой агрегатной функции. Хороший компилятор SQL должен привести формулировку  к виду.
И последнее замечание. Во всех приведенных формулировках в результат не попадут данные об отделах, в которых отсутствует руководитель (столбец DEPT.DEPT_MNG может содержать неопределенное значение). Вообще говоря, это не противоречит условию запроса, но если бы мы хотели выдавать в результате NULL в качестве имени руководителя отдела с отсутствующим руководителем, то можно было немного усложнить формулировку запроса, например, следующим образом):       

SELECT DEPT.DEPT_NO, 
   CASE WHEN DEPT.DEPT_MNG IS NULL THEN NULL 
     ELSE (SELECT EMP.EMP_NAME
       FROM EMP
       WHERE EMP.EMP_NO = DEPT.DEPT_MNG),
     COUNT(*), MIN(EMP1.EMP_SAL),
     MAX(EMP1.EMP_SAL), AVG(EMP1.EMP_SAL)
FROM DEPT, EMP, EMP EMP1
WHERE DEPT.DEPT_NO = EMP1.DEPT_NO
GROUP BY DEPT.DEPT_NO;  

Предикат between
SELECT DEPT_NO, MIN(EMP_SAL), MAX(EMP_SAL)
FROM EMP
WHERE DEPT_NO IS NOT NULL
GROUP BY DEPT_NO
HAVING AVG(EMP_SAL) BETWEEN
   (SELECT AVG(EMP_SAL)
   FROM EMP) AND 30000.00;

Еще раз приведем возможную формулировку этого запроса без использования разделов GROUP BY и HAVING):        

SELECT DISTINCT DEPT_NO, (SELECT MIN(EMP1.EMP_SAL)
   FROM EMP EMP1
   WHERE EMP1.DEPT_NO = EMP.DEPT_NO),
   (SELECT MAX(EMP1.EMP_SAL)
   FROM EMP EMP1
   WHERE EMP1.DEPT_NO = EMP.DEPT_NO)
FROM EMP
WHERE (SELECT AVG(EMP1.EMP_SAL)
     FROM EMP EMP1
     WHERE EMP1.DEPT_NO = EMP.DEPT_NO) BETWEEN
   (SELECT AVG(EMP_SAL)
     FROM EMP) AND 30000.00;

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

Предикат null
SELECT DEPT.DEPT_NO, COUNT(*)
FROM DEPT, EMP EMP1, EMP EMP2
WHERE DEPT.DEPT_NO = EMP2.DEPT_NO
 AND DEPT.DEPT_MNG = EMP1.EMP_NO
GROUP BY DEPT.DEPT_NO, EMP1.DEPT_NO
HAVING EMP1.DEPT_NO IS NULL;

Как и в, условие раздела HAVING можно переместить в раздел WHERE и получить вторую формулировку ():

SELECT DEPT.DEPT_NO, COUNT(*)
FROM DEPT, EMP EMP1, EMP EMP2
WHERE DEPT.DEPT_NO = EMP2.DEPT_NO AND
   DEPT.DEPT_MNG = EMP1.EMP_NO AND
   EMP1.DEPT_NO IS NULL
GROUP BY DEPT.DEPT_NO;

Кстати, в этом случае, поскольку в запросе присутствует только один вызов агрегатной функции, формулировка без использования раздела GROUP BY оказывается более понятной и не менее эффективной (даже при следовании предписанной семантике выполнения оператора SELECT), что показывает:

SELECT DEPT.DEPT_NO, (SELECT COUNT(*)
   FROM EMP
   WHERE DEPT.DEPT_NO = EMP.DEPT_NO)
FROM DEPT, EMP
WHERE DEPT.DEPT_MNG = EMP.EMP_NO AND
      EMP.DEPT_NO IS NULL;
Предикат in
SELECT DEPT.DEPT_NO
FROM DEPT, EMP
WHERE DEPT.DEPT_NO = EMP.DEPT_NO
GROUP BY DEPT.DEPT_NO
HAVING AVG(EMP.EMP_SAL) IN
   (SELECT MAX(EMP1.EMP_SAL)
     FROM EMP, DEPT DEPT1
     WHERE EMP.DEPT_NO = DEPT1.DEPT_NO
     AND DEPT1.DEPT_NO <> DEPT.DEPT_NO
     GROUP BY DEPT.DEPT_NO);

Этот запрос, помимо прочего, демонстрирует наличие в условии раздела HAVING вложенного подзапроса с корреляцией. Как и раньше, можно избавиться от разделов GROUP BY и HAVING во внешнем запросе ():

SELECT DEPT.DEPT_NO
FROM DEPT
WHERE (SELECT AVG(EMP_SAL)
   FROM EMP
   WHERE EMP.DEPT_NO = DEPT.DEPT_NO) IN
   (SELECT MAX(EMP1.EMP_SAL)
   FROM EMP, DEPT DEPT1
   WHERE EMP.DEPT_NO = DEPT1.DEPT_NO
     AND DEPT1.DEPT_NO <> DEPT.DEPT_NO
   GROUP BY DEPT.DEPT_NO);

Но в данном случае мы не можем отказаться от раздела GROUP BY во втором вложенном запросе, поскольку без этого невозможно получить множество значений результатов вызова агрегатной функции.

Предикат like
SELECT EMP_NAME, COUNT(*)
FROM EMP, DEPT
WHERE EMP.DEPT_NO = DEPT.DEPT_NO 
GROUP BY DEPT.DEPT_NO, EMP_NAME
HAVING COUNT(*) > 1 
   AND EMP.EMP_NAME LIKE (SELECT EMP1.EMP_NAME
     FROM EMP EMP1
     WHERE EMP1.EMP_NO = DEPT.DEPT_MNG) || '%';

Конечно, и в этом случае условие с предикатом LIKE можно переместить из раздела HAVING в раздел WHERE. Этот запрос можно переформулировать в виде, лишенном разделов GROUP BY и HAVING), но вряд ли это разумно, поскольку формулировка является менее понятной и существенно более сложной.

SELECT EMP_NAME, (SELECT COUNT(*)
   FROM EMP EMP1
   WHERE EMP1.DEPT_NO = EMP.DEPT_NO
     AND EMP1.EMP_NAME = EMP.EMP_NAME
     AND EMP1.EMP_NO <> EMP.EMP_NO) + 1
FROM EMP
WHERE (SELECT COUNT(*)
   FROM EMP EMP1
   WHERE EMP1.DEPT_NO = EMP.DEPT_NO
     AND EMP1.EMP_NAME = EMP.EMP_NAME
     AND EMP1.EMP_NO <> EMP.EMP_NO) > 1 
 AND EMP_NAME LIKE (SELECT EMP1.EMP_NAME
   FROM EMP EMP1, DEPT
   WHERE EMP.DEPT_NO = DEPT.DEPT_NO
   AND EMP1.EMP_NO = DEPT.DEPT_MNG) || '%';
    
редикат exists
SELECT DEPT.DEPT_NO
FROM DEPT, EMP
WHERE DEPT.DEPT_NO = EMP.DEPT_NO
GROUP BY DEPT.DEPT_NO
HAVING EXISTS (SELECT *
   FROM EMP EMP1
   WHERE EMP1.DEPT_NO <> DEPT.DEPT_NO
   GROUP BY EMP1.DEPT_NO 
   HAVING MAX (EMP1.EMP_SAL)= AVG (EMP.EMP_SAL));

В этой формулировке основной интерес представляет подзапрос, в котором корреляция с внешним запросом происходит через вызов агрегатной функции от группы строк внешнего запроса. Здесь также можно избавиться от разделов GROUP BY и HAVING во внешнем запросе):        

SELECT DEPT.DEPT_NO
FROM DEPT
WHERE EXISTS (SELECT EMP.DEPT_NO
   FROM EMP
   WHERE EMP.DEPT_NO <> DEPT.DEPT_NO
   GROUP BY EMP.DEPT_NO 
   HAVING MAX (EMP.EMP_SAL)= 
     (SELECT AVG (EMP1.EMP_SAL)
       FROM EMP EMP1
       WHERE EMP1.DEPT_NO = DEPT.DEPT_NO));
Предикат unique
SELECT DEPT.DEPT_NO, AVG (EMP.EMP_SAL)
FROM DEPT, EMP
WHERE DEPT.DEPT_NO = EMP.DEPT_NO
GROUP BY DEPT.DEPT_NO
HAVING UNIQIUE (SELECT AVG (EMP1.EMP_SAL)
   FROM EMP EMP1
   WHERE EMP1.DEPT_NO IS NOT NULL
   GROUP BY EMP1.DEPT_NO 
   HAVING AVG (EMP1.EMP_SAL) = AVG (EMP.EMP_SAL));

Вот альтернативная формулировка этого запроса с использованием предиката NOT EXISTS):

SELECT DEPT.DEPT_NO, AVG (EMP.EMP_SAL)
FROM DEPT, EMP
WHERE DEPT.DEPT_NO = EMP.DEPT_NO
GROUP BY DEPT.DEPT_NO
HAVING NOT EXISTS (SELECT EMP1.DEPT_NO
   FROM EMP EMP1
   WHERE EMP1.DEPT_NO <> DEPT.DEPT_NO
   GROUP BY EMP1.DEPT_NO 
   HAVING AVG (EMP1.EMP_SAL)= AVG (EMP.EMP_SAL));
Предикаты сравнения с квантором
SELECT DEPT_NO, AVG (CURRENT_DATE - EMP_BDATE)
FROM EMP
WHERE DEPT_NO IS NOT NULL
GROUP BY DEPT_NO
HAVING AVG (CURRENT_DATE - EMP_BDATE)< SOME
   (SELECT AVG (CURRENT_DATE - EMP1.EMP_BDATE) 
     FROM EMP EMP1
     WHERE EMP1.DEPT_NO IS NOT NULL
     GROUP BY EMP1.DEPT_NO);

Напомним, что "ниладическая" функция CURRENT_DATE выдает текущую дату, и, следовательно, значением выражения CURRENT_DATE - EMP_BDATE является интервал, представляющий текущий возраст сотрудника. На наш взгляд, формулировка этого запроса несколько упрощается, если пользоваться предикат предикатом EXISTS ():

SELECT DEPT_NO, AVG (CURRENT_DATE - EMP_BDATE)
FROM EMP
WHERE DEPT_NO IS NOT NULL
GROUP BY DEPT_NO
HAVING EXISTS (SELECT EMP1.DEPT_NO
   FROM EMP EMP1
   WHERE EMP1.DEPT_NO IS NOT NULL
   GROUP BY EMP1.DEPT_NO
   HAVING AVG (CURRENT_DATE - EMP1.EMP_BDATE) >
     AVG (CURRENT_DATE - EMP.EMP_BDATE));
SELECT DEPT_NO, AVG (CURRENT_DATE - EMP_BDATE)
FROM EMP
WHERE DEPT_NO IS NOT NULL
GROUP BY DEPT_NO
HAVING AVG (CURRENT_DATE - EMP_BDATE) <= ALL
   (SELECT AVG (CURRENT_DATE - EMP_BDATE)
     FROM EMP
     WHERE DEPT_NO IS NOT NULL
     GROUP BY DEPT_NO);

Этот запрос легко формулируется в более понятном виде с использованием предиката NOT EXISTS ():

SELECT DEPT_NO, AVG (CURRENT_DATE - EMP_BDATE)
FROM EMP
WHERE DEPT_NO IS NOT NULL
GROUP BY DEPT_NO
HAVING NOT EXISTS (SELECT EMP1.DEPT_NO
   FROM EMP EMP1
   WHERE EMP1.DEPT_NO IS NOT NULL
   GROUP BY EMP1.DEPT_NO
   HAVING AVG (CURRENT_DATE - EMP1.EMP_BDATE) <
     AVG (CURRENT_DATE - EMP.EMP_BDATE));
Предикат distinct
SELECT DEPT.DEPT_NO
FROM DEPT, EMP EMP1, EMP EMP2
WHERE DEPT.DEPT_NO = EMP1.DEPT_NO AND
   DEPT.DEPT_MNG = EMP2.EMP_NO
GROUP BY DEPT.DEPT_NO, EMP2.EMP_BDATE 
HAVING (EMP2.EMP_BDATE, AVG (EMP1.EMP_SAL)) DISTINCT FROM
   (SELECT EMP2.EMP_BDATE, AVG (EMP1.EMP_SAL)
     FROM DEPT DEPT1, EMP EMP1, EMP EMP2
     WHERE DEPT1.DEPT_NO = EMP1.DEPT_NO AND
     DEPT1.DEPT_MNG = EMP2.EMP_NO AND
     DEPT1.DEPT_NO <> DEPT.DEPT_NO
     GROUP BY DEPT.DEPT_NO, EMP2.EMP_BDATE);

Ссылки на порождаемые таблицы в разделе FROM
В этом разделе мы приведем несколько примеров запросов, в разделе FROM которых содержатся выражения запросов (ссылки на порождаемые таблицы, см. раздел "Общие синтаксические правила построения скалярных выражений" лекции 13).
Еще один способ формулировки запросов
Прежде всего, на простом примере покажем, как использование ссылок на порождаемые таблицы расширяет возможности формулировки запросов.
SELECT MNG.DEPT_NO, MNG.MNG_NAME
FROM (SELECT DEPT.DEPT_NO, EMP.DEPT_NO, EMP_NAME, EMP_SAL
FROM DEPT, EMP
WHERE DEPT.DEPT_MNG = EMP.EMP_NO)
AS MNG (DEPT_NO_1, DEPT_NO_2, MNG_NAME, MNG_SAL)
WHERE DEPT_NO_1 = DEPT_NO_2
AND MNG_SAL = (SELECT MAX (EMP_SAL)
FROM EMP
WHERE EMP.DEPT_NO = DEPT_NO_1);
Пример 15.14. Найти номера отделов и имена руководителей отделов, которые числятся в тех же отделах, которыми руководят, и получают зарплату, размер которой является максимальным для сотрудников данного отдела.
В этом запросе порождаемая таблица MNG содержит по одной строке для каждого служащего, являющегося руководителем отдела. Первый столбец этой таблицы - DEPT_NO_1 - содержит номер отдела, которым руководит данный служащий. В столбце DEPT_NO_1 хранятся номера отделов, в которых числятся руководители отделов, а в столбцах EMP_NAME и EMP_SAL содержатся имя служащего-руководителя отдела и размер его заработной платы соответственно.
Конечно, этот запрос можно сформулировать и без использования ссылки на порождаемую таблицу в разделе FROM, например, следующим образом (SELECT DEPT.DEPT_NO, EMP.EMP_NAME
FROM DEPT, EMP
WHERE DEPT.DEPT_MNG = EMP.EMP_NO
AND DEPT.DEPT_NO = EMP.DEPT_NO
AND EMP.EMP_SAL = (SELECT MAX(EMP_SAL)
FROM EMP
WHERE EMP.DEPT_NO = DEPT.DEPT_NO);
А вот как можно сформулировать тот же запрос с использованием раздела WITH):
WITH MNG (DEPT_NO_1, DEPT_NO_2, MNG_NAME, MNG_SAL) AS
(SELECT DEPT.DEPT_NO, EMP.DEPT_NO, EMP_NAME, EMP_SAL
FROM DEPT, EMP
WHERE DEPT.MNG_NO = EMP.EMP_NO),
MAX_DEPT_SAL (MAX_SAL, DEPT_NO) AS
(SELECT MAX (EMP_SAL), DEPT_NO
FROM EMP
WHERE DEPT_NO IS NOT NULL
GROUP BY DEPT_NO)
SELECT DEPT_NO_1, MNG_NAME
FROM MNG
WHERE DEPT_NO_1 = DEPT_NO_2
AND MNG_SAL = (SELECT MAX_SAL
FROM MAX_DEPT_SAL
WHERE MAX_DEPT_SAL.DEPT_NO = DEPT_NO_1);
Пример 15.14.2. Случаи, в которых без порождаемых таблиц обойтись невозможно
На самом деле,  демонстрирует лишь возможность альтернативных формулировок запросов с использованием ссылок на порождаемые таблицы в разделе FROM. Но в некоторых случаях без подобных конструкций просто невозможно обойтись. Вот простой пример.
SELECT SUM (TOTAL_EMP), MAX_SAL
FROM (SELECT MAX (EMP_SAL), COUNT (*)
FROM EMP
WHERE DEPT_NO IS NOT NULL
GROUP BY DEPT_NO ) AS DEPT_MAX_SAL (MAX_SAL, TOTAL_EMP)
GROUP BY MAX_SAL;
Пример 15.15. Найти общее число сотрудников и максимальный размер зарплаты в отделах с одинаковым максимальным размером зарплаты.
И в этом случае выражение запросов, содержащееся в разделе FROM, можно перенести в раздел WITH:
WITH DEPT_MAX_SAL (MAX_SAL, TOTAL_EMP) AS
(SELECT MAX (EMP_SAL), COUNT (*)
FROM EMP
WHERE DEPT_NO IS NOT NULL
GROUP BY DEPT_NO)
SELECT SUM (TOTAL_EMP), MAX_SAL
FROM DEPT_MAX_SAL
GROUP BY MAX_SAL;
Пример 15.15.1.
Здесь мы не можем обойтись "одноуровневой" конструкцией запроса, поскольку требуется двойная группировка, причем вторая группировка должна быть получена в соответствии с результатами первой. Еще один пример.
SELECT COUNT (*), PRO_EDATE, AVG_SAL
FROM (SELECT PRO_EDATE, AVG (EMP_SAL)
FROM (SELECT PRO_SDATE + PRO_DURAT, PRO_NO
FROM PRO) AS PRO1 (PRO_EDATE, PRO_NO), EMP
WHERE PRO1.PRO_NO = EMP.PRO_NO
GROUP BY PRO1.PRO_NO ) AS PRO_AVG_SAL (PRO_EDATE, AVG_SAL)
GROUP BY PRO_EDATE, AVG_SAL;
Пример 15.16. Найти число проектов, дату их завершения и средний размер зарплаты сотрудников, участвующих в проекте, для проектов с одной и той же датой завершения и одним и тем же средним размером зарплаты сотрудников, участвующих в проекте. ()
Заметим, что выражение запросов на третьей и четвертой строках примера необходимо только по той причине, что нам требуется группировка по дате окончания проектов, соответствующий столбец в таблице PRO отсутствует, а в списке группировки можно использовать только имена столбцов. Для упрощения вида формулировки это выражение разумно вынести в раздел WITH:
WITH PRO1 (PRO_EDATE, PRO_NO) AS
(SELECT PRO_SDATE + PRO_DURAT, PRO_NO
FROM PRO)
SELECT COUNT (*), PRO_EDATE, AVG_SAL
FROM (SELECT PRO_EDATE, AVG (EMP_SAL)
FROM PRO1, EMP
WHERE PRO1.PRO_NO = EMP.PRO_NO
GROUP BY PRO1.PRO_NO) AS PRO_AVG_SAL (PRO_EDATE, AVG_SAL)

GROUP BY PRO_EDATE, AVG_SAL;
Пример 15.16.1. ()

Более сложные конструкции оператора выборки
В этом разделе мы обсудим возможности языка SQL, касающиеся явного задания выражений с соединениями и порождаемых таблиц с горизонтальной связью (lateral_derived_table). Начнем с соединений.
Соединенные таблицы
В примерах предыдущей и данной лекций присутствовало много запросов с соединениями двух или более таблиц. Условия соединения задавались предикатами сравнения столбцов таблиц, специфицированных в разделе FROM, и входили в состав логических выражений раздела WHERE (или, реже, раздела HAVING). Поскольку на практике требуются разные виды соединений, в стандарте SQL/92 появилась альтернативная возможность спецификации соединений - соединенная таблица (joined table). Соответствующая конструкция может использоваться в разделе FROM выражения запросов и фактически позволяет строить выражения соединений таблиц. Синтаксические правила построения таких выражений выглядят следующим образом:
joined_table ::= cross_join
| qualified_join
| natural_join
| union_join
cross_join ::= table_reference CROSS JOIN table_primary
qualified_join ::= table_reference [ join_type ] JOIN
table_primary join_specification
natural_join ::= table_reference NATURAL [ join_type ]
JOIN table_primary
union_join ::= table_reference UNION JOIN table_primary
join_type ::= INNER | { LEFT | RIGHT | FULL } [ OUTER ]
join_specification ::= ON conditional_expression
| USING (column_comma_list)
Напомним, что синтаксические правила для table_reference и table_primary были показаны в лекции 13.
Как показывает сводка синтаксических правил, в SQL поддерживается много вариантов соединений. Чтобы объяснить особенности разных видов соединений на неформальном уровне, требуется очень большой объем текста с большим числом повторений. Поэтому сначала мы приведем достаточно формальное описание порядка определения заголовка и тела результирующей таблицы для всех разновидностей соединений. Фактически это описание напрямую позаимствовано из стандарта SQL:1999 с некоторыми незначительными упрощениями. Затем мы представим ряд иллюстрирующих примеров.
Формальные определения
Пусть требуется выполнить некоторую операцию соединения над таблицами table1 и table2. Тогда:

  • Обозначим через CP результат выполнения запроса
SELECT *
FROM table1, table2
  • Если задается операция JOIN (или NATURAL JOIN) без явного указания типа соединения (join_type), то по умолчанию имеется в виду INNER JOIN (или NATURAL INNER JOIN).
  • Если в спецификации соединения (join_specification) указано ключевое слово ON, то все ссылки на столбцы, встречающиеся в условном выражении (conditional_expression), должны указывать на столбцы таблиц table1 и table2 или на столбцы таблиц внешнего запроса. Если в этом условном выражении присутствует вызов агрегатной функции, то соединенная таблица может фигурировать только в подзапросах, используемых в разделах HAVING или SELECT внешнего запроса, и ссылка на столбец в вызове функции должна указывать на столбец таблицы внешнего запроса.
  • Для прямых соединений (CROSS JOIN) и всех других видов соединения, включающих раздел ON, заголовок результата операции совпадает с заголовком таблицы CP.
  • Если в спецификации вида соединения присутствуют ключевые слова NATURAL или USING, то заголовок результата операции определяется следующим образом:
    • если в спецификации вида соединения присутствует ключевое слово NATURAL, то будем называть соответствующими столбцами соединения (corresponding join column) все столбцы таблиц table1 и table2, которые имеют в заголовках этих таблиц одинаковые имена. Если в спецификации вида соединения присутствует ключевое слово USING, то будем называть соответствующими столбцами соединения (corresponding join column) все столбцы таблиц table1 и table2, имена которых входят в список имен столбцов раздела USING (эти столбцы должны быть одноименными в заголовках обеих таблиц). В обоих случаях типы данных каждой пары соответствующих столбцов должны быть совместимыми;
    • будем называть списком выборки соответствующих столбцов соединения (select_list of corresponding join columns - SLCC) список элементов вида COALESCE (table1.c, table2.c) AS c*, где c является именем соответствующего столбца соединения. Элементы располагаются в том порядке, в котором они появляются в заголовке таблицы table1. Обозначим через SLT1 (SLT2) список имен столбцов таблицы table1 (table2), которые не являются соответствующими столбцами соединения. Имена располагаются в том же порядке, в котором они появляются в заголовке соответствующей таблицы;
    • заголовок результата совпадает с заголовком результата запроса
SELECT SLCC, SLT1, SLT2
FROM table1, table2;
  • Набор строк результата (множество или мультимножество) определяется по следующим правилам. Обозначим через T следующие наборы строк:
    • если видом соединения является UNION JOIN, то T - пусто;
    • если видом соединения является CROSS JOIN, то T включает все строки, входящие в CP;
    • если в спецификацию вида соединения входит раздел ON, то T включает все строки CP, для которых результатом вычисления условного выражения является true;
    • если в спецификацию вида соединения входят разделы NATURAL или USING, и список SLCC не является пустым, то T включает все строки CP, для которых значения соответствующих столбцов соединения совпадают;
    • если в спецификацию вида соединения входят разделы NATURAL или USING, и список SLCC является пустым, то T включает все строки CP.
  • Обозначим через P1 (P2) набор (множество или мультимножество) всех строк таблицы table1 (table2), каждая из которых участвует в образовании некой строки T.
  • Обозначим через U1 (U2) набор (множество или мультимножество) всех строк таблицы table1 (table2), ни одна из которых не участвует в образовании какой-либо строки T.
  • Обозначим через X1 набор (множество или мультимножество) всех строк, образуемых из строк набора U1 путем добавления справа подстроки из неопределенных значений, содержащей столько неопределенных значений, сколько столбцов содержит таблица table2. Обозначим через X2 набор (множество или мультимножество) всех строк, образуемых из строк набора U2 путем добавления слева подстроки из неопределенных значений, содержащей столько неопределенных значений, сколько столбцов содержит таблица table1.
  • Для соединений вида CROSS JOIN и INNER JOIN пусть S обозначает тот же набор строк, что и T.
  • Для соединений вида LEFT OUTER JOIN пусть S обозначает набор строк, являющийся результатом выражения запросов
SELECT * FROM T
UNION ALL
SELECT * FROM X1;
  • Для соединений вида RIGHT OUTER JOIN пусть S обозначает набор строк, являющийся результатом выражения запросов
SELECT * FROM T
UNION ALL
SELECT * FROM X2;
  • Для соединений вида FULL OUTER JOIN пусть S обозначает набор строк, являющийся результатом выражения запросов
SELECT * FROM T
UNION ALL
SELECT * FROM X1
UNION ALL
SELECT * FROM X2;
  • Для соединений вида UNION JOIN пусть S обозначает набор строк, являющийся результатом выражения запросов
SELECT * FROM X1
UNION ALL
SELECT * FROM X2;
  • Если в спецификации вида соединения присутствуют ключевые слова NATURAL или USING, то результат операции совпадает с результатом выражения запросов
SELECT SLCC, SLT1, SLT2
FROM S;
  • Во всех остальных случаях результат операции совпадает с S.

Примеры соединений разного вида
Основное назначение приводимых ниже примеров состоит не в том, чтобы продемонстрировать практическую значимость разнообразных соединений, а лишь в том, чтобы помочь в них разобраться. Поэтому мы будем использовать упрощенные и формальные таблицы и показывать заголовки и тела результирующих таблиц.
Итак, пусть имеются таблицы table1 (a1, a2, c1, c2) и table2 (b1, b2, c1, c2) со следующими телами:

table1

a1

a2

c1

c2

1

1

1

1

1

1

2

3

1

1

2

3

2

3

4

NULL

3

NULL

NULL

5

table2

b1

b2

c1

c2

1

1

1

1

1

2

2

3

3

3

2

3

4

4

4

4

3

NULL

NULL

5

3

NULL

NULL

5

Обозначим через JR таблицу, являющуюся результатом соединения. Тогда для операции table1 INNER JOIN table2 ON a1=b1 AND a2<b2 (внутреннее соединение по условию) тело JR будет следующим:


JR

a1

a2

table1.c1

table1.c2

b1

b2

table2.c1

table2.c2

1

1

1

1

1

2

2

3

1

1

2

3

1

2

2

3

1

1

2

3

1

2

2

3

Строки-дубликаты появились в JR, поскольку в первом операнде присутствовали строки-дубликаты, удовлетворяющие условию соединения.
Результатом операции table1 INNER JOIN table2 USING (c2) (внутреннее соединение по совпадению значений указанных одноименных столбцов) будет следующая таблица.

JR

a1

a2

table1.c1

c2

b1

b2

table2.c1

1

1

1

1

1

1

1

1

1

2

3

1

2

2

1

1

2

3

3

3

2

1

1

2

3

1

2

2

1

1

2

3

3

3

2

3

NULL

NULL

5

3

NULL

NULL

3

NULL

NULL

5

3

NULL

NULL

 

 
На главную | Содержание | < Назад....Вперёд >
С вопросами и предложениями можно обращаться по nicivas@bk.ru. 2013 г.Яндекс.Метрика