Postgres获得个人供应商的第一个和最后一个版本

问题描述

表：

  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 
  

从我已经了解了窗口和分区。所以我试过下面的查询。

  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 
其中
 rfq_id = 1 
 WINDOW w AS（由供应商按订单按版本分配）
订单由供应商编号; 
  

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

解决方案

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

请注意您需要一个为 WINDOW 定义，以确保分区中的所有行都被考虑。默认情况下，分区中的帧（计算中使用的行）从分区的开头运行到当前行。因此， last_value（） window函数总是返回当前行的值;使用的框架，以无符号以下按钮将框架扩展到整个分区。

 code> SELECT  DISTINCT  
 vendor_id，
  
  
 first_value（amount）OVER w AS first_bid，
 last_value（amount）OVER w AS last_bid 
  
 FROM 
 rfq_vendor_version_mapping 
 WHERE rfq_id = 1 
 WINDOW w AS（PARTITION BY vendor_id ORDER BY version 
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ）
 ORDER BY vendor_id; code> 

I have mapping table for RFQ(request for quotation) and Vendor's bid amount with version.

Table :

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

In above table, I want analysis for vendor's first and last bid for particular rfq_id.

Expected Output for rfq_id=1 :

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

From Postgres : get min and max rows count in many to many relation table I have came to know about window and partition. So I have tried below query.

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;

With above query, every vendor's maximum rn is my output.

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

解决方案

Window functions add columns to all the existing rows, instead of grouping input rows into a single output row. Since you are only interested in the bid values, use a DISTINCT clause on the fields of interest.

Note that you need a frame clause for the WINDOW definition to make sure that all rows in the partition are considered. By default, the frame in the partition (the rows that are being used in calculations) runs from the beginning of the partition to the current row. Therefore, the last_value() window function always returns the value of the current row; use a frame of UNBOUNDED PRECEDING TO UNBOUNDED FOLLOWING to extend the frame to the entire partition.

SELECT DISTINCT
  vendor_id,


  first_value(amount) OVER w AS first_bid,
  last_value(amount) OVER w AS last_bid

FROM
   rfq_vendor_version_mapping
WHERE rfq_id = 1
WINDOW w AS (PARTITION BY vendor_id ORDER BY version
             ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
ORDER BY vendor_id;

这篇关于Postgres获得个人供应商的第一个和最后一个版本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！