我有用户购买行为的记录。但是,它很长,并且包含许多冗余数据。我想删除在5分钟内购买和删除的订单
到目前为止,我的查询:--TABLE 3 COD
select z.user_id,
z.date,
z.actions,
x.name,
x.email,
x.address
sum(z.debit) over (partition by z.seller_id order by z.created_at) -
sum(z.credit) over (partition by z.seller_id order by z.created_at)
as balance
from table_1 z
left join
table_2 x
on z.seller_id = x.uid
order by seller_id, created_at
为简单起见,我得到了这个结果
user actions credit debit balance date
1 do_action_A 5000 0 5000 2020-01-01 1:00:00 #no need these 2
1 cancel_A 0 5000 0 2020-01-01 1:03:00 #in result
1 do_action_A 5000 0 5000 2020-01-01 1:10:00
1 do_action_b 3000 0 8000 2020-01-01 1:20:00
1 do_action_c 0 7000 1000 2020-01-01 1:30:00
2 do_action_A 5000 0 5000 2020-01-01 1:00:00
2 do_action_B 3000 0 8000 2020-01-01 1:10:00
我们知道用户只能在5分钟内取消订单,很遗憾,取消的情况很多。我需要使此数据表简单而简短,以便轻松跟踪和可视化它。
这是我的期望:
user actions credit debit balance date
1 do_action_A 5000 0 5000 2020-01-01 1:10:00
1 do_action_b 3000 0 8000 2020-01-01 1:20:00
1 do_action_c 0 7000 1000 2020-01-01 1:30:00
2 do_action_A 5000 0 5000 2020-01-01 1:00:00
2 do_action_B 3000 0 8000 2020-01-01 1:10:00
您可以尝试使用lead()
select * from
(
select z.user_id,z.date,z.actions,x.name,
x.email,x.address,debtit, credit, balance,
lead(z.actions) over(parition by z.user_id order by z.created_at) as next_action
from table_1 z left join table_2 x
on z.seller_id = x.uid
)A where next_action not like '%cancel%' and actions not like '%cancel%'
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句