The Vertica Database Forums

Posted: **Thu Jul 23, 2015 5:46 pm**

could I please bother you with one last?

I have arranged my dataset in following format

ID1 ID2 EventATime Event SID InteractionTime InteractionNumber
3335989 887582837 5/31/2014 23:53 A 1186276 (null) (null)
3335989 887582837 5/31/2014 23:53 I 1186276 5/10/2014 0:52 1
3335989 887582837 5/31/2014 23:53 I 1186276 5/9/2014 21:54 2
3335989 887582837 5/31/2014 23:51 A 1186276 (null) (null)
3461302 587582838 5/31/2014 23:51 I 1186276 5/31/2014 23:14 1
3461302 587582838 5/31/2014 23:51 I 1186276 5/31/2014 23:12 2
3461302 587582838 5/31/2014 23:51 I 1186276 5/31/2014 23:10 3
3461302 587582838 5/31/2014 23:51 I 1186276 5/31/2014 23:07 4
3461302 587582838 5/31/2014 23:51 I 1186276 5/31/2014 23:03 5
3461302 587582838 5/31/2014 23:51 I 1186276 5/31/2014 22:52 6
3461302 587582838 5/31/2014 23:51 I 1186270 5/25/2014 19:51 7
3461302 587582838 5/31/2014 23:51 I 1186270 5/25/2014 19:51 8

It basically arranged by events that happen after every Event A for a userID (ID2) in descending order based on InteractionTIme.

Final result should be like this: Which is basically for every ID1,ID2,EventATime combination, find the SID corresponding to first and last InteractionNumber.

ID1 ID2 EventATime SID-First SID-Last
3335989 887582837 5/31/2014 23:53 1186276 1186276
3461302 587582838 5/31/2014 23:51 1186276 1186270

Posted: **Thu Jul 23, 2015 6:09 pm**

Vertica_grm wrote:could I please bother you with one last?

May I ask why you're not able to try this on your own first?

Posted: **Thu Jul 23, 2015 7:13 pm**

because this dataset is a result of a subquery and I'm not able to figure out how to write the Select which would pick only the max and min of the last column.

Posted: **Thu Jul 23, 2015 7:46 pm**

Vertica_grm wrote:because this dataset is a result of a subquery and I'm not able to figure out how to write the Select which would pick only the max and min of the last column.

Is it a job requirement to know how to do this? If so, I feel it would be extremely valuable to take time to learn this.

Posted: **Thu Jul 23, 2015 8:23 pm**

Once I see it how it's done, I'll be able to do myself next time.

Posted: **Thu Jul 23, 2015 8:49 pm**

Hi!

IMO:

Vertica_grm wrote:Once I see it how it's done, I'll be able to do myself next time.

Worst comment you can give. This comment means:I can do my work with templates only and if there are no templates(tutorials) so I can NOT do it by myself. You are not able to do work without external help, you are not able to do your work with documentation - someone have to show you how to do it, so what are you doing in Hi-Tech/IT?

PS
Are you agree that someone have to be a first? Why not you? Why you are asking from us to show you? Its your requirements or ours?
Show this comment to your Team Lead and you probably you will loose your job.

Posted: **Fri Jul 24, 2015 1:20 pm**

Take a look at the FIRST_VALUE and LAST_VALUE analytic functions!

http://my.vertica.com/docs/7.1.x/HTML/i ... alytic.htm

http://http://my.vertica.com/docs/7.1.x ... alytic.htm

Example:

Code: Select all

dbadmin=> SELECT * FROM test ORDER BY ID1, ID2, InteractionNumber;
   ID1   |    ID2    |     EventATime      | Event |   SID   |   InteractionTime   | InteractionNumber
---------+-----------+---------------------+-------+---------+---------------------+-------------------
 3335989 | 887582837 | 2014-05-31 23:51:00 | A     | 1186276 |                     |
 3335989 | 887582837 | 2014-05-31 23:53:00 | A     | 1186276 |                     |
 3335989 | 887582837 | 2014-05-31 23:53:00 | I     | 1186276 | 2014-05-10 00:52:00 |                 1
 3335989 | 887582837 | 2014-05-31 23:53:00 | I     | 1186276 | 2014-05-09 21:54:00 |                 2
 3461302 | 587582838 | 2014-05-31 23:51:00 | I     | 1186276 | 2014-05-31 23:14:00 |                 1
 3461302 | 587582838 | 2014-05-31 23:51:00 | I     | 1186276 | 2014-05-31 23:12:00 |                 2
 3461302 | 587582838 | 2014-05-31 23:51:00 | I     | 1186276 | 2014-05-31 23:10:00 |                 3
 3461302 | 587582838 | 2014-05-31 23:51:00 | I     | 1186276 | 2014-05-31 23:07:00 |                 4
 3461302 | 587582838 | 2014-05-31 23:51:00 | I     | 1186276 | 2014-05-31 23:03:00 |                 5
 3461302 | 587582838 | 2014-05-31 23:51:00 | I     | 1186276 | 2014-05-31 22:52:00 |                 6
 3461302 | 587582838 | 2014-05-31 23:51:00 | I     | 1186270 | 2014-05-25 19:51:00 |                 7
 3461302 | 587582838 | 2014-05-31 23:51:00 | I     | 1186270 | 2014-05-25 19:51:00 |                 8
(12 rows)

Code: Select all

dbadmin=> select DISTINCT ID1, ID2, EventATime, first_value(SID) over (partition by ID1, ID2 order by InteractionNumber  rows between unbounded preceding and unbounded following) "SID-First", last_value(SID) over (partition by ID1, ID2 order by InteractionNumber rows between unbounded preceding and unbounded following) "SID-Last" from test WHERE event = 'I';
   ID1   |    ID2    |     EventATime      | SID-First | SID-Last
---------+-----------+---------------------+-----------+----------
 3335989 | 887582837 | 2014-05-31 23:53:00 |   1186276 |  1186276
 3461302 | 587582838 | 2014-05-31 23:51:00 |   1186276 |  1186270
(2 rows)

The Vertica Database Forums

Help with Code

Re: Help with Code

Re: Help with Code

Re: Help with Code

Re: Help with Code

Re: Help with Code

Re: Help with Code

Re: Help with Code