Открыто о СУБД Oracle на русском : Получение max и sum из двух разных таблиц в одном запросе

Выпуск 21

Получение max и sum из двух разных таблиц в одном запросе

Уважаемые подписчики! Этот выпуск, как и было обещано, посвящен получению значений функций агрегирования из нескольких таблиц в одном запросе. Том Кайт отвечал на исходный вопрос 6 ноября 2002 года. В обсуждении он также раскрыл свои планы по написанию новых книг...

Как получить max и sum из двух разных таблиц в одном операторе SELECT

Привет, Том!

У меня есть таблицы со счетами, платежами по счетам и итогами по счетам. Я хочу выдать следующее:

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

Я хочу сделать это одним sql-оператором и, по возможности, без использования подставляемых представлений (inline views). Мне кажется, можно использовать аналитическую функцию, но я не могу получить требуемый результат, не используя подставляемых представлений. Если ли другой способ?

Спасибо, что уделил мне время.

Ответ Тома Кайта:

А почемы вы "боитесь" подставляемых представлений???

В любом случае, мне нужно больше подробностей:

Номер счета -- все понятно

Последний платеж по счету -- это последнее значение payment_amount для max(payment_date) или просто max(payment_date)? Например: вы спрашиваете зарплату сотрудника, принятого на работу последним, или дату, когда его приняли на работу?

Общая итоговая сумма -- все понятно

Ладно, я дам вам все возможные ответы, начиная с создания тестовых данных:

ops$tkyte@ORA920.US.ORACLE.COM> create table t1 (id number primary key);

Table created. 

ops$tkyte@ORA920.US.ORACLE.COM> create table t2 (id references t1, payment_date date, amt number);

Table created. 

ops$tkyte@ORA920.US.ORACLE.COM> create table t3 (id references t1, amt number);

Table created.

ops$tkyte@ORA920.US.ORACLE.COM> insert into t1 select rownum from all_users where rownum <= 5;

5 rows created.

ops$tkyte@ORA920.US.ORACLE.COM> insert into t2 select mod(rownum,5)+1, sysdate-dbms_random.value(1, 100),
  2    round(dbms_random.value( 1, 100 ), 2)
  3    from all_users; 

38 rows created. 

ops$tkyte@ORA920.US.ORACLE.COM> insert into t3 select mod(rownum,5)+1, round(dbms_random.value(1, 100), 2)
  2    from (select * from all_users union all select * from all_users ); 

76 rows created. 

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

ops$tkyte@ORA920.US.ORACLE.COM> select t1.id, max(t2.payment_date) max_pay_date
  2    from t1, t2
  3   where t1.id = t2.id
  4   group by t1.id
  5  /

        ID MAX_PAY_D
---------- ---------
         1 31-OCT-02
         2 31-OCT-02
         3 02-NOV-02
         4 31-OCT-02
         5 16-OCT-02 

ops$tkyte@ORA920.US.ORACLE.COM> select t1.id, 
  2   substr(max(to_char(t2.payment_date,'yyyymmddhh24miss') || amt), 15) max_t2_amt
  3    from t1, t2
  4   where t1.id = t2.id
  5   group by t1.id
  6  / 

        ID MAX_T2_AMT
---------- ----------------------------------------
         1 20.22
         2 23.48
         3 71.17
         4 76.45
         5 81.02 

(Это сумма за максимальную дату платежа)

ops$tkyte@ORA920.US.ORACLE.COM> select t1.id, sum(t3.amt) sum_t3_amt
  2    from t1, t3
  3   where t1.id = t3.id
  4   group by t1.id
  5  / 

        ID SUM_T3_AMT
---------- ----------
         1     601.01
         2     821.66
         3     706.87
         4     857.05
         5     893.75 

ops$tkyte@ORA920.US.ORACLE.COM> select t1.id,
  2         (select max(payment_date) from t2 where t2.id = t1.id) max_pay_date,
  3         (select to_number(substr(max(to_char(t2.payment_date,'yyyymmddhh24miss') || amt), 15 ))
  4           from t2
  5          where t2.id = t1.id) max_t2_amt,
  6         (select sum(amt) from t3 where t3.id = t1.id )  sum_t3_amt
  7    from t1
  8  / 

        ID MAX_PAY_D MAX_T2_AMT SUM_T3_AMT
---------- --------- ---------- ----------
         1 31-OCT-02      20.22     601.01
         2 31-OCT-02      23.48     821.66
         3 02-NOV-02      71.17     706.87
         4 31-OCT-02      76.45     857.05
         5 16-OCT-02      81.02     893.75 

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

ops$tkyte@ORA920.US.ORACLE.COM> select t1.id,
  2         max(payment_date) max_pay_date,
  3         to_number(substr( max(to_char(t2.payment_date,'yyyymmddhh24miss') || t2.amt), 15)) max_t2_amt,
  4         sum(t3.amt)/count(distinct t2.rowid) sum_t3_amt
  5    from t1, t2, t3
  6   where t1.id = t2.id and t2.id = t3.id
  7   group by t1.id
  8  / 

        ID MAX_PAY_D MAX_T2_AMT SUM_T3_AMT
---------- --------- ---------- ----------
         1 31-OCT-02      20.22     601.01
         2 31-OCT-02      23.48     821.66
         3 02-NOV-02      71.17     706.87
         4 31-OCT-02      76.45     857.05
         5 16-OCT-02      81.02     893.75 

Разберитесь сами, почему при отсутствии подзапросов или подставляемых представлений этот запрос все же работает... Вместо count(distinct rowid) можно было указать и count(distinct t2.id)...

ops$tkyte@ORA920.US.ORACLE.COM> select t1.id, max_pay_date, max_t2_amt, sum_t3_amt
  2    from t1,
  3         (select id, max(payment_date) max_pay_date,
  4           to_number(substr(max(to_char(payment_date,'yyyymmddhh24miss') || amt), 15)) max_t2_amt
  5           from t2
  6          group by id) t2,
  7        (select id, sum(amt) sum_t3_amt
  8            from t3
  9           group by id) t3
 10   where t1.id = t2.id and t1.id = t3.id
 11  /

        ID MAX_PAY_D MAX_T2_AMT SUM_T3_AMT
---------- --------- ---------- ----------
         1 31-OCT-02      20.22     601.01
         2 31-OCT-02      23.48     821.66
         3 02-NOV-02      71.17     706.87
         4 31-OCT-02      76.45     857.05
         5 16-OCT-02      81.02     893.75 

Ну, и подставляемое представление, для полноты картины...

Комментарий читателя от 6 ноября 2002 года

Нельзя ли подойти к проблеме по-другому, с точки зрения модели данных?

Я не знаю всех подробностейоб этой базе данных, но мне кажется, что между платежами по счетам и итогами по счетам отношение - один-к-одному.

В этом случае, созданные Томом таблицы t2 и t3 должны иметь дополнительные столбцы,"id2".

Тогда запрос упрощается

ops$tkyte@ORA920.US.ORACLE.COM> select t1.id,
  2         max(payment_date) max_pay_date,
  3         to_number(substr(max(to_char(t2.payment_date,'yyyymmddhh24miss') || t2.amt), 15)) max_t2_amt,
  4         sum(t3.amt) sum_t3_amt --/count(distinct t2.rowid) -- Не надо Count
  5    from t1, t2, t3
  6   where t1.id = t2.id and -- t2.id = t3.id
      t2.id2 = t3.id2
  7   group by t1.id
  8  /

Ответ Тома Кайта

Это было бы верно только для отношения один-к-одному, - а было сказано, что это не так: имеются отношения 1:n и 1:m. Вот почему необходимо деление для получения правильной суммы.

Не уверен... Комментарий читателя от 6 ноября 2002 года

Привет, Том!

Я немного не понял твой второй запрос, в котором используется count (distinct t2.rowid). Как он работает? Я знаю, что count (distinct t2.rowid) даст количество различных идентфикаторов строк для данного идентификатора, но дальше я не понимаю. Если мы на это значение не делим, то получаем общую сумму платежей в t3, умноженную на количество строк в t2. Но зачем? И почему distinct? Наверное, мне не хватает понимания того, как работают соединения и группировки...

Ответ Тома Кайта

Хорошо, пусть имеется:

1 строка в таблице t1 с id = 1
2 строки в таблице t2 с id = 1
3 строки в таблице t3 с id = 1 

from t1, t2, t3
where t1.id = t2.id and t1.id = t3.id 

мы получим 6 строк -- (1*2 от соединения t1 с t2) * 3 от соединения этого результата с t3.

Мы просто учитываем каждое значение T3.amt 2 раза -- по одному для каждой строки в T2. Так работает соединение. Рассмотрим пример:

ops$tkyte@ORA920.US.ORACLE.COM> create table t1 (id int);

Table created. 

ops$tkyte@ORA920.US.ORACLE.COM> create table t2 (id int, msg varchar2(10));

Table created.

ops$tkyte@ORA920.US.ORACLE.COM> create table t3 (id int, amt int);

Table created.

ops$tkyte@ORA920.US.ORACLE.COM> insert into t1 values (1);

1 row created.

ops$tkyte@ORA920.US.ORACLE.COM> insert into t2 values (1, 'row1');

1 row created.

ops$tkyte@ORA920.US.ORACLE.COM> insert into t2 values (1, 'row2'); 

1 row created. 

ops$tkyte@ORA920.US.ORACLE.COM> insert into t3 values (1, 2);

1 row created.

ops$tkyte@ORA920.US.ORACLE.COM> insert into t3 values (1, 4);

1 row created.

ops$tkyte@ORA920.US.ORACLE.COM> insert into t3 values (1, 6);

1 row created.

ops$tkyte@ORA920.US.ORACLE.COM> select t1.id, t2.msg, t3.amt
  2    from t1, t2, t3
  3   where t1.id = t2.id
  4     and t1.id = t3.id
  5  / 

        ID MSG               AMT
---------- ---------- ----------
         1 row1                2
         1 row2                2
         1 row1                4
         1 row2                4
         1 row1                6
         1 row2                6 

6 rows selected. 

Итак, каждая строка в T3 "перемножена" на каждую строку в T2. Две строки в T2, - значит, строки T3 будут входить в соединение дважды. Три строки в T2  - трижды, и так далее. Поэтому, когда мы их просуммируем:

 ops$tkyte@ORA920.US.ORACLE.COM> select t1.id, sum(t3.amt), count(distinct t2.rowid)
  2    from t1, t2, t3
  3   where t1.id = t2.id
  4     and t1.id = t3.id
  5   group by t1.id
  6  / 

        ID SUM(T3.AMT) COUNT(DISTINCTT2.ROWID)
---------- ----------- -----------------------
         1          24                       2 

ops$tkyte@ORA920.US.ORACLE.COM>

окажется, что мы дважды (в этом примере) все посчитали. Разделив сумму на количество строк в таблице T2, вошедших в эту группу, мы это исправим: 24/2 = 12 = 6+4+2...

Комментарий читателя от 7 ноября 2002 года

Дорогой Том,

Спасибо за детальное объяснение. В ответе вы написали, что: "Вместо count(distinct rowid) можно было указать и count(distinct t2.id)..." Но они возвращают разные результаты! Функция count(distinct t2.id) для заданного t1.id всегда возвращает 1, в отличие от count(distinct t2.rowid), которая возвращает значение, на которое надо делить результат. Правильно?

Кроме того, документация по Discoverer и Business Objects называет подобные ситуации "Fan Trap" или "Chasm Trap". Было бы здорово, если бы вы перечислили общие условия, при которых возможны подобные "ловушки" в SQL, чтобы мы знали, за чем следить.

Ответ Тома Кайта

Прошу прощения, - вы, конечно же, правы, - надо использовать count(distinct t2.rowid). Нам же надо узнать количество строк...

Я никогда не слышал о "ловушках" "Fan Trap" или "Chasm Trap". Я понимаю, причем тут "fan" - раскрывается ("fans out") результирующее множество. Но вот chasm (разногласия?) ни о чем мне не говорит.

В любом случае, вот правила:

Если:

1. используется агрегирование
2. в запросе участвует более двух таблиц (имеется N таблиц, что дает N-1 соединение)
3. и между таблицами нет, по крайней мере, N-2 отношений один-к-одному

то возникает эта проблема (раскрытие).

Например, пусть имеется:

select ..
from t1, t2, t3 where t1.x=t2.x and t1.x=t3.x   N=3, имеем 2 соединения 

t1 1:m t2
t1 1:m t3   имеется 0 отношений 1:1, 0 < 3-2, так что это - "раскрытие" 

Пусть имеется:

select ...
from t1, t2, t3 where t1.x=t2.x and t1.x=t3.x   N=3, имеем 2 соединения 

t1 1:1 t2
t1 1:m t3  имеется 1 отношение 1:1, 1 >= 3-2, так что это - НЕ раскрытие

Как насчет новой книги "Cool Little SQL Tricks". Комментарий читателя от 7 ноября 2002 года

to_number(substr(max(to_char(t2.payment_date, 'yyyymmddhh24miss') || t2.amt), 15)) max_t2_amt 

Хитроумно! Спасибо.

Ответ Тома Кайта

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

Что-то вроде:

http://www.amazon.com/exec/obidos/ASIN/0201310058

Интересно, как бы люди отнеслись к такому формату книги, посвященной эффективным приемам работы в Oracle? На сайте есть содержание, так что, можете оценить, на что это похоже. (В моем списке уже около 100 приемов.)

Оригинал обсуждения этого вопроса можно найти здесь.

Copyright © 2002 Oracle Corporation

В следующем выпуске

Возможно, о реализации аудита операторов ЯОД (этих самых CREATE, ALTER, DROP...) и, соответственно, о триггерах на операторы ЯОД. Выпуск выйдет в конце следующей недели. Следите за новостями на сайте проекта Open Oracle.

С наилучшими пожеланиями,

  В.К.