Postgres：在多对多关系表中获取最小和最大行数

问题描述

rfq_vendor_mapping

  id rfq_id（FK）vendor_id（FK）amount 
 ------------------- -------------------- 
 
 1 1 1 100 
 2 1 2 50 
 3 2 1 200 
 4 2 3 300 
 5 2 2 40 
 6 3 4 70 
 7 3 1 90 
 8 3 2 250 
 9 4 3 30 
 10 5 1 500 
  

在上表中，我想分析供应商提交的最低和最大数量出价每个RFQ。

预期输出

  vendor_id min_bid_count max_bid_count 
 ----------------------------------------- 
 1 1 2 
 2 2 1 
 3 1 2 
 4 1 0 
  

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

rfq_vendor_mapping :

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

 1      1           1            100
 2      1           2            50
 3      2           1            200
 4      2           3            300
 5      2           2            40
 6      3           4            70
 7      3           1            90
 8      3           2            250
 9      4           3            30
 10     5           1            500

In above table, I want analysis for how many times vendor has submitted minimum and maximum bid for each RFQ.

Expected Output :

vendor_id   min_bid_count   max_bid_count
-----------------------------------------
    1           1               2
    2           2               1
    3           1               2
    4           1               0

http://sqlfiddle.com/#!15/60198/1

解决方案

Compare the vendor's amount with min and max from a window function and run a conditional count on outer query level:

SELECT vendor_id
     , count(min_bid OR NULL) AS min_bid_count
     , count(max_bid OR NULL) AS max_bid_count
FROM  (
   SELECT vendor_id
        , amount = min(amount) OVER w AS min_bid
        , amount = max(amount) OVER w AS max_bid
   FROM   rfq_vendor_mapping
   WINDOW w AS (PARTITION BY rfq_id)
   ) sub
GROUP  BY 1
ORDER  BY 1;

SQL Fiddle.

这篇关于Postgres：在多对多关系表中获取最小和最大行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！