我有一个包含以下字段的表:
RefDate
Symbol
Timestamp
Sequence
Quantity
Price
SaleCondition
Pid
SubMkt它有一个股票交易列表,上面有日期(RefDate),符号(苹果,微软,戴夫,亚马逊),数量,价格等。
SaleCondition包含一个我需要用来查找各种东西的代码列表,例如,"O"代表“打开交易”,"6"代表“关闭交易”,等等。
我需要计算开盘价,收盘价,最低/最高/平均价格,VWAP (成交量加权平均价格),交易数量,交易股票数量和波动率(计算为最高价格-最低价格/最后价格)。
我必须排除SaleCondition中的许多代码才能得到min/max/avg/VWAP,我做得很正确。
但是我不知道如何将开盘价和收盘价拉到查询中。我基本上需要给出一个计算的字段条件(Select PRICE where SaleCondition="O" and PID="Q")。显然,我不能这样做,因为我需要WHERE子句来排除许多其他代码。
这是我想出来的。第一个函数产生正确的min/max/average/vwap,但开盘/收盘价是占位符,股票/交易是不正确的。后两个查询是正确的开盘价和收盘价。
dbGetQuery(nqdb, statement =
"select
RefDate,
Symbol,
Price as OpeningPrice,
Price as ClosingPrice,
Min(Price) as MinPrice,
Max(Price) as MaxPrice,
AVG(Price) as AvgPrice,
Sum(Quantity*Price)/Sum(Quantity) as VWAP,
Count(Quantity) as Trades,
Sum(Quantity) as Shares,
(Max(Price)-Min(Price))/(Price) as PctRange
from trds
where
SaleCondition not like '%C%' and
SaleCondition not like '%G%' and
SaleCondition not like '%I%' and SaleCondition not like '%H%' and
SaleCondition not like '%M%' and SaleCondition not like '%N%' and
SaleCondition not like '%P%' and SaleCondition not like '%Q%' and
SaleCondition not like '%R%' and SaleCondition not like '%T%' and
SaleCondition not like '%U%' and SaleCondition not like '%V%' and
SaleCondition not like '%W%' and SaleCondition not like '%Z%' and
SaleCondition not like '%4%' and SaleCondition not like '%7%' and
SaleCondition not like '%9%'
group by Symbol order by PctRange DESC")
dbGetQuery(nqdb, statement =
"select
RefDate,
Symbol,
Price as OpeningPrice
from trds
where SaleCondition like '%O%' and Pid='Q'")
dbGetQuery(nqdb, statement =
"select
RefDate,
Symbol,
Price as ClosingPrice
from trds
where SaleCondition like '%6%' and Pid='Q'")发布于 2019-03-04 06:13:39
我认为您正在寻找条件聚合。您可以在聚合函数中实现逻辑,而不是在WHERE子句中排除记录。SInce你不是在排除记录,你有权访问所有需要做所有计算的数据。
这是一个基于原始查询的开盘价和收盘价的示例。对于其他计算,您可以根据需要添加任意数量的列。
SELECT
RefDate,
Symbol,
MAX(CASE WHEN SaleCondition LIKE '%O%' AND Pid='Q' THEN Price END) as OpeningPrice,
MAX(CASE WHEN SaleCondition LIKE '%6%' AND Pid='Q' THEN Price END) as ClosingPrice
FROM trds
GROUP BY
RefDate,
Symbolhttps://stackoverflow.com/questions/54973539
复制相似问题