CODEWITHSUNDEEP
sqlteradata
dimitriy
dimitriy

Reputation: 9470

Nesting qualify statements in Teradata

Is it possible to "nest" qualify statements in Teradata?

I have some data that looks like this:

event_id = 1:

 user_id        action_timestamp
 971,134,265   17mar2010 20:16:56
 739,071,748   17mar2010 22:19:59
 919,853,934   18mar2010 15:47:49
 919,853,934   18mar2010 15:55:21
 919,853,934   18mar2010 16:01:20
 919,853,934   18mar2010 16:01:48
 919,853,934   18mar2010 16:04:52
 472,665,603   20mar2010 18:23:58
 472,665,603   20mar2010 18:24:07
 472,665,603   20mar2010 18:24:26
  ....
 event_id = 2:     
 971,134,265   17mar2069 20:16:56
 739,071,748   17mar2069 22:19:59
 919,853,934   18mar2069 15:47:49
 919,853,934   18mar2069 15:55:21
 919,853,934   18mar2069 16:01:20
 919,853,934   18mar2069 16:01:48
 919,853,934   18mar2069 16:04:52
 472,665,603   20mar2069 18:23:58
 472,665,603   20mar2069 18:24:07
 472,665,603   20mar2069 18:24:26

For user 919,853,934, I would like to grab "18mar2010 16:04:52" action (the last one in the first cluster of events).

I tried this, which does not grab the right date:

SELECT action_timestamp
       ,user_id
       ,event_id
FROM table
WHERE ...
QUALIFY (
    MAX(action_timestampt) OVER (PARTITION BY user_id, event_id) = action_timestamp
    AND MIN(action_timestamp) OVER (PARTITION BY user_id) = action_timestamp
)

This actually makes sense since the MAX and MIN apply to the whole data, rather than sequentially.

I also tried 2 separate qualify statements to get the MIN() part to apply to the subset of the data created by the MAX() part, but that errors.

Upvotes: 0

Views: 2745

Answers (3)

anwaar_hell
anwaar_hell

Reputation: 776

Just add the order by claue and check it will fetch you the last value of the first cluster.

QUALIFY (
    RANK() OVER (PARTITION BY user_id ORDER BY event_id order by action_timestamp desc ) = 1 
)

Note : Will work ff you are interested in getting only this value 18mar2010 16:04:52 of used_id =1

Upvotes: 0

dimitriy
dimitriy

Reputation: 9470

This seems to accomplish what I want:

SELECT *
FROM
(SELECT *
  FROM table
  WHERE ...
  QUALIFY (MAX(action_date) OVER (PARTITION BY user_id, event_id) = action_date)
) AS a
QUALIFY (
    MIN(a.action_date) OVER (PARTITION BY a.user_id) = a.action_date
)

Upvotes: 1

dnoeth
dnoeth

Reputation: 60482

How is this query failing?

Of course you can use multiple conditions in a QUALIFY, your query is syntactically correct.

But it's logic will not return a row for the given data :-)

You probably need to rewrite it, maybe 

QUALIFY (
    RANK() OVER (PARTITION BY user_id ORDER BY event_id) = 1 
)

Upvotes: 0

Related Questions