Получение 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 (разногласия?) ни о чем мне не говорит.

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

Если:

используется агрегирование
в запросе участвует более двух таблиц (имеется N таблиц, что дает N-1 соединение)
и между таблицами нет, по крайней мере, 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 приемов.)

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