在子查询中使用别名 [英] Using alias in subquery
问题描述
我正在运行以下查询以获取投资组合中的未平仓头寸:
I'm running the following query to get the open positions on a portfolio:
SELECT SUM(trades.quantity) as total_quantity, SUM(trades.price) as total_cost, SUM(trades.price)/SUM(trades.quantity) as cost_per_share,
trades.ticker, tickers.code
FROM (trades)
LEFT JOIN tickers
ON trades.ticker = tickers.id
GROUP BY tickers.code
HAVING total_quantity > 0
ORDER BY tickers.code
我想添加一列以显示头寸的权重,即:
I'd like to add an extra column to show the weightening of a position, i.e.:
total_cost/SUM(total_cost) -- Dividing any given position cost by the total cost of the portfolio
由于不能在计算中使用别名,我认为我需要使用一个查询。我已经尝试了一些方法,但无法使其正常工作。
Since aliases cannot be used in calculations, I thought I'd need to use a sub-query. I've tried a few things but couldn't make it to work.
有人能对此有所启发吗?子查询走了吗?还有其他更好的方法吗?
Can anyone shed some light on this? Is sub-query the way to go? Is there any other better way to do it?
推荐答案
不确定查询(您似乎正在对GROUP BY LEFT JOINed表中的一个字段,对于未找到匹配的行,该字段可以为null),但也可以交叉连接到子选择项以获取所有价格的总和。
Not sure on your query (you appear to be doing a GROUP BY on a field from a LEFT JOINed table, which could be null for non found matching rows), but maybe cross join to a subselect to get the total of all prices
SELECT total_quantity, total_cost, cost_per_share, trades.ticker, tickers.code, total_cost/total_of_prices
FROM
(
SELECT SUM(trades.quantity) as total_quantity, SUM(trades.price) as total_cost, SUM(trades.price)/SUM(trades.quantity) as cost_per_share,
trades.ticker, tickers.code
FROM trades
LEFT JOIN tickers
ON trades.ticker = tickers.id
GROUP BY tickers.code
HAVING total_quantity > 0
) Sub1
CROSS JOIN
(
SELECT SUM(price) as total_of_prices
FROM trades
WHERE quantity > 0
) Sub2
ORDER BY tickers.code
这篇关于在子查询中使用别名的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!