如何優化我們使用事務板的查詢？

我需要根據事務板生成老化報告，即間隔 2、5、10 等。用戶將選擇報告開始日期和結束日期，然后我們需要為該特定日期范圍生成報告。報告的方式應該是將交易分成幾塊；例如 0-2、3-4、5-6、6-8 和大于 8 天。我為每個平板寫了一個子查詢，它給了我所需的資料。但是由于資料每天都在增加，因此花費了太多時間。我也嘗試過索引，但它并沒有太大提高性能。

select id_ledger as ledger_id,
       customer_id,
       title     as ledger,
       (
           select COALESCE((sum(dr_amount) - sum(clearance_amount)), 0) as diff_value
           from journal_voucher_details
           where cr_amount = 0
             and (('2021-10-23' - transaction_date) <= (2))
             and ledger_id = l.customer_id
             and transaction_date >= '2021-10-01'
             and transaction_date <= '2021-10-24'
           group by ledger_id
       )         as slab1,
       (
           select COALESCE((sum(dr_amount) - sum(clearance_amount)), 0) as diff_value
           from journal_voucher_details
           where cr_amount = 0
             and (('2021-10-23' - transaction_date) <= (2 * 2))
             and (('2021-10-23' - transaction_date) > (2 * 1))
             and ledger_id = l.customer_id
             and transaction_date >= '2021-10-01'
             and transaction_date <= '2021-10-24'
           group by ledger_id
       )         as slab2,
       (
           select COALESCE((sum(dr_amount) - sum(clearance_amount)), 0) as diff_value
           from journal_voucher_details
           where cr_amount = 0
             and (('2021-10-23' - transaction_date) <= (2 * 3))
             and (('2021-10-23' - transaction_date) > (2 * 2))
             and ledger_id = l.customer_id
             and transaction_date >= '2021-10-01'
             and transaction_date <= '2021-10-24'
           group by ledger_id
       )         as slab3,
       (
           select COALESCE((sum(dr_amount) - sum(clearance_amount)), 0) as diff_value
           from journal_voucher_details
           where cr_amount = 0
             and (('2021-10-23' - transaction_date) <= (2 * 4))
             and (('2021-10-23' - transaction_date) > (2 * 3))
             and ledger_id = l.customer_id
             and transaction_date >= '2021-10-01'
             and transaction_date <= '2021-10-24'
           group by ledger_id
       )         as slab4,
       (
           select COALESCE((sum(dr_amount) - sum(clearance_amount)), 0) as diff_value
           from journal_voucher_details
           where cr_amount = 0
             and (('2021-10-23' - transaction_date) > (2 * 4))
             and ledger_id = l.customer_id
             and transaction_date >= '2021-10-01'
             and transaction_date <= '2021-10-24'
           group by ledger_id
       )         as slab5,
       (
           select COALESCE((sum(dr_amount) - sum(clearance_amount)), 0) as diff_value
           from journal_voucher_details
           where cr_amount = 0
             and 1 = 1
             and ledger_id = l.customer_id
             and transaction_date >= '2021-10-01'
             and transaction_date <= '2021-10-24'
           group by ledger_id
       )         as balance
from ledgers l;

注意：2021-10-1 ==> 開始日期

2021-10-24 ==> 結束日期

2021-10-23 ==> 報告日期

并且選擇2作為slab編號。

如何提高此查詢的性能？

提前致謝。

uj5u.com熱心網友回復：

你可以試試這個，應該更快：

select id_ledger as ledger_id, customer_id, title as ledger,
     , COALESCE( sum(dr_amount) FILTER (WHERE ('2021-10-23' - transaction_date) <= (2)) OVER () 
                 - sum(clearance_amount) FILTER (WHERE ('2021-10-23' - transaction_date) <= (2)) OVER ()
               , 0
               ) as slab1
     , COALESCE(sum(dr_amount) FILTER (where ('2021-10-23' - transaction_date) <= (2 * 2) and ('2021-10-23' - transaction_date) > (2 * 1)) OVER () 
                - sum(clearance_amount) FILTER (where ('2021-10-23' - transaction_date) <= (2 * 2) and ('2021-10-23' - transaction_date) > (2 * 1)) OVER () 
               , 0
               ) as slab2
     , COALESCE(sum(dr_amount) FILTER (where ('2021-10-23' - transaction_date) <= (2 * 3) and ('2021-10-23' - transaction_date) > (2 * 2)) OVER () 
                 - sum(clearance_amount) FILTER (where ('2021-10-23' - transaction_date) <= (2 * 3) and ('2021-10-23' - transaction_date) > (2 * 2)) OVER () 
               , 0
               ) as slab3
     , COALESCE(sum(dr_amount) FILTER (where ('2021-10-23' - transaction_date) <= (2 * 4) and ('2021-10-23' - transaction_date) > (2 * 3)) OVER () 
                 - sum(clearance_amount) FILTER (where ('2021-10-23' - transaction_date) <= (2 * 4) and ('2021-10-23' - transaction_date) > (2 * 3)) OVER () 
               , 0
               ) as slab4
     , COALESCE(sum(dr_amount) FILTER (where ('2021-10-23' - transaction_date) > (2 * 4)) OVER ()
                - sum(clearance_amount) FILTER (where ('2021-10-23' - transaction_date) > (2 * 4)) OVER ()
               , 0
               ) as slab5
     , COALESCE(sum(dr_amount) - sum(clearance_amount), 0) AS balance
from ledgers l
inner join journal_voucher_details j
on j.ledger_id = l.customer_id
where cr_amount = 0
and transaction_date >= '2021-10-01'
and transaction_date <= '2021-10-24'
group by j.ledger_id

標籤：sql PostgreSQL 查询优化

上一篇：如何復制與另一列相關的列

下一篇：結合SQLFULLJOINS