主页 / dba / 问题 / 254049
Accepted
Core
Asked: 2019-11-25 23:51:37 +0800 CST

查询速度提升（期初库存、运行库存和期末库存）

  • 772

我有一张表格（请参阅下面的结构），我可以在其中跟踪所有库存进出。现在我想从这个表中生成一个报告，根据提供的日期范围查找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);

有什么可以增强查询的吗？

performance firebird

2 个回答

  1. 据我所知，您可以将其重写为每个时间范围（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。

    • 2
  2. Best Answer

    在这种情况下，(item_id, ref_type, entry_date) 上的索引是强制性的。这将避免通过以下方式为组创建临时表：

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

相关问题