Rolling Average

 

Rolling Average — সম্পূর্ণ ব্যাখ্যা 📖

---

১. আগে একটা Real Story দিয়ে শুরু করি

🌡️ তোমার নিজের জীবনের Example

ধরো তুমি প্রতিদিন পড়াশোনা করো। এই মাসের প্রথম ৭ দিন:

সোমবার    → ৩ ঘণ্টা
মঙ্গলবার  → ১ ঘণ্টা  (অসুস্থ ছিলে)
বুধবার    → ৫ ঘণ্টা
বৃহস্পতিবার → ২ ঘণ্টা
শুক্রবার  → ৮ ঘণ্টা  (exam এর আগের দিন)
শনিবার    → ১ ঘণ্টা  (exam দিলে)
রবিবার    → ৪ ঘণ্টা

এখন তোমার মা জিজ্ঞেস করলো —

"তুমি প্রতিদিন গড়ে কতক্ষণ পড়ো?"

তুমি যদি শুধু শুক্রবার এর কথা বলো → ৮ ঘণ্টা (সত্য না) তুমি যদি শুধু মঙ্গলবার এর কথা বলো → ১ ঘণ্টা (সত্য না)

সত্যিকারের picture পেতে হলে কয়েকদিনের average দেখতে হবে।

এই "কয়েকদিনের চলমান average" টাই হলো Rolling Average।

---

২. Rolling Average কী? (Definition)

Rolling Average হলো — একটা নির্দিষ্ট সংখ্যক দিনের data এর average, যেটা প্রতিদিন এগিয়ে যায় (roll করে)।

সহজ ভাষায়:

আজকের Rolling Average (৭ দিনের)
= আজ সহ গত ৭ দিনের average

কালকের Rolling Average (৭ দিনের)
= কাল সহ গত ৭ দিনের average
(মানে আজ থেকে ৮ দিন আগেরটা বাদ যাবে, কালকেরটা ঢুকবে)

এভাবে প্রতিদিন "roll" করে এগিয়ে যায়।

---

৩. Rolling Average এর প্রকারভেদ

Rolling Average কত দিনের হবে সেটা
use case এর উপর নির্ভর করে।

সবচেয়ে Common:

7-Day Rolling Average   → সাপ্তাহিক trend দেখতে
14-Day Rolling Average  → দুই সপ্তাহের trend দেখতে
30-Day Rolling Average  → মাসিক trend দেখতে

---

৪. Step by Step Calculate করি

FoodBD App এর Daily Orders এর Data:

তারিখ        Orders
──────────────────
Jan 1   →    100
Jan 2   →    120
Jan 3   →     80   (বৃষ্টির দিন, কম order)
Jan 4   →    200   (বিশেষ offer ছিলো)
Jan 5   →    150
Jan 6   →    130
Jan 7   →    110
Jan 8   →    140
Jan 9   →    160
Jan 10  →    170

---

3-Day Rolling Average Calculate করি:

Jan 1  → শুধু ১ দিনের data আছে, rolling avg নেই
Jan 2  → শুধু ২ দিনের data আছে, rolling avg নেই
Jan 3  → (100 + 120 + 80)  ÷ 3 = 100.0
Jan 4  → (120 + 80 + 200)  ÷ 3 = 133.3
Jan 5  → (80 + 200 + 150)  ÷ 3 = 143.3
Jan 6  → (200 + 150 + 130) ÷ 3 = 160.0
Jan 7  → (150 + 130 + 110) ÷ 3 = 130.0
Jan 8  → (130 + 110 + 140) ÷ 3 = 126.7
Jan 9  → (110 + 140 + 160) ÷ 3 = 136.7
Jan 10 → (140 + 160 + 170) ÷ 3 = 156.7

লক্ষ্য করো:

Jan 4 এ actual orders ছিলো ২০০ (হঠাৎ বেশি)
কিন্তু Jan 4 এর Rolling Average = 133.3

মানে Rolling Average এই হঠাৎ বাড়াটাকে
smooth করে দিয়েছে।
এটাই Rolling Average এর কাজ।

---

৫. কেন Rolling Average দরকার?

সমস্যা — Raw Data তে Noise থাকে:

Raw Daily Orders দেখলে:

400 ┤                    ╭╮
350 ┤                   ╭╯╰╮
300 ┤          ╭╮       ╯   ╰╮
250 ┤         ╭╯╰╮           ╰╮    ╭╮
200 ┤    ╭╮  ╭╯   ╰╮          ╰╮  ╭╯╰
150 ┤   ╭╯╰╮╭╯     ╰╮          ╰╮╭╯
100 ┤  ╭╯  ╰╯        ╰╮         ╰╯
 50 ┤ ╭╯               ╰╮
  0 ┼─────────────────────────────────
    Jan1                          Jan30

খুব উঁচু-নিচু (Noisy) — trend বোঝা কঠিন।

সমাধান — Rolling Average দিয়ে Smooth করো:

7-Day Rolling Average দেখলে:

400 ┤
350 ┤
300 ┤                        ╭───
250 ┤              ╭─────────╯
200 ┤        ╭─────╯
150 ┤  ╭─────╯
100 ┤──╯
 50 ┤
  0 ┼─────────────────────────────────
    Jan1                          Jan30

মসৃণ line — trend clearly বোঝা যাচ্ছে।
Orders ধীরে ধীরে বাড়ছে।

---

৬. Real Business Example — FoodBD App

Scenario:

Manager জিজ্ঞেস করলো:
"জানুয়ারিতে আমাদের daily orders এর
trend কেমন ছিলো?"

Raw data দেখলে বোঝা কঠিন কারণ:
- Weekend এ বেশি order
- বৃষ্টির দিনে কম order
- Offer এর দিনে হঠাৎ বেশি order

7-Day Rolling Average দেখলে:
এই সব fluctuation smooth হয়ে
actual trend বের হয়ে আসে।

---

৭. SQL দিয়ে Rolling Average Calculate করা

Query 1 — 7-Day Rolling Average (Basic)

SELECT
    order_date,
    daily_orders,

    -- 7-Day Rolling Average
    ROUND(
        AVG(daily_orders) OVER (
            ORDER BY order_date
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        )
    , 2) AS rolling_avg_7day

FROM (
    -- প্রতিদিনের total orders count করো
    SELECT
        DATE(created_at)    AS order_date,
        COUNT(*)            AS daily_orders
    FROM orders
    WHERE created_at >= NOW() - INTERVAL '30 days'
    GROUP BY DATE(created_at)
) daily_data

ORDER BY order_date;

Output:

order_date  | daily_orders | rolling_avg_7day
------------|--------------|------------------
2024-01-01  |    100       |   100.00
2024-01-02  |    120       |   110.00
2024-01-03  |     80       |   100.00
2024-01-04  |    200       |   125.00
2024-01-05  |    150       |   130.00
2024-01-06  |    130       |   130.00
2024-01-07  |    110       |   127.14   ← এখন থেকে পুরো ৭ দিনের avg
2024-01-08  |    140       |   132.86
2024-01-09  |    160       |   138.57
2024-01-10  |    170       |   151.43

---

Query 2 — Multiple Rolling Averages একসাথে

SELECT
    order_date,
    daily_orders,

    -- 7-Day Rolling Average
    ROUND(AVG(daily_orders) OVER (
        ORDER BY order_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ), 2)  AS rolling_7day,

    -- 14-Day Rolling Average
    ROUND(AVG(daily_orders) OVER (
        ORDER BY order_date
        ROWS BETWEEN 13 PRECEDING AND CURRENT ROW
    ), 2)  AS rolling_14day,

    -- 30-Day Rolling Average
    ROUND(AVG(daily_orders) OVER (
        ORDER BY order_date
        ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
    ), 2)  AS rolling_30day

FROM (
    SELECT
        DATE(created_at)  AS order_date,
        COUNT(*)          AS daily_orders
    FROM orders
    GROUP BY DATE(created_at)
) daily_data

ORDER BY order_date;

Output:

order_date  | daily_orders | rolling_7day | rolling_14day | rolling_30day
------------|--------------|--------------|---------------|---------------
2024-01-01  |    100       |   100.00     |    100.00     |    100.00
2024-01-07  |    110       |   127.14     |    127.14     |    127.14
2024-01-14  |    180       |   155.71     |    141.43     |    141.43
2024-01-21  |    220       |   198.57     |    177.14     |    163.33
2024-01-30  |    260       |   238.57     |    215.71     |    178.00

---

Query 3 — Rolling Average দিয়ে Anomaly ধরা

-- যেদিন actual orders, rolling average এর চেয়ে
-- ৫০% বেশি বা কম সেদিন alert করো

SELECT
    order_date,
    daily_orders,
    rolling_7day,

    -- Difference percentage
    ROUND(
        (daily_orders - rolling_7day) * 100.0
        / NULLIF(rolling_7day, 0)
    , 2) AS diff_percentage,

    -- Flag করো
    CASE
        WHEN daily_orders > rolling_7day * 1.5
            THEN '🔺 Unusually HIGH'
        WHEN daily_orders < rolling_7day * 0.5
            THEN '🔻 Unusually LOW'
        ELSE '✅ Normal'
    END AS status

FROM (
    SELECT
        order_date,
        daily_orders,
        ROUND(AVG(daily_orders) OVER (
            ORDER BY order_date
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ), 2) AS rolling_7day
    FROM (
        SELECT
            DATE(created_at) AS order_date,
            COUNT(*)         AS daily_orders
        FROM orders
        GROUP BY DATE(created_at)
    ) d
) final_data

ORDER BY order_date;

Output:

order_date  | daily_orders | rolling_7day | diff_pct | status
------------|--------------|--------------|----------|-------------------
2024-01-04  |    200       |   125.00     |  +60.0%  | 🔺 Unusually HIGH
2024-01-11  |     50       |   130.00     |  -61.5%  | 🔻 Unusually LOW
2024-01-18  |    150       |   145.00     |   +3.4%  | ✅ Normal

Jan 4 — কোনো special offer ছিলো? Jan 11 — কোনো সমস্যা হয়েছিলো? App down ছিলো?

---

৮. Python দিয়ে Rolling Average Calculate করা

import pandas as pd
import psycopg2

conn = psycopg2.connect(
    host="your-host",
    database="your_db",
    user="your_user",
    password="your_password"
)

query = """
    SELECT
        DATE(created_at)  AS order_date,
        COUNT(*)          AS daily_orders
    FROM orders
    WHERE created_at >= NOW() - INTERVAL '30 days'
    GROUP BY DATE(created_at)
    ORDER BY order_date
"""

df = pd.read_sql(query, conn)
conn.close()

# Date column কে datetime এ convert করো
df['order_date'] = pd.to_datetime(df['order_date'])
df = df.sort_values('order_date').reset_index(drop=True)

# =============================================
# ROLLING AVERAGE CALCULATE
# =============================================

# 7-Day Rolling Average
df['rolling_7day'] = (
    df['daily_orders']
    .rolling(window=7, min_periods=1)
    .mean()
    .round(2)
)

# 14-Day Rolling Average
df['rolling_14day'] = (
    df['daily_orders']
    .rolling(window=14, min_periods=1)
    .mean()
    .round(2)
)

# 30-Day Rolling Average
df['rolling_30day'] = (
    df['daily_orders']
    .rolling(window=30, min_periods=1)
    .mean()
    .round(2)
)

print("============================================")
print("      ROLLING AVERAGE REPORT               ")
print("============================================")
print(df[[
    'order_date',
    'daily_orders',
    'rolling_7day',
    'rolling_14day',
    'rolling_30day'
]].to_string(index=False))

Output:

============================================
      ROLLING AVERAGE REPORT
============================================
order_date  daily_orders  rolling_7day  rolling_14day  rolling_30day
2024-01-01           100        100.00         100.00         100.00
2024-01-02           120        110.00         110.00         110.00
2024-01-03            80        100.00         100.00         100.00
2024-01-04           200        125.00         125.00         125.00
2024-01-05           150        130.00         130.00         130.00
2024-01-06           130        130.00         130.00         130.00
2024-01-07           110        127.14         127.14         127.14
2024-01-08           140        132.86         132.86         132.86
2024-01-09           160        138.57         138.57         138.57
2024-01-10           170        151.43         151.43         151.43

---

# =============================================
# ANOMALY DETECTION
# =============================================
df['diff_pct'] = (
    (df['daily_orders'] - df['rolling_7day'])
    * 100 / df['rolling_7day']
).round(2)

def get_status(diff):
    if diff > 50:
        return '🔺 Unusually HIGH'
    elif diff < -50:
        return '🔻 Unusually LOW'
    else:
        return '✅ Normal'

df['status'] = df['diff_pct'].apply(get_status)

# শুধু Anomaly গুলো দেখাও
anomalies = df[df['status'] != '✅ Normal']

print("\n===== Anomaly Report =====")
if len(anomalies) == 0:
    print("কোনো Anomaly নেই। সব Normal ✅")
else:
    print(anomalies[[
        'order_date',
        'daily_orders',
        'rolling_7day',
        'diff_pct',
        'status'
    ]].to_string(index=False))

Output:

===== Anomaly Report =====
order_date  daily_orders  rolling_7day  diff_pct  status
2024-01-04           200        125.00     60.00   🔺 Unusually HIGH
2024-01-11            50        130.00    -61.54   🔻 Unusually LOW

---

৯. ETL — Daily Rolling Average Save করা

# etl_rolling_average.py
# প্রতিদিন সকাল ৭টায় CRON এ run হবে

import psycopg2
from datetime import datetime

def calculate_and_save_rolling_avg():
    conn = psycopg2.connect(
        host="your-host",
        database="your_db",
        user="your_user",
        password="your_password"
    )
    cursor = conn.cursor()

    # Calculate করো এবং summary table এ save করো
    upsert_query = """
        INSERT INTO order_rolling_avg_summary (
            summary_date,
            daily_orders,
            rolling_avg_7day,
            rolling_avg_14day,
            rolling_avg_30day
        )
        SELECT
            order_date,
            daily_orders,

            ROUND(AVG(daily_orders) OVER (
                ORDER BY order_date
                ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
            ), 2),

            ROUND(AVG(daily_orders) OVER (
                ORDER BY order_date
                ROWS BETWEEN 13 PRECEDING AND CURRENT ROW
            ), 2),

            ROUND(AVG(daily_orders) OVER (
                ORDER BY order_date
                ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
            ), 2)

        FROM (
            SELECT
                DATE(created_at)  AS order_date,
                COUNT(*)          AS daily_orders
            FROM orders
            WHERE created_at >= NOW() - INTERVAL '30 days'
            GROUP BY DATE(created_at)
        ) daily_data

        ON CONFLICT (summary_date)
        DO UPDATE SET
            daily_orders      = EXCLUDED.daily_orders,
            rolling_avg_7day  = EXCLUDED.rolling_avg_7day,
            rolling_avg_14day = EXCLUDED.rolling_avg_14day,
            rolling_avg_30day = EXCLUDED.rolling_avg_30day,
            updated_at        = NOW()
    """

    cursor.execute(upsert_query)
    conn.commit()

    print(f"[{datetime.now()}] Rolling Average ETL Complete.")
    cursor.close()
    conn.close()

if __name__ == "__main__":
    calculate_and_save_rolling_avg()

Crontab:

# প্রতিদিন সকাল ৭টায় চলবে
0 7 * * * /usr/bin/python3 /home/etl/etl_rolling_average.py

---

১০. Superset এ Rolling Average দেখানো

-- Superset Line Chart এর জন্য
-- Raw data এবং Rolling Average একসাথে দেখাও

SELECT
    summary_date        AS date,
    daily_orders        AS "Daily Orders",
    rolling_avg_7day    AS "7-Day Rolling Avg",
    rolling_avg_30day   AS "30-Day Rolling Avg"
FROM order_rolling_avg_summary
WHERE summary_date >= NOW() - INTERVAL '30 days'
ORDER BY summary_date;

Superset এ Line Chart বানাও। তিনটা line একসাথে দেখাবে। Raw data উঁচু-নিচু হবে। Rolling Average smooth line হবে।

---

১১. Window Size কত রাখবো?

Window ছোট (3-7 দিন):
→ Recent change দ্রুত ধরা যায়
→ কিন্তু এখনো একটু noisy থাকে
→ কখন use করবে: Short-term trend দেখতে

Window বড় (14-30 দিন):
→ অনেক smooth হয়
→ কিন্তু recent change দেরিতে দেখা যায়
→ কখন use করবে: Long-term trend দেখতে

Production এ সাধারণত:
Daily dashboard → 7-Day Rolling Average
Monthly report  → 30-Day Rolling Average

---

১২. Common Mistakes ⚠️

Mistake	সমস্যা	সমাধান
min_periods না দেওয়া	প্রথম কয়েকদিন NULL আসে	min_periods=1 দাও
Window size ঠিক না করা	অনেক বড় window দিলে সব smooth হয়ে যায়	Use case অনুযায়ী window বেছে নাও
Missing dates handle না করা	কোনো দিনের data না থাকলে skip হয়	সব dates fill করো, missing = 0
Timezone ignore করা	ভুল দিনে data পড়ে	সবসময় timezone specify করো

---

সারসংক্ষেপ 🎯

বিষয়	মূল কথা
Rolling Average কী	নির্দিষ্ট কয়েকদিনের চলমান average
কেন দরকার	Raw data এর noise কমিয়ে actual trend দেখাতে
Common Windows	7-day, 14-day, 30-day
SQL এ	AVG() OVER (ROWS BETWEEN N PRECEDING AND CURRENT ROW)
Python এ	df['col'].rolling(window=7).mean()
Superset এ	Line chart এ raw + rolling একসাথে দেখাও

---