要計(jì)算SQL中的移動(dòng)平均，需使用AVG()函數(shù)結(jié)合OVER()窗口函數(shù)，并定義ROWS BETWEEN子句來指定參與計(jì)算的行范圍。1. 使用AVG(value) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)可計(jì)算包含當(dāng)前天在內(nèi)的7日移動(dòng)平均；2. 若排除當(dāng)前天，則使用ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING；3. 數(shù)據(jù)存在日期間隙時(shí)，應(yīng)先填充缺失日期以確保準(zhǔn)確性；4. 可根據(jù)不同需求調(diào)整窗口大小，如30天或52周；5. 對(duì)于多時(shí)間序列數(shù)據(jù)，可通過PARTITION BY子句按組分區(qū)計(jì)算。

Calculating a moving average in SQL is pretty straightforward once you understand how window functions work. The key is to use the AVG() function together with an OVER() clause that defines the window of rows you want to include in the average.

Use the AVG() function with a window frame

To calculate a moving average, you need to define a window of rows relative to the current row. This is done using the ROWS BETWEEN clause inside the OVER() function.

Here’s a basic example:

SELECT
  date,
  value,
  AVG(value) OVER (
    ORDER BY date 
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS seven_day_avg
FROM data_table;

This gives you a 7-day moving average, including the current day and the previous 6 days.
If you want to exclude the current row (e.g., average of the last 7 days not including today), change it to:

ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING

Make sure your data is ordered correctly—usually by date or timestamp.

Handle gaps in dates carefully

If your dataset has missing dates (for example, weekends in business data), using ROWS BETWEEN might skip some actual days and give you an inaccurate average.

A better approach is to convert your data into a daily format first, filling in missing dates with zeros or NULLs, then apply the window function.

You can do this using a date dimension table or generate a series of dates depending on your SQL dialect (like in PostgreSQL with generate_series() or BigQuery with DATE_ADD() and UNNEST()).

Once your data has no gaps, you can safely use:

AVG(value) OVER (
  ORDER BY date 
  ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
)

Otherwise, you might end up averaging fewer rows than expected when dates are missing.

Adjust the window size based on your needs

The number of rows you include depends on what kind of smoothing you're after. Common setups include:

• 7-day moving average for weekly trends
• 30-day moving average for monthly patterns
• 52-week moving average for annual comparisons

Just tweak the ROWS BETWEEN clause accordingly. For example:

• 30-day average:

  ROWS BETWEEN 29 PRECEDING AND CURRENT ROW

• Rolling 12-month average:

  ROWS BETWEEN 11 PRECEDING AND CURRENT ROW

Also consider partitioning by group if you have multiple time series (like per user or product):

AVG(value) OVER (
  PARTITION BY category 
  ORDER BY date 
  ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
)

This way, the average resets for each group.

Depending on your SQL engine, you might also explore built-in functions like MOVING_AVG() in Oracle or specific time-series extensions, but sticking with standard window functions keeps things portable.

基本上就這些。

以上是如何計(jì)算SQL中的移動(dòng)平均值的詳細(xì)內(nèi)容。更多信息請(qǐng)關(guān)注PHP中文網(wǎng)其他相關(guān)文章！