sql - Postgres 获得单个供应商的第一个和最后一个版本

Question

我有 RFQ(报价请求)和供应商投标金额与版本的映射表。

表格:

id  rfq_id(FK)  vendor_id(FK)   amount  version
-----------------------------------------------

 1      1           1            100      1
 2      1           1            90       2
 3      1           1            80       3
 4      1           2            50       1
 5      1           7            500      1
 6      1           7            495      2
 7      1           7            500      3
 8      1           7            525      4
 9      1           7            450      5
 10     1           7            430      6 
 11     2           1            200      1
 12     2           2            300      1
 13     2           2            350      2
 14     2           3            40       1
 15     3           4            70       1 

在上表中，我想分析供应商对特定 rfq_id 的第一次和最后一次出价。

rfq_id=1 的预期输出:

vendor_id   first_bid   last_bid
---------------------------------
    1           100         80
    2           50          50
    7           500         430

来自 Postgres : get min and max rows count in many to many relation table我开始了解 window 和 partition。所以我尝试了以下查询。

SELECT 
  vendor_id,
  version,
  amount,
  first_value(amount) over w as first_bid,
  last_value(amount) over w as last_bid,
  row_number() over w as rn
FROM   
   rfq_vendor_version_mapping
where
   rfq_id=1
   WINDOW w AS (PARTITION BY vendor_id order by version)
ORDER by vendor_id;

通过上述查询，每个供应商的最大 rn 是我的输出。

http://sqlfiddle.com/#!15/f19a0/7

Answer 1

窗口函数向所有现有行添加列，而不是将输入行分组到单个输出行中。由于您只对出价感兴趣，因此在感兴趣的字段上使用 DISTINCT 子句。

请注意，您需要一个 frame clause WINDOW 定义以确保考虑分区中的所有行。默认情况下，分区中的帧(计算中使用的行)从分区的开头运行到当前行。因此，last_value() 窗口函数总是返回当前行的值；使用 UNBOUNDED PRECEDING TO UNBOUNDED FOLLOWING 的框架将框架扩展到整个分区。

SELECT <b>DISTINCT</b>
  vendor_id,
  <strike>version,</strike>
  <strike>amount,</strike>
  first_value(amount) OVER w AS first_bid,
  last_value(amount) OVER w AS last_bid
 <strike> row_number() over w as rn</strike>
FROM   
   rfq_vendor_version_mapping
WHERE rfq_id = 1
WINDOW w AS (PARTITION BY vendor_id ORDER BY version
             <b>ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING</b>)
ORDER BY vendor_id;