Group Concatenation in Teradata

Database
Enthusiast

Group Concatenation in Teradata

I have a query that results in data containing customer id numbers, marketing channel, timestamp. So, the results might look something like this.

id marketingChannel TimeStamp      
1 SEO 5/18 23:11:43
1 SEO 5/18 24:12:43
1 Paid 5/18 24:13:43
2 Paid 5/18 24:12:43
2 Paid 5/18 24:14:43
2 Affiliate 5/18 24:20:43
2 Paid 5/18 24:22:43
3 SEO 5/18 24:10:43
3 Affiliate 5/18 24:11:43
4 SEO 5/18 23:11:43
4 SEO 5/18 24:12:43
4 Paid 5/18 24:13:43

I'm hoping to construct a query that will do a few things.

1. Concatenate the marketing channels a specific member used into a string and order them by timestamp.

2. Count the number of times a particular marketing channel order occured.

For example, the result might look something like this.

Marketing Path                  Count
SEO
> SEO > Paid 2
Paid
> Paid > Affiliate > Paid 1
SEO
> Affiliate 1

I believe this could be done with group_concat in MySQL, not sure how to acomplish it with Teradata.

1 REPLY
Enthusiast

Re: Group Concatenation in Teradata

From StackOverflow.  For TD 13

http://stackoverflow.com/questions/23741925/aggregation-by-timestamp/23770295?noredirect=1#23770295

CREATE VOLATILE TABLE vt AS 
(
SELECT
id
,MarketingChannel
,ROW_NUMBER() OVER (PARTITION BY id ORDER BY TS DESC) AS rn
,COUNT(*) OVER (PARTITION BY id) AS max_rn
FROM t
) WITH DATA
PRIMARY INDEX (id)
ON COMMIT PRESERVE ROWS;

WITH RECURSIVE cte(id, path, rn) AS
(
SELECT
id,

-- modify VARCHAR size to fit your maximum number of rows, that's better than VARCHAR(64000)
CAST(MarketingChannel AS VARCHAR(10000)) AS PATH,
rn
FROM vt
WHERE rn = max_rn
UNION ALL
SELECT
cte.ID,
cte.PATH || '>' || vt.MarketingChannel,
cte.rn-1
FROM vt JOIN cte
ON vt.id = cte.id
AND vt.rn = cte.rn - 1
)
SELECT
PATH,
COUNT(*)
FROM cte
WHERE rn = 1
GROUP BY path
ORDER BY PATH
;