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

正如你们可能知道的--这个查询计算给定时间桶分布的消息的“得分”属性的平均值，然后与它一起计算整个存储桶的累积值(使用窗口)。

接下来我要做的是找到最接近每个桶的平均值的前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

1. 对步骤2中的每个操作在相同的计算中使用abs()
2. 使用rank()并适当地订购它们
3. WHERE rank BETWEEN 1 AND 5