查詢速度提升（期初庫存、執行庫存和期末庫存）

我有一張表格（請參閱下面的結構），我可以在其中跟踪所有庫存進出。現在我想從這個表中生成一個報告，根據提供的日期範圍查找opening_stock、purchased、purchased_return、sales、sales_returned、damaged_inventory 。

詢問：

SELECT ITEM_ID,  

(SELECT (SUM(PURCHASE_QUANTITY) - SUM(SALE_QUANTITY)) FROM INV_ITEM_LEDGER WHERE ENTRY_DATE < '2019-09-31' AND ITEM_ID = l.ITEM_ID) AS OPENING
, (SELECT COALESCE(SUM(PURCHASE_QUANTITY), 0) FROM INV_ITEM_LEDGER WHERE ENTRY_DATE >= '2019-09-31' AND ITEM_ID = l.ITEM_ID AND REF_TYPE = 'PURCHASE') AS PURCHASE
, (SELECT COALESCE(SUM(SALE_QUANTITY), 0) FROM INV_ITEM_LEDGER WHERE ENTRY_DATE >= '2019-09-31' AND ITEM_ID = l.ITEM_ID AND REF_TYPE = 'PURCHASE RETURN') AS PURCHASE_RETURN
, (SELECT COALESCE(SUM(SALE_QUANTITY),0) FROM INV_ITEM_LEDGER WHERE ENTRY_DATE >= '2019-09-31' AND ITEM_ID = l.ITEM_ID AND REF_TYPE = 'SALE') AS SALE
, (SELECT COALESCE(SUM(PURCHASE_QUANTITY), 0) FROM INV_ITEM_LEDGER WHERE ENTRY_DATE >= '2019-09-31' AND ITEM_ID = l.ITEM_ID AND REF_TYPE = 'SALE RETURN') AS SALE_RETURN
, (SELECT COALESCE(SUM(SALE_QUANTITY), 0) FROM INV_ITEM_LEDGER WHERE ENTRY_DATE >= '2019-09-31' AND ITEM_ID = l.ITEM_ID AND REF_TYPE = 'DMG') AS DAMAGED_INVENTORY
FROM INV_ITEM_LEDGER l
GROUP BY ITEM_ID

當我對47,619條記錄（目前查詢總數）執行此查詢時，系統掛起並停止響應。

表結構：

CREATE TABLE INV_ITEM_LEDGER (
 ITEM_LEDGER_ID     INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
 ITEM_ID            INTEGER,
 REF_ID             INTEGER,
 REF_TYPE           VARCHAR(20),
 PURCHASE_QUANTITY  DECIMAL(18,4),
 SALE_QUANTITY      DECIMAL(18,4),
 DESCRIPTION        VARCHAR(512),
 BATCH_NO           VARCHAR(20),
 EXPIRY             VARCHAR(20),
 STOCK_ID           INTEGER,
 BRANCH_CODE        VARCHAR(20),
 LAST_SYNCED        VARCHAR(20),
 ENTRY_DATE         VARCHAR(20),
 LAST_UPDATED       VARCHAR(20),
 ENTRY_DATE2        VARCHAR(20),
 /* Keys */
 PRIMARY KEY (ITEM_LEDGER_ID)
);

CREATE INDEX INV_ITEM_LEDGER_INDEX01
 ON INV_ITEM_LEDGER
 (ITEM_LEDGER_ID, ITEM_ID);

有什麼可以增強查詢的嗎？

在這種情況下，(item_id, ref_type, entry_date) 上的索引是強制性的。這將避免通過以下方式為組創建臨時表：

   CREATE INDEX INV_ITEM_LEDGER_INDEX02
   ON INV_ITEM_LEDGER
   (ITEM_ID, REF_TYPE, ENTRY_DATE);

據我所知，您可以將其重寫為每個時間範圍（2019-09-31 之前及之後）的條件聚合。通常，“普通”聚合比多個標量子查詢更快。

SELECT l.item_id, 
      l.opening, 
      x.purchase, 
      x.purchase_return,
      x.sale,
      x.sale_return,
      x.damaged_inventory
FROM (
 -- aggregate items before 2019-09-31
 SELECT item_id, 
        sum(purchase_quantity) - sum(sale_quantity) as opening, 
 FROM inv_item_ledger
 WHERE entry_date < '2019-09-31'
 GROUP BY ITEM_ID              
) l
 JOIN (
   -- calculate the conditional aggregates for rows after 2019-09-31
   SELECT item_id, 
          sum(CASE WHEN ref_type = 'PURCHASE' THEN purchase_quantity END) as purchase,
          sum(CASE WHEN ref_type = 'PURCHASE RETURN' THEN sale_quantity END) as purchase_return,
          sum(CASE WHEN ref_type = 'SALE' THEN sale_quantity END) as sale,
          sum(CASE WHEN ref_type = 'SALE RETURN' THEN purchase_quantity END) as sale_return,
          sum(CASE WHEN ref_type = 'DMG' THEN sale_quantity END) as damaged_inventory
   FROM inv_item_ledger
   WHERE entry_date >= '2019-09-31'
   GROUP BY ITEM_ID   
 ) x ON x.Item_id = l.item_id

這使用了聚合函式（如sum()）忽略 NULL 值的事實。ref_typesum() 函式中的 CASE 表達式僅在該行的 匹配時返回一個值，而對於所有其他行則返回 NULL。

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