使用新版成本管家

基础语法

新版成本管家以外表形式关联到Logstore，查询时需要通过查询外表来查询账单数据。

• 查询语句只能为星号（*） ，即竖线（|）前面只能为星号（*）。

• 分析语句中的外表名称固定为instance_bill，需统一将查询条件添加在where子句中。

* | select xxx from instance_bill where xxx group by xxx limit xxx

例如查询SLS产品每日消费金额，查询语句如下所示。

* |
select
  date_trunc('day', __time__) as day,
  sum(PretaxAmount) as cost
FROM  instance_bill
where
  productcode = 'sls'
group by
  day

示例1：聚合查询

例如：获取SLS总的账单费用。

• 查询和分析语句

  * |
  select
    sum(PretaxAmount) as cost
  FROM  instance_bill
  where
    productcode = 'sls'

• 查询和分析结果

示例2：分组查询

例如：查询不同产品的账单费用。

• 查询和分析语句

  * |
  select
    productcode,
    sum(PretaxAmount) as cost
  FROM  instance_bill
  group by
    productcode

• 查询和分析结果

示例3：同比环比分析

例如：与上月进行同比分析。

• 查询和分析语句

  * |
  SELECT
    diff [1] AS "本月费用",
    diff [2] AS "上月费用",
    diff [3] * 100 -100 as "同比增加%"
  FROM (
      SELECT
        compare(amount, 2592000) as diff
      FROM (
          SELECT
            sum(PretaxAmount) AS amount
          FROM instance_bill
        )
    )

• 查询和分析结果

示例4：分析产品计费项信息

例如：查询SLS产品每个计费项的消费金额与用量。

• 查询和分析语句

  * |
  SELECT
    BillingItem,
    sum(PretaxAmount) AS "消费",
    sum(Usage) as "用量"
  FROM  instance_bill
  where
    productcode = 'sls'
  GROUP by
    BillingItem

• 查询和分析结果

示例5：按天统计计费项用量趋势​

例如：按天统计计费项用量趋势。

• 查询和分析语句

  * |
  SELECT
  	date_trunc('day', __time__) as t,
    BillingItem,
    sum(PretaxAmount) AS "消费",
    sum(Usage) as "用量"
  FROM  instance_bill
  where
    productcode = 'sls'
  GROUP by
    BillingItem,
    t
  ORDER by
    t

• 查询和分析结果

  

示例6：分析产品实例信息

例如：查询SLS产品每个实例的消费金额。

• 查询和分析语句

  * |
  SELECT
    InstanceID,
    sum(PretaxAmount) AS "消费"
  FROM  instance_bill
  where
    productcode = 'sls'
  GROUP by
    InstanceID

• 查询和分析结果

示例7：按天统计产品实例费用趋势

例如：按天统计产品实例费用趋势。

• 查询和分析语句

  * |
  SELECT
    date_trunc('day', __time__) as t,
    InstanceID,
    sum(PretaxAmount) AS "消费"
  FROM  instance_bill
  where
    productcode = 'sls'
  GROUP by
    InstanceID,
    t
  ORDER by
    t

• 查询和分析结果

  

示例8：外表联合查询

例如：联合查询账单数据与您自定义的外表数据。

其中外表为instance_name_table，是您自定义的instanceID与instanceName映射表。

• 查询和分析语句

  * | with t1 as (
    select
      InstanceID,
      sum(PretaxAmount) as PretaxAmount
    FROM    instance_bill
  ),
  t2 as (
    select
      InstanceID,
      InstanceName
    FROM    instance_name_table
  )
  select
    t1.InstanceID,
    t1.PretaxAmount,
    t2.InstanceName
  FROM  t1
    left join t2 on t1.InstanceID = t2.InstanceID