sql - postgres 中时间序列数据的复杂 SQL 查询

Question

我有两个具有以下架构的表：

一个用户可以有多个交易来购买或出售集合。套装具有价格历史记录（价格）。价格目前是一个 timescaledb 超表。请注意，sets_traded_count 对于卖出也可能为负数（如果用户卖出两套，则该值为 -2）

根据交易，对于给定的 user_id，我们需要返回用户的投资组合价值和资本收益的时间序列数据。而公式是：

投资组合价值（天）=总和（用户拥有的集合（天）*相应集合的价格（天））

capital_gains(day) = 投资组合价值(day) + 购买(day) - 投资组合价值(day_0)

和购买（天）= sum（sets_traded_count*-1）（天）

到目前为止我已经尝试过：

select prices.time, SUM(portfolio_user_transactions.sets_traded_count) AS 

sum_sets_traded_count, 
SUM(prices.price*portfolio_user_transactions.sets_traded_count) AS portfolio_value,

SUM(prices.price*portfolio_user_transactions.sets_traded_count)-FIRST_VALUE(SUM(prices.price*portfolio_user_transactions.sets_traded_count))
OVER(ORDER BY prices.time asc) AS capital_gains,

AVG(SUM(prices.price*portfolio_user_transactions.sets_traded_count)) OVER(ORDER BY time) as avg_invested_capital 
from prices, portfolio_user_transactions 
where portfolio_user_transactions.set_id=prices.set_id
and prices.source='Geizhals.de'
and portfolio_user_transactions.user_id='2'
and prices.time >= portfolio_user_transactions.date 
group by prices.time
order by time asc;

但是，查询是否越来越复杂，我离想要的结果还很远。我完全被困在如何处理它上。计算portfolio_value 和capital_gains 的查询应该是有效的。两列（计算投资组合价值和资本收益）应与指示日期的列时间一起返回。输出应包含以下列： time | 投资组合价值 | 资本收益

非常感谢帮助！

标签： sqlpostgresqltime-series