如何在报告非常慢的情况下优化postgreql。我正在寻找一种减少子查询的方法。我有多个子查询执行相同的计算，但参数间隔不同。例如间隔"第271天"、间隔"第365天"。目前，每个间隔的每个cal都是在一个单独的子查询中完成的，我想用另一种方式实现这一点，并避免子查询，因为我意识到它们会使报告生成非常缓慢。
SELECT
merchandiseCode,
merchandiseDescription,
merchandiseCategory_ID,
merchandiseCategory,
Case OnHand WHEN 0 THEN 0 ELSE CurrentCost*OnHand END AS CurrentCost,
CASE WHEN qty5>0 OR qty4>0 OR qty3>0 OR qty2>0 THEN stock1 ELSE CASE WHEN
qty1 >0 THEN qty1 ELSE 0 END END as qty1,
CASE WHEN qty5>0 OR qty4>0 OR qty3>0 THEN stock2 ELSE CASE WHEN qty2>0
THEN qty2 ELSE 0 END END as qty2,
CASE WHEN qty5>0 OR qty4>0 THEN stock3 ELSE CASE WHEN qty3 >0 THEN qty3
ELSE 0 END END AS qty3,
CASE WHEN qty5>0 THEN Stock4 ELSE CASE WHEN qty4> 0 THEN qty4 ELSE 0 END
END AS qty4,
CASE WHEN qty5>0 THEN qty5 ELSE 0 END As qty5,
stock1,
stock2,
stock3,
stock4,
OnHand,
CASE WHEN qty5>0 OR qty4>0 OR qty3>0 OR qty2>0 THEN round(stock1*
(cost1),2) ELSE CASE WHEN qty1 >0 THEN round(qty1*(cost1),2) ELSE 0 END
END as value1,
CASE WHEN qty5>0 OR qty4>0 OR qty3>0 THEN round(stock2*(cost2),2) ELSE
CASE WHEN qty2>0 THEN round(qty2*(cost2),2) ELSE 0 END END as value2,
CASE WHEN qty5>0 OR qty4>0 THEN round(Stock3*(Cost3),2) ELSE CASE WHEN
qty3 >0 THEN round(qty3*(cost3),2) ELSE 0 END END as value3,
CASE WHEN qty5>0 THEN round(Stock4*(Cost4),2) ELSE CASE WHEN qty4>0 THEN
round(qty4*(Cost4),2) ELSE 0 END END as value4,
CASE WHEN qty5>0 THEN round(qty5*(cost4),2) ELSE 0 END as value5
from (
SELECT
prod.value as merchandiseCode,
prod.description as merchandiseDescription,
prod.merchandiseCategory_ID as merchandiseCategory_ID,
prodcat.name AS merchandiseCategory,
(SELECT COALESCE((cos.NewCostPrice),0)
FROM M_CostM cos WHERE cos.Created = (SELECT MAX(c.Created)
FROM M_CostM c
LEFT JOIN M_CostElement ce on ce.M_CostElement_UU='bf6ee9b8-4600-480b-
b415-f94c698b0648'
WHERE
prod.M_Product_ID = c.M_Product_ID AND prod.ProductType !='A'
AND c.created <= ('2020-02-17'::date)
AND c.Customer_ID =1000008 AND c.C_AcctSchema_ID = 1000009
AND c.M_CostElement_ID=ce.M_CostElement_ID
AND c.rg_ID = CASE WHEN c.AD_Org_ID =0 Then c.AD_Org_ID ELSE 1000002
END
)) as CurrentCost,
(SELECT COALESCE(SUM(t.movementqty),0) FROM M_merchandise t WHERE
t.M_Product_ID=1013449 AND t.Customer_ID =1000008
AND t.movementdate > (('2020-02-17'::date)-interval 'day 90')
AND t.movementdate <('2020-02-17'::date)
AND t.rg_ID =1000044
AND t.movementtype IN('I+','M+','P+','V+','W+')) AS stock1, ----stock
exist from previous brackets
(SELECT (SELECT SUM(t.movementqty) FROM M_merchandise t WHERE
t.M_Product_ID=1013449
AND t.Customer_ID =1000008
AND t.movementtype IN('I+','M+','P+','V+','W+')
AND t.movementdate<('2020-02-17'::date)
AND t.rg_ID =1000044 ) +
(SELECT SUM(t.movementqty) FROM M_merchandise t WHERE
t.M_Product_ID=1013449
AND t.Customer_ID =1000008
AND t.movementtype IN('I-','C-','M-','P-','V-','W-')
AND t.movementdate<('2020-02-17'::date)
AND t.rg_ID = 1000044)) AS qty1,
(SELECT COALESCE(SUM(t.movementqty),0) FROM M_merchandise t WHERE
t.M_Product_ID=1013449 AND t.Customer_ID =1000008
AND t.movementdate > (('2020-02-17'::date)-interval 'day 180')
AND t.movementdate <= (('2020-02-17'::date)-interval 'day 91')
AND t.rg_ID =1000044
AND t.movementtype IN('I+','M+','P+','V+','W+')) AS stock2, ----stock
exist from previous brackets
(SELECT (SELECT SUM(t.movementqty) FROM M_merchandise t WHERE
t.M_Product_ID=1013449
AND t.Customer_ID =1000008
AND t.movementtype IN('I+','M+','P+','V+','W+')
AND t.movementdate<(('2020-02-17'::date)- interval 'day 91')
AND t.rg_ID =1000044 ) +
(SELECT SUM(t.movementqty) FROM M_merchandise t WHERE
t.M_Product_ID=1013449
AND t.Customer_ID =1000008
AND t.movementtype IN('I-','C-','M-','P-','V-','W-')
AND t.movementdate<('2020-02-17'::date)
AND t.rg_ID = 1000044)) AS qty2,
(SELECT COALESCE(SUM(t.movementqty),0) FROM M_merchandise t WHERE
t.M_Product_ID=1013449 AND t.Customer_ID =1000008
AND t.movementdate > (('2020-02-17'::date)-interval 'day 181')
AND t.movementdate <= (('2020-02-17'::date)-interval 'day 270')
AND t.rg_ID =1000002
AND t.movementtype IN('I+','M+','P+','V+','W+')) AS stock3, ----stock
exist from previous brackets
(SELECT (SELECT SUM(t.movementqty) FROM M_merchandise t WHERE
t.M_Product_ID=1013449
AND t.Customer_ID =1000008
AND t.movementtype IN('I+','M+','P+','V+','W+')
AND t.movementdate<(('2020-02-17'::date)- interval 'day 181')
AND t.rg_ID =1000044) +
(SELECT SUM(t.movementqty) FROM M_merchandise t WHERE
t.M_Product_ID=1013449
AND t.Customer_ID =1000008
AND t.movementtype IN('I-','C-','M-','P-','V-','W-')
AND t.movementdate<('2020-02-17'::date)
AND t.rg_ID = 1000044)) AS qty3,
(SELECT COALESCE(SUM(t.movementqty),0) FROM M_merchandise t WHERE
t.M_Product_ID=1013449 AND t.Customer_ID =1000008
AND t.movementdate > (('2020-02-17'::date)-interval 'day 271') AND
t.movementdate > (('2020-02-17'::date)-interval 'day 365')
AND t.rg_ID =1000044
AND t.movementtype IN('I+','M+','P+','V+','W+')) AS stock4,
(SELECT (SELECT SUM(t.movementqty) FROM M_merchandise t WHERE
t.M_Product_ID=1013449
AND t.Customer_ID=1000008
AND t.movementtype IN('I+','M+','P+','V+','W+')
AND t.movementdate<(('2020-02-17'::date)- interval 'day 271')
AND t.rg_ID =1000044 ) +
(SELECT SUM(t.movementqty) FROM M_merchandise t WHERE
t.M_Product_ID=1013449
AND t.Customer_ID =1000008
AND t.movementtype IN('I-','C-','M-','P-','V-','W-')
AND t.movementdate<('2020-02-17'::date)
AND t.rg_ID = 1000002)) AS qty4, -----no previous stock in previous
brackets
(SELECT (SELECT SUM(t.movementqty) FROM M_merchandise t WHERE
t.M_Product_ID=1013449
AND t.Customer_ID =1000008
AND t.movementtype IN('I+','M+','P+','V+','W+')
AND t.movementdate<(('2020-02-17'::date)-interval 'day 366')
AND t.rg_ID =1000044 ) +
(SELECT SUM(t.movementqty) FROM M_merchandise t WHERE
t.M_Product_ID=1013449
AND t.Customer_ID =1000008
AND t.movementtype IN('I-','C-','M-','P-','V-','W-')
AND t.movementdate<('2020-02-17'::date)
AND t.rg_ID = 1000044 ) ) AS qty5,
(SELECT COALESCE((cos.NewCostPrice),0)
FROM history cos
LEFT JOIN M_CostElement ce on ce.M_CostElement_UU='bf6ee9b8-4600-480b-
b415-
f94c698b0648'
LEFT JOIN m_costdetail md ON md.m_costdetail_id=cos.m_costdetail_id
WHERE prod.M_Product_ID = md.M_Product_ID AND prod.ProductType !='A'
AND cos.Customer_ID =1000008 AND md.Schema_ID = 1000004
AND cos.created >= (('2020-02-17'::date) - interval '3 month' )
AND cos.created < ('2020-02-17'::date)
AND cos.M_CostElement_ID=ce.M_CostElement_ID
AND cos.rg_ID = CASE WHEN cos.rg_ID =0 Then cos.rg_ID ELSE
1000002 END
ORDER BY cos.created DESC limit 1
)as Cost1,
(SELECT COALESCE((cos.NewCostPrice),0)
FROM history cos
LEFT JOIN M_CostElement ce on ce.M_CostElement_UU='bf6ee9b8-4600-480b-
b415-
f94c698b0648'
LEFT JOIN m_costdetail md ON md.m_costdetail_id=cos.m_costdetail_id
WHERE prod.M_Product_ID = md.M_Product_ID AND prod.ProductType !='A'
AND cos.Customer_ID =1000008 AND md.Schema_ID= 1000004
AND cos.created < (('2020-02-17'::date) - interval '3 month')
AND cos.created < (('2020-02-17'::date) - interval '6 month')
AND cos.M_CostElement_ID=ce.M_CostElement_ID
AND cos.rg_ID = CASE WHEN cos.rg_ID =0 Then cos.rg_ID ELSE
1000002 END
ORDER BY cos.created DESC limit 1
)as Cost2,
(SELECT COALESCE((cos.NewCostPrice),0)
FROM history cos
LEFT JOIN M_CostElement ce on ce.M_CostElement_UU='bf6ee9b8-4600-480b-
b415-
f94c698b0648'
LEFT JOIN m_costdetail md ON md.m_costdetail_id=cos.m_costdetail_id
WHERE prod.M_Product_ID = md.M_Product_ID AND prod.ProductType !='A'
AND cos.Customer_ID =1000008 AND md.Schema_ID = 1000004
AND cos.created >= (('2020-02-17'::date) - interval '12 month' )
AND cos.created < (('2020-02-17'::date) - interval '6 month')
AND cos.M_CostElement_ID=ce.M_CostElement_ID
AND cos.rg_ID = CASE WHEN cos.rg_ID =0 Then cos.rg_ID ELSE
1000002 END
ORDER BY cos.created DESC limit 1
)as Cost3,
(SELECT COALESCE((cos.NewCostPrice),0)
FROM history cos
LEFT JOIN M_CostElement ce on ce.M_CostElement_UU='bf6ee9b8-4600-480b-b415-
f94c698b0648'
LEFT JOIN m_costdetail md ON md.m_costdetail_id=cos.m_costdetail_id
WHERE prod.M_Product_ID = md.M_Product_ID AND prod.ProductType !='A'
AND cos.Customer_ID =1000008 AND md.Schema_ID = 1000004
AND cos.created < (('2020-02-17'::date)- interval '12 month')
AND cos.M_CostElement_ID=ce.M_CostElement_ID
AND cos.rg_ID = CASE WHEN cos.rg_ID =0 Then cos.rg_ID ELSE
1000002 END
ORDER BY cos.created DESC limit 1
) as Cost4,
(SELECT COALESCE (SUM (st.qtyonhand),0)FROM M_Storage st
WHERE prod.M_Product_ID = st.M_Product_ID
AND st.rg_ID = 1000044 AND prod.ProductType !='A'
AND st.DateMaterialPolicy <= ( '2020-02-17'::date)
) as OnHand
(SELECT COALESCE((cos.NewCostPrice),0)
FROM m_costhistory cos
LEFT JOIN CostElem ce on ce.M_CostElem_UU='bf6ee9b8-4600-480b-b415-
f94c698b0648'
LEFT JOIN costd md ON md.m_costd_id=cos.m_c
你的摘录在我看来语法不正确。请在更大的上下文中显示。
通常，您可以采用通用条件并将其放入查询本身的WHERE子句中，也可以采用特殊条件并将它们放入FILTER的WHERE子句。
所以你可以有这样的东西：
select
SUM(t.merchandiseqty) FILTER
(where t.merchandisetype IN('I+','M+','P+','V+','W+') AND t.merchandisedate<('2020-02-17'::date- interval 'day 271')) as whatever1,
SUM(t.merchandiseqty) FILTER
(where t.merchandisetype IN ('I-','C-','M-','P-','V-','W-') AND t.merchandisedate<('2020-02-17'::date)) as qty1
FROM t WHERE t.M_merchandise_ID=1013449 AND t.customer_ID =1000008
由于你的例子不完整，我不得不凭直觉来填补空白。
将限制性最小的商品条件从FILTER移到主WHERE也可能很有帮助。