sql n日均线-mysql presto

最新推荐文章于 2023-02-24 14:55:02 发布

原创最新推荐文章于 2023-02-24 14:55:02 发布 · 331 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#sql #mysql #数据库

本文通过SQL查询展示了如何利用窗口函数计算指定日期范围内（前2天加上当天）的数值总和与平均值，例如3日均线。示例中使用了`SUM()`和`AVG()`函数结合`ROWS BETWEEN`子句来实现这一功能。

with day_num as (
    select '2021-01-01' fd_day, 1 num
    union all select '2021-01-02', 2
    union all select '2021-01-03', 3
    union all select '2021-01-04', 4
    union all select '2021-01-05', 5
)
select fd_day, num, sum(num) over(
    order by fd_day
    --前2条、本条、后0条记录的和
    rows between 2 preceding and 0 following
) as sum_num, avg(num) over(
    order by fd_day
    --前2条、本条、后0条记录的均值，即3日均线
    rows between 2 preceding and 0 following
) as avg_num
from day_num