diff options
Diffstat (limited to 'stats')
| -rw-r--r-- | stats/Cargo.toml | 2 | ||||
| -rw-r--r-- | stats/src/main.rs | 29 |
2 files changed, 12 insertions, 19 deletions
diff --git a/stats/Cargo.toml b/stats/Cargo.toml index a012b94..c7e36b1 100644 --- a/stats/Cargo.toml +++ b/stats/Cargo.toml @@ -1,6 +1,6 @@ [package] name = "corgi-stats" -version = "0.1.0" +version = "1.1.0" edition = "2024" [dependencies] diff --git a/stats/src/main.rs b/stats/src/main.rs index 7e3f922..5b83bdd 100644 --- a/stats/src/main.rs +++ b/stats/src/main.rs @@ -3,18 +3,16 @@ use std::{io::Write, time::Instant}; use rusqlite::{Connection, params}; use time::{Duration, OffsetDateTime}; -// Thank you, cat, for optimizing my query -const TOP_TEN_ALL_TIME: &str = "\ - SELECT reqs.cnt, agents.agent - FROM agents - JOIN ( - SELECT count(id) as cnt, agent_id - FROM requests - GROUP BY agent_id - ) reqs - ON reqs.agent_id=agents.id - ORDER BY reqs.cnt DESC LIMIT 10; -"; +const TOP_TEN_ALL_TIME: &str = + "SELECT request_count, agent FROM agents ORDER BY request_count DESC LIMIT 10;"; + +const LAST_FIFTEEN_MINUTES: &str = "\ + SELECT count(ephemeral_requests.request_id) as request_count, agents.agent + FROM ephemeral_requests + INNER JOIN requests ON ephemeral_requests.request_id = requests.id + INNER JOIN agents ON requests.agent_id = agents.id + WHERE ephemeral_requests.timestamp > ?1 + GROUP BY requests.agent_id;"; const STYLE: &'static str = include_str!("style.css"); const FAVICON: &'static [u8] = include_bytes!("favicon.gif"); @@ -48,13 +46,8 @@ fn main() { let now = OffsetDateTime::now_utc(); let fifteen_ago = now - Duration::minutes(15); - let query = "SELECT count(requests.id) AS request_count, agents.agent FROM requests \ - INNER JOIN agents ON requests.agent_id = agents.id \ - WHERE requests.timestamp > ?1 \ - GROUP BY requests.agent_id;"; - let start = Instant::now(); - let mut prepared = db.prepare(query).unwrap(); + let mut prepared = db.prepare(LAST_FIFTEEN_MINUTES).unwrap(); let mut agents: Vec<(usize, String)> = prepared .query_map(params![fifteen_ago], |row| Ok((row.get(0)?, row.get(1)?))) .unwrap() |