Postgres获得个人供应商的第一个和最后一个版本 [英] Postgres get first and last version for individual vendor
问题描述
表：
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：获取最小和最大行数在许多关系表中我已经了解了窗口和分区。所以我试过下面的查询。
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 是我的输出。
http：/ /sqlfiddle.com/#!15/f19a0/7
窗口功能添加列到所有现有行，而不是将输入行分组到单个输出行。由于您只对出价值感兴趣，请在感兴趣的字段上使用 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
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
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,
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
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
ORDER BY vendor_id;
这篇关于Postgres获得个人供应商的第一个和最后一个版本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
