如何在此查詢中使用 distinct on 來避免聚合列？

我想做的是我想計算公式的數量inventory+該庫存receive_order_entries.quantity的所有總和inventory_transactions.quantity

我目前的方法如下所示，但在分組和聚合方面存在問題

如何避免對我在此查詢中選擇的每一列使用聚合或分組依據？

我知道該列應該被聚合或分組，因為inventory有很多inventory transactions

我已經從 postgres sql 中讀到過distinct on，但我不知道如何使用它我試圖在DISTINCT ON (inventories.id,receive_order_entries.product_id)之後添加，select但它仍然給出相同的錯誤消息

the error messages

ERROR:  column "products.id" must appear in the GROUP BY clause or be used in an aggregate function

my db schema from rails

# in rails every table automatically
has autoincrement bigint id for primary key, and datetime for created_at

create_table "receive_order_entries", force: :cascade do |t|
 t.bigint "product_id", null: false
 t.datetime "expiry_date"
end

create_table "products", force: :cascade do |t|
 t.string "nama", default: "", null: false
end

create_table "inventories", force: :cascade do |t|
 t.bigint "receive_order_entry_id", null: false
 t.decimal "harga_jual", default: "0.0", null: false
end

create_table "inventory_transactions", force: :cascade do |t|
 t.bigint "inventory_id", null: false
 t.string "type"
 t.integer "quantity", default: 0, null: false
end

my current sql syntax

select inventories.id, products.id as product_id, receive_order_entries.id as receive_order_entry_id,
products.nama,
receive_order_entries.expiry_date,
inventories.harga_jual,
sum(receive_order_entries.quantity + 
  COALESCE((SELECT SUM(case 
       when inventory_transactions.type = 'DecrementInventoryTransaction' then -(inventory_transactions.quantity)
       when inventory_transactions.type = 'IncrementInventoryTransaction' then inventory_transactions.quantity
       else 0 end
   ) 
       FROM inventory_transactions
       WHERE inventory_transactions.inventory_id = inventories.id
       and not inventory_transactions.approved_at is NULL
       and not inventory_transactions.approved_by_id is NULL),0)
  ) as qty
from inventories 
inner join receive_order_entries 
on inventories.id = receive_order_entries.id 
inner join products
on receive_order_entries.product_id = products.id 
group by inventories.id, products.id, receive_order_entries.id

填寫問題中的一些不一致之處，應該這樣做：

SELECT i.id, r.product_id, i.receive_order_entry_id, p.nama, r.expiry_date, i.harga_jual
    , r.quantity + it.qty AS qty
FROM   inventories i
JOIN   receive_order_entries r ON r.id = i.receive_order_entry_id
JOIN   products              p ON p.id = r.product_id
LEFT   JOIN (
  SELECT inventory_id
       , COALESCE(sum(quantity) FILTER (WHERE type = 'IncrementInventoryTransaction'), 0)
       - COALESCE(sum(quantity) FILTER (WHERE type = 'DecrementInventoryTransaction'), 0) AS qty
  FROM   inventory_transactions
  WHERE  approved_at IS NOT NULL
  AND    approved_by_id IS NOT NULL
  GROUP  BY 1
  ) it ON it.inventory_id = i.id;

inventory_transactions關鍵是在加入子查詢之前從子查詢中聚合數量。這也是處理整個或大部分錶格時最快的方式。對於少量選擇，請改用LATERAL子查詢。看：

• 將多對多連接的右側轉換為數組

關於聚合FILTER子句：

• 在單個 SELECT 語句中返回多個範圍的計數

引用自：https://dba.stackexchange.com/questions/307024