CTE 中的“select count(1)”性能

鑑於以下 CTE：

with seekResults as
(
select DISTINCT
trx_id_fk,
account_FK, 
customer_cif,
customer_tin,
TIN_Type, 
ID_NUMBER ,
SEARCH_NUMBER
FROM transactionsOP 
) 

select trx_id_fk 
 ,case when account_FK is not null then (select count(1) from seekResults a where a.account_fk = s.account_fk and a.SEARCH_NUMBER= s.SEARCH_NUMBER) 
  when customer_cif is not null then (select count(1) from seekResults a where a.customer_cif = s.customer_cif and a.SEARCH_NUMBER= s.SEARCH_NUMBER) 
  when customer_tin is not null then (select count(1) from seekResults a where a.customer_tin = s.customer_tin and a.TIN_Type = s.TIN_Type and a.SEARCH_NUM = s.SEARCH_NUM) 
  when ID_NUMBER is not null then (select count(1) from seekResults a where a.ID_NUMBER = s.ID_NUMBER and a.SEARCH_NUMBER= s.SEARCH_NUMBER)
  else (select count(1) from seekResults a where a.SEARCH_NUMBER= s.SEARCH_NUMBER)
      end as 'transaction_period'
from seekResults s  ;

我注意到計算需要花費大量時間，因為它seekResults一遍又一遍地呼叫 CTE，主要是在select count(1) section：

select count(1) from seekResults  a 

有沒有更好的方法來改進那部分？到目前為止，我還沒有找到任何可以替換它的東西。

我認為您的查詢可以使用視窗聚合來編寫。我不知道它是否會更有效：

with seekResults as
(
 select distinct
   trx_id_fk,
   account_fk
   customer_cif,
   customer_tin,
   tin_type,
   id_number,
   search_number
 from transactionsOP 
) 
select 
 trx_id_fk,  
 case 
   when account_fk is not null then
     count(*) over (partition by search_number, account_fk) 
   when customer_cif is not null then
     count(*) over (partition by search_number, customer_cif) 
   when customer_tin is not null then
     count(*) over (partition by search_number, customer_tin, tin_type)
   when id_number is not null then
     count(*) over (partition by search_number, id_number)
   else 
     count(*) over (partition by search_number)
 end as transaction_period
from seekResults ;

您可以嘗試更改它以添加一個具有 windows 功能的 CTE，然後從中選擇。

with seekResults as
(
select DISTINCT
trx_id_fk,
account_FK,
customer_cif,
customer_tin,
ID_NUMBER ,
SEARCH_NUMBER
FROM transactionsOP 
) ,
seekResults2 as 
(
 select  trx_id_fk,
 account_FK,
customer_cif,
customer_tin,
ID_NUMBER ,
SEARCH_NUMBER,
count(1) over (partition by account_fk ,SEARCH_NUMBER) as count1,
count(1) over (partition by customer_cif ,SEARCH_NUMBER) as count2,
count(1) over (partition by customer_tin , TIN_Type, SEARCH_NUMBER) as count3, 
 count(1) over (partition by ID_NUMBER , SEARCH_NUMBER) as count4
from seekResults
)
SELECT  trx_id_fk 
 ,case when account_FK is not null then count1 .... 
FROM seekResults2

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