Help with Code

Moderator: NorbertKrupa

Vertica_grm
Newbie
Newbie
Posts: 17
Joined: Tue Apr 21, 2015 8:35 pm

Re: Help with Code

Post by Vertica_grm » 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

NorbertKrupa
GURU
GURU
Posts: 527
Joined: Tue Oct 22, 2013 9:36 pm
Location: Chicago, IL
Contact:

Re: Help with Code

Post by NorbertKrupa » 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?
Checkout vertica.tips for more Vertica resources.

Vertica_grm
Newbie
Newbie
Posts: 17
Joined: Tue Apr 21, 2015 8:35 pm

Re: Help with Code

Post by Vertica_grm » 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.

NorbertKrupa
GURU
GURU
Posts: 527
Joined: Tue Oct 22, 2013 9:36 pm
Location: Chicago, IL
Contact:

Re: Help with Code

Post by NorbertKrupa » 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.
Checkout vertica.tips for more Vertica resources.

Vertica_grm
Newbie
Newbie
Posts: 17
Joined: Tue Apr 21, 2015 8:35 pm

Re: Help with Code

Post by Vertica_grm » Thu Jul 23, 2015 8:23 pm

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

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: Help with Code

Post by id10t » 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.

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1824
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Help with Code

Post by JimKnicely » 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)
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “New to Vertica SQL”