creating a time series table...

Hello all,

I am working in a tool for generating analytic tables to which apply Data Mining algorithms. There is a special kind of tables I need to create which in addition to the grouping criteria:

GROUP BY Account_ID, Person_ID, Month

also include a time series corresponding to that grouping which consists in a "inferior" time unit i.e. For Month grouping then I can use time series of Weeks or Days, for Year grouping I can use Month, Weeks or Days, ect. So that the final thing is that time series appear as additional columns.

Example table:

• Basic data columns first and day time serie columns data after... Account_ID Person_ID Month Avg_balance Avg_balance_Day1 Avg_balance_Day2 ... Avg_balance_DayN 1 1 1 15000CHF 13987CHF 14987CHF 18987CHF 1 1 2 16000CHF 16600CHF 15000CHF 20000CHF etc..

Now the problem I have is again the SELECT SQL statements for populating this table since I need it to be only one SELECT to collect all data for inserting
having that these Analytic tables lack of any unique keys (I can not do N*M N serie size and M number of rows updates as separate steps) or would be quite complex and hard to maintain afterwards.

There is also another requirement which is to smooth the function of Avg_balance of days so that it doesn't have "rough peaks", this is done by calculating the average on a sliding window of the data.

e.g.

Avg_balance_Day1 is the avg(day1)
Avg_balance_Day2 is the avg(day1 + day2)
Avg_balance_Day3 is the avg(day1 + day2 + day3)
Avg_balance_Day4 is the avg(day2 + day3 + day4)

so the required statement would look like this:

INSERT INTO analytic_table1
SELECT
  Account_ID
, Person_ID
, Month
, avg(balance) as Avg_balance
, (???) as Avg_balance_Day1
, (???) as Avg_balance_Day2

...
,
, (???) as Avg_balance_DayN

FROM transactions
GROUP BY Account_ID, Person_ID, Month

my idea of the ??? is:
avg(amount) over (partition by Account_ID, Person_ID, Month

                            order by Day
                            range 3 preceding)

but this doesn't work because amount in this case amount is not part of the outer GROUP BY clause... also because I don't want to generate more rows than 1 for each time serie column and I don't have a way to limit the ROWNUM of how many rows the analytic function should process, in this case should be 5 to make sure it doesn't split in max 31/5 = 6 rows per month.

TIA,
Best Regards,
Giovanni Received on Tue Jan 14 2003 - 09:20:39 CST