| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> SQL: "periodicity" + grouping
Hello,
I implemented the following script for creating some basic and unary "user-defined" aggregations from a transaction table. The basic idea was just to aggregate grouping by N consecutive transactions (period length) in descending order, that is counting, 64 consecutive transactions for this specific demonstration (this kind of code is generated from a "PL/SQL engine" so the N is configurable), this was rather easy to achieve using Analytic Functions (see 4.1 bellow).
The problem I am facing now is that in addition to the period length (counting N consecutive transactions) I was introduced also the periodicity (P1) i.e. instead of getting in descending order the next chunk/group of N transactions (not overlapping), move using the booking_date by periods of days/weeks/months/quarters/years that could easily be expressed in days and then get for each the last N (64) consecutive transactions from exactly each of the computed absolute points in time, which might make it overlapping but not necessarily depending on period length and periodicity length. Additionally this periodicity should start from an absolute date (starting date) which is the first time the "PL/SQL engine" created the script.
Here is a descriptive example I can only express in words unfortunatly :-(
Given a fixed date e.g. P1=07/15/2003 compute weekly (as 7 days) the aggregations grouping the last 64 transactions from each absolute point in time as result of selecting the M absolute dates that correspond to all weeks after the P1 starting date:
min(booking_date) sum(amount) count(*)
1 07/15/2003 57657.75CHF 64 2 07/22/2003 47657.75CHF 64 3 08/05/2003 34436.00CHF 64 4 08/12/2003 47634.87CHF 64 5 08/19/2003 85765.33CHF 64 5 08/26/2003 86765.78CHF 64
Thanks in advance,
Best regards,
Giovanni
--/******** Script start ***********/
--/****************************************************/--/*****(1) Materialized view of transactions table ***/
--/****************************************************/
create or replace view V_TRANSACTION_IMPORT
(booking_date, amount, sign, cash, main_account,
related_account, related_party_country, main_account_owner)
as
SELECT /*+ ordered use_hash (T_TRANSACTION2) INDEX (T_TRANSACTION2
T_TRANSACTION_SOURCE_ACCOUN_IN) */
booking_date
, volume_org AS amount
, '-' AS sign /* OUTGOING... */
, NVL(TRIM(info), 'NONE') AS cash
, source_account AS main_account
, NVL(TO_CHAR(target_account)
, beneficiary_bank || '-' || beneficiary_account_no) AS related_account
, NVL(TRIM(beneficiary_country_code), 'NONE') AS related_party_country
, party_id AS main_account_owner
FROM t_account_party
, T_TRANSACTION2
WHERE source_account > 0
AND T_TRANSACTION2.source_account = t_account_party.account_id
AND account_party_type_code = 'OWNER'
UNION ALL
SELECT /*+ ordered index (T_TRANSACTION2 t_transaction_target_accoun_in) */
booking_date
, volume_org AS amount
, '+' AS sign /* INCOMING... */
, NVL(TRIM(info), 'NONE') AS cash
, target_account AS main_account
, NVL(TO_CHAR(source_account)
, originate_bank || '-' || originate_account_no) AS related_account
, NVL(TRIM(originate_account_no), 'NONE') AS related_party_country
, party_id AS main_account_owner
FROM t_account_party
, T_TRANSACTION2
WHERE target_account > 0
AND T_TRANSACTION2.target_account = t_account_party.account_id
AND account_party_type_code = 'OWNER'
/
--/****************************************************/ --/********(2) Required sequence **********************/ --/****************************************************/
create sequence KDPAE_SEQUC_MARE64
increment by 1
start with 1
nomaxvalue
minvalue 1
nocycle
cache 5000000
/
--/****************************************************/ --/********(3) Target table ***************************/ --/****************************************************/
create table KDPAE_TABLAGG_MARE64 (
id number,
main_account number,
related_account varchar2(100),
countintv64 number,
mindate date,
abs_all_amount number, avg_incoming_amount number, avg_outgoing_amount number,
--/****************************************************/ --/********(4) Loading procedure **********************/ --/****************************************************/
create or replace procedure KDPAE_PROCAGG_MARE64(ipfrom_date in date default
null, ipto_date in date := sysdate)
as
--/************ Arbitrary date in the past ************/ pvfrom_date date := nvl(ipfrom_date, to_date('01.01.1900', 'dd.mm.yyyy'));
begin
--/****************************************************/ --/********(4.1) Aggregations building ****************/ --/****************************************************/insert /*+ append */ into KDPAE_TABLAGG_MARE64 select KDPAE_SEQUC_MARE64.nextval as id, t1.* from ( select
main_account,
related_account,
trunc(rnum/65) as countintv64,
min(booking_date) as mindate,
(sum(abs(amount - avg_all_amount))) as abs_all_amount,
avg(decode(sign, '+', 1, null) * amount) as avg_incoming_amount, avg(decode(sign, '-', 1, null) * amount) as avg_outgoing_amount, count(decode(sign, '+', 1, null) * amount) as count_incoming_amount, count(decode(sign, '-', 1, null) * amount) as count_outgoing_amount,stddev_samp(decode(sign, '+', 1, null) * amount) as stddev_samp_incoming_amount,
row_number() over (partition by main_account, related_account order by booking_date desc) as rnum,
avg(amount) over (partition by main_account, related_account) as
avg_all_amount
from MV_TRANSACTION_IMPORT
order by main_account, related_account)
group by main_account, related_account, trunc(rnum/65)) t1;
end;
/
Received on Fri May 23 2003 - 03:07:37 CDT
 |
 |