Postgresql - оконные функции внутри оконных функций

Question

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

У меня есть таблица фактов:

       Column   |            Type             |                       Modifiers                       
------------+-----------------------------+-------------------------------------------------------
 id         | integer                     | not null default nextval('messages_id_seq'::regclass)
 type       | character varying(255)      | 
 ts         | numeric                     | 
 text       | text                        | 
 score      | double precision            | 
 user_id    | integer                     | 
 channel_id | integer                     | 
 time_id    | integer                     | 
 created_at | timestamp without time zone | 
 updated_at | timestamp without time zone | 

В настоящее время я выполняю некоторые аналитические запросы, один из которых (например) будет:

  with intervals as (
  select 
    (select '09/27/2014'::date) + (n      || ' minutes')::interval start_time,
    (select '09/27/2014'::date) + ((n+60) || ' minutes')::interval end_time
      from generate_series(0, (24*60*7), 60 * 4) n
  )
  select 
    extract(epoch from i.start_time)::numeric * 1000 as ts, 
    extract(epoch from i.end_time)::numeric * 1000 as end_ts,
    sum(avg(messages.score)) over (order by i.start_time) as score

  from messages
  right join intervals i
    on messages.timestamp >= i.start_time and messages.timestamp < i.end_time

  where messages.timestamp between '09/27/2014' and '10/04/2014'

  group by i.start_time, i.end_time 
  order by i.start_time

Как вы, ребята, вероятно, можете сказать - этот запрос вычисляет среднее значение атрибута "Score" для сообщений для заданного распределения периодов времени, а затем вместе с этим вычисляет совокупное значение по сегментам (используя окно).

Что я пытаюсь сделать дальше, это найти 5 лучших (например) messages.text которые ближе всего к среднему для каждого ведра.

Прямо сейчас единственный план, который я имею, состоит в том, чтобы:

1) Join messages with the time-buckets
2) Compute a score - avg(score) over (partition by start_time) as deviation and save it against each record of the joined relation
3) Compute a rank() over (order by deviation) as rank
4) Select where rank between 1 and 5

Причина, по которой я настойчиво изложил это, потому что моя первая попытка придумать дизайн включала использование оконной функции в оконной функции. (rank() over (partition by start_time, order by score - avg(score) over (partition by start_time)) и я даже не собирался делать это, чтобы посмотреть, сработает ли это.

Могу ли я получить совет о том, правильно ли я направляюсь?

Answer 1

Дракончик - вот что у меня есть и похоже на работу:

Теперь открытым для критики является структурирование, оптимизация производительности и избыточность в моем запросе! ^_^ (минус генерация временных рядов напрямую вместо всех искаженных математических интервалов, которые я исправлю в конце концов!)

with intervals as (
    select 
        (select '09/29/2014'::date) + (n      || ' minutes')::interval start_time,
        (select '09/29/2014'::date) + ((n+60) || ' minutes')::interval end_time
        from generate_series(0, (24*60*7), 60 * 4) n
), intervaled_messages as (
    select
        extract(epoch from i.start_time)::numeric * 1000 as ts, 
        extract(epoch from i.end_time)::numeric * 1000 as end_ts,
        abs(score - avg(score) over (partition by i.start_time)) as deviation
    from messages
    right join intervals i
        on messages.timestamp >= i.start_time and messages.timestamp < i.end_time
    where messages.timestamp between '09/29/2014' and '10/06/2014'
), ranked_messages as (
    select ts, end_ts, deviation, 
    rank() over (partition by ts order by deviation) as rank,
    row_number() over (partition by ts order by deviation) as row_number
    from intervaled_messages
)
select ts, end_ts, deviation, rank 
from ranked_messages 
where rank between 1 and 5
  and row_number between 1 and 5
order by ts;

Answer 2

Направление, в котором вы должны двигаться (это только мое предложение):

1. Получить средний балл (по всем записям)
2. операция MINUS на (row score, avg(score))

-- This will leave you with values also positive and negative

3. использование abs() на каждой операции из шага 2, в том же вычислении
4. использование rank() и заказать их соответствующим образом
5. WHERE rank BETWEEN 1 AND 5