4i.3.344. Product: B91627 - Object Storage - Requests, between 2020-05-11 and 2020-06-20 (OCI360_REPORTS_COST)
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
----------------------------------------------------------------------------------- -------- --------------------------------------------------------
lineItem/referenceNo VARCHAR2(4000)
lineItem/tenantId VARCHAR2(4000)
lineItem/intervalUsageStart VARCHAR2(4000)
lineItem/intervalUsageEnd VARCHAR2(4000)
product/service VARCHAR2(4000)
product/compartmentId VARCHAR2(4000)
product/compartmentName VARCHAR2(4000)
product/region VARCHAR2(4000)
product/availabilityDomain VARCHAR2(4000)
product/resourceId VARCHAR2(4000)
usage/billedQuantity VARCHAR2(4000)
usage/billedQuantityOverage VARCHAR2(4000)
cost/subscriptionId VARCHAR2(4000)
cost/productSku VARCHAR2(4000)
product/Description VARCHAR2(4000)
cost/unitPrice VARCHAR2(4000)
cost/unitPriceOverage VARCHAR2(4000)
cost/myCost VARCHAR2(4000)
cost/myCostOverage VARCHAR2(4000)
cost/currencyCode VARCHAR2(4000)
cost/billingUnitReadable VARCHAR2(4000)
cost/overageFlag VARCHAR2(4000)
lineItem/isCorrection VARCHAR2(4000)
lineItem/backreferenceNo VARCHAR2(4000)
tags/Oracle-Tags.CreatedBy VARCHAR2(4000)
tags/Oracle-Tags.CreatedOn VARCHAR2(4000)
tags/orcl-cloud.free-tier-retained VARCHAR2(4000)
WITH t1 AS ( /* Cost of the resource group by ENDTIME */
SELECT SUM("cost/myCost") COMPUTEDAMOUNT,
TO_TIMESTAMP("lineItem/intervalUsageEnd",'YYYY-MM-DD"T"HH24:MI"Z"') ENDTIMEUTC
FROM OCI360_REPORTS_COST
WHERE "cost/productSku" = 'B91627'
GROUP BY TO_TIMESTAMP("lineItem/intervalUsageEnd",'YYYY-MM-DD"T"HH24:MI"Z"')
),
trange as ( /* Min and Max range of the costs */
select trunc(min(TO_TIMESTAMP("lineItem/intervalUsageEnd",'YYYY-MM-DD"T"HH24:MI"Z"')),'HH24') min,
trunc(max(TO_TIMESTAMP("lineItem/intervalUsageEnd",'YYYY-MM-DD"T"HH24:MI"Z"')),'HH24') max
FROM OCI360_REPORTS_COST
),
tcycle_account AS (
SELECT /*+ materialize */
TO_TIMESTAMP(add_months(trunc(trange.min,'mm'),ROWNUM-1)) STARTDATE,
TO_TIMESTAMP(add_months(trunc(trange.min,'mm'),ROWNUM)) - numToDSInterval( 1, 'second' ) ENDDATE,
ROWNUM seq
FROM trange
CONNECT BY LEVEL <= ceil(months_between(trange.max,trange.min)) + 1
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:15:55