5e.3.526. Resource: Block Volumes, between 2020-04-19 and 2020-06-29 (OCI360_USAGECOSTS)
Notes:
1) Values are approximated
2) Hovering on the bars show more info.
3) Bars in purple are estimations using linear regression from previous values.
Name Null? Type
----------------------------------------------------------------------------------- -------- --------------------------------------------------------
COSTS$UNITPRICE NUMBER
COSTS$OVERAGESFLAG VARCHAR2(1)
COSTS$COMPUTEDAMOUNT NUMBER
COSTS$COMPUTEDQUANTITY NUMBER
CURRENCY VARCHAR2(4)
QUANTITY NUMBER
ENDTIMEUTC VARCHAR2(32)
SERVICENAME VARCHAR2(32)
DATACENTERID VARCHAR2(16)
GSIPRODUCTID VARCHAR2(8)
RESOURCENAME VARCHAR2(64)
STARTTIMEUTC VARCHAR2(32)
SUBSCRIPTIONID VARCHAR2(8)
SUBSCRIPTIONTYPE VARCHAR2(16)
LASTCOMPUTATIONDATE VARCHAR2(32)
SERVICEENTITLEMENTID VARCHAR2(16)
WITH t1 AS ( /* Cost of the resource group by ENDTIME */
SELECT SUM(COSTS$COMPUTEDAMOUNT) COMPUTEDAMOUNT,
TO_TIMESTAMP(ENDTIMEUTC,'YYYY-MM-DD"T"HH24:MI:SS.FF6TZH:TZM') ENDTIMEUTC
FROM OCI360_USAGECOSTS
WHERE RESOURCENAME = 'PIC_BLOCK_STORAGE_STANDARD'
GROUP BY TO_TIMESTAMP(ENDTIMEUTC,'YYYY-MM-DD"T"HH24:MI:SS.FF6TZH:TZM')
),
trange as ( /* Min and Max range of the costs */
select trunc(min(TO_TIMESTAMP(ENDTIMEUTC,'YYYY-MM-DD"T"HH24:MI:SS.FF6TZH:TZM')),'HH24') min,
trunc(max(TO_TIMESTAMP(ENDTIMEUTC,'YYYY-MM-DD"T"HH24:MI:SS.FF6TZH:TZM')),'HH24') max
FROM OCI360_USAGECOSTS
),
tcycle_account AS (
SELECT /*+ materialize */
TO_TIMESTAMP(PURCHASE$PURCHASEDRESOURCES$STARTDATE,'YYYY-MM-DD"T"HH24:MI:SS.FF6TZH:TZM') STARTDATE,
TO_TIMESTAMP(PURCHASE$PURCHASEDRESOURCES$ENDDATE,'YYYY-MM-DD"T"HH24:MI:SS.FF6TZH:TZM') ENDDATE,
rank() over (order by TO_TIMESTAMP(PURCHASE$PURCHASEDRESOURCES$STARTDATE,'YYYY-MM-DD"T"HH24:MI:SS.FF6TZH:TZM')) seq
FROM OCI360_ACCOUNTDETAILS, trange
WHERE PURCHASE$ID is not null
AND PURCHASE$PURCHASEDRESOURCES$NAME = 'CLOUD_CREDIT_AMOUNT'
ORDER BY STARTDATE
),
tcycle_gaps AS (
SELECT CAST(ADD_MONTHS(LAST_START,ROWNUM) AS TIMESTAMP) STARTDATE,
CAST(ADD_MONTHS(LAST_END,ROWNUM) AS TIMESTAMP) ENDDATE
FROM (SELECT MAX(STARTDATE) LAST_START, MAX(ENDDATE) LAST_END, CEIL(MONTHS_BETWEEN(TRANGE.MAX,MAX(ENDDATE))) NUMMON FROM TCYCLE_ACCOUNT, TRANGE GROUP BY TRANGE.MAX)
WHERE NUMMON > 0
CONNECT BY LEVEL <= NUMMON + 3),
tcycle AS (
SELECT STARTDATE, ENDDATE,SEQ FROM TCYCLE_ACCOUNT
UNION ALL
SELECT STARTDATE, ENDDATE,RANK() OVER (ORDER BY ENDDATE ASC)+ LAST SEQ
FROM TCYCLE_GAPS, (SELECT MAX(SEQ) LAST FROM TCYCLE_ACCOUNT)
),
result AS (
select seq snap_id,
TO_CHAR(ENDDATE, 'YYYY-MM-DD') bar_name,
TO_CHAR(STARTDATE, 'YYYY-MM-DD HH24:MI') || ' - ' || TO_CHAR(ENDDATE, 'YYYY-MM-DD HH24:MI') bar_desc,
TO_CHAR(NVL(CEIL(SUM(COMPUTEDAMOUNT)*100)/100,0),'99999990D00') bar_value,
CASE WHEN STARTDATE > min AND ENDDATE < max THEN 'Y' ELSE 'N' END completed /* Check for incomplete periods */
from t1, tcycle, trange
WHERE t1.ENDTIMEUTC(+) >= tcycle.STARTDATE AND t1.ENDTIMEUTC(+) < tcycle.ENDDATE /* The Billing end time must be inside the range until the computed hour */
AND (tcycle.STARTDATE between trange.min and trange.max OR tcycle.ENDDATE between trange.min and trange.max)
group by seq, tcycle.STARTDATE, tcycle.ENDDATE, min, max
order by seq
),
statistics as (
select REGR_SLOPE(bar_value,snap_id) slope,
REGR_INTERCEPT(bar_value,snap_id) intercept
from result
where completed = 'Y' /* Don't consider incomplete periods for calc */
),
result_next3 as (
select seq snap_id,
TO_CHAR(ENDDATE, 'YYYY-MM-DD') bar_name,
TO_CHAR(STARTDATE, 'YYYY-MM-DD HH24:MI') || ' - ' || TO_CHAR(ENDDATE, 'YYYY-MM-DD HH24:MI') bar_desc,
TO_CHAR(NVL(CEIL((slope*seq+intercept)*100)/100,0),'99999990D00') bar_value /* Linear Regression calculate. */
from tcycle, trange, statistics
WHERE (tcycle.STARTDATE >= trange.max OR tcycle.ENDDATE >= trange.max)
and slope is not null and intercept is not null
group by seq, tcycle.STARTDATE, tcycle.ENDDATE, slope, intercept
order by seq
),
result_final as (
select snap_id,
bar_name,
bar_value,
'USD ' || bar_value bar_desc,
'opacity: 0.6' colour
from result
where completed = 'Y'
union all
select snap_id,
bar_name,
bar_value,
'USD ' || bar_value bar_desc,
'color: #703593; opacity: 0.6' colour
from result_next3
where rownum <= 3
)
select bar_name, bar_value, colour, bar_desc
from result_final
order by snap_id;
0 rows selected.
oci360 (c) 2020, All rights reserved. oci360 v20.06 (2020-06-25) based on moat369 v2002 (2020-02-27). Timestamp: 2020-06-30T16:40:14