OLAP functions

Database

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 (1)
6 REPLIES
N/A

Re: OLAP functions

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

Re: OLAP functions

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.

Re: OLAP functions

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

Re: OLAP functions

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

N/A

Re: OLAP functions

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


 

 

Re: OLAP functions

Thank you! ulrich...

- G