Удалите избыточный код SQL

Проблема немного сложнее, чем в вашем обобщении. Я бы сформулировал это следующим образом:

SELECT a.group, func(a.group, avg_avg)
FROM a
    (SELECT AVG(field1_avg) as avg_avg
     FROM (SELECT a.group, AVG(field1) as field1_avg
           FROM a
           WHERE (YOUR_CONDITION)
           GROUP BY a.group) as several_lines -- potentially
    ) as one_line -- always
WHERE (YOUR_CONDITION)
GROUP BY a.group -- again, potentially several lines

У вас есть подмножество данных (ограниченное вашим состоянием), которое сгруппировано, и для каждой группы производится агрегирование. Затем вы объединяете агрегаты в одно значение и хотите снова применить функцию значения к каждой группе. Очевидно, вы не можете повторно использовать условие, пока на результат сгруппированного подзапроса не будет ссылаться как на сущность.

В MSSQL и Oracle можно использовать оператор WITH . В MySQL единственный вариант - использовать временную таблицу. Я предполагаю, что в вашем отчете больше одного года (иначе запрос был бы намного проще).

UPD : извините, я не могу опубликовать готовый код сейчас (могу сделать это завтра), но у меня есть идея:

Вы можете объединить данные, которые нужно вывести в подзапрос, с помощью GROUP_CONCAT И разделить его обратно во внешнем запросе с помощью функций FIND_IN_SET и SUBSTRING_INDEX . внешний запрос ПРИСОЕДИНЯЕТСЯ только к YEAR_REF и результату агрегирования.

Тогда условие во внешнем запросе будет просто WHERE FIND_IN_SET (year, concatenated_years) .

UPD :

Вот версия, которая использует GROUP_CONCAT для передачи необходимых данных внешнему JOIN.

Мои комментарии начинаются с - newtover: . Кстати, 1) я не думаю, что STRAIGHT_JOIN дает какие-либо преимущества, и 2) COUNT (*) имеет особое значение в MySQL и следует использовать , когда вы хотите подсчитать строки .

SELECT STRAIGHT_JOIN
  -- newtover: extract the corresponding amount back
  SUBSTRING_INDEX(SUBSTRING_INDEX(GROUPED_AMOUNTS, '|', @pos),'|', -1) as AMOUNT,
  Y.YEAR * ymxb.SLOPE + ymxb.INTERCEPT as REGRESSION_LINE,
  Y.YEAR as YEAR,
  MAKEDATE(Y.YEAR,1) as AMOUNT_DATE,
  ymxb.SLOPE,
  ymxb.INTERCEPT,
  ymxb.CORRELATION,
  ymxb.MEASUREMENTS
FROM
  -- newtover: list of tables now contains only the subquery, YEAR_REF for grouping and init_vars to define the variable
  YEAR_REF Y,
  (SELECT
    SUM(MEASUREMENTS) as MEASUREMENTS,
    ((sum(t.YEAR) * sum(t.AMOUNT)) - (count(1) * sum(t.YEAR * t.AMOUNT))) /
    (power(sum(t.YEAR), 2) - count(1) * sum(power(t.YEAR, 2))) as SLOPE,
    ((sum( t.YEAR ) * sum( t.YEAR * t.AMOUNT )) -
    (sum( t.AMOUNT ) * sum(power(t.YEAR, 2)))) /
    (power(sum(t.YEAR), 2) - count(1) * sum(power(t.YEAR, 2))) as INTERCEPT,
    ((avg(t.AMOUNT * t.YEAR)) - avg(t.AMOUNT) * avg(t.YEAR)) /
    (stddev( t.AMOUNT ) * stddev( t.YEAR )) as CORRELATION,
    -- newtover: grouped fields for matching years and the corresponding amounts
    GROUP_CONCAT(Y.YEAR) as GROUPED_YEARS,
    GROUP_CONCAT(AMOUNT SEPARATOR '|') as GROUPED_AMOUNTS
  FROM (
    SELECT STRAIGHT_JOIN
      COUNT(1) as MEASUREMENTS,
      AVG(D.AMOUNT) as AMOUNT,
      Y.YEAR as YEAR
    FROM
      CITY C,
      STATION S,
      STATION_DISTRICT SD,
      YEAR_REF Y,
      MONTH_REF M,
      DAILY D
    WHERE
      -- For a specific city ...
      $X{ IN, C.ID, CityCode } AND
      -- Find all the stations within a specific unit radius ...
      6371.009 *
      SQRT(
        POW(RADIANS(C.LATITUDE_DECIMAL - S.LATITUDE_DECIMAL), 2) +
        (COS(RADIANS(C.LATITUDE_DECIMAL + S.LATITUDE_DECIMAL) / 2) *
         POW(RADIANS(C.LONGITUDE_DECIMAL - S.LONGITUDE_DECIMAL), 2)) ) <= $P{Radius} AND
      SD.ID = S.STATION_DISTRICT_ID AND
      -- Gather all known years for that station ...
      Y.STATION_DISTRICT_ID = SD.ID AND
      -- The data before 1900 is shaky; insufficient after 2009.
      Y.YEAR BETWEEN 1900 AND 2009 AND
      -- Filtered by all known months ...
      M.YEAR_REF_ID = Y.ID AND
      -- Whittled down by category ...
      M.CATEGORY_ID = $P{CategoryCode} AND
      -- Into the valid daily climate data.
      M.ID = D.MONTH_REF_ID AND
      D.DAILY_FLAG_ID <> 'M'
    GROUP BY
      Y.YEAR
  ) t
) ymxb,
(SELECT @pos:=NULL) as init_vars
WHERE
    -- newtover: check if the year is in the list and store the index into the variable
    @pos:=CAST(FIND_IN_SET(Y.YEAR, GROUPED_YEARS) as UNSIGNED)
GROUP BY
  Y.YEAR

Поскольку SQL в вопросе был существенно изменен (теперь показаны только соответствующие части), вот мой новый ответ

Предположение: условие действительно такое же, и между подзапросом и внешним запросом не возникает сложного псевдонима столбцов

Ответ : Вы можете удалить where во внешнем запросе.

SELECT
  /* aggregate data */
  ymxb.*
FROM (
  SELECT
    /* similar aggregate data */
  WHERE
    /* some condition */
  GROUP BY
    YEAR
) ymxb
GROUP BY
  YEAR

Это должно дать вам тот же результат.

(Также обратите внимание, что вы могли удалить внутреннее where и сохранить внешнее - результаты должны быть такими же, но производительность может не быть).

Наконец, повторение предложения where, вероятно, не сильно повлияет на производительность - оценка дополнительных условий (даже таких выражений, как sqrt и т. Д.) Очень дешево по сравнению с любым вводом-выводом (и эти условия не работают ни с какими новыми столбцами, поэтому все операции ввода-вывода уже были выполнены)

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

Это делает любые агрегатные функции во внешнем запросе избыточными (строки из подзапроса, которые являются источником внешнего запроса, уже сгруппированы по годам).

Это делает весь подзапрос избыточным.

Можете ли вы использовать временную таблицу в вашей ситуации? Хотя для этого по-прежнему требуется дважды использовать предложение WHERE, это должно значительно повысить вашу производительность.

DROP TEMPORARY TABLE IF EXISTS TEMP_DATA

CREATE TEMPORARY TABLE TEMP_DATA 
    (SELECT AVG(field1_avg) as avg_avg
     FROM (SELECT a.group, AVG(field1) as field1_avg
           FROM a
           WHERE (SOME_CONDITION)
           GROUP BY a.group)
    )

SELECT t.group, func(t.group, t.avg_avg)
FROM TEMP_DATA AS t
WHERE (SOME_CONDITION)
GROUP BY t.group

Надеюсь, это поможет! - Дубли