performing function on multiple rows [message #252930] |
Fri, 20 July 2007 13:40  |
ford_orafaq
Messages: 5 Registered: July 2007
|
Junior Member |
|
|
Hi all,
I have a table name 'user' as follows:
Date' ' User
01-jun-2007 100
01-jun-2007 110
01-jun-2007 120
02-jun-2007 110
02-jun-2007 130
02-jun-2007 140
03-jun-2007 130
03-jun-2007 140
..........................
like that millions of rows in that table
Here I need to generate a report to show total returned users from day to day- expected result shd be something like:
Date ' ' Total rep users
01-jun-2007 0 (bcoz there is no data available before
01-jun-2007)
02-jun-2007 1 (only one returned user 110)
03-jun-2007 2 (users 130 and 140)
..........................
I can get such output using stored procedure. But in this scenario we can implement such result by employing a stored function.
Anybody has any idea how to get that one.
I will appreciate if anybody can provide bit in detail plz.
Thanks
[Updated on: Fri, 20 July 2007 13:42] Report message to a moderator
|
|
|
|
|
|
|
Re: performing function on multiple rows [message #253213 is a reply to message #253058] |
Mon, 23 July 2007 02:45   |
ford_orafaq
Messages: 5 Registered: July 2007
|
Junior Member |
|
|
Hi Michael,
Thanks for your reply. That LAG function is not returning all the user_id information. I have used that function as follows:
FIRST_VALUE(USER_ID) OVER ( ORDER BY trunc(DATE) RANGE BETWEEN
INTERVAL '1' DAY PRECEDING AND INTERVAL '1' DAY PRECEDING)
I used 'FIRST_VALUE' key word bcoz i want to return user details by time period not by rows.
Do you think the LAG function returns values by time period?
LAG(User_Id, 1) OVER ( ORDER BY trunc(date) )
If anybody have any solution let me know.
Thanks
|
|
|
|
Re: performing function on multiple rows [message #256988 is a reply to message #252930] |
Tue, 07 August 2007 05:46   |
darshanmeel
Messages: 44 Registered: June 2007 Location: India
|
Member |
|
|
Hi i have created a table and tried to simulate the same scenario.I am using the same table twice so i am going through same table two times which is not as efficient if you could find any other method by using analytic function but still it is good method to use.
select * from us;
ID DT
----- -----------
1 07-AUG-2007
2 06-AUG-2007
1 06-AUG-2007
3 06-AUG-2007
3 08-AUG-2007
2 08-AUG-2007
1 08-AUG-2007
4 09-AUG-2007
3 09-AUG-2007
2 09-AUG-2007
5 10-AUG-2007
select fst.* from
(select id, dt from us) fst
,(select id, (dt+1) as next_date from us) scnd
where fst.id=scnd.id
and fst.dt=scnd.next_date
ID DT
---- -----------
1 07-AUG-2007
1 08-AUG-2007
2 09-AUG-2007
3 09-AUG-2007
Please let me know if you have any question.
|
|
|
|
|