Expensive self join

Database

Expensive self join

Hi

I have a CATEGORY table with data like the below:

ACC_ID  COUNTRY_ID ACC_CATEGORY

101         USA                  NEW

105         DE                    OLD

106          BE                  RECENT

...........................................................

...................................................

AND a REVENUE table with the revenue against each account

ACC_ID  COUNTRY_ID   REVENUE   DATE_OF_TRADE

101             USA              501.30        5/17/2014

I have a requirement to calculate the revenue based on account category for a particular week (Have a calendar table to identify the week based on the date of trade)

So the end result should be:

COUNTRY_ID    WEEK_ID   REVENUE_OLD   REVENUE_NEW   REVENUE_RECENT

Please let me know the fastest means to achieve this.I am currently using a self join to separate the accounts as OLD/NEW/RECENT.This is turning out to be very expensive as my revenue fact table and ACCOUNTS table are quite huge.

Please note that my account category can only be either of NEW/OLD/RECENT

This is what I am trying to do:

SELECT ACC_ID,WEEK_ID,SUM(NEW.REVENUE) AS REVENUE_NEW,SUM(OLD.REVENUE) as REVENUE_OLD,......

REVENUE  inner join ACCOUNT NEW

ON ACCOUNT.ACC_ID=REVENUE.ACC_ID and ACCOUNT.ACCOUNT_CAT='NEW'

inner join ACCOUNT OLD

ON ACCOUNT.ACC_ID=REVENUE.ACC_ID AND ACCOUNT.ACCOUNT_CAT='OLD'

JOIN

CALENDAR On CALENDAR.DATE_DT=REVEUE.DT_OF_TRADE

2 REPLIES
Teradata Employee

Re: Expensive self join

SELECT REVENUE.ACC_ID, CALENDAR.WEEK_ID,

SUM(CASE WHEN ACCOUNT.ACCOUNT_CAT='NEW' THEN REVENUE.REVENUE END) AS REVENUE_NEW,

SUM(CASE WHEN ACCOUNT.ACCOUNT_CAT='OLD' THEN REVENUE.REVENUE END) AS REVENUE_OLD, ...

FROM REVENUE INNER JOIN ACCOUNT ON ACCOUNT.ACC_ID = REVENUE.ACC_ID

JOIN CALENDAR ON CALENDAR.DATE_DT = REVENUE.DT_OF_TRADE

GROUP BY 1, 2

Re: Expensive self join

Thanks so much Fred!!!!!