Materialized View Creator

-- PostgreSQL: Sales summary materialized view
CREATE MATERIALIZED VIEW sales_monthly_summary AS
SELECT 
    DATE_TRUNC('month', order_date) as month,
    product_category,
    COUNT(*) as order_count,
    SUM(total_amount) as total_revenue,
    AVG(total_amount) as avg_order_value,
    COUNT(DISTINCT customer_id) as unique_customers
FROM orders o
JOIN products p ON o.product_id = p.id
WHERE order_date >= '2020-01-01'
GROUP BY DATE_TRUNC('month', order_date), product_category
WITH DATA;

-- Create indexes for common query patterns
CREATE INDEX idx_sales_summary_month ON sales_monthly_summary(month);
CREATE INDEX idx_sales_summary_category ON sales_monthly_summary(product_category);

-- Oracle: Materialized view with fast refresh capability
CREATE MATERIALIZED VIEW LOG ON orders WITH ROWID;
CREATE MATERIALIZED VIEW LOG ON products WITH ROWID;

CREATE MATERIALIZED VIEW customer_order_stats
REFRESH FAST ON DEMAND
AS
SELECT 
    o.customer_id,
    COUNT(*) as total_orders,
    SUM(o.total_amount) as lifetime_value,
    MAX(o.order_date) as last_order_date,
    COUNT(DISTINCT p.category) as categories_purchased
FROM orders o
JOIN products p ON o.product_id = p.id
GROUP BY o.customer_id;

-- PostgreSQL: Rolling metrics materialized view
CREATE MATERIALIZED VIEW user_activity_rolling AS
SELECT 
    user_id,
    activity_date,
    daily_sessions,
    -- 7-day rolling averages
    AVG(daily_sessions) OVER (
        PARTITION BY user_id 
        ORDER BY activity_date 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) as sessions_7day_avg,
    -- 30-day rolling totals
    SUM(daily_sessions) OVER (
        PARTITION BY user_id 
        ORDER BY activity_date 
        ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
    ) as sessions_30day_total
FROM (
    SELECT 
        user_id,
        DATE(created_at) as activity_date,
        COUNT(*) as daily_sessions
    FROM user_sessions
    WHERE created_at >= CURRENT_DATE - INTERVAL '90 days'
    GROUP BY user_id, DATE(created_at)
) daily_stats
WITH DATA;

-- Concurrent refresh with minimal locking
CREATE MATERIALIZED VIEW product_stats AS
SELECT 
    category,
    COUNT(*) as product_count,
    AVG(price) as avg_price,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY price) as median_price
FROM products
GROUP BY category
WITH DATA;

-- Refresh without blocking queries
REFRESH MATERIALIZED VIEW CONCURRENTLY product_stats;

-- Snowflake: Auto-refreshing materialized view
CREATE DYNAMIC TABLE sales_dashboard
TARGET_LAG = '5 minutes'
WAREHOUSE = analytics_wh
AS
SELECT 
    DATE_TRUNC('hour', transaction_time) as hour,
    region,
    SUM(amount) as hourly_sales,
    COUNT(DISTINCT customer_id) as unique_customers
FROM transactions
WHERE transaction_time >= DATEADD('day', -7, CURRENT_TIMESTAMP())
GROUP BY DATE_TRUNC('hour', transaction_time), region;

-- PostgreSQL: Partitioned materialized view
CREATE MATERIALIZED VIEW monthly_metrics_2024_01 AS
SELECT 
    metric_type,
    AVG(value) as avg_value,
    MAX(value) as max_value,
    COUNT(*) as sample_count
FROM metrics
WHERE recorded_at >= '2024-01-01' 
  AND recorded_at < '2024-02-01'
GROUP BY metric_type
WITH DATA;

-- Automate refresh with proper constraint
ALTER MATERIALIZED VIEW monthly_metrics_2024_01 
ADD CONSTRAINT check_month 
CHECK (recorded_at >= '2024-01-01' AND recorded_at < '2024-02-01');

-- Use appropriate data types and compression
CREATE MATERIALIZED VIEW compact_analytics AS
SELECT 
    event_date::DATE,
    user_segment::VARCHAR(20),
    metric_value::DECIMAL(10,2),
    event_count::INTEGER
FROM detailed_events
WHERE event_date >= CURRENT_DATE - INTERVAL '1 year'
WITH DATA;

-- Set appropriate fillfactor for frequent updates
ALTER MATERIALIZED VIEW compact_analytics SET (fillfactor = 85);

-- Create refresh procedure
CREATE OR REPLACE FUNCTION refresh_sales_views()
RETURNS void AS $$
BEGIN
    -- Refresh in dependency order
    REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales;
    REFRESH MATERIALIZED VIEW CONCURRENTLY weekly_sales;
    REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales;

    -- Log refresh completion
    INSERT INTO mv_refresh_log (view_name, refresh_time, status)
    VALUES ('sales_views', NOW(), 'success');
EXCEPTION
    WHEN OTHERS THEN
        INSERT INTO mv_refresh_log (view_name, refresh_time, status, error_msg)
        VALUES ('sales_views', NOW(), 'failed', SQLERRM);
        RAISE;
END;
$$ LANGUAGE plpgsql;

-- Monitor materialized view usage and performance
SELECT 
    schemaname,
    matviewname,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||matviewname)) as size,
    n_tup_ins + n_tup_upd + n_tup_del as total_changes
FROM pg_stat_user_tables t
JOIN pg_matviews mv ON t.relname = mv.matviewname
ORDER BY pg_total_relation_size(schemaname||'.'||matviewname) DESC;