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