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
> sql query grop id ranges to chunks
oskar horch
post Sep 23 2010, 01:46 PM
Post #1


Newbie
*

Group: Members
Posts: 5
Joined: 23-September 10
Member No.: 43,901



Hi,

I've the following table:
MEASURE_VALUE(ID, VALUE) containing measure values. I would like to calculate the average of a specific id interval. In my case the id is the position where the value was captures.
Example:
id[m] value[mm]
1 1.2
2 1.5
9 2
11 3
18 1
28 1.2

I would like to group ids in a specified range. For n = 3 the result should look like this:
from_id, to_id, avg
1 2 1.35
9 11 1.5
18 18 1
28 28 1.2

I have to find a way to group ids to chunks. Has anyone an idea how to manage this in a query (without a stored procedure)?
Go to the top of the page
 
+Quote Post
SteveC
post Sep 23 2010, 03:01 PM
Post #2


Advanced Member
***

Group: Members
Posts: 2,835
Joined: 11-March 08
Member No.: 18,933



The rules don't make any sense. How does n=3 apply to the data? Plus, not all from-to combinations are shown.
Go to the top of the page
 
+Quote Post
oskar horch
post Sep 23 2010, 03:15 PM
Post #3


Newbie
*

Group: Members
Posts: 5
Joined: 23-September 10
Member No.: 43,901



QUOTE (SteveC @ Sep 23 2010, 03:01 PM) *
The rules don't make any sense. How does n=3 apply to the data? Plus, not all from-to combinations are shown.

Hi thanks for your reply.

All combinations are shown. The example has 6 entries. The chunk size is 3.
Entry with id 1 and id 2 are put into one chunk because they are smaller than 3.
Another example with chunk size 5 and data without gaps.
id, value
6, ...
7,..
8,...
9,...
10,...
11,...
12,...
13,...
14,...
15,...
16,...

The result should be:
from_id, till_id, avg_value
6, 10, ...
11, 15, ...
16, 16, ...

I hope it's better to understand now!

Thanks for your hints.
Go to the top of the page
 
+Quote Post
Laurent Schneide...
post Sep 24 2010, 04:17 AM
Post #4


Advanced Member
***

Group: Members
Posts: 243
Joined: 24-June 07
From: Switzerland
Member No.: 9,590



I would start with moving average. To have the first value of a group, you can use first value. Then you can get the min and the max of each group.

I agree with Steven that the requirement could be clearer.

Just to start with your first question, here is my solution. Maybe this will give you some direction.

CODE
SQL> SELECT * FROM t

        ID      VALUE
---------- ----------
         1        1.2
         2        1.5
         9          2
        11          3
        18          1
        28        1.2


SQL> SELECT
  ID,
  AVG (VALUE) OVER
  (
    ORDER BY ID RANGE BETWEEN 3 PRECEDING AND 3 FOLLOWING
  ) AVG,
  FIRST_VALUE (ID IGNORE NULLS) OVER
  (
    ORDER BY ID RANGE BETWEEN 3 PRECEDING AND 3 FOLLOWING
  ) FIRST
FROM t

        ID        AVG      FIRST
---------- ---------- ----------
         1       1.35          1
         2       1.35          1
         9        2.5          9
        11        2.5          9
        18          1         18
        28        1.2         28


SQL> SELECT
  MIN(ID) FROM_ID,
  MAX(ID) TO_ID,
  AVG
FROM
(
  SELECT
    ID,
    AVG (VALUE) OVER
    (
      ORDER BY ID RANGE BETWEEN 3 PRECEDING AND 3 FOLLOWING
    ) AVG,
    FIRST_VALUE (ID IGNORE NULLS) OVER
    (
      ORDER BY ID RANGE BETWEEN 3 PRECEDING AND 3 FOLLOWING
    ) FIRST
  FROM t
)
GROUP BY FIRST, AVG
ORDER BY FROM_ID

   FROM_ID      TO_ID        AVG
---------- ---------- ----------
         1          2       1.35
         9         11        2.5
        18         18          1
        28         28        1.2


--------------------
Go to the top of the page
 
+Quote Post
RHR
post Sep 24 2010, 02:43 PM
Post #5


Advanced Member
***

Group: Admin
Posts: 230
Joined: 25-September 07
Member No.: 12,334



Thanks, Laurent!

In the event you missed the little link at the end of Laurent's post, his excellent book is available through Rampant TechPress:

http://www.rampant-books.com/book_0802_adv...programming.htm

RHR
Go to the top of the page
 
+Quote Post
oskar horch
post Sep 26 2010, 08:04 AM
Post #6


Newbie
*

Group: Members
Posts: 5
Joined: 23-September 10
Member No.: 43,901



Hi Laurent,

thanks for your reply! You're right. The requrement could be clearer. I wrote a complete sample with sample values and a stored procedure, which mets my requirements.
My code:


CODE
CREATE TABLE "SCOTT"."MEASUR_VALUE"
(
"POS" NUMBER(9,0) NOT NULL ENABLE,
"VALUE" NUMBER(12,3) NOT NULL ENABLE,
CONSTRAINT "TABLE1_PK" PRIMARY KEY ("POS") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" ENABLE
)
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
(
INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
)
TABLESPACE "USERS" ;

INSERT INTO MEASUR_VALUE ( POS, VALUE ) VALUES (
1612, 19.479);
INSERT INTO MEASUR_VALUE ( POS, VALUE ) VALUES (
1620, 20.299);
INSERT INTO MEASUR_VALUE ( POS, VALUE ) VALUES (
1632, 20);
INSERT INTO MEASUR_VALUE ( POS, VALUE ) VALUES (
1652, 20.104);
INSERT INTO MEASUR_VALUE ( POS, VALUE ) VALUES (
1670, 19.688);
INSERT INTO MEASUR_VALUE ( POS, VALUE ) VALUES (
1691, 19.388);
INSERT INTO MEASUR_VALUE ( POS, VALUE ) VALUES (
1700, 20.625);
INSERT INTO MEASUR_VALUE ( POS, VALUE ) VALUES (
1712, 21.172);
INSERT INTO MEASUR_VALUE ( POS, VALUE ) VALUES (
1721, 20.391);
INSERT INTO MEASUR_VALUE ( POS, VALUE ) VALUES (
1740, 20.313);
INSERT INTO MEASUR_VALUE ( POS, VALUE ) VALUES (
1750, 19.427);
INSERT INTO MEASUR_VALUE ( POS, VALUE ) VALUES (
1770, 19.492);
INSERT INTO MEASUR_VALUE ( POS, VALUE ) VALUES (
1792, 20.664);
INSERT INTO MEASUR_VALUE ( POS, VALUE ) VALUES (
1802, 21.875);
INSERT INTO MEASUR_VALUE ( POS, VALUE ) VALUES (
1811, 21.432);
INSERT INTO MEASUR_VALUE ( POS, VALUE ) VALUES (
1820, 23.789);
INSERT INTO MEASUR_VALUE ( POS, VALUE ) VALUES (
1832, 24.219);
INSERT INTO MEASUR_VALUE ( POS, VALUE ) VALUES (
1840, 22.513);
INSERT INTO MEASUR_VALUE ( POS, VALUE ) VALUES (
1852, 24.167);
INSERT INTO MEASUR_VALUE ( POS, VALUE ) VALUES (
1860, 23.841);
INSERT INTO MEASUR_VALUE ( POS, VALUE ) VALUES (
1882, 24.805);
INSERT INTO MEASUR_VALUE ( POS, VALUE ) VALUES (
1890, 23.294);
INSERT INTO MEASUR_VALUE ( POS, VALUE ) VALUES (
1902, 21.471);
INSERT INTO MEASUR_VALUE ( POS, VALUE ) VALUES (
1911, 21.341);
INSERT INTO MEASUR_VALUE ( POS, VALUE ) VALUES (
1920, 20.586);
INSERT INTO MEASUR_VALUE ( POS, VALUE ) VALUES (
1933, 20.859);
INSERT INTO MEASUR_VALUE ( POS, VALUE ) VALUES (
1943, 19.323);
INSERT INTO MEASUR_VALUE ( POS, VALUE ) VALUES (
1951, 21.12);
INSERT INTO MEASUR_VALUE ( POS, VALUE ) VALUES (
1961, 20.534);
INSERT INTO MEASUR_VALUE ( POS, VALUE ) VALUES (
1581, 19.427);
INSERT INTO MEASUR_VALUE ( POS, VALUE ) VALUES (
1590, 19.388);
INSERT INTO MEASUR_VALUE ( POS, VALUE ) VALUES (
1602, 19.987);
INSERT INTO MEASUR_VALUE ( POS, VALUE ) VALUES (
1973, 21.042);
INSERT INTO MEASUR_VALUE ( POS, VALUE ) VALUES (
1991, 20.104);
INSERT INTO MEASUR_VALUE ( POS, VALUE ) VALUES (
2001, 21.029);
COMMIT;

create or replace
TYPE T_MEASUR_VALUE_GROUP AS OBJECT
(
POS NUMBER (9),
VALUE NUMBER(12,3),
INGROUP NUMBER(3)
);

create or replace
TYPE T_MEASUR_VALUE_GROUP_TABLE AS TABLE OF T_MEASUR_VALUE_GROUP;


create or replace
FUNCTION GET_MEASUR_VALUE_GROUP(IN_FROM_POS IN NUMBER, IN_TO_POS IN NUMBER, IN_CHUNK_SIZE IN NUMBER) RETURN T_MEASUR_VALUE_GROUP_TABLE PIPELINED
AS
BEGIN
DECLARE
CURSOR C_MEASUR_VALUE (CI_FROM_POS NUMBER, CI_TO_POS NUMBER)
IS
SELECT POS,
VALUE
FROM MEASUR_VALUE
WHERE POS BETWEEN CI_FROM_POS AND CI_TO_POS
ORDER BY POS ASC;

L_CHUNK_SIZE NUMBER := 0;
L_NEXT_POS NUMBER := 0;
L_GROUP NUMBER := 0;

L_RETURN T_MEASUR_VALUE_GROUP := T_MEASUR_VALUE_GROUP(NULL, NULL, NULL);
BEGIN
L_CHUNK_SIZE := NVL(IN_CHUNK_SIZE, 0);

IF L_CHUNK_SIZE > 0 THEN
FOR L_MEASUR_VALUE IN C_MEASUR_VALUE(IN_FROM_POS, IN_TO_POS) LOOP
IF L_NEXT_POS < L_MEASUR_VALUE.POS THEN
L_NEXT_POS := L_MEASUR_VALUE.POS + L_CHUNK_SIZE;
L_GROUP := L_GROUP + 1;
END IF;

L_RETURN.POS := L_MEASUR_VALUE.POS;
L_RETURN.VALUE := L_MEASUR_VALUE.VALUE;
L_RETURN.INGROUP := L_GROUP;

PIPE ROW(L_RETURN);

END LOOP;
END IF;

RETURN;
END;
END GET_MEASUR_VALUE_GROUP;




For illustrating the grouping with the function I used the following statement:

CODE
WITH GROUP_SELECT
AS
(SELECT POS,
VALUE,
INGROUP
FROM TABLE(GET_MEASUR_VALUE_GROUP(1612, 1792, 20)))
SELECT *
FROM GROUP_SELECT


Result:
CODE
"POS" "VALUE" "INGROUP"
1612 19,479 1
1620 20,299 1
1632 20 1
1652 20,104 2
1670 19,688 2
1691 19,388 3
1700 20,625 3
1712 21,172 4
1721 20,391 4
1740 20,313 5
1750 19,427 5
1770 19,492 6
1792 20,664 7


For the final result I used the following select statement:

CODE
WITH GROUP_SELECT
AS
(SELECT POS,
VALUE,
INGROUP
FROM TABLE(GET_MEASUR_VALUE_GROUP(1612, 1792, 20)))
SELECT DISTINCT
MIN(POS) OVER (PARTITION BY INGROUP) AS FROM_POS,
MAX(POS) OVER (PARTITION BY INGROUP) AS TO_POS,
AVG(VALUE) OVER (PARTITION BY INGROUP) AS AVG_VALUE
FROM GROUP_SELECT
ORDER BY FROM_POS, TO_POS


Result:
CODE
"FROM_POS" "TO_POS" "AVG_VALUE"
1612 1632 19,926
1652 1670 19,896
1691 1700 20,0065
1712 1721 20,7815
1740 1750 19,87
1770 1770 19,492
1792 1792 20,664


And now my question again: How can I achieve a grouping like my stored procedure does?
Is it possible to get the same result without the stored procedure.
I've read in your book "Advanced SQL Programming" about model statements.
May be it's possible to use model-statements for the solution without the stored-procedure/function.

Thanks for your help!

Regards Oskar

QUOTE (Laurent Schneider @ Sep 24 2010, 04:17 AM) *
I would start with moving average. To have the first value of a group, you can use first value. Then you can get the min and the max of each group.

I agree with Steven that the requirement could be clearer.

Just to start with your first question, here is my solution. Maybe this will give you some direction.

CODE
SQL> SELECT * FROM t

        ID      VALUE
---------- ----------
         1        1.2
         2        1.5
         9          2
        11          3
        18          1
        28        1.2


SQL> SELECT
  ID,
  AVG (VALUE) OVER
  (
    ORDER BY ID RANGE BETWEEN 3 PRECEDING AND 3 FOLLOWING
  ) AVG,
  FIRST_VALUE (ID IGNORE NULLS) OVER
  (
    ORDER BY ID RANGE BETWEEN 3 PRECEDING AND 3 FOLLOWING
  ) FIRST
FROM t

        ID        AVG      FIRST
---------- ---------- ----------
         1       1.35          1
         2       1.35          1
         9        2.5          9
        11        2.5          9
        18          1         18
        28        1.2         28


SQL> SELECT
  MIN(ID) FROM_ID,
  MAX(ID) TO_ID,
  AVG
FROM
(
  SELECT
    ID,
    AVG (VALUE) OVER
    (
      ORDER BY ID RANGE BETWEEN 3 PRECEDING AND 3 FOLLOWING
    ) AVG,
    FIRST_VALUE (ID IGNORE NULLS) OVER
    (
      ORDER BY ID RANGE BETWEEN 3 PRECEDING AND 3 FOLLOWING
    ) FIRST
  FROM t
)
GROUP BY FIRST, AVG
ORDER BY FROM_ID

   FROM_ID      TO_ID        AVG
---------- ---------- ----------
         1          2       1.35
         9         11        2.5
        18         18          1
        28         28        1.2
Go to the top of the page
 
+Quote Post
Laurent Schneide...
post Sep 27 2010, 02:55 AM
Post #7


Advanced Member
***

Group: Members
Posts: 243
Joined: 24-June 07
From: Switzerland
Member No.: 9,590



It is possible with MODEL indeed

CODE
select from_pos,max(pos) to_pos,avg(value)avg_value
from
(select *
FROM MEASUR_VALUE
WHERE POS between 1612 and 1792
model
dimension by (row_number() over (order by pos) r)
measures (pos,value, to_number(null) from_pos)
rules
(
from_pos[any] order by pos=case when from_pos[cv()-1]>=pos[cv()]-20 then from_pos[cv()-1] else pos[cv()] end
)
)
group by from_pos
order by from_pos




--------------------
Go to the top of the page
 
+Quote Post
oskar horch
post Sep 27 2010, 03:29 AM
Post #8


Newbie
*

Group: Members
Posts: 5
Joined: 23-September 10
Member No.: 43,901



Hi Laurent!

It works fine! Thats cool!

Please, could you explain your solution in a word or two? Why do you use the row_number function in the dimension?

Thanks a lot Laurent!

QUOTE (Laurent Schneider @ Sep 27 2010, 03:55 AM) *
It is possible with MODEL indeed

CODE
select from_pos,max(pos) to_pos,avg(value)avg_value
from
(select *
FROM MEASUR_VALUE
WHERE POS between 1612 and 1792
model
dimension by (row_number() over (order by pos) r)
measures (pos,value, to_number(null) from_pos)
rules
(
from_pos[any] order by pos=case when from_pos[cv()-1]>=pos[cv()]-20 then from_pos[cv()-1] else pos[cv()] end
)
)
group by from_pos
order by from_pos

Go to the top of the page
 
+Quote Post
Laurent Schneide...
post Sep 27 2010, 04:26 AM
Post #9


Advanced Member
***

Group: Members
Posts: 243
Joined: 24-June 07
From: Switzerland
Member No.: 9,590



QUOTE (oskar horch @ Sep 27 2010, 10:29 AM) *
Hi Laurent!

It works fine! Thats cool!

Please, could you explain your solution in a word or two? Why do you use the row_number function in the dimension?

Thanks a lot Laurent!



You are welcome.
With row_number() it was easy to check the last_value, with CV()-1.

It is quite difficult to "program" with MODEL, due to the numerous limitations of the clause.

In this example, I used row_number to access the previous row and then I summed outside of the model clause.

I did not find a solution without subquery because I could only form the groups after having identified the pos of the previous row.

The algorythm of the solution would be :
CODE
for i in pos
  if from_pos[i-1]>=pos[i]-20
  then #the first pos of the group is less than 20 difference with current row, we are still in the same group
    from_pos[i]=from_pos[i]-1
  else # new group
    from_pos[i]=pos[i]

then the avg per group.


--------------------
Go to the top of the page
 
+Quote Post
oskar horch
post Sep 27 2010, 07:06 AM
Post #10


Newbie
*

Group: Members
Posts: 5
Joined: 23-September 10
Member No.: 43,901



Thanks! smile.gif
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: 18th December 2014 - 10:55 PM