新版成本管家可视化账单报表实践
新钛云服已累计为您分享780篇技术干货
PART.01
*| select * from instance_bill 数据表名称 | 表主键 | 主键类型 | 是否允许更新 | 是否加密 | 创建二级索引 |
splititem_bill | hash_key | 字符串 | 是 | 否 | 否 |
__time__ | 整型 |
双击名为“aliyun_bill”的logstore，在搜索框中填写下面的SQL语句。其中 {endpoint}需要替换为表格存储中’bss-‘开头命名的实例经典网络访问地址，{ots_instance_name}需要替换为表格存储中’bss-‘开头命名的实例名称，{uid} 需要替换为主账号uid。点击“查询/搜索”按钮。
* | 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小时更新。过滤器名称 | 类型 | 显示组件类型 | Key值 | 别名 | 日志库 |
财务单元筛选 | 变量替换 | 下拉列表 | cost_unit | 财务单元筛选 | aliyun_bill |
*|select distinct costunit from splititem_bill where ResourceGroup like '${{resource_group|%%}}'添加完成后，点击“保存”按钮，即可选择财务单元。
添加资源组过滤器
过滤器名称 | 类型 | 显示组件类型 | Key值 | 别名 | 日志库 |
资源组筛选 | 变量替换 | 下拉列表 | resource_group | 资源组筛选 | aliyun_bill |
*|select distinct ResourceGroup from splititem_bill where costunit like '${{cost_unit|%%}}'* | selectround(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)))* | selectcoalesce(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 ELSE0 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 ELSE0 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 elsenull 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 10004.2.6.1 数据转换
* | 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"日期"4.2.6.2 字段配置
* | 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点击“添加”按钮，选择“值”，输入“NaN”，“映射类型”选择“文本”，“映射值”填为“0%”。继续点击“添加”按钮，选择“值”，输入“null”，“映射类型”选择“文本”，“映射值”填为“0%”。完成上述配置后，点击“确认”按钮。
推荐阅读
推荐视频
最新评论
推荐文章
作者最新文章
你可能感兴趣的文章
Copyright Disclaimer: The copyright of contents (including texts, images, videos and audios) posted above belong to the User who shared or the third-party website which the User shared from. If you found your copyright have been infringed, please send a DMCA takedown notice to [email protected]. For more detail of the source, please click on the button "Read Original Post" below. For other communications, please send to [email protected].
版权声明：以上内容为用户推荐收藏至CareerEngine平台，其内容（含文字、图片、视频、音频等）及知识版权均属用户或用户转发自的第三方网站，如涉嫌侵权，请通知[email protected]进行信息删除。如需查看信息来源，请点击“查看原文”。如需洽谈其它事宜，请联系[email protected]。
版权声明：以上内容为用户推荐收藏至CareerEngine平台，其内容（含文字、图片、视频、音频等）及知识版权均属用户或用户转发自的第三方网站，如涉嫌侵权，请通知[email protected]进行信息删除。如需查看信息来源，请点击“查看原文”。如需洽谈其它事宜，请联系[email protected]。