All Forums Database
cloud36 5 posts Joined 04/14
19 May 2014
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.

cloud36 5 posts Joined 04/14
21 May 2014

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
;

 

You must sign in to leave a comment.