All Forums Database
KVB 124 posts Joined 09/12
10 Dec 2013
Rewriting the Self join

MERGE_ID is the primary index in  CUST_HIST
SELECT * FROM
(SEL A.MERGE_ID,A.REM_ID, A.EFF_DT, A.FIRST_MERGE_DT , A.CURR_CUST_MERGE_DT FROM
CUST_HIST A
INNER JOIN
CUST_HIST B ON A.REM_ID=B.MERGE_ID
UNION ALL
SEL B.MERGE_ID,B.REM_ID, B.EFF_DT, B.FIRST_MERGE_DT , B.CURR_CUST_MERGE_DT FROM
CUST_HIST A
INNER JOIN
CUST_HIST B ON A.REM_ID=B.MERGE_ID )A
GROUP BY 1,2,3,4,5
 
Is there any other better way to rewrite this.Like duplicating the table with REM_ID as PI.Or else this is fine?
Regards
Bikky

Raja_KT 1246 posts Joined 07/09
10 Dec 2013

Hi Bikky,
My thought is that the first select  within the derive is enough.Do I miss anything here, due to slight astigmatism :) ? Why don't your see the explain and validate the data too?
Cheers,
Raja
 

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

ulrich 816 posts Joined 09/09
10 Dec 2013

 

hm, try and check the differences in explain and run times

 

select A.MERGE_ID,
       A.REM_ID, 
       A.EFF_DT, 
       A.FIRST_MERGE_DT , 
       A.CURR_CUST_MERGE_DT 
FROM CUST_HIST A
where exists ( select * 
               from CUST_HIST B
               where A.REM_ID=B.MERGE_ID
             ) 
UNION -- this would make the group by obsolate
select A.MERGE_ID,
       A.REM_ID, 
       A.EFF_DT, 
       A.FIRST_MERGE_DT , 
       A.CURR_CUST_MERGE_DT 
FROM CUST_HIST A
where exists ( select * 
               from CUST_HIST B
               where B.REM_ID=A.MERGE_ID
             ) 

 

 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

ulrich 816 posts Joined 09/09
10 Dec 2013

This should create also the same output an would do the self join only once but comes with a smal prod join

SELECT
	case when c.id = 1 then A.MERGE_ID else B.REM_ID end,
	case when c.id = 1 then A.REM_ID else B.REM_ID end, 
	case when c.id = 1 then A.EFF_DT else B.EFF_DT end, 
	case when c.id = 1 then A.FIRST_MERGE_DT else B.FIRST_MERGE_DT end , 
	case when c.id = 1 then A.CURR_CUST_MERGE_DT else b.CURR_CUST_MERGE_DT end 
FROM CUST_HIST A
     INNER JOIN
     CUST_HIST B 
     	ON A.REM_ID=B.MERGE_ID
     CROSS JOIN 
     (select calendar_date - current_date as id from sys_calendar.calendar where id in (1,2)) as c
group by 1,2,3,4,5

 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

ih 1 post Joined 07/15
09 Jul 2015

Hi all,
https://www.periscope.io/blog/use-self-joins-to-calculate-your-retention-churn-and-reactivation-metrics.html
is there a terada sql assistant equivalent to this query as I really need it to calculate the churn and re-engagement. I would be very grateful for guidance as I am new to teradata. I am not able to show my query since it contains confidential data, but there are a few sub-queries based on multiple joins.
Many thanks in advance

You must sign in to leave a comment.