04. 시계열 트렌드 분석 (Time Series)

SELECT
    DATE(opened_at) as date,
    COUNT(*) as ticket_count
FROM `your-project-id.retail_analytics_us.cs_tickets_dummy`
GROUP BY date
ORDER BY date;

# 일별 집계
daily_counts = tickets.groupby(tickets['opened_at'].dt.date).size()
 
# 시각화
plt.figure(figsize=(12, 5))
daily_counts.plot(kind='line', marker='o')
plt.title('Daily Ticket Volume')
plt.grid(True)
plt.show()

WITH daily_stats AS (
    SELECT DATE(opened_at) as date, COUNT(*) as count
    FROM `your-project-id.retail_analytics_us.cs_tickets_dummy`
    GROUP BY 1
)
SELECT
    date,
    count,
    ROUND(AVG(count) OVER (
        ORDER BY date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ), 2) as ma_7day
FROM daily_stats
ORDER BY date;

# 이동 평균 계산
rolling_7d = daily_counts.rolling(window=7).mean()
 
# 시각화 비교
plt.figure(figsize=(12, 5))
daily_counts.plot(label='Daily (Raw)', alpha=0.3)
rolling_7d.plot(label='7-Day MA', color='red', linewidth=2)
plt.legend()
plt.show()

WITH weekly_stats AS (
    SELECT
        DATE_TRUNC(opened_at, WEEK(MONDAY)) as week_start,
        COUNT(*) as weekly_count
    FROM `your-project-id.retail_analytics_us.cs_tickets_dummy`
    GROUP BY week_start
)
SELECT
    week_start,
    weekly_count,
    LAG(weekly_count) OVER (ORDER BY week_start) as prev_week_count,
    ROUND(
        (weekly_count - LAG(weekly_count) OVER (ORDER BY week_start)) * 100.0 /
        LAG(weekly_count) OVER (ORDER BY week_start),
        2
    ) as wow_change_pct
FROM weekly_stats
ORDER BY week_start;

# 주별 리샘플링
weekly_counts = tickets.set_index('opened_at').resample('W-MON').size()
 
# 변화율 계산
wow_pct = weekly_counts.pct_change() * 100
 
print(wow_pct.tail())

WITH stats AS (
    SELECT
        DATE(opened_at) as date,
        COUNT(*) as count
    FROM `your-project-id.retail_analytics_us.cs_tickets_dummy`
    GROUP BY 1
),
bounds AS (
    SELECT
        AVG(count) as avg_val,
        STDDEV(count) as std_val
    FROM stats
)
SELECT
    s.date,
    s.count,
    CASE
        WHEN s.count > (b.avg_val + 2 * b.std_val) THEN 'Anomaly High'
        WHEN s.count < (b.avg_val - 2 * b.std_val) THEN 'Anomaly Low'
        ELSE 'Normal'
    END as status
FROM stats s, bounds b
ORDER BY s.count DESC;

mean = daily_counts.mean()
std = daily_counts.std()
threshold = mean + 2 * std
 
# 이상치 필터링
anomalies = daily_counts[daily_counts > threshold]
 
print(f"평균: {mean:.1f}, 임계값: {threshold:.1f}")
print("이상 급증일:")
print(anomalies)