Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> 3 Day intervals with a Rolling Date, Activity by a 3 day rolling period
Dillon
post Sep 9 2017, 02:30 PM
Post #1


Newbie
*

Group: Members
Posts: 3
Joined: 9-September 17
From: NYC
Member No.: 52,155



working on SQL syntax using a oracle database. I was able to get my dataset by daily activity summed up by a ID number.

select DATE, ID_NUMBER, SUM(Daily_Money), SUM (Daily_Count)
from Database
Where Date > = '01-JAN-2017'

However I want to take this same dataset but sum for each ID number by activity based on do a rolling 3 day window, aka Jan 1-3, Jan 2-4, Jan 3-5...etc.

What options do I have to make this idea run and work efficiently? Or is this not feasible?
Go to the top of the page
 
+Quote Post
burleson
post Sep 10 2017, 06:54 AM
Post #2


Advanced Member
***

Group: Members
Posts: 13,532
Joined: 26-January 04
Member No.: 13



Hi Dillon, and welcome to the forum!

This example may work for you:

http://www.dba-oracle.com/t_advanced_sql_w...wing_clause.htm

Let us know.

Good luck!


--------------------
Hope this helps . . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
Dillon
post Sep 11 2017, 01:39 AM
Post #3


Newbie
*

Group: Members
Posts: 3
Joined: 9-September 17
From: NYC
Member No.: 52,155



`
Go to the top of the page
 
+Quote Post
boobal_ganesan
post Sep 11 2017, 08:11 AM
Post #4


Advanced Member
***

Group: Members
Posts: 77
Joined: 6-June 16
From: India
Member No.: 51,370



QUOTE (Dillon @ Sep 10 2017, 01:00 AM) *
working on SQL syntax using a oracle database. I was able to get my dataset by daily activity summed up by a ID number.

select DATE, ID_NUMBER, SUM(Daily_Money), SUM (Daily_Count)
from Database
Where Date > = '01-JAN-2017'

However I want to take this same dataset but sum for each ID number by activity based on do a rolling 3 day window, aka Jan 1-3, Jan 2-4, Jan 3-5...etc.

What options do I have to make this idea run and work efficiently? Or is this not feasible?


Hello Dillon,

The query you posted may not work as it misses GROUP BY function while you are aggregating the money and count columns.

You can modify the below query as per your need.

CODE
WITH DATABASE(dt,id_number,daily_money,daily_count)AS
(SELECT TRUNC(sysdate,'RRRR')+level-1,
level,
level*100,
level
FROM dual
CONNECT BY level<=100
)
SELECT dt,
id_number,
sum_money sum_money_today,
lead(sum_money,1)over(order by dt) sum_money_nextday,
lead(sum_money,2)over(order by dt) sum_money_dayafter_nextday,
sum_money+lead(sum_money,1)over(order by dt)+lead(sum_money,2)over(order by dt) sum_3days
FROM
(SELECT dt,
ID_NUMBER,
SUM(Daily_Money) sum_money,
SUM (Daily_Count) sum_count
FROM DATABASE
WHERE dt > = '01-JAN-2017'
GROUP BY dt,
id_number
ORDER BY dt
);



Thank you,
Boobal Ganesan


--------------------
Check out my new Advanced PL/SQL book here - http://www.rampant-books.com/book_1701_pls...initive_ref.htm
Go to the top of the page
 
+Quote Post
Dillon
post Sep 12 2017, 04:12 AM
Post #5


Newbie
*

Group: Members
Posts: 3
Joined: 9-September 17
From: NYC
Member No.: 52,155



Thanks for reaching out Boobal! I like the function recommendation but that function would only work if a transactions occurs every day.

QUOTE (boobal_ganesan @ Sep 11 2017, 08:11 AM) *
Hello Dillon,

The query you posted may not work as it misses GROUP BY function while you are aggregating the money and count columns.

You can modify the below query as per your need.

CODE
WITH DATABASE(dt,id_number,daily_money,daily_count)AS
(SELECT TRUNC(sysdate,'RRRR')+level-1,
level,
level*100,
level
FROM dual
CONNECT BY level<=100
)
SELECT dt,
id_number,
sum_money sum_money_today,
lead(sum_money,1)over(order by dt) sum_money_nextday,
lead(sum_money,2)over(order by dt) sum_money_dayafter_nextday,
sum_money+lead(sum_money,1)over(order by dt)+lead(sum_money,2)over(order by dt) sum_3days
FROM
(SELECT dt,
ID_NUMBER,
SUM(Daily_Money) sum_money,
SUM (Daily_Count) sum_count
FROM DATABASE
WHERE dt > = '01-JAN-2017'
GROUP BY dt,
id_number
ORDER BY dt
);



Thank you,
Boobal Ganesan

Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 23rd September 2017 - 10:18 PM