Home » SQL & PL/SQL » SQL & PL/SQL » performing function on multiple rows  () 1 Vote
performing function on multiple rows [message #252930] Fri, 20 July 2007 13:40 Go to next message
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 #252940 is a reply to message #252930] Fri, 20 July 2007 15:52 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
do a google on "oracle analytic lag lead -olap"
Re: performing function on multiple rows [message #252977 is a reply to message #252930] Sat, 21 July 2007 02:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do you count all users present in the current since the beginning or only those present the day before?

Forgot:
Please read and follow How to format your posts
Make sure that lines of code do not exceed 80 or 100 characters when you format.
Please always post your Oracle version (4 decimals).


Regards
Michel

[Updated on: Sat, 21 July 2007 02:35]

Report message to a moderator

Re: performing function on multiple rows [message #253039 is a reply to message #252977] Sat, 21 July 2007 20:17 Go to previous messageGo to next message
ford_orafaq
Messages: 5
Registered: July 2007
Junior Member
i want to count all the people from the day before
Re: performing function on multiple rows [message #253058 is a reply to message #253039] Sun, 22 July 2007 01:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So LAG function is the one for you (as Andrew already said).

Regards
Michel
Re: performing function on multiple rows [message #253213 is a reply to message #253058] Mon, 23 July 2007 02:45 Go to previous messageGo to next message
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 #253221 is a reply to message #253213] Mon, 23 July 2007 03:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post the full query for your example.
Copy and paste your execution screen.
And FORMAT it.

Regards
Michel
Re: performing function on multiple rows [message #256988 is a reply to message #252930] Tue, 07 August 2007 05:46 Go to previous messageGo to next message
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.
Re: performing function on multiple rows [message #257003 is a reply to message #256988] Tue, 07 August 2007 06:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Darshan Singh,

Please read How to format your posts

Regards
Michel
Re: performing function on multiple rows [message #257101 is a reply to message #252930] Tue, 07 August 2007 10:28 Go to previous message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Just as an aside.

If you really have a table called USER, that is probably going to get you into trouble at some point.

Previous Topic: two decimal place
Next Topic: XML to oracle
Goto Forum:
  


Current Time: Sun Dec 04 20:46:30 CST 2016

Total time taken to generate the page: 0.10779 seconds