Oracle SQL Query для расчета среднего значения набора данных, исключая выбросы

Question

У меня есть запрос, который содержит правильные условия и поля, которые я хочу отобразить:

  SELECT t.business_process_id,
         COUNT (tsp.status) AS COUNT,
         ROUND (AVG (tsp.end_date - tsp.start_date), 2) * 24 * 60 AS average,
         ROUND (MAX (tsp.end_date - tsp.start_date), 2) * 24 * 60 AS MAX,
         ROUND (MIN (tsp.end_date - tsp.start_date), 2) * 24 * 60 AS MIN,
         ROUND (MEDIAN (tsp.end_date - tsp.start_date), 2) * 24 * 60 AS MEDIAN,
         ROUND (STDDEV (tsp.end_date - tsp.start_date), 2) AS std_deviation
    FROM transaction_status_period tsp, transaction t
   WHERE     t.trans_id = tsp.trans_id
         AND tsp.status = 'R'
         AND tsp.end_date IS NOT NULL
         AND tsp.userid NOT IN ('X', 'Y', 'Z', 'A')
         AND EXTRACT (DAY FROM tsp.start_date) =
                 EXTRACT (DAY FROM tsp.end_date)
         AND EXTRACT (YEAR FROM tsp.start_date) =
                 EXTRACT (YEAR FROM tsp.end_date)
         AND EXTRACT (MONTH FROM tsp.start_date) =
                 EXTRACT (MONTH FROM tsp.end_date)
         AND EXTRACT (YEAR FROM tsp.start_date) = 2013
         AND NOT EXISTS
                     (SELECT 1
                        FROM transaction_status_period tsp1
                       WHERE     tsp1.trans_id = tsp.trans_id
                             AND tsp.userid = tsp1.userid
                             AND tsp1.status = 'S'
                             AND tsp1.timestamp < tsp.timestamp)
GROUP BY t.business_process_id

Среднее значение, рассчитанное по запросу, относится ко всему набору данных (для года = 2013). Есть ли способ получить запрос для расчета среднего значения всех данных за 2013 год, исключая выбросы? Т.е. найти среднее значение разницы дат (tsp.end_date - tsp.start_date) где, большинство наблюдений падает, за 2013 год?

Будет ли percentile_cont функция работы? Я не знаком с этим, но я знаю, что он рассчитывает процентиль конкретного столбца. В моем случае я ищу среднюю разницу между датами (tsp.end_date - tsp.start_date), но среднее значение большинства точек данных (исключая выбросы).

Любая помощь приветствуется. Возможно, я неправильно отвечаю на этот вопрос.

Answer 1

Решит ли что-то подобное вашу проблему?

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

SELECT calc.business_process_id,
 COUNT (calc.status) AS COUNT,
 ROUND (AVG (calc.end_date - calc.start_date), 2) * 24 * 60 AS average,
 ROUND (MAX (calc.end_date - calc.start_date), 2) * 24 * 60 AS MAX,
 ROUND (MIN (calc.end_date - calc.start_date), 2) * 24 * 60 AS MIN,
 ROUND (MEDIAN (calc.end_date - calc.start_date), 2) * 24 * 60 AS MEDIAN,
 ROUND (STDDEV (calc.end_date - calc.start_date), 2) AS std_deviation
FROM (SELECT t.business_process_id,
         tsp.status,
         tsp.start_date,
         tsp.end_date, 
         ntile(100) over (order by (tsp.end_date-tsp.start_date)) as percentiles
      FROM transaction_status_period tsp, transaction t 
      WHERE     t.trans_id = tsp.trans_id
      AND tsp.status = 'R'
      AND tsp.end_date IS NOT NULL
      AND tsp.userid NOT IN ('X', 'Y', 'Z', 'A')
      AND EXTRACT (DAY FROM tsp.start_date) =
          EXTRACT (DAY FROM tsp.end_date)
      AND EXTRACT (YEAR FROM tsp.start_date) =
          EXTRACT (YEAR FROM tsp.end_date)
      AND EXTRACT (MONTH FROM tsp.start_date) =
          EXTRACT (MONTH FROM tsp.end_date)
      AND EXTRACT (YEAR FROM tsp.start_date) = 2013
      AND NOT EXISTS
             (SELECT 1
                FROM transaction_status_period tsp1
               WHERE     tsp1.trans_id = tsp.trans_id
                     AND tsp.userid = tsp1.userid
                     AND tsp1.status = 'S'
                     AND tsp1.timestamp < tsp.timestamp)
  ) calc
WHERE calc.percentiles >=10 
AND calc.percentiles <=90
GROUP BY calc.business_process_id