PostgreSQL查询：获取各拍卖最优低价投标及投标人数

需求说明 #

需要从PostgreSQL数据库中查询每个拍卖的最低投标价，以及提交该最低报价的用户数量，同时要包含没有任何投标的拍卖记录（对应字段为null）。

解决方案SQL #

下面提供两种实现方式，都能满足需求：

方式一：两次聚合查询（适合新手理解）

SELECT
    a.auction_id,
    min_prices.best_bid_price,
    min_bids.best_bid_submitted_times,
    a.auction_data
FROM auctions a
-- 左连接获取每个拍卖的最低投标价
LEFT JOIN (
    SELECT
        auction_id,
        MIN(bid_price) AS best_bid_price
    FROM bids
    GROUP BY auction_id
) AS min_prices ON a.auction_id = min_prices.auction_id
-- 左连接获取每个拍卖中提交最低价的用户数
LEFT JOIN (
    SELECT
        b.auction_id,
        COUNT(b.user_id) AS best_bid_submitted_times
    FROM bids b
    JOIN (
        SELECT auction_id, MIN(bid_price) AS best_bid_price
        FROM bids
        GROUP BY auction_id
    ) AS mp ON b.auction_id = mp.auction_id AND b.bid_price = mp.best_bid_price
    GROUP BY b.auction_id
) AS min_bids ON a.auction_id = min_bids.auction_id
ORDER BY a.auction_id;

方式二：窗口函数实现（更简洁）

SELECT
    a.auction_id,
    sub.best_bid_price,
    sub.best_bid_submitted_times,
    a.auction_data
FROM auctions a
LEFT JOIN (
    SELECT
        auction_id,
        MIN(bid_price) AS best_bid_price,
        COUNT(user_id) AS best_bid_submitted_times
    FROM (
        -- 用窗口函数获取当前拍卖的最低投标价
        SELECT
            auction_id,
            user_id,
            bid_price,
            MIN(bid_price) OVER (PARTITION BY auction_id) AS best_bid_price
        FROM bids
    ) AS bid_details
    -- 筛选出等于当前拍卖最低价的投标记录
    WHERE bid_price = best_bid_price
    GROUP BY auction_id, best_bid_price
) AS sub ON a.auction_id = sub.auction_id
ORDER BY a.auction_id;

对原查询的改进说明 #

你原查询使用rank()窗口函数能筛选出最低价的投标，但存在两个问题：

1. 会返回多条同一拍卖的最低价记录（比如A2的两条P4投标），无法直接统计人数；
2. 没有处理无投标的拍卖（原查询中A3会被过滤掉，因为bidRank为null，不满足bidRank=1的条件）。

上面的两种方式都通过LEFT JOIN保留了所有拍卖记录，同时通过聚合统计出了最低价的提交人数，完全符合预期输出要求。

内容的提问来源于stack exchange，提问作者Vegan Vegeta