📋 サプライチェーン技術：WMSとAIの融合 | 第7回：WMS-AI統合の総括とチェックリスト #バイナリテック

import pandas as pd
import psycopg2
from sklearn.metrics import mean_squared_error
import numpy as np

def get_db_connection():
    return psycopg2.connect(
        dbname="dw_db",
        user="postgres",
        password="password",
        host="localhost",
        port="5432"
    )

def aggregate_kpis():
    conn = get_db_connection()
    
    # 同期エラー率
    error_query = """
        SELECT system, COUNT(*) as error_count
        FROM integration_logs
        WHERE status = 'ERROR'
        AND created_at >= CURRENT_DATE - INTERVAL '30 days'
        GROUP BY system
    """
    
    # データ遅延
    latency_query = """
        SELECT system, AVG(EXTRACT(EPOCH FROM (processed_at - created_at))) as avg_latency_seconds
        FROM integration_logs
        WHERE status = 'SUCCESS'
        AND created_at >= CURRENT_DATE - INTERVAL '30 days'
        GROUP BY system
    """
    
    # AI予測精度
    forecast_query = """
        SELECT actual_quantity, predicted_quantity
        FROM inventory_forecasts
        WHERE forecast_date >= CURRENT_DATE - INTERVAL '30 days'
    """
    
    error_df = pd.read_sql(error_query, conn)
    latency_df = pd.read_sql(latency_query, conn)
    forecast_df = pd.read_sql(forecast_query, conn)
    
    conn.close()
    
    # RMSE計算
    rmse = np.sqrt(mean_squared_error(forecast_df['actual_quantity'], forecast_df['predicted_quantity'])) if not forecast_df.empty else None
    
    # KPI集計
    summary = error_df.merge(latency_df, on='system', how='outer').fillna(0)
    summary['inventory_forecast_rmse'] = rmse
    summary.to_csv('wms_ai_kpis.csv', index=False)
    return summary

if __name__ == '__main__':
    kpi_summary = aggregate_kpis()
    print(kpi_summary)