Home » SQL & PL/SQL » SQL & PL/SQL » Filtering (11.1 g)
Filtering [message #568663] Mon, 15 October 2012 10:54 Go to next message
primer2020
Messages: 32
Registered: August 2012
Member
Hello,
I have the following table:

{code}
create table test
(
id number,
class varchar (10),
value number,
year number
);


insert into test
values (300,
'AA',
100,
1997);

insert into test
values (300,
'AA',
1700,
1998);

insert into test
values (300,
'AA',
2900,
1999);

insert into test
values (300,
'AA',
0,
2000);

insert into test
values (300,
'AA',
0,
2001);

insert into test
values (200,
'BB',
0,
1997);

insert into test
values (200,
'BB',
2600,
1998);

insert into test
values (200,
'BB',
3400,
1999);

insert into test
values (200,
'BB',
100,
2000);

insert into test
values (200,
'BB',
0,
2001);


insert into test
values (100,
'CC',
0,
1997);

insert into test
values (100,
'CC',
0,
1998);

insert into test
values (100,
'CC',
0,
1999);

insert into test
values (100,
'CC',
500,
2000);

insert into test
values (100,
'CC',
0,
2001);


select id,
class,
value,
year,
case value when 0 then 0 else 1 end as bool
from test order by id, class, year;

ID CLASS VALUE YEAR BOOL
100 CC 0 1997 0
100 CC 0 1998 0
100 CC 0 1999 0
100 CC 500 2000 1
100 CC 0 2001 0
200 BB 0 1997 0
200 BB 2600 1998 1
200 BB 3400 1999 1
200 BB 100 2000 1
200 BB 0 2001 0
300 AA 100 1997 1
300 AA 1700 1998 1
300 AA 2900 1999 1
300 AA 0 2000 0
300 AA 0 2001 0
{code}

I want use only SQL to filter out data base on BOOL vector for each group of ID and CLASS. Please note that the data is in the unpivot form and do not want to pivot it.

For example: ID=100 AND CLASS =CCC ===> BOOL verctor (1,1,1,0,0)
Filter out if Vector starts with 1 and ends with 0
or Vector all 0

Desired result:
ID CLASS VALUE YEAR BOOL
100 CC 0 1997 0
100 CC 0 1998 0
100 CC 0 1999 0
100 CC 500 2000 1
100 CC 0 2001 0
200 BB 0 1997 0
200 BB 2600 1998 1
200 BB 3400 1999 1
200 BB 100 2000 1
200 BB 0 2001 0



Thank you




[Updated on: Mon, 15 October 2012 11:20]

Report message to a moderator

Re: Filtering [message #568665 is a reply to message #568663] Mon, 15 October 2012 11:58 Go to previous messageGo to next message
Michel Cadot
Messages: 58618
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

SQL> with 
  2    data as (
  3      select id, class, value, year,
  4             first_value (value) over 
  5              (partition by id, class order by year
  6               rows between unbounded preceding and unbounded following)
  7               fval,
  8             last_value (value) over 
  9              (partition by id, class order by year
 10               rows between unbounded preceding and unbounded following)
 11               lval,
 12             max (value) over 
 13              (partition by id, class order by year
 14               rows between unbounded preceding and unbounded following)
 15               mval
 16     from test
 17    )
 18  select id, class, value, year
 19  from data
 20  where ( fval = 0 or lval != 0 )
 21    and mval != 0
 22  order by id, class, year
 23  /
        ID CLASS           VALUE       YEAR
---------- ---------- ---------- ----------
       100 CC                  0       1997
       100 CC                  0       1998
       100 CC                  0       1999
       100 CC                500       2000
       100 CC                  0       2001
       200 BB                  0       1997
       200 BB               2600       1998
       200 BB               3400       1999
       200 BB                100       2000
       200 BB                  0       2001

Regards
Michel
Re: Filtering [message #568678 is a reply to message #568665] Mon, 15 October 2012 13:46 Go to previous messageGo to next message
primer2020
Messages: 32
Registered: August 2012
Member
Hi,
How can control this statment so I get the min of value where year > minimum year and < maximum year.
In this example: where year > 1997 and year < 2001.
 max (value) over 
                (partition by id, class order by year
                rows between unbounded preceding and unbounded following)
                 mval


Thank you

Re: Filtering [message #568679 is a reply to message #568678] Mon, 15 October 2012 13:48 Go to previous messageGo to next message
Michel Cadot
Messages: 58618
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Add "where year > 1997 and year < 2001" in the "data" subquery.

Regards
Michel
Re: Filtering [message #568680 is a reply to message #568678] Mon, 15 October 2012 13:53 Go to previous message
Solomon Yakobson
Messages: 1965
Registered: January 2010
Senior Member
primer2020 wrote on Mon, 15 October 2012 14:46
Hi,
How can control this statment so I get the min of value where year > minimum year and < maximum year.


min(case when year > 1997 and year < 2001 then value end) over(partition by id) mval


SY.

[Updated on: Mon, 15 October 2012 13:54]

Report message to a moderator

Previous Topic: Automation script related to creation of tablespace
Next Topic: Date
Goto Forum:
  


Current Time: Wed Jul 30 08:25:13 CDT 2014

Total time taken to generate the page: 0.13643 seconds