CTE Oracle Merge语句

Question

我正试图创建一个CTE在Oracle的合并语句中使用相同的，但面对错误，所以请看看并帮助我，早些时候我们使用子查询而不是CTE，但为了提高查询的响应时间，我正在尝试CTE，所以请建议我另一种方法来提高查询的响应时间。很想知道oracle是否支持CTE和Merge语句，就像我在下面的代码中做的那样。

With TRANS_HIST 
As 
(Select 
NUMERO_DE_CUENTA,
TRANS_DATETIME,
Lag(TRANS_DATETIME, 1) 
over 
(
ORDER BY NUMERO_DE_CUENTA,TRANS_DATETIME) lag_trans_datetime 
FROM db_fraud_bpd.tbl_event_new_transaction_h
) 
MERGE 
INTO DB_FRAUD_BPD.TBL_RT_FEATURES_TEMP t1
USING 
(
SELECT 
RTTEMP.ACCOUNT_NUMBER,
RTTEMP.TRANS_DATETIME,
CASE WHEN STDDEV(TRANS_HIST.TRANS_DATETIME - TRANS_HIST.lag_trans_datetime) = 0 THEN NULL 
WHEN ROUND(( ( (RTTEMP.TRANS_DATETIME - Max(TRANS_HIST.TRANS_DATETIME)) - Avg(TRANS_HIST.TRANS_DATETIME - TRANS_HIST.lag_trans_datetime)) / STDDEV(TRANS_HIST.TRANS_DATETIME -TRANS_HIST.lag_trans_datetime)),3) >999999999999999 THEN 999999999999999 
WHEN ROUND(( ( (RTTEMP.TRANS_DATETIME - Max(TRANS_HIST.TRANS_DATETIME)) - Avg(TRANS_HIST.TRANS_DATETIME - TRANS_HIST.lag_trans_datetime)) / STDDEV(TRANS_HIST.TRANS_DATETIME -TRANS_HIST.lag_trans_datetime)),3) <-99999999999999 THEN -99999999999999 
ELSE ROUND(( ( (RTTEMP.TRANS_DATETIME - Max(TRANS_HIST.TRANS_DATETIME)) - Avg(TRANS_HIST.TRANS_DATETIME - TRANS_HIST.lag_trans_datetime)) / STDDEV(TRANS_HIST.TRANS_DATETIME -TRANS_HIST.lag_trans_datetime)),3) 
END AS TIME_DELTA_ZSCORE_PAST_90_DAYS 
FROM  TRANS_HIST
right outer join 
db_fraud_bpd.tbl_rt_features_temp RTTEMP 
ON Cast(RTTEMP.account_number AS INTEGER) = Cast(TRANS_HIST.NUMERO_DE_CUENTA AS INTEGER) 
WHERE 
(
TRANS_HIST.TRANS_DATETIME      <   RTTEMP.TRANS_DATETIME 
AND TRANS_HIST.TRANS_DATETIME  >= (RTTEMP.TRANS_DATETIME-90)
) 
or  TRANS_HIST.TRANS_DATETIME IS NULL 
GROUP BY 
RTTEMP.account_number,
RTTEMP.TRANS_DATETIME
)TEMP 
ON (t1.TRANS_DATETIME=TEMP.TRANS_DATETIME AND t1.ACCOUNT_NUMBER=TEMP.ACCOUNT_NUMBER) 
WHEN MATCHED 
THEN UPDATE 
SET t1.TIME_DELTA_ZSCORE_PAST_90_DAYS = TEMP.TIME_DELTA_ZSCORE_PAST_90_DAYS

Answer 1

您可以在merge语句中使用CTE，但在合并子查询的右侧位置。

见下面的例子

merge into tab
using (with t as (
select 1 id, 'x' x from dual union all
select 2 id, 'y' x from dual)
select * from t) b
on (tab.id = b.id)
when matched then
update set tab.x = b.x
when not matched then
insert (id, x)
values (b.id, b.x);

Answer 2

不要将TRANS_HIST用作CTE，而是用作子查询。

MERGE INTO DB_FRAUD_BPD.TBL_RT_FEATURES_TEMP t1
USING (  SELECT RTTEMP.ACCOUNT_NUMBER,
RTTEMP.TRANS_DATETIME,
CASE
WHEN STDDEV (
TRANS_HIST.TRANS_DATETIME
- TRANS_HIST.lag_trans_datetime) =
0
THEN
NULL
WHEN ROUND (
(  (  (  RTTEMP.TRANS_DATETIME
- MAX (TRANS_HIST.TRANS_DATETIME))
- AVG (
TRANS_HIST.TRANS_DATETIME
- TRANS_HIST.lag_trans_datetime))
/ STDDEV (
TRANS_HIST.TRANS_DATETIME
- TRANS_HIST.lag_trans_datetime)),
3) >
999999999999999
THEN
999999999999999
WHEN ROUND (
(  (  (  RTTEMP.TRANS_DATETIME
- MAX (TRANS_HIST.TRANS_DATETIME))
- AVG (
TRANS_HIST.TRANS_DATETIME
- TRANS_HIST.lag_trans_datetime))
/ STDDEV (
TRANS_HIST.TRANS_DATETIME
- TRANS_HIST.lag_trans_datetime)),
3) <
-99999999999999
THEN
-99999999999999
ELSE
ROUND (
(  (  (  RTTEMP.TRANS_DATETIME
- MAX (TRANS_HIST.TRANS_DATETIME))
- AVG (
TRANS_HIST.TRANS_DATETIME
- TRANS_HIST.lag_trans_datetime))
/ STDDEV (
TRANS_HIST.TRANS_DATETIME
- TRANS_HIST.lag_trans_datetime)),
3)
END AS TIME_DELTA_ZSCORE_PAST_90_DAYS
FROM (SELECT NUMERO_DE_CUENTA,
TRANS_DATETIME,
LAG (TRANS_DATETIME, 1)
OVER (
ORDER BY NUMERO_DE_CUENTA, TRANS_DATETIME) lag_trans_datetime
FROM db_fraud_bpd.tbl_event_new_transaction_h)
TRANS_HIST
RIGHT OUTER JOIN db_fraud_bpd.tbl_rt_features_temp RTTEMP
ON CAST (RTTEMP.account_number AS INTEGER) =
CAST (TRANS_HIST.NUMERO_DE_CUENTA AS INTEGER)
WHERE    (    TRANS_HIST.TRANS_DATETIME < RTTEMP.TRANS_DATETIME
AND TRANS_HIST.TRANS_DATETIME >=
(RTTEMP.TRANS_DATETIME - 90))
OR TRANS_HIST.TRANS_DATETIME IS NULL
GROUP BY RTTEMP.account_number, RTTEMP.TRANS_DATETIME) TEMP
ON (    t1.TRANS_DATETIME = TEMP.TRANS_DATETIME
AND t1.ACCOUNT_NUMBER = TEMP.ACCOUNT_NUMBER)
WHEN MATCHED
THEN
UPDATE SET
t1.TIME_DELTA_ZSCORE_PAST_90_DAYS = TEMP.TIME_DELTA_ZSCORE_PAST_90_DAYS