The version of the Oracle database at work is: 'Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production"
Here's my dilemma for a query I am trying to produce that will be used as the basis for a BI (Business Intelligence) dashboard and graph on Tableau.
I am trying to calculate a running sum over time based on rank but the problem is values come in and out based on time, thus the ranked value of a summed value changes.
Here's my table and data and SQL query and current output and desired out
DDL
drop table business_data cascade constraints purge;
create table business_data
(
dt_time timestamp(6),
lob varchar2(20 byte),
business_flow varchar2(80 byte),
region varchar2(20 byte),
app varchar2(32 byte),
attribute varchar2(100 byte),
attribute_type varchar2(4 byte),
value number
);
DML
set define off
insert into business_data values (to_timestamp('09-05-2018 07:00:16.000000', 'mm-dd-yyyy hh24:mi:ss.ff'), 'Liquidity', 'Portfolio', 'EMEA', 'App2', 'MarketOrders', 'C', 0);
insert into business_data values (to_timestamp('09-05-2018 06:00:16.000000', 'mm-dd-yyyy hh24:mi:ss.ff'), 'Liquidity', 'Portfolio', 'APR', 'App1', 'MarketOrders', 'C', 25011);
insert into business_data values (to_timestamp('09-05-2018 06:00:16.000000', 'mm-dd-yyyy hh24:mi:ss.ff'), 'Liquidity', 'Portfolio', 'JAPAN', 'App1', 'MarketOrders', 'C', 4222);
insert into business_data values (to_timestamp('09-05-2018 06:00:16.000000', 'mm-dd-yyyy hh24:mi:ss.ff'), 'Liquidity', 'Portfolio', 'APR', 'App3', 'MarketOrders', 'C', 832);
insert into business_data values (to_timestamp('09-05-2018 06:00:16.000000', 'mm-dd-yyyy hh24:mi:ss.ff'), 'Liquidity', 'Portfolio', 'JAPAN', 'App3', 'MarketOrders', 'C', 322);
insert into business_data values (to_timestamp('09-05-2018 07:30:14.000000', 'mm-dd-yyyy hh24:mi:ss.ff'), 'Liquidity', 'Portfolio', 'EMEA', 'App2', 'MarketOrders', 'C', 693);
insert into business_data values (to_timestamp('09-05-2018 06:30:11.000000', 'mm-dd-yyyy hh24:mi:ss.ff'), 'Liquidity', 'Portfolio', 'APR', 'App1', 'MarketOrders', 'C', 26487);
insert into business_data values (to_timestamp('09-05-2018 06:30:11.000000', 'mm-dd-yyyy hh24:mi:ss.ff'), 'Liquidity', 'Portfolio', 'JAPAN', 'App1', 'MarketOrders', 'C', 4656);
insert into business_data values (to_timestamp('09-05-2018 06:30:11.000000', 'mm-dd-yyyy hh24:mi:ss.ff'), 'Liquidity', 'Portfolio', 'APR', 'App3', 'MarketOrders', 'C', 833);
insert into business_data values (to_timestamp('09-05-2018 06:30:11.000000', 'mm-dd-yyyy hh24:mi:ss.ff'), 'Liquidity', 'Portfolio', 'JAPAN', 'App3', 'MarketOrders', 'C', 322);
insert into business_data values (to_timestamp('09-05-2018 07:00:21.000000', 'mm-dd-yyyy hh24:mi:ss.ff'), 'Liquidity', 'Portfolio', 'APR', 'App1', 'MarketOrders', 'C', 27106);
insert into business_data values (to_timestamp('09-05-2018 07:00:21.000000', 'mm-dd-yyyy hh24:mi:ss.ff'), 'Liquidity', 'Portfolio', 'JAPAN', 'App1', 'MarketOrders', 'C', 4958);
insert into business_data values (to_timestamp('09-05-2018 07:00:21.000000', 'mm-dd-yyyy hh24:mi:ss.ff'), 'Liquidity', 'Portfolio', 'APR', 'App3', 'MarketOrders', 'C', 833);
insert into business_data values (to_timestamp('09-05-2018 07:00:21.000000', 'mm-dd-yyyy hh24:mi:ss.ff'), 'Liquidity', 'Portfolio', 'JAPAN', 'App3', 'MarketOrders', 'C', 322);
insert into business_data values (to_timestamp('09-05-2018 08:30:12.000000', 'mm-dd-yyyy hh24:mi:ss.ff'), 'Liquidity', 'Portfolio', 'EMEA', 'App2', 'MarketOrders', 'C', 6982);
insert into business_data values (to_timestamp('09-05-2018 07:30:10.000000', 'mm-dd-yyyy hh24:mi:ss.ff'), 'Liquidity', 'Portfolio', 'APR', 'App1', 'MarketOrders', 'C', 27486);
insert into business_data values (to_timestamp('09-05-2018 07:30:10.000000', 'mm-dd-yyyy hh24:mi:ss.ff'), 'Liquidity', 'Portfolio', 'JAPAN', 'App1', 'MarketOrders', 'C', 4958);
insert into business_data values (to_timestamp('09-05-2018 07:30:10.000000', 'mm-dd-yyyy hh24:mi:ss.ff'), 'Liquidity', 'Portfolio', 'APR', 'App3', 'MarketOrders', 'C', 834);
insert into business_data values (to_timestamp('09-05-2018 07:30:10.000000', 'mm-dd-yyyy hh24:mi:ss.ff'), 'Liquidity', 'Portfolio', 'JAPAN', 'App3', 'MarketOrders', 'C', 322);
insert into business_data values (to_timestamp('09-05-2018 09:00:17.000000', 'mm-dd-yyyy hh24:mi:ss.ff'), 'Liquidity', 'Portfolio', 'EMEA', 'App2', 'MarketOrders', 'C', 9585);
insert into business_data values (to_timestamp('09-05-2018 08:00:12.000000', 'mm-dd-yyyy hh24:mi:ss.ff'), 'Liquidity', 'Portfolio', 'APR', 'App1', 'MarketOrders', 'C', 28032);
insert into business_data values (to_timestamp('09-05-2018 08:00:12.000000', 'mm-dd-yyyy hh24:mi:ss.ff'), 'Liquidity', 'Portfolio', 'JAPAN', 'App1', 'MarketOrders', 'C', 4663);
insert into business_data values (to_timestamp('09-05-2018 08:00:12.000000', 'mm-dd-yyyy hh24:mi:ss.ff'), 'Liquidity', 'Portfolio', 'APR', 'App3', 'MarketOrders', 'C', 836);
insert into business_data values (to_timestamp('09-05-2018 08:00:12.000000', 'mm-dd-yyyy hh24:mi:ss.ff'), 'Liquidity', 'Portfolio', 'JAPAN', 'App3', 'MarketOrders', 'C', 322);
insert into business_data values (to_timestamp('09-05-2018 09:30:12.000000', 'mm-dd-yyyy hh24:mi:ss.ff'), 'Liquidity', 'Portfolio', 'EMEA', 'App2', 'MarketOrders', 'C', 9913);
insert into business_data values (to_timestamp('09-05-2018 08:30:11.000000', 'mm-dd-yyyy hh24:mi:ss.ff'), 'Liquidity', 'Portfolio', 'APR', 'App1', 'MarketOrders', 'C', 28479);
insert into business_data values (to_timestamp('09-05-2018 08:30:11.000000', 'mm-dd-yyyy hh24:mi:ss.ff'), 'Liquidity', 'Portfolio', 'JAPAN', 'App1', 'MarketOrders', 'C', 4737);
insert into business_data values (to_timestamp('09-05-2018 08:30:11.000000', 'mm-dd-yyyy hh24:mi:ss.ff'), 'Liquidity', 'Portfolio', 'APR', 'App3', 'MarketOrders', 'C', 863);
insert into business_data values (to_timestamp('09-05-2018 08:30:11.000000', 'mm-dd-yyyy hh24:mi:ss.ff'), 'Liquidity', 'Portfolio', 'JAPAN', 'App3', 'MarketOrders', 'C', 322);
insert into business_data values (to_timestamp('09-05-2018 10:00:17.000000', 'mm-dd-yyyy hh24:mi:ss.ff'), 'Liquidity', 'Portfolio', 'EMEA', 'App2', 'MarketOrders', 'C', 10420);
insert into business_data values (to_timestamp('09-05-2018 09:00:11.000000', 'mm-dd-yyyy hh24:mi:ss.ff'), 'Liquidity', 'Portfolio', 'APR', 'App1', 'MarketOrders', 'C', 28634);
insert into business_data values (to_timestamp('09-05-2018 09:00:11.000000', 'mm-dd-yyyy hh24:mi:ss.ff'), 'Liquidity', 'Portfolio', 'JAPAN', 'App1', 'MarketOrders', 'C', 4737);
insert into business_data values (to_timestamp('09-05-2018 09:00:11.000000', 'mm-dd-yyyy hh24:mi:ss.ff'), 'Liquidity', 'Portfolio', 'APR', 'App3', 'MarketOrders', 'C', 865);
insert into business_data values (to_timestamp('09-05-2018 09:00:11.000000', 'mm-dd-yyyy hh24:mi:ss.ff'), 'Liquidity', 'Portfolio', 'JAPAN', 'App3', 'MarketOrders', 'C', 322);
insert into business_data values (to_timestamp('09-05-2018 10:30:13.000000', 'mm-dd-yyyy hh24:mi:ss.ff'), 'Liquidity', 'Portfolio', 'EMEA', 'App2', 'MarketOrders', 'C', 10987);
insert into business_data values (to_timestamp('09-05-2018 09:30:08.000000', 'mm-dd-yyyy hh24:mi:ss.ff'), 'Liquidity', 'Portfolio', 'APR', 'App1', 'MarketOrders', 'C', 28732);
insert into business_data values (to_timestamp('09-05-2018 09:30:08.000000', 'mm-dd-yyyy hh24:mi:ss.ff'), 'Liquidity', 'Portfolio', 'JAPAN', 'App1', 'MarketOrders', 'C', 4737);
insert into business_data values (to_timestamp('09-05-2018 09:30:08.000000', 'mm-dd-yyyy hh24:mi:ss.ff'), 'Liquidity', 'Portfolio', 'APR', 'App3', 'MarketOrders', 'C', 866);
insert into business_data values (to_timestamp('09-05-2018 09:30:08.000000', 'mm-dd-yyyy hh24:mi:ss.ff'), 'Liquidity', 'Portfolio', 'JAPAN', 'App3', 'MarketOrders', 'C', 322);
insert into business_data values (to_timestamp('09-05-2018 11:00:18.000000', 'mm-dd-yyyy hh24:mi:ss.ff'), 'Liquidity', 'Portfolio', 'EMEA', 'App2', 'MarketOrders', 'C', 12108);
insert into business_data values (to_timestamp('09-05-2018 10:00:12.000000', 'mm-dd-yyyy hh24:mi:ss.ff'), 'Liquidity', 'Portfolio', 'APR', 'App1', 'MarketOrders', 'C', 28732);
insert into business_data values (to_timestamp('09-05-2018 10:00:12.000000', 'mm-dd-yyyy hh24:mi:ss.ff'), 'Liquidity', 'Portfolio', 'JAPAN', 'App1', 'MarketOrders', 'C', 4737);
insert into business_data values (to_timestamp('09-05-2018 10:00:12.000000', 'mm-dd-yyyy hh24:mi:ss.ff'), 'Liquidity', 'Portfolio', 'APR', 'App3', 'MarketOrders', 'C', 866);
insert into business_data values (to_timestamp('09-05-2018 10:00:12.000000', 'mm-dd-yyyy hh24:mi:ss.ff'), 'Liquidity', 'Portfolio', 'JAPAN', 'App3', 'MarketOrders', 'C', 322);
insert into business_data values (to_timestamp('09-05-2018 11:30:13.000000', 'mm-dd-yyyy hh24:mi:ss.ff'), 'Liquidity', 'Portfolio', 'EMEA', 'App2', 'MarketOrders', 'C', 13922);
insert into business_data values (to_timestamp('09-05-2018 10:30:11.000000', 'mm-dd-yyyy hh24:mi:ss.ff'), 'Liquidity', 'Portfolio', 'APR', 'App1', 'MarketOrders', 'C', 28748);
insert into business_data values (to_timestamp('09-05-2018 10:30:11.000000', 'mm-dd-yyyy hh24:mi:ss.ff'), 'Liquidity', 'Portfolio', 'JAPAN', 'App1', 'MarketOrders', 'C', 4737);
insert into business_data values (to_timestamp('09-05-2018 10:30:11.000000', 'mm-dd-yyyy hh24:mi:ss.ff'), 'Liquidity', 'Portfolio', 'APR', 'App3', 'MarketOrders', 'C', 866);
insert into business_data values (to_timestamp('09-05-2018 10:30:11.000000', 'mm-dd-yyyy hh24:mi:ss.ff'), 'Liquidity', 'Portfolio', 'JAPAN', 'App3', 'MarketOrders', 'C', 322);
insert into business_data values (to_timestamp('09-05-2018 12:00:17.000000', 'mm-dd-yyyy hh24:mi:ss.ff'), 'Liquidity', 'Portfolio', 'EMEA', 'App2', 'MarketOrders', 'C', 16215);
insert into business_data values (to_timestamp('09-05-2018 12:30:12.000000', 'mm-dd-yyyy hh24:mi:ss.ff'), 'Liquidity', 'Portfolio', 'EMEA', 'App2', 'MarketOrders', 'C', 17041);
insert into business_data values (to_timestamp('09-05-2018 13:00:18.000000', 'mm-dd-yyyy hh24:mi:ss.ff'), 'Liquidity', 'Portfolio', 'EMEA', 'App2', 'MarketOrders', 'C', 18413);
insert into business_data values (to_timestamp('09-05-2018 12:30:47.000000', 'mm-dd-yyyy hh24:mi:ss.ff'), 'Liquidity', 'Portfolio', 'AMRS', 'App4', 'ChildOrders', 'C', 788);
insert into business_data values (to_timestamp('09-05-2018 12:35:14.000000', 'mm-dd-yyyy hh24:mi:ss.ff'), 'Liquidity', 'Portfolio', 'AMRS', 'App4', 'ChildOrders', 'C', 794);
insert into business_data values (to_timestamp('09-05-2018 12:40:20.000000', 'mm-dd-yyyy hh24:mi:ss.ff'), 'Liquidity', 'Portfolio', 'AMRS', 'App4', 'ChildOrders', 'C', 905);
insert into business_data values (to_timestamp('09-05-2018 12:45:30.000000', 'mm-dd-yyyy hh24:mi:ss.ff'), 'Liquidity', 'Portfolio', 'AMRS', 'App4', 'ChildOrders', 'C', 932);
insert into business_data values (to_timestamp('09-05-2018 12:50:17.000000', 'mm-dd-yyyy hh24:mi:ss.ff'), 'Liquidity', 'Portfolio', 'AMRS', 'App4', 'ChildOrders', 'C', 932);
insert into business_data values (to_timestamp('09-05-2018 12:55:11.000000', 'mm-dd-yyyy hh24:mi:ss.ff'), 'Liquidity', 'Portfolio', 'AMRS', 'App4', 'ChildOrders', 'C', 970);
commit;
Here's the SQL stages. At 07:00;15 seconds, currently there are cumulative summed up values for 2 regions (JAPAN, APR [i.e. Asia Pacific Region]) and they're dense ranked partitioned by 3 columns (currently only 2 rankings)
SQL
set serveroutput on size unlimited -
linesize 180
column dt_time format a20
column lob format a10
column business_flow format a13
column region format a6
column cume_sum_value format 99999
column myrank format 999
select to_char(bd.dt_time, 'mm-dd-yyyy hh24:mi:ss') as dt_time
,bd.lob
,bd.business_flow
,bd.region
,sum(bd.value) as sum_value
,dense_rank() over (
partition by bd.lob
,bd.business_flow
,bd.region
order by bd.dt_time desc
) as myrank
from business_data bd
where 1 = 1
and bd.dt_time between trunc(bd.dt_time)
and trunc(bd.dt_time)
+ interval '07' hour
+ interval '00' minute
+ interval '15' second
group by bd.dt_time
,bd.lob
,bd.business_flow
,bd.region
order by bd.dt_time desc
,myrank asc;
Output
DT_TIME LOB BUSINESS_FLOW REGION SUM_VALUE MYRANK
-------------------- ---------- ------------- ------ ---------- ------
09-05-2018 06:30:11 Liquidity Portfolio JAPAN 4978 1
09-05-2018 06:30:11 Liquidity Portfolio APR 27320 1
09-05-2018 06:00:16 Liquidity Portfolio JAPAN 4544 2
09-05-2018 06:00:16 Liquidity Portfolio APR 25843 2
Now if I increase the between clause end range by 1 (from 15 to 16), and re-run the query above, then a data point for a NEW region (EMEA) shows up but no other regions have data points.
+ interval '16' second
+ interval '15' second+ interval '15' second
Output
DT_TIME LOB BUSINESS_FLOW REGION SUM_VALUE MYRANK
-------------------- ---------- ------------- ------ ---------- ------
09-05-2018 07:00:16 Liquidity Portfolio EMEA 0 1
09-05-2018 06:30:11 Liquidity Portfolio JAPAN 4978 1
09-05-2018 06:30:11 Liquidity Portfolio APR 27320 1
09-05-2018 06:00:16 Liquidity Portfolio APR 25843 2
09-05-2018 06:00:16 Liquidity Portfolio JAPAN 4544 2
So far, so good, now there are 3 different regions with a rank of 1 at the latest point in time.
Now if I increase the between clause end range by 5 seconds (from 16 to 21), and re-run the query above, then a data point for a the existing regions (JAPAN, APR) shows up but none for EMEA
Output
DT_TIME LOB BUSINESS_FLOW REGION SUM_VALUE MYRANK
-------------------- ---------- ------------- ------ ---------- ------
09-05-2018 07:00:21 Liquidity Portfolio APR 27939 1
09-05-2018 07:00:21 Liquidity Portfolio JAPAN 5280 1
09-05-2018 07:00:16 Liquidity Portfolio EMEA 0 1
09-05-2018 06:30:11 Liquidity Portfolio APR 27320 2
09-05-2018 06:30:11 Liquidity Portfolio JAPAN 4978 2
09-05-2018 06:00:16 Liquidity Portfolio JAPAN 4544 3
09-05-2018 06:00:16 Liquidity Portfolio APR 25843 3
Desired Output -- Is this possible? To keep that previously ranked #1 row but as #2 as it ages out, yet also keep the row as #1 because currently there are no new data values at 7:00:21 seconds for region EMEA
DT_TIME LOB BUSINESS_FLOW REGION SUM_VALUE MYRANK
-------------------- ---------- ------------- ------ ---------- ------
09-05-2018 07:00:21 Liquidity Portfolio APR 27939 1
09-05-2018 07:00:21 Liquidity Portfolio JAPAN 5280 1
09-05-2018 07:00:16 Liquidity Portfolio EMEA 0 1
09-05-2018 07:00:16 Liquidity Portfolio EMEA 0 2 <-- Added this new row by hand to show the desired dataset
09-05-2018 06:30:11 Liquidity Portfolio APR 27320 2
09-05-2018 06:30:11 Liquidity Portfolio JAPAN 4978 2
09-05-2018 06:00:16 Liquidity Portfolio JAPAN 4544 3
09-05-2018 06:00:16 Liquidity Portfolio APR 25843 3