MySQL AVG function for recent 15 records by date (order date desc) in every symbol

Question

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.

Table columns:

• symbol
• open
• high
• close
• date

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.

Answer 1

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

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.

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

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).

Answer 2

Have a look at How to number rows in MySQL

You can create the row number per item for the date desc.

What you can do is to retrieve the Rows where the rownumber is between 1 and 15 and then apply the group by avg for the selected data you wish.

Answer 3

Use:

   SELECT DISTINCT
          t.symbol,
          x.avg_5_close,
          y.avg_15_close
     FROM CASHMARKET t
LEFT JOIN (SELECT cm_5.symbol,
                  AVG(cm_5.close) 'avg_5_close',
                  AVG(cm_5.trd_qty) 'avg_5_qty'
             FROM CASHMARKET cm_5
            WHERE cm_5.m_date BETWEEN DATE_SUB(NOW(), INTERVAL 5 DAY) AND NOW()
         GROUP BY cm_5.symbol) x ON x.symbol = t.symbol
LEFT JOIN (SELECT cm_15.symbol,
                  AVG(cm_15.close) 'avg_15_close',
                  AVG(cm_15.trd_qty) 'avg_15_qty'
             FROM CASHMARKET cm_15
            WHERE cm_15.m_date BETWEEN DATE_SUB(NOW(), INTERVAL 15 DAY) AND NOW()
         GROUP BY cm_15.symbol) y ON y.symbol = t.symbol

I'm unclear on what trd_qty is, or how it factors into your equation considering it isn't in your list of columns.

If you want to be able to specify a date rather than the current time, replace the NOW() with @your_date, an applicable variable. And you can change the interval values to suit, in case they should really be 7 and 21.

Answer 4

trdqty is the quantity traded on particular day. the days are not in order coz the market operates only on weekdays and there are holidays too so date may not be continuous