如何从原有的多表中创建表，并对新表的某几列进行假设

Question

问题遇到的现象和发生背景

从travels和users中，生成一个表user_daily_spent。其中，spent_amount_cents（新表中的列，在已有的表中没有）是用户当天所有形成的cost_amount_cents总和。可假设所有用户一开始的begin_balance为0，每次出行余额都会增加
travels里有id, user_id, status( started failed completed), started_at, completed_at, cost_amount_cents, base_cost_amount_cents, refunded_amount_cents
users里有用户ID以及其他个人信息

问题相关代码，请勿粘贴截图

运行结果及报错内容

我的解答思路和尝试过的方法

对于这个 begin_balance, spent_amount_cents这两列可能没有过多的思路不知道怎么弄，但是目前写了一部分代码如下：
Insert into user_daily_spent(date, user_id, begin_balance, spent_amount_cents)
Select date_format(started_at, ’%Y%m%d’) as date, t.user_id as user_id, begin_balance, sum(cost_amount_cents) spent_amount_cents
From trips as t left join users as u on t.user_id=u.id

我想要达到的结果

Answer 1

没明白你问题在哪,你写个查询sql,确定查询结果正确后,插入到目标表不就好了么？
我猜,目标表是不是需要4个字段,日期、用户、期初金额、花费金额,相当于从明细记录表汇总出一个按日期按用户的汇总表,其实这个关不关联用户表没有多大意义,除非是要做过滤条件,重点来看怎么从明细表汇总出你想要的数据。
首先,该问题标签添加了mysql和sqlserver两种数据库,答题人不知道你到底是哪种数据库,不同数据库的语法是不一样的,甚至同一种数据库,在不同版本支持的语法也不一样。由于题目中使用了date_format函数,暂且认为是mysql吧。
然后,你需要描述结果表中,begin_balance, spent_amount_cents之间的联系,以及这些值代表的含义,可以举几行数据的例子,说明每个值是怎么算的,然后答题人就可以根据你描述的逻辑,写出对应的查询sql
我这里再进行一次假设,你想要的可能是:

对于单个用户,
第一条 begin_balance 为0,spent_amount_cents从当日明细汇总
第二条 begin_balance 为前一条的spent_amount_cents,spent_amount_cents从当日明细汇总

如果是mysql8.0以上版本,可以直接用开窗函数lag来获取前一行记录中的值,如果是更低的版本,写起来就很麻烦了,因为对于同一个用户,日期可能不是连续的,因此要查小于当前行日期的最大日期的对应的行。

select date_format(started_at, ’%Y%m%d’) as date, t.user_id as user_id, 
lag(sum(cost_amount_cents)) over (partition by  t.user_id order by date_format(started_at, ’%Y%m%d’)) begin_balance,
sum(cost_amount_cents) spent_amount_cents from trips 
group by  date_format(started_at, ’%Y%m%d’) , t.user_id