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
Help with Code
Moderator: NorbertKrupa
-
- GURU
- Posts: 527
- Joined: Tue Oct 22, 2013 9:36 pm
- Location: Chicago, IL
- Contact:
Re: Help with Code
May I ask why you're not able to try this on your own first?Vertica_grm wrote:could I please bother you with one last?
Checkout vertica.tips for more Vertica resources.
-
- Newbie
- Posts: 17
- Joined: Tue Apr 21, 2015 8:35 pm
Re: Help with Code
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.
-
- GURU
- Posts: 527
- Joined: Tue Oct 22, 2013 9:36 pm
- Location: Chicago, IL
- Contact:
Re: Help with Code
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.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.
Checkout vertica.tips for more Vertica resources.
-
- Newbie
- Posts: 17
- Joined: Tue Apr 21, 2015 8:35 pm
Re: Help with Code
Once I see it how it's done, I'll be able to do myself next time.
Re: Help with Code
Hi!
IMO:
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.
IMO:
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?Vertica_grm wrote:Once I see it how it's done, I'll be able to do myself next time.
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.
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Help with Code
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:
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
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.