DB 조회 쿼리 총정리eval_ph1

-- 이 프로그램 실험 한 번에 (스코어보드)
SELECT exp_id, verdict, team_before||' → '||team_after AS teams,
       key_evidence, lesson
FROM experiments
WHERE exp_id IN (144,147,153,154,155,159,166,167)
ORDER BY exp_id;

-- (1a) 상세 — 가설·개입·판정·교훈
SELECT exp_id, title, verdict, hypothesis, intervention, lesson,
       batch_before, batch_after, concluded_at
FROM experiments WHERE exp_id IN (144,147,153,154,155,159,166,167)
ORDER BY exp_id;

-- (1b) key_evidence(JSONB) 메트릭만 펼치기 — before/after 배열
SELECT exp_id,
       key_evidence->'pass_at_1'      AS pass_at_1,
       key_evidence->'avg_tokens'     AS avg_tokens,
       key_evidence->'loop_rate'      AS loop_rate,
       key_evidence->'grounded_pct'   AS grounded_pct,
       key_evidence->'report_sources' AS report_sources
FROM experiments WHERE exp_id IN (144,147,153,154,155,159);

-- (1c) 특정 키가 있는 실험만 (예: report_sources를 움직인 실험)
SELECT exp_id, title, key_evidence->'report_sources'
FROM experiments WHERE key_evidence ? 'report_sources';

-- (1d) Grafana annotation 뷰 (개입 시점 마커)
SELECT * FROM v_experiment_annotations ORDER BY time DESC;

-- (2a) 이 프로그램 신규 팀 + 에이전트 수·종료조건 요약
SELECT team_id, topology,
       jsonb_array_length(agents) AS n_agents,
       jsonb_path_query_array(agents, '$[*].name')  AS agent_names,
       jsonb_path_query_array(agents, '$[*].tools') AS tools
FROM teams
WHERE team_id LIKE 'gaia-%' OR team_id LIKE 'role-%'
ORDER BY team_id;

-- (2b) 특정 팀의 구성 전문(프롬프트 해시·도구·종료조건)
SELECT team_id, description, prompt_bundle_hash, config_full
FROM teams WHERE team_id = 'role-research-v2';

-- (2c) before/after 팀이 1변수만 다른지 — 해시 비교(같으면 종료조건만 변경)
SELECT team_id, prompt_bundle_hash FROM teams
WHERE team_id IN ('role-research','role-research-v2');

REFRESH MATERIALIZED VIEW mv_batch_summary;  -- 먼저 갱신

-- (3a) 이 프로그램 모든 배치의 핵심 메트릭
SELECT batch_id, team_id, n_runs,
       round(pass_at_1::numeric,3)          AS pass_at_1,
       round(format_invalid_rate::numeric,3) AS fmt_invalid,
       round(loop_rate::numeric,3)          AS loop_rate,
       round(avg_turns::numeric,2)          AS avg_turns,
       round(avg_tokens::numeric,0)         AS avg_tokens,
       round(tokens_per_success::numeric,0) AS tok_per_succ,
       round(p95_latency::numeric,0)        AS p95_ms
FROM mv_batch_summary
WHERE batch_id LIKE 'metric_%' OR batch_id LIKE 'role_%'
ORDER BY batch_id;

-- (4a) 원장 ↔ 메트릭 자동 조인 — 한 실험의 before/after를 한 행 옆에
--      runs에서 batch 단위로 직접 집계(레벨 분할 없는 깔끔한 1:1). exp_id만 바꿔 재사용.
WITH e AS (SELECT * FROM experiments WHERE exp_id = 147),
agg AS (
  SELECT batch_id,
         round(avg(success::int)::numeric,3)                         AS pass_at_1,
         round(avg((coalesce(terminated_by,'')='max_messages')::int)::numeric,3) AS loop_rate,
         round(avg(total_turns)::numeric,2)                          AS avg_turns,
         round(avg(total_tokens)::numeric,0)                         AS avg_tokens,
         percentile_cont(0.95) WITHIN GROUP (ORDER BY latency_ms)::int AS p95_ms
  FROM runs WHERE status='completed' GROUP BY batch_id)
SELECT e.exp_id,
       b.pass_at_1 AS pass_before, a.pass_at_1 AS pass_after,
       b.loop_rate AS loop_before, a.loop_rate AS loop_after,
       b.avg_tokens AS tok_before, a.avg_tokens AS tok_after,
       b.p95_ms AS p95_before, a.p95_ms AS p95_after
FROM e
JOIN agg b ON b.batch_id = e.batch_before
JOIN agg a ON a.batch_id = e.batch_after;
-- ⚠️ mv_batch_summary는 (batch,team,level) 단위라 GAIA 혼합레벨 배치는 batch만으로 조인하면
--    행이 여러 개로 곱해진다 → 위처럼 runs에서 batch 단위 집계가 안전.

-- (4b) task별 정오 변화 (어느 task가 회복/회귀했나)
SELECT t.task_id, left(t.question,50) q, t.gold_answer,
       bf.success AS before_ok, bf.final_answer AS before_ans,
       af.success AS after_ok,  af.final_answer AS after_ans
FROM runs bf
JOIN runs af USING (task_id)
JOIN tasks t USING (task_id)
WHERE bf.batch_id = 'metric_grounding_web_20260615'   -- before
  AND af.batch_id = 'metric_ma_20260615'              -- after
  AND bf.success IS DISTINCT FROM af.success
ORDER BY t.task_id;

-- (5a) analysis 벤치(리서치 역할) — grounded_pct = 실제 검색 성공율
SELECT batch_id, team_id, n_runs, grounded_pct, avg_search_ok, avg_search_calls
FROM v_analysis_grounded_summary
WHERE batch_id LIKE 'role_research%' ORDER BY batch_id;

-- (5b) run 단위 grounded 판정 (검색 시도 vs 성공)
SELECT batch_id, team_id, task_id, n_search, n_search_ok, grounded
FROM v_analysis_grounded WHERE batch_id = 'role_research_v2_20260616';

-- (5c) GAIA grounding(데모#3/#4)은 전용 뷰가 없으니 events에서 직접 —
--      검색 성공(실결과) 수: tool_result 가 "(검색 실패…)"로 시작하지 않으면 성공
SELECT r.batch_id, r.team_id,
       count(*) FILTER (WHERE e.message_type='tool_result'
              AND NOT (e.tool_result #>> '{}') LIKE '(%')        AS search_ok,
       count(*) FILTER (WHERE e.message_type='tool_call')         AS search_calls
FROM runs r JOIN events e USING (run_id)
WHERE r.batch_id IN ('metric_grounding_web_20260615','metric_ma_20260615')
  AND e.tool_name LIKE '%search%'
GROUP BY r.batch_id, r.team_id;

-- (6a) report_sources 근사 — 본문 'http' 출현 수 (Grafana와 동일 방식)
SELECT batch_id, team_id,
       round(avg((length(final_answer) -
             length(replace(lower(final_answer),'http','')))/4.0)::numeric,1) AS report_sources
FROM runs WHERE batch_id IN ('role_research_base_20260616','role_research_v2_20260616')
  AND status='completed' GROUP BY batch_id, team_id;

# (6b) Python 파생(recency/CER/coverage/kill)은 scorer로 — 예:
.venv/bin/python -c "
import sys; sys.path.insert(0,'src')
from agent_eval.roles.scorer import ocr_cer            # CER
from agent_eval.research.recency import score_text     # recency
# DB에서 final_answer·gold 뽑아 함수에 넣어 계산"

-- (7a) 한 task의 before/after 최종답 원문
SELECT batch_id, final_answer, length(final_answer) AS len
FROM runs
WHERE task_id = 'analysis_ai_accelerator'
  AND batch_id IN ('role_research_base_20260616','role_research_v2_20260616');

-- (7b) run 전사 — 에이전트 발화 순서 (page의 '실제 전사')
SELECT e.seq, e.agent_name, e.message_type, left(e.content,200) AS content
FROM events e JOIN runs r USING (run_id)
WHERE r.batch_id = 'metric_loop_base_20260615'
  AND r.task_id = (SELECT task_id FROM tasks WHERE question ILIKE '%drive across the U.S%')
  AND e.message_type IN ('agent_message','tool_call','tool_result')
ORDER BY e.seq;

-- (7c) 검색어만 추출 (멀티에이전트 검색 journey)
SELECT e.seq, e.agent_name, e.tool_args->>'query' AS query
FROM events e JOIN runs r USING (run_id)
WHERE r.batch_id = 'metric_ma_20260615' AND e.message_type='tool_call'
  AND e.tool_name LIKE '%search%' ORDER BY e.seq;

REFRESH MATERIALIZED VIEW mv_tool_summary;
-- (8a) 배치별 도구 호출·에러
SELECT batch_id, tool_name, calls, errors, round(avg_latency::numeric,0) AS ms
FROM mv_tool_summary WHERE batch_id LIKE 'metric_grounding%' OR batch_id LIKE 'metric_ma%';

-- (8b) 검색 실패 사유 분포 (firecrawl vs DDG 차단 등)
SELECT (tool_result #>> '{}') AS result, count(*)
FROM events WHERE tool_name LIKE '%search%' AND message_type='tool_result'
  AND (tool_result #>> '{}') LIKE '(%' GROUP BY 1 ORDER BY 2 DESC;

REFRESH MATERIALIZED VIEW mv_passk;
SELECT batch_id, team_id,
       round(avg(pass_any::int)::numeric,2) AS pass_any,   -- 상한(가능성)
       round(avg(pass_all::int)::numeric,2) AS pass_all,   -- 하한(안정성)
       max(k) AS k
FROM mv_passk WHERE batch_id LIKE 'role_ocr_hard_v3%' GROUP BY 1,2;

-- 완료인데 success 누락
SELECT run_id FROM runs WHERE status='completed' AND success IS NULL;

-- tool_call 인데 응답(result/error) 짝 없음
SELECT e.run_id, e.seq FROM events e
WHERE e.message_type='tool_call' AND NOT EXISTS (
  SELECT 1 FROM events r WHERE r.run_id=e.run_id AND r.tool_call_id=e.tool_call_id
    AND r.message_type IN ('tool_result','tool_error'));

-- 특정 실험(EXP-N)을 통째로 보기: 원장 + 양쪽 배치 메트릭 + task별 정오
-- 1) experiments에서 batch_before/after 확인 → 2) §4a 조인 → 3) §4b task별