Materialize

-- 最近のイベント用のリアルタイムマテリアライズドビュー
CREATE MATERIALIZED VIEW analytics.recent_user_events AS
SELECT
    event_name,
    user_id,
    event_timestamp,
    event_properties,
    session_id,
    device_type
FROM raw_events.stream
WHERE event_timestamp >= NOW() - INTERVAL '1 day';

-- 高価値イベントマテリアライズドビュー
CREATE MATERIALIZED VIEW analytics.high_value_events AS
SELECT
    event_name,
    user_id,
    event_timestamp,
    event_properties || jsonb_build_object(
        'source', 'materialize',
        'value_tier', 'high'
    ) as event_properties
FROM raw_events.stream
WHERE (event_properties->>'value')::numeric > 100;

-- リアルタイムユーザーアクティビティサマリー
SELECT
    'activity_summary' as event_name,
    user_id,
    NOW() as event_timestamp,
    jsonb_build_object(
        'events_last_hour', COUNT(*),
        'unique_sessions', COUNT(DISTINCT session_id),
        'total_value', SUM((event_properties->>'value')::numeric),
        'last_seen', MAX(event_timestamp)
    ) as event_properties
FROM analytics.recent_user_events
WHERE event_timestamp >= NOW() - INTERVAL '1 hour'
GROUP BY user_id
HAVING COUNT(*) >= 5;

-- プログレッシブプロファイリングビュー
CREATE MATERIALIZED VIEW analytics.user_progression AS
SELECT
    user_id,
    COUNT(*) as total_events,
    COUNT(DISTINCT event_name) as unique_event_types,
    MAX(event_timestamp) as last_activity,
    CASE
        WHEN COUNT(*) >= 50 THEN 'power_user'
        WHEN COUNT(*) >= 20 THEN 'active_user'
        WHEN COUNT(*) >= 5 THEN 'engaged_user'
        ELSE 'new_user'
    END as user_segment
FROM raw_events.stream
GROUP BY user_id;