Window

AVG_WINDOW

DuckDB

Menghitung moving average atau running average menggunakan window function. Sangat berguna untuk smoothing data dan analisis tren.

Tipe hasil: DOUBLE

Syntax

SQL

AVG(expression) OVER ([PARTITION BY ...] [ORDER BY ...] [frame_clause])

Parameter

expressionNUMERICwajib

Kolom atau ekspresi numerik yang akan dihitung rata-ratanya

Contoh Penggunaan

3-Day Moving Average

SQL

1 SELECT
2   date,
3   price,
4   ROUND(AVG(price) OVER (
5     ORDER BY date
6     ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
7   ), 2) as moving_avg_3d
8 FROM stock_prices;

Menghitung 3-day moving average harga saham.

Hasil

date	price	moving_avg_3d
2024-01-01	100	100.00
2024-01-02	105	102.50
2024-01-03	98	101.00

Running Average

SQL

1 SELECT
2   date,
3   sales,
4   ROUND(AVG(sales) OVER (ORDER BY date), 2) as running_avg
5 FROM daily_sales;

Rata-rata berjalan dari awal sampai baris saat ini.

Hasil

date	sales	running_avg
2024-01-01	100	100.00
2024-01-02	200	150.00
2024-01-03	150	150.00

Kembali ke DuckDB Functions

1	SELECT
2	date,
3	price,
4	ROUND(AVG(price) OVER (
5	ORDER BY date
6	ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
7	), 2) as moving_avg_3d
8	FROM stock_prices;

1	SELECT
2	date,
3	sales,
4	ROUND(AVG(sales) OVER (ORDER BY date), 2) as running_avg
5	FROM daily_sales;