MySQL AVG函数可在每个符号中按日期(订购日期desc)显示最近15条记录 [英] MySQL AVG function for recent 15 records by date (order date desc) in every symbol

问题描述

我正在尝试在SQL中创建一个语句(用于在指定日期保存股票代码和价格的表)，每个代码的平均价格为5天，而平均价格为15天.

I am trying to create a statement in SQL (for a table which holds stock symbols and price on specified date) with avg of 5 day price and avg of 15 days price for each symbol.

表列:

• 符号
• 打开
• 高
• 关闭
• 日期

平均价格是从最近5天到最近15天计算得出的.我试过这个以获得1个符号:

The average price is calculated from last 5 days and last 15 days. I tried this for getting 1 symbol:

SELECT avg(close),
       avg(`trd_qty`) 
  FROM (SELECT *
          FROM cashmarket 
         WHERE symbol = 'hdil'
         ORDER BY `M_day` desc 
         LIMIT 0,15 ) s

但是我无法获得所需的列表来显示所有符号的平均值.

but I couldn't get the desired list for showing avg values for all symbols.

推荐答案

您可以使用行号作为

You can either do it with row numbers as suggested by astander, or you can do it with dates.

如果您每天没有行，那么此解决方案也将花费最近15天，而行号解决方案则花费最后15行.您必须确定哪个更适合您.

This solution will also take the last 15 days if you don't have rows for every day while the row number solution takes the last 15 rows. You have to decide which one works better for you.

编辑:替换了AVG，如果在该期间内未找到任何记录，请使用CASE避免被0除.

EDIT: Replaced AVG, use CASE to avoid division by 0 in case no records are found within the period.

SELECT
  CASE WHEN SUM(c.is_5) > 0 THEN SUM( c.close   * c.is_5 ) / SUM( c.is_5 )
       ELSE 0 END AS close_5,
  CASE WHEN SUM(c.is_5) > 0 THEN SUM( c.trd_qty * c.is_5 ) / SUM( c.is_5 )
       ELSE 0 END AS trd_qty_5,
  CASE WHEN SUM(c.is_15) > 0 THEN SUM( c.close   * c.is_15 ) / SUM( c.is_15 )
       ELSE 0 END AS close_15,
  CASE WHEN SUM(c.is_15) > 0 THEN SUM( c.trd_qty * c.is_15 ) / SUM( c.is_15 )
       ELSE 0 END AS trd_qty_15
FROM
(
  SELECT
    cashmarket.*,
    IF( TO_DAYS(NOW()) - TO_DAYS(m_day) < 15, 1, 0) AS is_15,
    IF( TO_DAYS(NOW()) - TO_DAYS(m_day) <  5, 1, 0) AS is_5
  FROM cashmarket
) c

查询返回最近5天和最近15天的close和trd_qty平均值.包括当前日期，因此实际上是今天加上最近的4天(将<替换为<=以获得当前日期加上5天).

The query returns the averages of close and trd_qty for the last 5 and the last 15 days. Current date is included, so it's actually today plus the last 4 days (replace < by <= to get current day plus 5 days).

这篇关于MySQL AVG函数可在每个符号中按日期(订购日期desc)显示最近15条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！