使用子查询为最后5行的样本计算列的平均值

Question

试图找出构造此查询的最佳方式。我只想为每个symbol获取最近5行/天的AVG() beta。然后用这个数字，我想找出过去5行/天的所有symbols的总平均值。

当我从头开始构建我的查询时，我正在测试要使用哪些变量。

SELECT DISTINCT symbol, beta
FROM api.security_stats
WHERE date =  (SELECT MAX(date) FROM api.security_price) - interval '1 day'  AND
     symbol in ('AAPL','MSFT','AMD','NVDA','TSLA')
GROUP BY symbol, beta;

 symbol |        beta
--------+--------------------
 AAPL   |  1.226175475928623
 AMD    | 1.2651464334484317
 MSFT   | 1.0922307100829312
 NVDA   | 1.3928523562615582
 TSLA   | 1.7399931738843037

在上面，我只能为beta获得一天的行数。

SELECT DISTINCT symbol, beta
FROM api.security_stats
WHERE date =  (SELECT MAX(date) FROM api.security_price) - interval '5 day'  AND
    symbol in ('AAPL','MSFT','AMD','NVDA','TSLA')
GROUP BY symbol, beta;

 symbol | beta
--------+------
(0 rows)

在这里，我尝试获取最近5天的数据，但没有数据显示。

如何创建子查询，返回每个symbol最近5天的平均测试版，然后将该子查询添加到主查询中，以计算子查询的总结果的平均值？

Answer 1

如果我理解正确的话，您可以使用row_number()

SELECT symbol, AVG(beta) as avg_beta
FROM (SELECT ss.*,
             ROW_NUMBER() OVER (PARTITION BY symbol ORDER BY date DESC) as seqnum
      FROM api.security_stats ss
     ) s
WHERE seqnum <= 5
GROUP BY symbol;

然后使用另一个子查询来计算总平均值：

SELECT avg(avg_beta)
FROM (SELECT symbol, AVG(beta) as avg_beta
      FROM (SELECT ss.*,
                   ROW_NUMBER() OVER (PARTITION BY symbol ORDER BY date DESC) as seqnum
            FROM api.security_stats ss
           ) ss
      WHERE seqnum <= 5
     ) ss;
GROUP BY symbol;

注意:如果所有的符号都有天数，那么你可以使用：

SELECT avg(avg_beta)
FROM (SELECT ss.*,
             ROW_NUMBER() OVER (PARTITION BY symbol ORDER BY date DESC) as seqnum
      FROM api.security_stats ss
     ) ss
WHERE seqnum <= 5;