144 lines
5.8 KiB
MySQL
144 lines
5.8 KiB
MySQL
-- =============================================================================
|
|
-- KI-Kostenverfolgung für Dokumenten-Check
|
|
-- Tabelle: dc_ai_cost_log
|
|
-- Package: DC_COSTS_PKG
|
|
-- =============================================================================
|
|
-- Deployment:
|
|
-- sqlplus user/pass@db @"Scripts/KI-Kostenverfolgung.sql"
|
|
--
|
|
-- Skript ist idempotent (DROP IF EXISTS vor jedem CREATE).
|
|
-- =============================================================================
|
|
|
|
|
|
-- =============================================================================
|
|
-- Tabelle: dc_ai_cost_log
|
|
-- =============================================================================
|
|
BEGIN
|
|
EXECUTE IMMEDIATE 'DROP TABLE dc_ai_cost_log CASCADE CONSTRAINTS';
|
|
EXCEPTION WHEN OTHERS THEN NULL;
|
|
END;
|
|
/
|
|
|
|
CREATE TABLE dc_ai_cost_log (
|
|
id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
called_at TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,
|
|
provider VARCHAR2(30) NOT NULL, -- mistral | ollama
|
|
model_name VARCHAR2(100) NOT NULL,
|
|
operation VARCHAR2(30) NOT NULL, -- TRANSLATE | EVALUATE | OCR | CATALOG
|
|
project_id NUMBER,
|
|
catalog_id NUMBER,
|
|
prompt_tokens NUMBER DEFAULT 0 NOT NULL,
|
|
completion_tokens NUMBER DEFAULT 0 NOT NULL,
|
|
total_tokens NUMBER DEFAULT 0 NOT NULL,
|
|
cost_eur NUMBER(14, 8) DEFAULT 0 NOT NULL,
|
|
created_at TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,
|
|
created_by VARCHAR2(200) DEFAULT 'SYSTEM' NOT NULL
|
|
);
|
|
|
|
COMMENT ON TABLE dc_ai_cost_log IS 'Protokoll aller KI-API-Aufrufe mit Token-Verbrauch und Kosten';
|
|
COMMENT ON COLUMN dc_ai_cost_log.operation IS 'TRANSLATE, EVALUATE, OCR, CATALOG';
|
|
COMMENT ON COLUMN dc_ai_cost_log.cost_eur IS 'Berechnete Kosten in EUR basierend auf konfigurierten Preisen';
|
|
|
|
CREATE INDEX idx_ai_cost_called_at ON dc_ai_cost_log (called_at);
|
|
CREATE INDEX idx_ai_cost_project_id ON dc_ai_cost_log (project_id);
|
|
CREATE INDEX idx_ai_cost_catalog_id ON dc_ai_cost_log (catalog_id);
|
|
CREATE INDEX idx_ai_cost_ym ON dc_ai_cost_log (TO_CHAR(called_at, 'YYYY-MM'));
|
|
|
|
|
|
-- =============================================================================
|
|
-- PL/SQL Package: DC_COSTS_PKG
|
|
-- =============================================================================
|
|
CREATE OR REPLACE PACKAGE DC_COSTS_PKG AS
|
|
|
|
-- -------------------------------------------------------------------------
|
|
-- Gesamtkosten + Kosten des laufenden Monats
|
|
-- -------------------------------------------------------------------------
|
|
PROCEDURE get_summary(
|
|
p_total_eur OUT NUMBER,
|
|
p_total_tokens OUT NUMBER,
|
|
p_total_calls OUT NUMBER,
|
|
p_month_eur OUT NUMBER,
|
|
p_month_tokens OUT NUMBER,
|
|
p_month_calls OUT NUMBER
|
|
);
|
|
|
|
-- -------------------------------------------------------------------------
|
|
-- Kosten pro Monat + Modell (für APEX-Reports / direkte Abfragen)
|
|
-- Gibt einen REF CURSOR zurück (Spalten: month, provider, model_name,
|
|
-- operation, call_count, prompt_tokens, completion_tokens, total_tokens, cost_eur)
|
|
-- -------------------------------------------------------------------------
|
|
FUNCTION get_monthly_detail RETURN SYS_REFCURSOR;
|
|
|
|
END DC_COSTS_PKG;
|
|
/
|
|
|
|
CREATE OR REPLACE PACKAGE BODY DC_COSTS_PKG AS
|
|
|
|
PROCEDURE get_summary(
|
|
p_total_eur OUT NUMBER,
|
|
p_total_tokens OUT NUMBER,
|
|
p_total_calls OUT NUMBER,
|
|
p_month_eur OUT NUMBER,
|
|
p_month_tokens OUT NUMBER,
|
|
p_month_calls OUT NUMBER
|
|
) IS
|
|
BEGIN
|
|
-- Gesamtkosten (all time)
|
|
SELECT NVL(SUM(cost_eur), 0),
|
|
NVL(SUM(total_tokens), 0),
|
|
COUNT(*)
|
|
INTO p_total_eur, p_total_tokens, p_total_calls
|
|
FROM dc_ai_cost_log;
|
|
|
|
-- Laufender Monat
|
|
SELECT NVL(SUM(cost_eur), 0),
|
|
NVL(SUM(total_tokens), 0),
|
|
COUNT(*)
|
|
INTO p_month_eur, p_month_tokens, p_month_calls
|
|
FROM dc_ai_cost_log
|
|
WHERE called_at >= TRUNC(SYSDATE, 'MM');
|
|
|
|
EXCEPTION
|
|
WHEN NO_DATA_FOUND THEN
|
|
p_total_eur := 0; p_total_tokens := 0; p_total_calls := 0;
|
|
p_month_eur := 0; p_month_tokens := 0; p_month_calls := 0;
|
|
END get_summary;
|
|
|
|
|
|
FUNCTION get_monthly_detail RETURN SYS_REFCURSOR IS
|
|
v_cur SYS_REFCURSOR;
|
|
BEGIN
|
|
OPEN v_cur FOR
|
|
SELECT
|
|
TO_CHAR(called_at, 'YYYY-MM') AS month,
|
|
provider,
|
|
model_name,
|
|
operation,
|
|
COUNT(*) AS call_count,
|
|
SUM(prompt_tokens) AS prompt_tokens,
|
|
SUM(completion_tokens) AS completion_tokens,
|
|
SUM(total_tokens) AS total_tokens,
|
|
ROUND(SUM(cost_eur), 6) AS cost_eur
|
|
FROM dc_ai_cost_log
|
|
WHERE called_at >= ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -11)
|
|
GROUP BY TO_CHAR(called_at, 'YYYY-MM'), provider, model_name, operation
|
|
ORDER BY month DESC, cost_eur DESC;
|
|
RETURN v_cur;
|
|
END get_monthly_detail;
|
|
|
|
END DC_COSTS_PKG;
|
|
/
|
|
|
|
-- =============================================================================
|
|
-- Kurztest (optionale Verifikation nach Deployment)
|
|
-- =============================================================================
|
|
-- DECLARE
|
|
-- v_te NUMBER; v_tt NUMBER; v_tc NUMBER;
|
|
-- v_me NUMBER; v_mt NUMBER; v_mc NUMBER;
|
|
-- BEGIN
|
|
-- DC_COSTS_PKG.get_summary(v_te,v_tt,v_tc,v_me,v_mt,v_mc);
|
|
-- DBMS_OUTPUT.PUT_LINE('Total EUR: ' || v_te || ', Calls: ' || v_tc);
|
|
-- DBMS_OUTPUT.PUT_LINE('Month EUR: ' || v_me || ', Calls: ' || v_mc);
|
|
-- END;
|
|
-- /
|