未来三个月预测费用汇总

关键词

嵌套子查询,机器学习算法分析,成本分析,预测分析

相关函数

date_format,sum,from_unixtime,sls_inner_ts_regression,cast,max,to_unixtime,date_trunc,date_add,now

先按天分组计算总费用，并按照时间排序，找到当前时间日期，通过sls_inner_ts_regression函数预估出之后的90个点，然后对预估出的点和时间按月做聚合，计算之后每月的预估费用。 图中使用柱状图展示，x轴时间，y轴为预估费用

source :bill |
select
  date_format(from_unixtime(time), '%Y-%m') as t,
  sum(predict) as "预测费用"
from(
    select
      *
    from
      (
        select
          res.stamp as time,
          res.real as src,
          res.pred as predict
        from
          (
            select
              sls_inner_ts_regression(
                cast(day as bigint),
                total,
                array ['total'],
                cast(86400 as bigint),
                cast(90 as bigint)
              ) 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
                          log
                        group by
                          day
                        order by
                          day
                      )
                  )
                where
                  day < lastday
              )
          )
      )
    where
      time >= to_unixtime(date_trunc('month', now()))
      and time < to_unixtime(date_add('month', 3, date_trunc('month', now())))
  )
group by
  t
order by
  t
limit
  100000

SQL查询结果样例：