本月每日消费及趋势预测

关键词

机器学习算法分析,嵌套子查询,消费分析

相关函数

date_format,case when,is_nan,sls_inner_ts_regression,cast,max,to_unixtime,date_trunc,sum,arbitrary

展示本月的消费情况以及剩余时间的消费预测，每天一个点；首先根据账单号对数据做分组，获取到对应的时间、产品标识、账单类型及应付金额。接着按天做聚合计算每天的费用，然后按照时间排序找到产生费用的最新一天。最后将时间、费用、传递的字符串(可以作为之后的别名)、预测周期、预测点的个数作为 sls_inner_ts_regression 的参数，获取当前每天的消费情况及之后的预测结果。 折线图展示，x轴时间、y轴展示 实际消费 、预测消费两个字段。

source :bill |
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
                  (
                    select
                      RecordID,
                      arbitrary(__time__) as __time__,
                      arbitrary(ProductCode) as ProductCode,
                      arbitrary(item) as item,
                      arbitrary(PretaxAmount) as PretaxAmount
                    from
                      log
                    group by
                      RecordID
                  )
                group by
                  day
                order by
                  day
              )
          )
        where
          day < lastday
      )
  )
limit
  1000

SQL查询结果样例：