Amazon RedShift sql: удалить верхние 1% данных как выбросы

Question

Я пытаюсь удалить 1% данных, поскольку мы рассматриваем те выбросы, которые сильно искажают данные. я пытался использовать SELECT TOP 99 PERC, но Amazon Redshift не поддерживает проценты с TOP.

Я пробовал что-то вроде:

WITH 
elapsed_times AS (
  SELECT 
    COALESCE(anonymous_id, distinct_id) as id,
    elapsed_time
  FROM studio_production.interaction
  WHERE project_id = '55062b464a9bc578006987ff'
),
max_elapsed_time AS (
  SELECT elapsed_time
  FROM elapsed_times
  ORDER BY elapsed_time ASC
  OFFSET ROUND(0.99 * (SELECT COUNT(*) FROM elapsed_times))
  LIMIT 1
),
user_times AS (
  SELECT 
    id, 
    LEAST(elapsed_time, max_elapsed_time) as elapsed_time
  FROM elapsed_times
  GROUP BY 1
)

SELECT AVG(elapsed_time) FROM user_times

Но я получаю: argument of OFFSET must not contain subqueries

Таким образом, мой запрос сейчас:

WITH 
elapsed_times AS (
  SELECT 
    COALESCE(anonymous_id, distinct_id) as id,
    elapsed_time,
    RANK() OVER (ORDER BY elapsed_time ASC) as rnk
  FROM studio_production.interaction
  WHERE project_id = '55062b464a9bc578006987ff'
), 
user_times AS (
  SELECT 
    id,
    LEAST(MAX(elapsed_time), (
      SELECT MIN(elapsed_time)
      FROM elapsed_times
      WHERE rnk > ROUND(0.99 * (SELECT COUNT(*) FROM elapsed_times))
    )) as elapsed_time
  FROM elapsed_times
  GROUP BY 1
)

SELECT AVG(elapsed_time) FROM user_times

что на самом деле довольно медленно. как правильно подойти к этой проблеме?

Answer 1

Вы могли бы использовать ntile() (см. здесь):

select avg(elapsed_time)
from (select et.*,
             ntile(100) over (order by elapsed_time) as thetile
      from elapsed_times et
     ) et
where thetile not in (1, 100);

РЕДАКТИРОВАТЬ:

Я признаю, что я часто делаю это, используя row_number() а также count():

select avg(elapsed_time)
from (select et.*,
             row_number() over (order by elapsed_time) as seqnum,
             count(*) over () as cnt
      from elapsed_times et
     ) et
where (seqnum <= 0.01 * cnt) or (seqnum >= 0.99 * cnt);