Re: Help with running totals
Date: Wed, 07 Apr 2004 12:58:04 GMT
Message-ID: <wVScc.2381$8P2.2077_at_news-server.bigpond.net.au>
"paul" <psgoogle_at_sympatico.ca> wrote in message
news:b02b1597.0404060704.580e82e4_at_posting.google.com...
> thanks for the input
> what you have shown me is the inner query I have before the LAG
> -the default RANGE when not specified is UNBOUNDED PRECEDING
> which in this case gives me ENDING balances that I lag(1,0) to get
> opening balances. ie: 19903 open_bal=0, mon_ttl=-172527,
> end_bal=-172527
>
> What I want is to makeup data at the end of the range and keep the
> ending balance going forward which is a little different than my
> original request.
> your recognition that i want to dump the lag and show the
> ending_balance (and calculate the opening by taking off the current
> activity has helped.
> What I really want is this
>
> DT BUCKET RUN_TTL
> ------ ---------- ----------
> 199903 0192 -172527
> 199906 0192 374180
> 199909 0192 95496
> 199910 0192 1195635
> 199911 0192 1690914
> 199912 0192 0
> 200003 0192 1025431
> 200006 0192 -706247
> 200009 0192 1273009
> 200012 0192 0
> 200101 0192 0
> 200102 0192 0
> [..]
> 200301 0192 0
>
> How can 'project' that last row forward?
>
> I am trying in 10G now with partitioned outer joins, but that does not
> seem to work if going off of analytic queries, only straight base
> tables.
>
>
>
> douglashawthorne_at_yahoo.com.au (Douglas Hawthorne) wrote in message
news:<cf15dee4.0404051825.7a0760dc_at_posting.google.com>...
> > psgoogle_at_sympatico.ca (paul) wrote in message
news:<b02b1597.0404051011.43b5aead_at_posting.google.com>...
> > > I have a query that takes monthly totals and rolls them up to get a
> > > balance at a specific time code
> > >
> > > eg:
> > > dt bucket mon_ttl
> > > --- ------ -------
> > > 199903 0192 -172527
> > > 199906 0192 546707
> > > 199909 0192 -278684
> > > 199910 0192 1100139
> > > 199911 0192 495279
> > > 199912 0192 -1690914
> > > 200003 0192 1025431
> > > 200006 0192 -1731678
> > > 200009 0192 1979256
> > > 200012 0192 -1273009
> > >
> > >
> > >
> > > Rolls up like
> > >
> > > DT BUCKET OPEN
> > > --------- ---------- ----------
> > > 199903 0192 0
> > > 199906 0192 -172527
> > > 199909 0192 374180 (-172527+546707)
> > > 199910 0192 95496 etc...
> > > 199911 0192 1195635
> > > 199912 0192 1690914
> > > 200003 0192 0
> > > 200006 0192 1025431
> > > 200009 0192 -706247
> > > 200012 0192 1273009
> > >
> > > I get that using
> > >
> > > select dt, bucket, lag(run_ttl,1,0)
> > > over (order by dt) open
> > > from
> > > (select dt, bucket, ttl,sum(ttl)
> > > over (partition by bucket order by dt) run_ttl
> > > from
> > > mon_tot
> > > where bucket='0192')
> > >
> > > But I want to keep that last balance going forward...
> > > eg: if I want the open as at 200301 and 200012 was the last non-null
> > > balance
> > > I want to carry that forward.
> > > 200012 0192 1273009
> > > 200101 0192 1273009
> > > [..]
> > > 200301 0192 1273009
> > >
> > > But I need to 'make up' the data going forward
> > >
> > > I'm on 9.2.0.3 anyone have any tips/ideas?
> >
> >
> > You were almost there except you did not have the RANGE UNBOUNDED
> > PRECEDING clause. My suggestion is as follows:
> > SELECT
> > dt,
> > bucket,
> > SUM( ttl )
> > OVER(
> > PARTITION BY bucket
> > ORDER BY dt
> > RANGE UNBOUNDED PRECEDING
> > ) run_ttl
> > FROM
> > mon_tot
> > WHERE
> > bucket='0192'
> > ;
> >
> > My test results are:
> > DT BUCK RUN_TTL
> > ------ ---- ----------
> > 199903 0192 -172527
> > 199906 0192 374180
> > 199909 0192 95496
> > 199910 0192 1195635
> > 199911 0192 1690914
> > 199912 0192 0
> > 200003 0192 1025431
> > 200006 0192 -706247
> > 200009 0192 1273009
> > 200012 0192 0
> >
> > 10 rows selected.
> >
> > I found this hint on p.560 of "Expert One-on-One Oracle" by Thomas
> > Kyte (A-Press:2003). He also has an on-line example at
> >
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:305416600201
> >
> > Douglas Hawthorne
Paul,
What you want is a source of rows that have the generated DT and BUCKET columns with NULL for the TTL column. My suggested solution is to use a 'temporary' table to store the projected values of DT for every BUCKET value. I then modified my original query to be over an in-line view that is a UNION ALL of the original table and the projected values table.
First, I created the 'temporary' table to hold the projected values using
CTAS and selecting no rows:
CREATE TABLE projected_mon_tot
AS SELECT * FROM mon_tot
WHERE 1=0
;
Second, I populated this table based on the current values from the MON_TOT
table:
INSERT INTO projected_mon_tot
SELECT
TO_CHAR(
ADD_MONTHS(
TO_DATE(
t.base_dt,
'YYYYMM'
),
a.num_months
),
'YYYYMM'
) AS dt,
t.bucket,
NULL AS ttl
FROM
(
SELECT
rownum AS num_months
FROM
all_objects
WHERE
rownum <= 25
) a,
(
SELECT
MAX( dt ) AS base_dt,
bucket
FROM
mon_tot
GROUP BY
bucket
) t
;
Here I am projecting 25 months past the last DT value for each value in the
BUCKET column by using a cartesian join between a range of row numbers from
the ALL_OBJECTS table and the calculated maximum value of DT for each value
of BUCKET. I have assumed that the DT column is of data type, VARCHAR2 or
CHAR.
Third, I ran the modified query:
SELECT
dt,
bucket,
SUM( ttl )
OVER(
PARTITION BY bucket
ORDER BY dt
) run_ttl
FROM
(
SELECT
*
FROM
mon_tot
UNION ALL
SELECT
*
FROM
projected_mon_tot
)
WHERE
bucket='0192'
;
The only difference between this query and my original one is the use of the in-line view with the UNION ALL. I used UNION ALL because I know that there are no overlaps in values between the two tables.
Thanks for pointing out that RANGE UNBOUNDED PRECEEDING is the default.
The result is:
DT BUCK RUN_TTL
------ ---- ----------
199903 0192 -172527
199906 0192 374180 199909 0192 95496
199910 0192 1195635
199911 0192 1690914
199912 0192 0
200003 0192 1025431
200006 0192 -706247
200009 0192 1273009
200012 0192 0 200101 0192 0 200102 0192 0 200103 0192 0 200104 0192 0 200105 0192 0 200106 0192 0 200107 0192 0 200108 0192 0 200109 0192 0 200110 0192 0 200111 0192 0 200112 0192 0 200201 0192 0 200202 0192 0 200203 0192 0 200204 0192 0 200205 0192 0 200206 0192 0 200207 0192 0 200208 0192 0 200209 0192 0 200210 0192 0 200211 0192 0 200212 0192 0 200301 0192 0
35 rows selected.
Douglas Hawthorne Received on Wed Apr 07 2004 - 14:58:04 CEST