All Forums Database
guru_new 6 posts Joined 03/12
22 Mar 2012
OLAP functions

Following is the data I currently have

 DateTime                User                   Number

2012/03/22 00:00:00 abcd   10
2012/03/22 01:00:00 abcd   20
2012/03/22 02:00:00 1111   30
2012/03/22 03:00:00 1111   40
2012/03/22 04:00:00 1111   35
2012/03/22 05:00:00 2222   60
2012/03/22 06:00:00 2222   70
2012/03/22 07:00:00 2222   62
2012/03/22 08:00:00 abcd   90
2012/03/22 09:00:00 abcd   100
2012/03/22 10:00:00 abcd   20
2012/03/22 11:00:00 1111   120
2012/03/22 12:00:00 1111   130
2012/03/22 13:00:00 3333   140
2012/03/22 14:00:00 3333   150
2012/03/22 15:00:00 abcd   160
2012/03/22 16:00:00 abcd   70
2012/03/22 17:00:00 2222   180
2012/03/22 18:00:00 2222   190
2012/03/22 19:00:00 2222   20

The results needed are

 

User 

first_number 

last_number 

min_dt 

max_dt

abcd

10

20

3/22/2012 0:00

3/22/2012 1:00

1111

30

35

3/22/2012 2:00

3/22/2012 4:00

2222

60

62

3/22/2012 5:00

3/22/2012 7:00

abcd

90

20

3/22/2012 8:00

3/22/2012 10:00

1111

120

130

3/22/2012 11:00

3/22/2012 12:00

3333

140

150

3/22/2012 13:00

3/22/2012 14:00

abcd

160

70

3/22/2012 15:00

3/22/2012 16:00

2222

180

20

3/22/2012 17:00

3/22/2012 19:00

 

In short, for the user, we need the first and last number and the minimum and maximum date before the user changes.

I used the min and max of the datetime with olap functions, but I always get the following results

abcd 2012/03/22 00:00:00 2012/03/22 19:00:00

for all abcd rows.

Sample sql used

select user,
case when datetime = min_dt then number else 0 end first_number,
case when datetime = max_dt then number else 0 end last_number,
min(datetime) over (partition by user order by datetime) min_dt,
max(datetime) over (partition by user order by datetime) max_dt
from table tt
 

I tried the rows between preceeding and following, but was not able to achieve the desired results.

Please advise

 

Please advise.

Tags:
dnoeth 4628 posts Joined 11/04
23 Mar 2012

You need to look for rows where the previous or next row has a different user:

SELECT
   usr, 
   num,
   CASE
     WHEN MIN(usr) OVER (ORDER BY dt ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) = usr 
     THEN MIN(num) OVER (ORDER BY dt ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING)
   END,
   dt, 
   CASE
     WHEN MIN(usr) OVER (ORDER BY dt ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) = usr 
     THEN MIN(dt)  OVER (ORDER BY dt ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING)
   END
FROM
 (
   SELECT 
      usr, dt, num
   FROM tab
   QUALIFY
      MIN(usr) OVER (ORDER BY dt ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) <> usr
   OR MIN(usr) OVER (ORDER BY dt ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) IS NULL
   OR MIN(usr) OVER (ORDER BY dt ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) <> usr
   OR MIN(usr) OVER (ORDER BY dt ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) IS NULL
 ) x 
QUALIFY
   MIN(usr) OVER (ORDER BY dt ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) <> usr
OR MIN(usr) OVER (ORDER BY dt ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) IS NULL 

Looks complicated, but code is mainly cut & paste.

Dieter

Dieter

guru_new 6 posts Joined 03/12
23 Mar 2012

THANK YOU!!!!!

You are awesome!!!

I was headed down that path after all my failed efforts. I will try it out and keep this post updated.

I had dumbed down the table, but if there is not just one column, but the data is to be partitioned by multiple columns, I am going to concatenate them. Please advise if that would be an incorrect approach.

guru_new 6 posts Joined 03/12
30 Mar 2012

Dieter,

Your suggestions worked, but I concatenated columns to get my desired result.

For example, if my data was as follows

DateTime                    U    S    E    R    Number

2012/03/22 00:00:00 a    b    c    d   10
2012/03/22 01:00:00 a    b    c    d   20
2012/03/22 02:00:00 1   1     1    1   30
2012/03/22 03:00:00 1   1     1    1   40
2012/03/22 04:00:00 1   1     1    1   35
2012/03/22 05:00:00 2   2     2    2   60

Then I concatenated the columns U, S, E and R to evaluate the min(usr) field.

Thank you once again

Cheers!

G

guru_new 6 posts Joined 03/12
30 Mar 2012

Another Follow up question, please let me know if I need to create a new thread

For the following data

ticket               service_tech          dept           duration       rowid

1000                beta                      hr                100             10

1000                alpha                     it                  200             20

1000                gamma                   hr                50              30

1000                delta                      hr                 70               40

 

Desired result

ticket             first_serv_tech           last_serv_tech             first_dur           last_dur

1000            beta                            delta                             100                   70

 

If  I use min(service_tech) over (partition by ticket order by rowid rows between unbounded preceding and unbounded following) I get alpha and not beta, but if I concatenate with rowid, I can get the value as beta.

Similarly for max(service_tech) over (partition by ticket order by rowid rows between unbounded preceding and unbounded following) I get gamma and not delta.

Is concatenating with rowid the right approach?

- G

ulrich 816 posts Joined 09/09
30 Mar 2012

 

yes concat the rowid in from and do a substr afterwards

 

something like

 

cast(substr(min(rowid !! duration) ,xx) as integer)

 

here you might be able to use a plain min and max and group by

 

 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

guru_new 6 posts Joined 03/12
30 Mar 2012

Thank you! ulrich...

- G

You must sign in to leave a comment.