聚合查询

Question

具有以下结构的product_stock表：

product_id
warehouse_id
stock
price

其中有许多记录(相同的产品可以在不同的仓库中)：

1, 1, 0, 500
1, 2, 5, 505
1, 3, 7, 508
2, 1, 0, 400
2, 2, 0, 404

现在，对于每一个product_id，我希望选择库存中最便宜的一个，如果产品不再有库存，选择价格和仓库为NULL -基本上，结果应该是：

1, 2, 5, 505
2, NULL, 0, NULL

这是一个木琴。

更新：几乎成功了(现在需要弄清楚如何选择合适的仓库)：

select product_id, min(price) from (
    select product_id,
        CASE WHEN stock = 0 then NULL else warehouse_id end,
        CASE WHEN stock = 0 then NULL else price end from stock
) AS f group by product_id;

返回(仍然需要计算出warehouse_id)：

product_id  min
1   505
2   (null)

更新2：我得到了warehouse_id，但是这个查询可以在没有代价的情况下杀死行：

SELECT stock.product_id, stock.warehouse_id, stock.price FROM (
    SELECT product_id, min(price) as price FROM (
        SELECT product_id,
          CASE WHEN stock = 0 then NULL else warehouse_id end,
          CASE WHEN stock = 0 then NULL else price end
        FROM stock
    ) AS f GROUP by product_id
) AS ff JOIN
stock on stock.product_id=ff.product_id and stock.price = ff.price;

结果：

product_id  warehouse_id    price
1   2   505

Answer 1

使用DISTINCT ON，这可能要简单得多：

SELECT DISTINCT ON (product_id)
       product_id
     , CASE WHEN stock = 0 THEN NULL ELSE warehouse_id END AS warehouse_id
     , stock
     , CASE WHEN stock = 0 THEN NULL ELSE price END AS price
FROM   product_stock
ORDER  BY product_id, (stock = 0), price;

假设stock是NOT NULL。

SQL Fiddle

关于DISTINCT ON：

• 在每一组中选择第一行？

Postgres有一个适当的boolean类型，可以ORDER BY任何布尔表达式。FALSE排序比TRUE排序早于NULL排序。因此，带有(stock = 0)的行在行后面排序，行后面还有stock的任何其他值--但NULL除外，后者是最后排序的。

Answer 2

两个类似的版本。第一：

-- query 1 --
WITH a AS
  ( SELECT 
        product_id, warehouse_id, stock, price,
        MIN(CASE WHEN stock > 0 THEN price END) 
            OVER (PARTITION BY product_id)
          AS min_price,
        MIN(CASE WHEN stock = 0 THEN price END) 
            OVER (PARTITION BY product_id)
          AS min_non_stock_price
    FROM stock
  )  
SELECT product_id, 
       CASE WHEN stock > 0 THEN warehouse_id END AS warehouse_id, 
       stock, 
       CASE WHEN stock > 0 THEN price END AS price
FROM a 
WHERE stock > 0         AND price = min_price
   OR min_price IS NULL AND price = min_non_stock_price ;  

第二项：

-- query 2 --
WITH a AS
  ( SELECT 
        product_id, warehouse_id, stock, price,
        MIN(CASE WHEN stock > 0 THEN price END) 
            OVER (PARTITION BY product_id)
          AS min_price
    FROM stock
  )  
SELECT product_id, warehouse_id, stock, price
FROM a 
WHERE price = min_price 

UNION ALL

SELECT product_id, NULL, 0, NULL
FROM a 
WHERE min_price IS NULL
GROUP BY product_id ; 

在SQLfiddle测试。

Answer 3

我最终尝试了CTE-s：

WITH min_prices AS (
  SELECT product_id, min(price) price FROM (
    SELECT product_id,
      CASE WHEN stock = 0 then NULL else price end
    FROM stock) as _ GROUP BY product_id
), existing_stock AS (
  SELECT product_id, warehouse_id, price FROM stock WHERE stock > 0
)
SELECT min_prices.product_id,
       existing_stock.warehouse_id,
       min_prices.price FROM min_prices
LEFT JOIN existing_stock ON
    min_prices.product_id = existing_stock.product_id AND
    min_prices.price = existing_stock.price;

导致win :)

product_id  warehouse_id    price
1   2   505
2   (null)  (null)

然而，执行计划看上去并不那么令人印象深刻：

Hash Join  (cost=49.97..114.58 rows=3 width=12)
  Hash Cond: ((stock.product_id = min_prices.product_id) AND (stock.price = min_prices.price))
CTE min_prices
    ->  HashAggregate  (cost=40.97..42.97 rows=200 width=12)
    ->  Seq Scan on stock stock_1  (cost=0.00..27.70 rows=1770 width=12)
    ->  Seq Scan on stock  (cost=0.00..32.12 rows=590 width=12)
Filter: (stock > 0)
    ->  Hash  (cost=4.00..4.00 rows=200 width=8)
    ->  CTE Scan on min_prices  (cost=0.00..4.00 rows=200 width=8)