fn-registry agent
101 lines
3.8 KiB
SQL
101 lines
3.8 KiB
SQL
-- Vista agregada por funcion del registry. Lectura O(N) sobre tablas event-log.
|
|
-- Si performance degrada, materializar como TABLE refrescada por cron.
|
|
|
|
DROP VIEW IF EXISTS function_stats;
|
|
|
|
CREATE VIEW function_stats AS
|
|
WITH
|
|
call_agg AS (
|
|
SELECT
|
|
function_id,
|
|
COUNT(*) AS calls_total,
|
|
SUM(CASE WHEN ts >= CAST(strftime('%s','now','-1 day') AS INTEGER) THEN 1 ELSE 0 END) AS calls_24h,
|
|
SUM(CASE WHEN ts >= CAST(strftime('%s','now','-7 days') AS INTEGER) THEN 1 ELSE 0 END) AS calls_7d,
|
|
SUM(CASE WHEN ts >= CAST(strftime('%s','now','-30 days') AS INTEGER) THEN 1 ELSE 0 END) AS calls_30d,
|
|
SUM(CASE WHEN ts >= CAST(strftime('%s','now','-90 days') AS INTEGER) THEN 1 ELSE 0 END) AS calls_90d,
|
|
SUM(CASE WHEN success = 0 THEN 1 ELSE 0 END) AS errors_total,
|
|
AVG(duration_ms) AS mean_duration_ms,
|
|
MAX(ts) AS last_used_at,
|
|
MAX(CASE WHEN success = 0 THEN ts ELSE 0 END) AS last_error_ts
|
|
FROM calls
|
|
WHERE function_id != ''
|
|
GROUP BY function_id
|
|
),
|
|
write_agg AS (
|
|
SELECT function_id, COUNT(*) AS writes_count, MAX(ts) AS last_write_at
|
|
FROM code_writes
|
|
WHERE function_id != ''
|
|
GROUP BY function_id
|
|
),
|
|
test_agg AS (
|
|
SELECT
|
|
function_id,
|
|
COUNT(*) AS tests_total,
|
|
SUM(CASE WHEN passed = 0 THEN 1 ELSE 0 END) AS tests_failed,
|
|
MAX(CASE WHEN passed = 0 THEN ts ELSE 0 END) AS last_test_failed_at
|
|
FROM test_runs
|
|
WHERE function_id != ''
|
|
GROUP BY function_id
|
|
),
|
|
e2e_agg AS (
|
|
SELECT
|
|
function_id,
|
|
COUNT(*) AS e2e_total,
|
|
SUM(CASE WHEN passed = 0 THEN 1 ELSE 0 END) AS e2e_failed,
|
|
COUNT(DISTINCT app_id) AS consumer_apps_count
|
|
FROM e2e_runs_fn
|
|
GROUP BY function_id
|
|
),
|
|
viol_agg AS (
|
|
SELECT function_id, COUNT(*) AS violations_caused
|
|
FROM violations
|
|
WHERE function_id != ''
|
|
GROUP BY function_id
|
|
),
|
|
all_fns AS (
|
|
SELECT function_id FROM call_agg
|
|
UNION
|
|
SELECT function_id FROM write_agg
|
|
UNION
|
|
SELECT function_id FROM test_agg
|
|
UNION
|
|
SELECT function_id FROM e2e_agg
|
|
UNION
|
|
SELECT function_id FROM viol_agg
|
|
)
|
|
SELECT
|
|
f.function_id,
|
|
COALESCE(c.calls_total, 0) AS calls_total,
|
|
COALESCE(c.calls_24h, 0) AS calls_24h,
|
|
COALESCE(c.calls_7d, 0) AS calls_7d,
|
|
COALESCE(c.calls_30d, 0) AS calls_30d,
|
|
COALESCE(c.calls_90d, 0) AS calls_90d,
|
|
COALESCE(c.errors_total, 0) AS errors_total,
|
|
CASE WHEN COALESCE(c.calls_total, 0) > 0
|
|
THEN CAST(c.errors_total AS REAL) / c.calls_total
|
|
ELSE 0 END AS error_rate,
|
|
COALESCE(c.mean_duration_ms, 0) AS mean_duration_ms,
|
|
c.last_used_at,
|
|
CASE WHEN c.last_error_ts > 0 THEN c.last_error_ts END AS last_error_ts,
|
|
COALESCE(w.writes_count, 0) AS writes_count,
|
|
w.last_write_at,
|
|
COALESCE(t.tests_total, 0) AS tests_total,
|
|
COALESCE(t.tests_failed, 0) AS tests_failed,
|
|
CASE WHEN COALESCE(t.tests_total, 0) > 0
|
|
THEN CAST(t.tests_failed AS REAL) / t.tests_total
|
|
ELSE 0 END AS test_fail_rate,
|
|
CASE WHEN t.last_test_failed_at > 0 THEN t.last_test_failed_at END AS last_test_failed_at,
|
|
COALESCE(e.e2e_total, 0) AS e2e_total,
|
|
COALESCE(e.e2e_failed, 0) AS e2e_failed,
|
|
CASE WHEN COALESCE(e.e2e_total, 0) > 0
|
|
THEN CAST(e.e2e_failed AS REAL) / e.e2e_total
|
|
ELSE 0 END AS e2e_fail_rate,
|
|
COALESCE(e.consumer_apps_count, 0) AS consumer_apps_count,
|
|
COALESCE(v.violations_caused, 0) AS violations_caused
|
|
FROM all_fns f
|
|
LEFT JOIN call_agg c ON c.function_id = f.function_id
|
|
LEFT JOIN write_agg w ON w.function_id = f.function_id
|
|
LEFT JOIN test_agg t ON t.function_id = f.function_id
|
|
LEFT JOIN e2e_agg e ON e.function_id = f.function_id
|
|
LEFT JOIN viol_agg v ON v.function_id = f.function_id;
|