Странная периодическая группа по задачам (ORA-00979)

Мы используем следующий SQL-запрос для получения среднемесячных значений. Этот оператор часто используется и работает довольно хорошо, но он терпит неудачу с 'ORA-00979: not a GROUP BY expression' каждый месяц или два, и мы понятия не имеем, почему .

Сначала о процессе:

  • у нас необработанные данные каждые несколько минут,
  • => необработанные данные усредняются до ежечасных, дневных, месячных и годовых значений

сырые -> ежечасно

  • создает записи в таблице средних значений со средним_типом 2
  • никогда не будет проблем

ежечасно => ежедневно / ежедневно => ежемесячно / ежемесячно => ежегодно

  • Заявления очень похожи
  • средние «более низкого» типа усредняются к более высокому типу

  • Средние типы: 2 часа, 3 дня, (4 недели не используются) 5 ежемесячно и 6 раз в год.

  • Ошибка появляется только на шаге «ежедневно => ежемесячно».

Запрос:

  • Мы не можем воспроизвести ошибку, следующий запуск задания агрегирования обычно проходит без проблем.
  • Ошибки возникают каждые 50-60 дней, без реальной закономерности.
  • Среда: Oracle 10g

Кто-нибудь знает, в чем может быть проблема?

INSERT INTO averages
SELECT averages_seq.NEXTVAL,
       avg.*
FROM (
  SELECT
      m.city_id,            m.city_name,
      m.state_id,           m.state_name,
      m.district_id,        m.district_name,
      m.country_id,         m.country_name,
      m.currency_id,        m.currency_name,
      m.category_id,        m.category_name,
      5 average_type, -- average_type 5 ==> monthly average
      0 analysis_type,
      TRUNC(m.average_date, 'MM')  average_date,
      AVG(m.value) value,
      SUM(m.sum) sum,
      NULL uncertainty,
      NULL uncertainty_type,
      MIN(m.value_min) value_min,
      MAX(m.value_max) value_max,
      SUM(number_of_measurements) number_of_measurements,
      -- 6 * 24 => measurements per day
      -- (ADD_MONTHS(...)) => days per month 
      100 * SUM(number_of_measurements) / 
           (6 * 24 *
           (ADD_MONTHS(TRUNC(average_date, 'MM'), 1)  - TRUNC(average_date, 'MM'))) coverage_percent,
      SUM(customers) customers,
      NULL dummy_field,
      CURRENT_TIMESTAMP calculation_date,
      CURRENT_TIMESTAMP creation_date,
      'AGGREGATION' creation_user,
      CURRENT_TIMESTAMP modification_date,
      'AGGREGATION' modification_user,
      'n' constant_1,
      3   constant_2,
      -1 average_state
  FROM averages m
  WHERE   m.average_type = 3 -- average type 3 ==> daily average
  AND     m.average_date
      BETWEEN
        TO_TIMESTAMP('2011-06-01T00:00:00Z', 'YYYY-MM-DD"T"HH24:MI:SS"Z"')
        AND
        TO_TIMESTAMP('2011-06-30T23:59:59Z', 'YYYY-MM-DD"T"HH24:MI:SS"Z"')
  AND     m.analysis_type = 0
  GROUP BY
        m.city_id,            m.city_name,
        m.state_id,           m.state_name,
        m.district_id,        m.district_name,
        m.country_id,         m.country_name,
        m.currency_id,        m.currency_name,
        m.category_id,        m.category_name,
        TRUNC(m.average_date, 'MM')
  ) avg

sql oracle group-by ora-00979
person reto    schedule 04.08.2011    source источник
comment
Я удалил свой ответ, поскольку документы говорят, что sysdate всегда возвращает одно и то же значение DATE в контексте одного оператора SQL (oracle.su/docs/11g/timesten.112/e13070/ttsql247.htm).   -  person Gerrat    schedule 04.08.2011
comment
Мне интересно, есть ли когда-нибудь случай, когда один из столбцов в разделе GROUP BY может когда-либо быть нулевым. Это единственное, о чем я могу думать.   -  person Narnian    schedule 04.08.2011
comment
@Gerrat: спасибо за идею и даже за то, что убедились, что это не может быть проблемой :)! Нарниан: Я не уверен, почему null может быть проблемой?   -  person reto    schedule 04.08.2011
comment
@ reto / @ Narnian ... Я сомневаюсь, что null может быть проблемой ... вы можете сгруппировать по столбцу, содержащему нули (это просто отдельная группа)   -  person Gerrat    schedule 04.08.2011
comment
Интересная особенность ошибки ORA-00979 (или, скорее, выражений GROUP BY) заключается в том, что они проверяются не во время компиляции, а во время выполнения, если они являются частью процедуры PL / SQL. (Попробуйте это с помощью SELECT A, B FROM T GROUP BY A). Нулевые значения, даты или любое содержимое таблицы вряд ли являются проблемой, так как правильность зависит только от определения таблицы. Похоже, что это либо ошибка Oracle, либо ваша таблица AGGREGATES изменена (DDL) между запусками запроса. Вы перестраиваете индексы, добавляете или удаляете разделы, включаете или отключаете ограничения?   -  person Codo    schedule 05.08.2011
comment
@CODO: это просто sql, выполняемый поверх jdbc, ничего особенного. Нет DDL, по крайней мере, ничего явного (не знаю, восстанавливает ли Oracle индексы внутренне, но я сомневаюсь, что вы имели в виду такие вещи).   -  person reto    schedule 05.08.2011

Ответы (2)


arrow_upward
1
arrow_downward

Я бы добавил группу по:

(ADD_MONTHS(TRUNC(average_date, 'MM'), 1)  - TRUNC(average_date, 'MM'))

Я знаю, что это не может измениться без изменения TRUNC(m.average_date, 'MM'), но, похоже, это единственный неагрегированный столбец, которого нет в вашей GROUP BY.

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

eg.

INSERT INTO averages(city_id, city_name, ...average_type, analysis_type, ...)
SELECT averages_seq.NEXTVAL,
avg.city_id, avg.city_name, ...
5, 0, ...

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

person Gerrat    schedule 04.08.2011
comment
Если бы я мог воспроизвести проблему, это было бы легкой задачей. Но я попробую, если ничего не выйдет! - person reto; 04.08.2011
comment
Я не понимаю, почему за этот ответ проголосовали. Если TRUNC(average_date, 'MM') является группой по выражению, все выражения строятся на нем без введения каких-либо новых неагрегированных или несгруппированных столбцов. Предлагаемые решения - это не просто догадки (которые иногда нужны для поиска ошибки), они противоречат логике предложения GROUP BY в SQL. - person Codo; 05.08.2011
comment
Я убрал все неагрегированные части из внутреннего запроса. Посмотрим, исправит ли он. - person reto; 05.08.2011
comment
Пока ошибка не появлялась снова. Помогло перемещение всего неагрегируемого материала во внешний запрос. Спасибо за помощь! - person reto; 02.09.2011
comment
Хорошо, теперь ясно, что перезапись запроса решила "ORA-00979", ошибка больше не появлялась. - person reto; 30.09.2011

arrow_upward
0
arrow_downward

Судя по сравнению, average_date - это метка времени с часовым поясом (местный часовой пояс?), Но TRUNC работает с датой. Мне интересно, что произойдет, если есть какая-то странность, когда выбранная дата «прыгает» из одного месяца в другой (например, это произошло в январе в одном часовом поясе, а в феврале в другом).

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

Я бы расширил предложение Геррата об указании имен столбцов, чтобы вы могли отделить константы

INSERT INTO averages
  (average_type, analysis_type, uncertainty, uncertainty_type,
  dummy_field, calculation_date, creation_date, creation_user, 
  modification_date, modification_user, constant_1, constant_2,
   ....
SELECT averages_seq.NEXTVAL,
      5 average_type, -- average_type 5 ==> monthly average
      0 analysis_type,
      NULL uncertainty,
      NULL uncertainty_type,
      NULL dummy_field,
      CURRENT_TIMESTAMP calculation_date,
      CURRENT_TIMESTAMP creation_date,
      'AGGREGATION' creation_user,
      CURRENT_TIMESTAMP modification_date,
      'AGGREGATION' modification_user,
      'n' constant_1,
      3   constant_2,
      -1 average_state
       avg.*
FROM (
  SELECT ...
person Gary Myers    schedule 05.08.2011
comment
Все данные utc. Есть только один клиент, который правильно устанавливает часовой пояс. Работа работает без проблем при повторном запуске. Спасибо за ваши мысли! - person reto; 17.08.2011