Query the daily consumption and trend prediction for this month

Keyword

Analysis of Machine Learning Algorithms,nested subqueries,Consumer analysis

correlation function

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

Display the daily consumption of the current month and the predicted consumption for the remaining period, one point per day. Group the data based on the bill number to obtain the corresponding time, service identifier, bill type, and payable amount.Aggregate and calculate the daily expense, and then find the latest day on which the expense is incurred by time.Use the time, expense, passed string (which can be used as an alias later), prediction period, and number of prediction points as parameters in the sls_inner_ts_regression function to obtain the current daily consumption and subsequent prediction results. The results are displayed in a line chart, in which the x-axis represents the time and the y-axis represents the real consumption and predicted consumption.

source :bill |
select
  date_format(res.stamp, '%Y-%m-%d') as time,
  res.real as "Real consumption",case
    when is_nan(res.real) then res.pred
    else null
  end as "Predicted consumption",
  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