基于平均值返回多个供应商的文本

Question

我有一个表格，给出了各种供应商/领域的评级。格式如下。

我想知道，对于每个不同的月份，以及供应商（exp_id）

什么是最高和最低评级的拾音器_ward_text

我的预期输出类似于：

【年】【月】【exp_id】【最高额定拾波方向】【最低额定拾波向】

其中"rated"是rating_driver、rating_pointicity&车辆比率（_V）

我完全不知道如何做到这一点，我试图正确地越过下表的第一行。

Year    Month   exp_id  RATING_DRIVER   RATING_PUNCTUALITY  RATING_VEHICLE  booking_id  pickup_date ratingjobref    rating_date PICKUP_WARD_TEXT
2013       10    4            5.00       5.00                      5.00     1559912    30:00.0    1559912       12/10/2013 18:29       N4

Answer 1

有一种常见的模式，使用row_number（）来查找最小值或最大值。你可以把它们和一个小把戏结合起来：

select
    year,
    month,
    exp_id,
    max(case rn1 when 1 then pickup_ward_text end) as min_pickup_ward_text,
    max(case rn2 when 1 then pickup_ward_text end) as max_pickup_ward_text
from (
    select
        year,
        month,
        exp_id,
        pickup_ward_text,
        row_number() over (
            partition by year, month, exp_id 
            order By rating_driver + rating_punctuality + rating_vehicle
        ) rn1,
        row_number() over (
            partition by year, month, exp_id 
            order By rating_driver + rating_punctuality + rating_vehicle desc
        ) rn2
    from
        mytable
    ) x
where
    rn1 = 1 or rn2 = 1 -- this line isn't necessary, but might make things quicker
group by
    year,
    month,
    exp_id
order by
    year,
    month,
    exp_id

实际上，为每个部分创建两个派生表并在内部连接它们可能会更快。一些测试正在进行中：

select
    n.year,
    n.month,
    n.exp_id,
    n.pickup_ward_text as min_pickup_ward_text,
    x.pickup_ward_text as max_pickup_ward_text
from (
    select
        year,
        month,
        exp_id,
        pickup_ward_text,
        row_number() over (
            partition by year, month, exp_id 
            order By rating_driver + rating_punctuality + rating_vehicle
        ) rn
    from
        mytable
    ) n
    inner join (
    select
        year,
        month,
        exp_id,
        pickup_ward_text,
        row_number() over (
            partition by year, month, exp_id 
            order By rating_driver + rating_punctuality + rating_vehicle desc
        ) rn
    from
        mytable
    ) x
    on n.year = x.year and n.month = x.month and n.exp_id = x.exp_id
where
    n.rn = 1 and
    x.rn = 1 
order by
    year,
    month,
    exp_id