\pset pager off
\pset tuples_only on
\pset fieldsep '\t'
-- 20:30-21:20 account 218 汇总
SELECT 'window_total', count(*) FROM ops_error_logs
WHERE created_at >= TIMESTAMPTZ '2026-05-11 20:30:00+08' AND created_at < TIMESTAMPTZ '2026-05-11 21:20:00+08' AND account_id=218;
SELECT 'window_by_msg', count(*), coalesce(upstream_status_code,0), left(error_message,120)
FROM ops_error_logs
WHERE created_at >= TIMESTAMPTZ '2026-05-11 20:30:00+08' AND created_at < TIMESTAMPTZ '2026-05-11 21:20:00+08' AND account_id=218
GROUP BY 3,4 ORDER BY 2 DESC LIMIT 20;
-- 从 upstream_errors 抽具体 request_error message
SELECT 'reqerr', count(*), left(elem->>'message',220)
FROM ops_error_logs l
CROSS JOIN LATERAL jsonb_array_elements(coalesce(l.upstream_errors,'[]'::jsonb)) elem
WHERE l.created_at >= TIMESTAMPTZ '2026-05-11 20:30:00+08' AND l.created_at < TIMESTAMPTZ '2026-05-11 21:20:00+08' AND l.account_id=218
  AND (elem->>'kind') ILIKE '%request_error%'
GROUP BY 3 ORDER BY 2 DESC LIMIT 20;
-- 从 upstream_errors 抽 failover message/status
SELECT 'failmsg', count(*), left(coalesce(elem->>'message', elem->>'detail'),220)
FROM ops_error_logs l
CROSS JOIN LATERAL jsonb_array_elements(coalesce(l.upstream_errors,'[]'::jsonb)) elem
WHERE l.created_at >= TIMESTAMPTZ '2026-05-11 20:30:00+08' AND l.created_at < TIMESTAMPTZ '2026-05-11 21:20:00+08' AND l.account_id=218
  AND (elem->>'kind') ILIKE '%failover%'
GROUP BY 3 ORDER BY 2 DESC LIMIT 20;
