时间戳

首页 > 解决方案 > Postgresql SUM 计算列

postgresql - Postgresql SUM 计算列

问题描述

我正在尝试创建一些 sql 来计算用户库存的价值，并设法让它工作到最后一步。

SELECT DISTINCT ON (pricing_cards.card_id)
    (inventory_cards.nonfoil * pricing_cards.nonfoil) + (inventory_cards.foil * pricing_cards.foil) as x
FROM inventory_cards 
    INNER JOIN pricing_cards ON pricing_cards.card_id = inventory_cards.card_id
WHERE inventory_cards.user_id = 1
ORDER BY pricing_cards.card_id, pricing_cards.date DESC;

上面的代码带回了一个具有正确计算卡的列。我现在需要将这一列相加，但是当我尝试对其求和时不断出错。

添加SUM((inventory_cards.nonfoil * pricing_cards.nonfoil) + (inventory_cards.foil * pricing_cards.foil))会引发以下错误

ERROR:  column "pricing_cards.card_id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 6: ORDER BY pricing_cards.card_id, pricing_cards.date DESC;

添加GROUP BY pricing_cards.card_id, pricing_cards.date似乎可以修复错误，但会返回同一列计算值。

所以：

SELECT DISTINCT ON (pricing_cards.card_id)
    SUM((inventory_cards.nonfoil * pricing_cards.nonfoil) + (inventory_cards.foil * pricing_cards.foil)) as x
FROM inventory_cards 
    INNER JOIN pricing_cards ON pricing_cards.card_id = inventory_cards.card_id
WHERE inventory_cards.user_id = 1
GROUP BY pricing_cards.card_id, pricing_cards.date
ORDER BY pricing_cards.card_id, pricing_cards.date DESC;

回报：

X
0.71
29.92
25.67
171.20
0.32
0.26

标签： postgresql

解决方案

我建议您使用子查询来获取最新的定价数据，然后加入并求和：

SELECT
  SUM(inventory_cards.nonfoil * latest_pricing.nonfoil + inventory_cards.foil * latest_pricing.foil)
FROM inventory_cards 
INNER JOIN (
  SELECT DISTINCT ON (card_id)
    card_id, nonfoil, foild
  FROM pricing_cards
  ORDER BY pricing_cards.card_id, pricing_cards.date DESC
) AS latest_pricing USING (card_id)
WHERE inventory_cards.user_id = 1

有关子查询中的备选方案，另请参阅选择每个 GROUP BY 组中的第一行？并优化 GROUP BY 查询以检索每个用户的最新行。

推荐阅读