postgresql数学公式误差

Question

嗨，我试图在postgresql中的每一行上使用一个数学函数。但它给了我一个错误。

我的问题是：

Select
  stock_inventory_line.product_code AS Sku,
  COUNT(sale_order_line.name) AS Qty_Sold,
  stock_inventory_line.product_qty AS Current_Qty,
  (stock_inventory_line.product_qty / Qty_Sold) AS NOM
From
  sale_order_line,
  product_product,
  product_template,
  product_category,
  stock_inventory_line
WHERE
  sale_order_line.product_id = product_product.id AND
  product_product.product_tmpl_id = product_template.id AND
  product_template.categ_id = product_category.id AND
  product_product.default_code = stock_inventory_line.product_code
GROUP BY
  Sku,
  Current_Qty,
  NOM;

在这个查询中，它给出了一个错误:列qty_sold不存在。如果我将第5行改为

(stock_inventory_line.product_qty / COUNT(sale_order_line.name)) AS NOM

它给了我一个错误:聚合函数不允许在组。

Answer 1

您正在尝试按项目使用计数(sale_order_line.name)作为一个组。Aggreagte函数用于分组项目。他们不是用来分组的。

我不知道你的桌子，但试试看

Select
  stock_inventory_line.product_code AS Sku,
  COUNT(sale_order_line.name) AS Qty_Sold,
  stock_inventory_line.product_qty AS Current_Qty,
  (stock_inventory_line.product_qty / COUNT(sale_order_line.name)) AS NOM
From
  sale_order_line,
  product_product,
  product_template,
  product_category,
  stock_inventory_line
WHERE
  sale_order_line.product_id = product_product.id AND
  product_product.product_tmpl_id = product_template.id AND
  product_template.categ_id = product_category.id AND
  product_product.default_code = stock_inventory_line.product_code
GROUP BY
  stock_inventory_line.product_code,
  stock_inventory_line.product_qty;

基本上我把诺姆从小组里删除了。它是每一组的产品，而不是你分组的东西。

Answer 2

我经常遇到这样的问题。使用CTE总是对我有用的。在CTE中进行聚合，然后在外部语句中调用它们时，Postgres将它们视为数值而不是聚合。您的查询也可能运行得更快一些！

示例：

WITH cte AS( 
  SELECT
  sale_order_line.product_id,
  stock_inventory_line.product_code,
  stock_inventory_line.product_code AS Sku,
  COUNT(sale_order_line.name) AS Qty_Sold,
  stock_inventory_line.product_qty AS Current_Qty,
  (stock_inventory_line.product_qty / Qty_Sold) AS NOM
  FROM
  sale_order_line,
  stock_inventory_line)

SELECT Sku, Qty_Sold, Current_Qty, NOM
FROM 
cte,
product_product,
product_template,
product_category

WHERE
  cte.product_id = product_product.id AND
  product_product.product_tmpl_id = product_template.id AND
  product_template.categ_id = product_category.id AND
  product_product.default_code = cte.product_code
GROUP BY
  Sku,
  Current_Qty,
  NOM;