*| select * from instance_bill 

* | create table splititem_bill (hash_key varchar, __time__ bigint, __source__ varchar, instanceconfig varchar, productcode varchar, internetip varchar, outstandingamount double, pretaxamount double, instancespec varchar, subscriptiontype varchar, billingitem varchar, pretaxgrossamount double, usage double, producttype varchar, paymentamount double, listprice double, zone varchar, usageunit varchar, nickname varchar, productname varchar, currency varchar, tag varchar, invoicediscount double, productdetail varchar, item varchar, resourcegroup varchar, deductedbycashcoupons double, deductedbyprepaidcard double, ownerid varchar, billingdate varchar, listpriceunit varchar, deductedbycoupons double, instanceid varchar, serviceperiod double, deductedbyresourcepackage double, billingtype varchar, region varchar, intranetip varchar, accountname varchar, AdjustAmount double, BizType varchar,CashAmount double, CommodityCode varchar, CostUnit varchar, ServicePeriodUnit varchar, SplitAccountID varchar, SplitAccountName varchar, SplitBillingCycle varchar, SplitBillingDate varchar, SplitCommodityCode varchar, SplitItemID varchar, SplitItemName varchar, SplitProductDetail varchar ) with ( endpoint='{endpoint}',accessid='rolearn',accesskey='acs:ram::{uid}:role/aliyunserviceroleforslsaudit',instance_name='{ots_instance_name}',table_name='splititem_bill',type='tablestore')

账单总览和预测
1、分账账单当月数据查询结果仅供参考，不作为对账依据；当月最终账单在次月4日12点后支持查看（月中会发生包括但不限于以下少数场景：延迟出账，退款，调账，欠费核销等）。
2、当月明细账单数据中不包含未结算（未出账，累账中）的后付费数据。
3、分账账单数据相对于实际费用消耗延迟48小时更新，其中分拆型云产品（例如：CDN、OSS、共享带宽等）各个分拆项（例如：域名、Bucket、EIP等）的具体费用延迟72小时更新。

*|select distinct costunit from splititem_bill where ResourceGroup like '${{resource_group|%%}}'

*|select distinct ResourceGroup from splititem_bill where costunit like '${{cost_unit|%%}}'

* | select round(diff[1],2),round(diff[2] ,2), round(diff[3]*100-100,2) from(select compare("昨日消费",2592000) as diff from(select round(coalesce(sum(PretaxAmount),0),3) as "昨日消费" from instance_bill where costunit like '${{cost_unit|%%}}' and resourcegroup like '${{resource_group|%%}}'))

* |  select  coalesce(cast( round(diff[1],2) as varchar),'无数据'),round(diff[2] ,2),round(diff[3]*100-100,2)  from( select compare("最大日消费",2592000) as diff from( select round(max(this_month),2) as "最大日消费" from ( select  date_trunc('day',__time__) as day,  sum(  PretaxAmount ) as "this_month" from splititem_bill where costunit like '${{cost_unit|%%}}' and resourcegroup like '${{resource_group|%%}}' group by day)))

*  | select coalesce(cast(round(diff[1],2) as varchar),'无数据'),diff[2], round(diff[3]*100-100,2) from( select compare(this_month,2592000) as diff from( select this_month  from( select  sum(   PretaxAmount       ) as "this_month" from splititem_bill where costunit like '${{cost_unit|%%}}' and resourcegroup like '${{resource_group|%%}}' )))

*  | SELECT COALESCE(Cast(Round("本月预计消费",2) AS VARCHAR),'无数据') AS "本月预计消费",Round("上月消费" ,2) AS "上月消费",Round("本月预计消费" / "上月消费"*100-100,2) AS PERCENT FROM (SELECT Sum (CASE WHEN time >=To_unixtime(Date_trunc('month',Now())) AND time < To_unixtime(Date_add('month',1,Date_trunc('month',Now()))) THEN predict ELSE 0 END ) AS "本月预计消费",Sum(CASE WHEN time <To_unixtime(Date_trunc('month',Now())) AND time >= To_unixtime(Date_add('month',-1,Date_trunc('month',Now()))) THEN src ELSE 0 END ) AS "上月消费" FROM(SELECT res.stamp as time,  res.real as src,res.pred as predict,res.instances[1] as ins FROM(SELECT sls_inner_ts_regression(Cast(day AS BIGINT), total, array['total'], 86400, 30) AS res FROM(SELECT * FROM(SELECT *,max(day) OVER() AS lastday FROM (SELECT   to_unixtime(date_trunc('day',__time__)) AS day, sum(pretaxamount) AS total from splititem_bill  where costunit like '${{cost_unit|%%}}' and resourcegroup like '${{resource_group|%%}}' GROUP BY day ORDER BY day))))))

* | select date_format(res.stamp,'%Y-%m-%d') as time,res.real as "实际消费",case when is_nan(res.real) then res.pred else null end as "预测消费",res.instances from(select sls_inner_ts_regression(cast(day as bigint),total,array['total'],86400,60) as res from ( select * from ( select *, max(day) over() as lastday  from (select to_unixtime( date_trunc('day',__time__)) as day, sum(PretaxAmount) as total from splititem_bill where costunit like '${{cost_unit|%%}}' and resourcegroup like '${{resource_group|%%}}' group by day  order by day)) where day < lastday )) limit 1000

* | select date_format(__time__,'%Y-%m-%d') as "日期", round(sum(pretaxamount),3)  as "金额" from splititem_bill where costunit like '${{cost_unit|%%}}' and resourcegroup like '${{resource_group|%%}}' group by "日期" order by "日期"

* | select productname as "产品名称",round(diffreal [1], 3) as "折后费用(元)",cast(round(diffreal [1] / sum(diffreal [1]) over() * 100, 2) as varchar ) as "产品费用占比",cast(round(diffreal [3] * 100 -100, 2) as varchar) as "同比上月(折后费用)",round(diffgross [1], 3) as "原始消费(元)",cast(round(diffgross [3] * 100 -100, 2) as varchar) as "同比上月(原始消费)",(days) as "30天费用趋势" from(select productname,compare(pretaxamount, 2592000) as diffreal,compare(PretaxGrossAmount, 2592000) as diffgross,arbitrary(days) as days from(select productname,sum(PretaxAmount) as PretaxAmount,sum(PretaxGrossAmount) as PretaxGrossAmount,array_agg(pretaxAmount) as days from(select productname,sum(PretaxAmount) as PretaxAmount,sum(PretaxGrossAmount) as PretaxGrossAmount,date_trunc('day', __time__) as t from splititem_bill where costunit like '${{cost_unit|%%}}' and resourcegroup like '${{resource_group|%%}}' group by productname,t order by t) group by productname) group by productname) order by diffreal [1] desc limit 2147483647