niXforums Forum Index
 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   PreferencesPreferences   Log in to check your private messagesLog in to check your private messages   Log inLog in 
·  nixdoc.net ·  man pages ·  Linux HOWTOs ·  FreeBSD Tips ·  Forums
navigation Forum index » Databases » Oracle
complex? SQL time series problem
Post new topic   Reply to topic Page 1 of 1 [8 Posts] View previous topic :: View next topic
Author Message
Jimmy
*nix forums beginner


Joined: 22 Mar 2005
Posts: 39

PostPosted: Mon Feb 28, 2005 1:45 pm    Post subject: Re: complex? SQL time series problem Reply with quote
gmuldoonnospam@scu.edu.au says...


Quote:
CREATE TABLE TEST3
(
C_SET VARCHAR2(25),
C_ID VARCHAR2(25),
STTS VARCHAR2(25),
S_DATE DATE,
E_DATE DATE
)


Could you possibly explain in more detail exactly what it is you want
and why?


Paul...
Back to top
Geoff M
*nix forums beginner


Joined: 23 Feb 2005
Posts: 18

PostPosted: Mon Feb 21, 2005 4:44 am    Post subject: Re: complex? SQL time series problem Reply with quote
In article <1108737483.685588.220970@c13g2000cwb.googlegroups.com>,
Mark.Powell@eds.com says...
Quote:
Geoff, I suspect that you have not described the problem correctly.
The answer I gave should work for the problem as you described it.
Obviously neither I nor Daniel understand correctly what it is you need
to do.

OK trying again ..

CREATE TABLE TEST3
(
C_SET VARCHAR2(25),
C_ID VARCHAR2(25),
STTS VARCHAR2(25),
S_DATE DATE,
E_DATE DATE
)

INSERT INTO TEST3 ( C_SET, C_ID, STTS, S_DATE, E_DATE ) VALUES (
'a2', '125', 'A', '01-JAN-2000', '15-MAR-2000');
INSERT INTO TEST3 ( C_SET, C_ID, STTS, S_DATE, E_DATE ) VALUES (
'a2', '125', 'B', '16-MAR-2000', '16-JUL-2000');
INSERT INTO TEST3 ( C_SET, C_ID, STTS, S_DATE, E_DATE ) VALUES (
'a2', '125', 'A', '17-JUL-2000', '01-JAN-3000');
INSERT INTO TEST3 ( C_SET, C_ID, STTS, S_DATE, E_DATE ) VALUES (
'a2', '889', 'B', '03-FEB-2000', '13-MAY-2000');
INSERT INTO TEST3 ( C_SET, C_ID, STTS, S_DATE, E_DATE ) VALUES (
'a2', '889', 'A', '14-MAY-2000', '01-JAN-3000');
COMMIT;

A simple select/group by of:

select c_set, stts, s_date, e_date, count(c_id)
from test3
group by c_set, stts, s_date, e_date
order by c_set, stts, s_date

gives (note date overlaps here, the crux of my problem):

c_set stts s_date e_date count(c_id)
a2 A 01-JAN-2000 15-MAR-2000 1
a2 A 14-MAY-2000 01-JAN-3000 1
a2 A 17-JUL-2000 01-JAN-3000 1
a2 B 03-FEB-2000 13-MAY-2000 1
a2 B 16-MAR-2000 16-JUL-2000 1

What I need is:

c_set stts s_date e_date count(c_id)
a2 A 01-JAN-2000 15-MAR-2000 1
a2 A 14-MAY-2000 16-JUL-2000 1
a2 A 17-JUL-2000 01-JAN-3000 2
a2 B 03-FEB-2000 15-MAR-2000 1
a2 B 16-MAR-2000 13-MAY-2000 2
a2 B 14-MAY-2000 16-JUL-2000 1

Geoff M
Back to top
Mark D Powell
*nix forums Guru


Joined: 23 Apr 2005
Posts: 701

PostPosted: Fri Feb 18, 2005 1:38 pm    Post subject: Re: complex? SQL time series problem Reply with quote
Geoff, I suspect that you have not described the problem correctly.
The answer I gave should work for the problem as you described it.
Obviously neither I nor Daniel understand correctly what it is you need
to do.

HTH -- Mark D Powell --
Back to top
Geoff M
*nix forums beginner


Joined: 23 Feb 2005
Posts: 18

PostPosted: Thu Feb 17, 2005 9:08 pm    Post subject: Re: complex? SQL time series problem Reply with quote
In article <1108659136.624096@yasure>, damorgan@x.washington.edu says...

Quote:
s_date e_date c_set c_id stts

01-jan-2000 15-mar-2000 a2 125 A
16-mar-2000 16-jul-2000 a2 125 B
17-jul-2000 sysdate a2 125 A
....
03-feb-2000 13-may-2000 a2 158 B
14-may-2000 sysdate a2 158 A
....

I need to group by c_set, stts and time interval, giving the count of
c_id, eg:

c-set stts s_date e_date count(c_id)

a2 A 01-jan-2000 15-mar-2000 1
a2 A 16-mar-2000 13-may-2000 0
a2 A 14-may-2000 16-jul-2000 1
a2 A 17-jul-2000 sysdate 2
a2 B 03-feb-2000 15-mar-2000 1
a2 B 16-mar-2000 13-may-2000 2
a2 B 14-may-2000 16-jul-2000 1
a2 B 17-jul-2000 sysdate 0

And exactly what is it about GROUP BY that won't work?

How do I get the e_date of 13-may-2000 in the stts=A group by?

Geoff M
Back to top
DA Morgan
*nix forums Guru


Joined: 06 Mar 2005
Posts: 1042

PostPosted: Thu Feb 17, 2005 3:55 pm    Post subject: Re: complex? SQL time series problem Reply with quote
Geoff M wrote:

Quote:
Hi all,

Oracle 9.2.0.6 on Linux ....

I have a table, a log over time of the status of clients in client sets
resembling the following:

s_date e_date c_set c_id stts

01-jan-2000 15-mar-2000 a2 125 A
16-mar-2000 16-jul-2000 a2 125 B
17-jul-2000 sysdate a2 125 A
....
03-feb-2000 13-may-2000 a2 158 B
14-may-2000 sysdate a2 158 A
....

I need to group by c_set, stts and time interval, giving the count of
c_id, eg:

c-set stts s_date e_date count(c_id)

a2 A 01-jan-2000 15-mar-2000 1
a2 A 16-mar-2000 13-may-2000 0
a2 A 14-may-2000 16-jul-2000 1
a2 A 17-jul-2000 sysdate 2
a2 B 03-feb-2000 15-mar-2000 1
a2 B 16-mar-2000 13-may-2000 2
a2 B 14-may-2000 16-jul-2000 1
a2 B 17-jul-2000 sysdate 0

Can anyone suggest a way this could be achieved in SQL without resorting
to PL/SQL, as I want if possible to create a materialized view of this
aggregation.

TIA

Geoff M

And exactly what is it about GROUP BY that won't work?
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)
Back to top
Geoff M
*nix forums beginner


Joined: 23 Feb 2005
Posts: 18

PostPosted: Mon Feb 14, 2005 10:51 pm    Post subject: Re: complex? SQL time series problem Reply with quote
Mark.Powell@eds.com says...
Quote:
Geoff, wouldn't that be just group by c_set, stts, (e_date - s_date)
with a matching select list plus count(*)?

Can't see it. More simply, I need to eventually plot this against a date
dimension, so I need:

s_date e_date c_set c_id stts
01-jan-2000 15-mar-2000 a2 125 A
17-jul-2000 sysdate a2 125 A
14-may-2000 sysdate a2 158 A

to become:
c-set stts s_date e_date count(c_id)
a2 A 01-jan-2000 15-mar-2000 1
a2 A 14-may-2000 16-jul-2000 1
a2 A 17-jul-2000 sysdate 2

Need to derive an e_date value of 16-jul-2000 amongst other things.

Geoff M
Back to top
Mark D Powell
*nix forums Guru


Joined: 23 Apr 2005
Posts: 701

PostPosted: Mon Feb 14, 2005 1:20 pm    Post subject: Re: complex? SQL time series problem Reply with quote
Geoff, wouldn't that be just group by c_set, stts, (e_date - s_date)
with a matching select list plus count(*)?

HTH -- Mark D Powell --
Back to top
Geoff M
*nix forums beginner


Joined: 23 Feb 2005
Posts: 18

PostPosted: Mon Feb 14, 2005 1:47 am    Post subject: complex? SQL time series problem Reply with quote
Hi all,

Oracle 9.2.0.6 on Linux ....

I have a table, a log over time of the status of clients in client sets
resembling the following:

s_date e_date c_set c_id stts

01-jan-2000 15-mar-2000 a2 125 A
16-mar-2000 16-jul-2000 a2 125 B
17-jul-2000 sysdate a2 125 A
.....
03-feb-2000 13-may-2000 a2 158 B
14-may-2000 sysdate a2 158 A
.....

I need to group by c_set, stts and time interval, giving the count of
c_id, eg:

c-set stts s_date e_date count(c_id)

a2 A 01-jan-2000 15-mar-2000 1
a2 A 16-mar-2000 13-may-2000 0
a2 A 14-may-2000 16-jul-2000 1
a2 A 17-jul-2000 sysdate 2
a2 B 03-feb-2000 15-mar-2000 1
a2 B 16-mar-2000 13-may-2000 2
a2 B 14-may-2000 16-jul-2000 1
a2 B 17-jul-2000 sysdate 0

Can anyone suggest a way this could be achieved in SQL without resorting
to PL/SQL, as I want if possible to create a materialized view of this
aggregation.

TIA

Geoff M
Back to top
Google
Back to top
Display posts from previous:   
Post new topic   Reply to topic Page 1 of 1 [8 Posts] View previous topic :: View next topic
The time now is Thu Jan 08, 2009 3:49 am | All times are GMT
navigation Forum index » Databases » Oracle
Jump to:  

Similar Topics
Topic Author Forum Replies Last Post
No new posts Unknown in header problem -SOLVED- Light Speed Postfix 0 Thu Jul 03, 2008 10:40 am
No new posts problem with sending mail nuxia Postfix 0 Mon Apr 21, 2008 3:58 am
No new posts Postfix 2.3.8 Virtual problem Blotto Postfix 0 Fri Apr 04, 2008 6:11 am
No new posts Postfix sending problem for local domain remote email monkey_magix Postfix 0 Mon Sep 10, 2007 10:17 am
No new posts bounce problem murkis Postfix 0 Sun Oct 08, 2006 3:45 pm

Debt Consolidation | Credit Cards | Bankruptcy | Hotels in Rome | Online Advertising
Copyright © 2004-2005 DeniX Solutions SRL
 
Other DeniX Solutions sites: Unix/Linux blog |  electronics forum |  medicine forum |  science forum | 
Privacy Policy


Powered by phpBB © 2001, 2005 phpBB Group

[ Time: 0.2605s ][ Queries: 20 (0.1736s) ][ GZIP on - Debug on ]