AI成本归因：基于API日志的多维度统计与异常检测

cost = (input_tokens / 1000) × input_price + (output_tokens / 1000) × output_price

CREATE TABLE ai_usage_logs (
    request_id String,
    timestamp DateTime,
    team String,
    application String,
    model String,
    user_id String,
    scenario String,
    input_tokens UInt32,
    output_tokens UInt32,
    cost Float64,
    response_time_ms UInt32
) ENGINE = MergeTree()
ORDER BY (timestamp, team, application);

SELECT team, SUM(cost) as total_cost
FROM ai_usage_logs
WHERE timestamp >= '2026-03-01'
GROUP BY team
ORDER BY total_cost DESC;

SELECT application, model, SUM(cost) as cost
FROM ai_usage_logs
GROUP BY application, model;

SELECT user_id, SUM(cost) as cost, COUNT(*) as call_count
FROM ai_usage_logs
WHERE timestamp >= today() - INTERVAL 7 DAY
GROUP BY user_id
ORDER BY cost DESC
LIMIT 10;

SELECT scenario, 
       AVG(input_tokens + output_tokens) as avg_tokens,
       SUM(cost) as total_cost
FROM ai_usage_logs
GROUP BY scenario;

SELECT user_id, SUM(cost) as daily_cost
FROM ai_usage_logs
WHERE timestamp >= today()
GROUP BY user_id
HAVING daily_cost > 100;

SELECT user_id, scenario, input_text, COUNT(*) as dup_count
FROM ai_usage_logs
WHERE timestamp >= now() - INTERVAL 1 HOUR
GROUP BY user_id, scenario, input_text
HAVING COUNT(*) > 5;

WITH weekly AS (
  SELECT application, SUM(cost) as cost,
         toStartOfWeek(timestamp) as week
  FROM ai_usage_logs
  GROUP BY application, week
)
SELECT application, 
       cost - lagInFrame(cost, 1) OVER (PARTITION BY application ORDER BY week) as cost_diff
FROM weekly;