尝试使用BigQuery来计算EMA（指数移动平均数） [英] Trying to calculate EMA (exponential moving average) using BigQuery

问题描述

我试图计算股票价格的指数移动平均线（EMA）。我正在使用一个公式从这个网站计算EMA： http：//www.iexplain。 org / ema-how-to-calculate /

我完全卡在计算正确的EMA。查询运行并返回结果，但不返回准确的EMA。非常感谢帮助。您可以看到尝试使用EMA公式从第4行开始计算EMA。

  SELECT current_day.date as current_date，current_day。 avg_price as current_date_price，
 prev_day.prev_avg_price as previous_date_price，prev_2_day.prev_avg_price，
 
（（current_day.avg_price *（2 /（22 + 1）））+（（（prev_day.prev_avg_price * （2 /（22 
 + 1）））+ 
（prev_2_day.prev_avg_price *（2 /（22 + 1））））*（1-（2 /（22 + 1））））） ）作为EMA 
 
 
从
（选择DATE（时间）作为日期，ROUND（AVG（价格），2）作为avg_price从ds_5.tb_4978组减去
日期）作为
 current_day 
 JOIN 
（选择DATE（USEC_TO_TIMESTAMP（time- 86400000000））作为prev_date，ROUND（AVG（price），2）作为
 prev_avg_price from ds_5.tb_4978 GROUP BY prev_date）as prev_day 
 ON current_day.date = prev_day.prev_date 
 $ b JOIN 
（选择DATE（USEC_TO_TIMESTAMP（time-（86400000000 * 2）））作为prev_2_date，ROUND （AVG（价格），2）
 as 
 prev_avg_price from ds_5.tb_4978 GROUP BY prev_2_date）as prev_2_day 
 ON current_day.date = prev_2_day.prev_2_date 
 
 GROUP BY current_date，current_date_price，previous_date_price，
 prev_2_day.prev_avg_price ，EMA 
  

这是来自上述查询的json响应： https://json.datadives.com/01843e08d5127e9d26d03fe0f842e735.json

解决方案

我不认为你采取前一天avg_price。你需要拿到前一天的EMA。

我宁愿先计算EMA;如果不可能的话，也许看看MapReduce。

I am trying to calculate the exponential moving average (EMA) of a stock price. I am using a formula to calculate EMA from this site: http://www.iexplain.org/ema-how-to-calculate/

I am totally stuck calculating the proper EMA. The query runs and returns results but does not return an accurate EMA. Help is greatly appreciated. You can see the attempt to calculate EMA using the EMA formula starting at line 4.

SELECT current_day.date as current_date, current_day.avg_price as current_date_price,       
prev_day.prev_avg_price as previous_date_price, prev_2_day.prev_avg_price,

(  ( current_day.avg_price * (2 / (22 + 1) ) ) +  ( ( ( prev_day.prev_avg_price * (2 / (22     
+ 1) ) ) +   
( prev_2_day.prev_avg_price * (2 / (22 + 1) ) ) ) * (1 -( 2 / (22 + 1) ) ) ) ) as EMA


FROM
(select DATE(time) as date, ROUND(AVG(price),2) as avg_price FROM ds_5.tb_4978 group by     
date) as    
current_day
JOIN
(select DATE(USEC_TO_TIMESTAMP(time- 86400000000)) as prev_date, ROUND(AVG(price),2) as    
prev_avg_price from ds_5.tb_4978 GROUP BY prev_date) as prev_day
ON current_day.date=prev_day.prev_date

JOIN
(select DATE(USEC_TO_TIMESTAMP(time- (86400000000*2))) as prev_2_date, ROUND(AVG(price),2)     
as    
prev_avg_price from ds_5.tb_4978 GROUP BY prev_2_date) as prev_2_day
ON current_day.date=prev_2_day.prev_2_date

GROUP  BY current_date, current_date_price, previous_date_price,    
prev_2_day.prev_avg_price, EMA

This is the json response from the above query: https://json.datadives.com/01843e08d5127e9d26d03fe0f842e735.json

解决方案

I don't think you take the previous day avg_price. You need to take the previous day EMA.

I would rather calculate the EMA upfront; if not possible, maybe look into MapReduce.

这篇关于尝试使用BigQuery来计算EMA（指数移动平均数）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！