Запрос Oracle SQL для суммирования статистики с использованием GROUP BY

У меня есть таблица Oracle с данными, которые выглядят так:

ID   BATCH   STATUS
1    1       0
2    1       0
3    1       1
4    2       0

То есть ID является первичным ключом, для каждого "пакета" будет несколько строк, и каждая строка будет иметь код состояния в столбце STATUS. Есть куча других столбцов, но эти самые важные.

Мне нужно написать запрос, который будет суммировать коды состояния для каждого пакета; есть три возможных значения, которые могут быть в столбце STATUS: 0, 1 и 2, и я хотел бы, чтобы вывод выглядел примерно так:

BATCH  STATUS0  STATUS1  STATUS2
1      2        1        0
2      1        0        0

Эти числа будут считаться; для партии 1 есть

  • 2 записи, в которых STATUS имеет значение 0
  • 1 запись, где STATUS имеет значение 1, и
  • нет записей, в которых STATUS имеет значение 0.

Для партии 2 есть

  • 1 запись, где для STATUS установлено значение 0, и
  • нет записей, в которых STATUS имеет значение 1 или 2.

Есть ли способ сделать это в одном запросе, не переписывая запрос для каждого кода состояния? то есть я могу легко написать такой запрос и запустить его три раза:

SELECT batch, COUNT(status)
FROM table
WHERE status = 0
GROUP BY batch

Я мог бы запустить это, а затем снова запустить, где статус = 1, и снова, где статус = 2, но я надеюсь сделать это в одном запросе.

Если это имеет значение, помимо столбца STATUS есть еще один столбец, который я мог бы суммировать таким же образом — еще одна причина, по которой я не хочу иметь выполнить оператор SELECT после оператора SELECT и объединить все результаты.


sql oracle pivot group-by
person sernaferna    schedule 22.06.2009    source источник

Ответы (3)


arrow_upward
6
arrow_downward 

select batch 
,      count(case when status=1 then 1 end) status1
,      count(case when status=2 then 1 end) status2
,      count(case when status=3 then 1 end) status3
from   table
group by batch;

Этот запрос часто называют "сводным" запросом, и я написал статью о том, как динамически генерировать такие запросы в моем блоге.

Версия с использованием DECODE (специфичная для Oracle, но менее подробная):

select batch 
,      count(decode(status,1,1)) status1
,      count(decode(status,2,1)) status2
,      count(decode(status,3,1)) status3
from   table
group by batch;
person Tony Andrews    schedule 22.06.2009

arrow_upward
1
arrow_downward

OP спрашивает, есть ли какое-либо преимущество в производительности одного подхода (SUM) по сравнению с другим (COUNT). Выполнение простого теста для таблицы с 26 000 строк показывает, что метод COUNT значительно быстрее. YMMV.

DECLARE
  CURSOR B IS
     select batch_id
       FROM batch
      WHERE ROWNUM < 2000;

  v_t1  NUMBER;
  v_t2  NUMBER;
  v_c1  NUMBER;
  v_c2  NUMBER;
  v_opn INTEGER;
  v_cls INTEGER;
  v_btc VARCHAR2(100);
BEGIN
-- Loop using SUM
  v_t1 := dbms_utility.get_time;
  v_c1 := dbms_utility.get_cpu_time;
  FOR R IN B LOOP
     FOR R2 IN (SELECT batch_type_code
                     , SUM(decode(batch_status_code, 'CLOSED', 1, 0)) closed
                     , SUM(decode(batch_status_code, 'OPEN', 1, 0)) OPEN
                     , SUM(decode(batch_status_code, 'REWORK', 1, 0)) rework
                  FROM batch
                 GROUP BY batch_type_code) LOOP 
        v_opn := R2.open;
        v_cls := R2.closed;
     END LOOP;
  END LOOP;
  v_t2 := dbms_utility.get_time;
  v_c2 := dbms_utility.get_cpu_time;
  dbms_output.put_line('For loop using SUM:');
  dbms_output.put_line('CPU seconds used: '||(v_c2 - v_c1)/100);
  dbms_output.put_line('Elapsed time: '||(v_t2 - v_t1)/100);

-- Loop using COUNT
  v_t1 := dbms_utility.get_time;
  v_c1 := dbms_utility.get_cpu_time;
  FOR R IN B LOOP
     FOR R2 IN (SELECT batch_type_code
                     , COUNT(CASE WHEN batch_status_code = 'CLOSED' THEN 1 END) closed
                     , COUNT(CASE WHEN batch_status_code = 'OPEN' THEN 1 END) OPEN
                     , COUNT(CASE WHEN batch_status_code = 'REWORK' THEN 1 END) rework
                  FROM batch
                 GROUP BY batch_type_code) LOOP 
        v_opn := R2.open;
        v_cls := R2.closed;
     END LOOP;
  END LOOP;
  v_t2 := dbms_utility.get_time;
  v_c2 := dbms_utility.get_cpu_time;
  dbms_output.put_line('For loop using COUNT:');
  dbms_output.put_line('CPU seconds used: '||(v_c2 - v_c1)/100);
  dbms_output.put_line('Elapsed time: '||(v_t2 - v_t1)/100);
END;
/

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

For loop using SUM:
CPU seconds used: 40
Elapsed time: 40.09
For loop using COUNT:
CPU seconds used: 33.26
Elapsed time: 33.34

Я повторил тест пару раз, чтобы исключить любые эффекты кэширования. Я также поменял местами операторы select. Результаты были одинаковыми по всем направлениям.

РЕДАКТИРОВАТЬ: это тот же тестовый набор, который я использовал для ответа на аналогичный вопрос с.

person DCookie    schedule 22.06.2009

arrow_upward

arrow_downward

person    schedule
comment
Преимущество COUNT в том, что он никогда не возвращает NULL, поэтому ваш DECODE может быть еще более компактным: count((decode(status,0,1)) status0 - person Tony Andrews; 23.06.2009