I am using Teradata 15.10. I have a table with two columns last_name and first_name. For each row, I need to count how many others rows have the same last_name and first_name, but with the values reversed, and display that count (even if it is equal to 0). For the moment, I am using this query :
SELECT LAST_NAME,
FIRST_NAME,
(SELECT Count(*)
FROM myTable AS T1
WHERE T1.FIRST_NAME = T2.LAST_NAME
AND T1.LAST_NAME = T2.FIRST_NAME) AS REVERSE_NAME_COUNT
FROM myTable AS T2
Unfortunately this is not very fast and I have a lot of others columns where I am doing this kind of count. I was wondering if there is a way to translate the scalar subquery above into an OLAP function like this :
SUM(CASE WHEN T1.FIRST_NAME = T2.LAST_NAME AND T1.LAST_NAME = T2.FIRST_NAME THEN 1 ELSE 0 END) OVER(ROWS UNBOUNDED PRECEDING)
But from what I understand there is no way to access the values currently being processed in a partition. Is there any other way I could write my subquery ?
Input example :
FIRST_NAME LAST_NAME
----------------------------------
SYLVIE BOUVET
LUCIENNE BRUN
BOUVET SYLVIE
FRANCINE CARON
BRUN LUCIENNE
BRUN LUCIENNE
KEVIN MACHETEL
REMI MINVIELLE
QUENTIN THUILLIER
MINVIELLE REMI
Desired output example :
FIRST_NAME LAST_NAME REVERSE_NAME_COUNT
------------------------------------------------------
SYLVIE BOUVET 1
LUCIENNE BRUN 2
BOUVET SYLVIE 1
FRANCINE CARON 0
BRUN LUCIENNE 1
BRUN LUCIENNE 1
KEVIN MACHETEL 0
REMI MINVIELLE 1
QUENTIN THUILLIER 0
MINVIELLE REMI 1
Are you talking about a Scalar Subquery in Select?
SELECT
last_name
,(
SELECT Count(*)
FROM myTable AS T1
WHERE T1.FIRST_NAME = T2.LAST_NAME
)
FROM myTable AS t2
Then you're right, you can't rewrite it as an OLAP function.
Those Scalar Subqueries tend do have bad performance, but you can usually rewrite them using an Outer Join:
SELECT
t2.last_name
,t1.Cnt
FROM myTable AS t2
LEFT JOIN
(
SELECT first_name, Count(*) AS Cnt
FROM myTable
GROUP BY 1
) AS t1
ON T1.FIRST_NAME = T2.LAST_NAME