Hello Tom,
I am using following code to get average of group data partition by group id. I am getting correct values for current group with following code.
I am using Oracle 12.1.0.2.0
I want difference in average of current group(partition) - average of previous group(partition)
My code to get current group Average is
select
case
when LAG(CTGE."GROUP_ID", 1, -1) OVER (ORDER BY CTGE.ORDER_INDEX ASC) != CTGE."GROUP_ID" then
AVG(BI_COMET_ASSAY_REPORT.ANALYSE_PARAMETER_PER_ANIMAL(CSMA.ASSAY_ID, CSMAN.ANIMAL_ID, BI_COMET_ASSAY_REPORT.GET_TISSUE_BY_INDEX(CSMA.ASSAY_ID, 1), 'TAIL_INTENSITY', 'AVG','AVG')) over (PARTITION BY CTGE."GROUP_ID" ORDER BY CTGE.ORDER_INDEX ASC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
end as GROUP_MEAN,
case
when LAG(CTGE."GROUP_ID", 1, -1) OVER (ORDER BY CTGE.ORDER_INDEX ASC) != CTGE."GROUP_ID" then
AVG(BI_COMET_ASSAY_REPORT.ANALYSE_PARAMETER_PER_ANIMAL(CSMA.ASSAY_ID, CSMAN.ANIMAL_ID, BI_COMET_ASSAY_REPORT.GET_TISSUE_BY_INDEX(CSMA.ASSAY_ID, 1), 'TAIL_INTENSITY', 'AVG','AVG')) over (PARTITION BY CTGE."GROUP_ID" ORDER BY CTGE.ORDER_INDEX ASC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
end as GROUP_MEAN_DIFFERENCE
from
CSM_ASSAYS CSMA,
CSM_ANIMALS CSMAN,
CSM_TREATMENT_GROUPS CTG,
CSM_TREATMENT_GROUPS_EXPTS CTGE
where
CSMA.ASSAY_ID = 1 and
CSMAN."GROUP_ID" = CTG."GROUP_ID" and
CTG."GROUP_ID" = CTGE."GROUP_ID" and
CTGE.EXPERIMENT_ID = CSMA.EXPT_ID and
CSMAN.IS_MALE = 1 and
CSMAN.IS_INVALID = 0 and
BI_COMET_ASSAY_REPORT.GET_TISSUE_BY_INDEX(CSMA.ASSAY_ID, 1) is not null
order by
CTGE.ORDER_INDEX ASC,
TO_NUMBER(CSMAN.ANIMAL_CODE) ASC;Now I want something like :
Mean | Group Mean | Group Mean Difference
44.520 | 47.676 | -
47.760 | |
50.107 | |
48.353 | |
47.640 | |
----------------------------------------------------
48.353 | 48.696 | 1.02
50.447 | |
51.967 | |
45.800 | |
46.913 | |
AVG( this partitioned group) - Avg( previous partition group)
In this case I need ( 48.696 - 47.676 ) to compute in GROUP_MEAN_DIFFERENCE column.
Can you please assist?
How can I achieve this?
To get the difference between the means while still viewing all the rows, you can:
1: Get the avg within the group with avg(...) over (partition by ...)
2: Use last_value to find the avg from the previous group.
Lag doesn't work at step 2 because it operates on rows. You don't know how many there are in each group. With last_value you can use the windowing clause:
range between unbounded preceding and 1 preceding
If you order by your grouping column, this will search all the rows up to the value of the current group - 1. Put it all together and you get:
with rws as (
select rownum x, mod(rownum, 2) y from dual connect by level <= 10
), avgs as (
select x, y, avg(x) over (partition by y) mean from rws
)
select x, y, mean,
last_value(mean) over (
order by y range between unbounded preceding and 1 preceding
) prev_mean,
mean - last_value(mean) over (
order by y range between unbounded preceding and 1 preceding
) mean_diff
from avgs;
X Y MEAN PREV_MEAN MEAN_DIFF
4 0 6
8 0 6
2 0 6
6 0 6
10 0 6
9 1 5 6 -1
7 1 5 6 -1
3 1 5 6 -1
1 1 5 6 -1
5 1 5 6 -1