dml-analytics.sql (12967B)
1 -- countdown-bot analytic functions and procedures 2 3 DROP FUNCTION IF EXISTS speedData; 4 DROP PROCEDURE IF EXISTS progressStats; 5 DROP FUNCTION IF EXISTS progressData; 6 DROP FUNCTION IF EXISTS leaderboardData; 7 DROP FUNCTION IF EXISTS historicalContributorData; 8 DROP PROCEDURE IF EXISTS heatmapStats; 9 DROP FUNCTION IF EXISTS heatmapData; 10 DROP FUNCTION IF EXISTS etaData; 11 DROP FUNCTION IF EXISTS contributorData; 12 13 -- Get overall contributor data for a countdown 14 CREATE FUNCTION contributorData ( 15 _countdownID BIGINT -- The countdown channel ID 16 ) 17 RETURNS TABLE ( 18 ranking BIGINT, -- The user's (1-based) contribution ranking 19 userID BIGINT, -- The user ID 20 contributions BIGINT, -- The user's number of contributions 21 percentage FLOAT -- The user's percentage of all contributions 22 ) 23 LANGUAGE plpgsql AS $$ 24 DECLARE 25 progress INT; 26 BEGIN 27 -- Get total countdown progress 28 SELECT count(messageID) 29 INTO progress 30 FROM messages 31 WHERE countdownID = _countdownID; 32 33 RETURN QUERY 34 SELECT 35 rank() OVER (ORDER BY count(messageID) DESC) AS ranking, 36 messages.userID, 37 count(messageID) AS contributions, 38 (100.0 * count(messageID) / progress)::float AS percentage 39 FROM messages 40 WHERE countdownID = _countdownID 41 GROUP BY messages.userID; 42 END 43 $$; 44 45 -- Calculate the current ETA for each message in a countdown 46 CREATE FUNCTION etaData ( 47 _countdownID BIGINT -- The countdown channel ID 48 ) 49 RETURNS TABLE ( 50 _timestamp TIMESTAMP, -- The timestamp of the message 51 eta TIMESTAMP -- The timestamp of the current ETA 52 ) 53 LANGUAGE plpgsql AS $$ 54 DECLARE 55 total INT; 56 startTime INT; 57 _timezone INTERVAL; 58 BEGIN 59 -- Get total and startTime from first message 60 SELECT value, extract(epoch FROM timestamp) 61 INTO total, startTime 62 FROM messages 63 WHERE countdownID = _countdownID 64 ORDER BY messageID ASC 65 LIMIT 1; 66 67 -- Get timezone 68 SELECT timezone 69 INTO _timezone 70 FROM countdowns 71 WHERE countdownID = _countdownID; 72 73 -- Calculate eta for each message 74 RETURN QUERY 75 SELECT 76 timestamp AT TIME ZONE _timezone, 77 to_timestamp(startTime + total * 78 (extract(epoch FROM timestamp) - startTime) / (total - value) 79 ) AT TIME ZONE _timezone AS eta 80 FROM messages 81 WHERE countdownID = _countdownID AND value != total 82 ORDER BY messageID; 83 END 84 $$; 85 86 -- Count the number of contributions in a countdown for each day/hour zone 87 CREATE FUNCTION heatmapData ( 88 _countdownID BIGINT, -- The countdown channel ID 89 _userID BIGINT -- The user ID to filter by (or NULL for all users) 90 ) 91 RETURNS TABLE ( 92 dow NUMERIC, -- The day of the week (0-6 for Sun-Sat) 93 hour NUMERIC, -- The hour of the day (0-23) 94 messages BIGINT -- The number of contributions in the zone 95 ) 96 LANGUAGE plpgsql AS $$ 97 DECLARE 98 _timezone INTERVAL; 99 BEGIN 100 -- Get timezone 101 SELECT timezone 102 INTO _timezone 103 FROM countdowns 104 WHERE countdownID = _countdownID; 105 106 RETURN QUERY 107 SELECT 108 extract(dow FROM timestamp AT TIME ZONE _timezone) AS dow, 109 extract(hour FROM timestamp AT TIME ZONE _timezone) AS hour, 110 count(messageID) as messages 111 FROM messages 112 WHERE countdownID = _countdownID AND (userID = _userID OR _userID IS NULL) 113 GROUP BY dow, hour; 114 END 115 $$; 116 117 CREATE PROCEDURE heatmapStats ( 118 _countdownID IN BIGINT, -- The countdown channel ID 119 curDow OUT NUMERIC, -- The current day of the week (0-6 for Sun-Sat) 120 curHour OUT NUMERIC -- The current hour of the day (0-23) 121 ) 122 LANGUAGE plpgsql AS $$ 123 BEGIN 124 SELECT 125 extract(dow FROM NOW() AT TIME ZONE timezone) AS dow, 126 extract(hour FROM NOW() AT TIME ZONE timezone) AS hour 127 INTO curDow, curHour 128 FROM countdowns 129 WHERE countdownID = _countdownID; 130 END 131 $$; 132 133 -- Calculate each user's contribution percentage at each message in a countdown 134 CREATE FUNCTION historicalContributorData ( 135 _countdownID BIGINT -- The countdown channel ID 136 ) 137 RETURNS TABLE ( 138 progress INT, -- The current countdown progress (0-total) 139 userID BIGINT, -- The user ID 140 percentage FLOAT -- The user's percentage of all contributions so far 141 ) 142 LANGUAGE plpgsql AS $$ 143 DECLARE 144 total INT; 145 BEGIN 146 -- Get total from first message 147 SELECT value 148 INTO total 149 FROM messages 150 WHERE countdownID = _countdownID 151 ORDER BY messageID ASC 152 LIMIT 1; 153 154 -- Calculator percentage for each user for each message 155 RETURN QUERY 156 SELECT 157 (total - value) AS progress, 158 users.userID, 159 ( 160 sum(CASE messages.userID WHEN users.userID THEN 1 ELSE 0 END) 161 OVER (PARTITION BY users.userID ORDER BY messageID) 162 )::float / (total - value + 1) 163 FROM messages, ( 164 SELECT DISTINCT messages.userID 165 FROM messages 166 WHERE countdownID = _countdownID 167 ) users 168 WHERE countdownID = _countdownID; 169 END 170 $$; 171 172 -- Get the current leaderboard data for a countdown 173 CREATE FUNCTION leaderboardData ( 174 _countdownID BIGINT, -- The countdown channel ID 175 _userID BIGINT -- The user ID to filter by (or NULL for all users) 176 ) 177 RETURNS TABLE ( 178 ranking BIGINT, -- The user's (1-based) leaderboard ranking 179 userID BIGINT, -- The user ID 180 total BIGINT, -- The user's total leaderboard points 181 contributions BIGINT, -- The user's number of contributions 182 percentage FLOAT, -- The user's percentage of all contributions 183 r1 BIGINT, r2 BIGINT, r3 BIGINT, r4 BIGINT, r5 BIGINT, r6 BIGINT, 184 r7 BIGINT, r8 BIGINT, r9 BIGINT -- The number of each point rule applied 185 ) 186 LANGUAGE plpgsql AS $$ 187 DECLARE 188 total INT; 189 progress INT; 190 BEGIN 191 -- Get total from first message 192 SELECT value 193 INTO total 194 FROM messages 195 WHERE countdownID = _countdownID 196 ORDER BY messageID ASC 197 LIMIT 1; 198 199 -- Get progress from last message 200 SELECT total - value + 1 201 INTO progress 202 FROM messages 203 WHERE countdownID = _countdownID 204 ORDER BY messageID DESC 205 LIMIT 1; 206 207 RETURN QUERY 208 SELECT * FROM ( 209 -- Assign rankings based on total points 210 SELECT row_number() OVER (ORDER BY points.total DESC), * 211 FROM ( 212 -- Count points and rule breakdowns for each user 213 SELECT categorizedMessages.userID, 214 sum(CASE rule 215 WHEN 1 THEN 0 -- First 216 WHEN 2 THEN 1000 -- 1000s 217 WHEN 3 THEN 500 -- 1001s 218 WHEN 4 THEN 200 -- 200s 219 WHEN 5 THEN 100 -- 201s 220 WHEN 6 THEN 100 -- 100s 221 WHEN 7 THEN 50 -- 101s 222 WHEN 8 THEN 12 -- Odds 223 ELSE 10 -- Evens 224 END) AS total, 225 count(rule) AS contributions, 226 (100.0 * count(rule) / progress)::float AS percentage, 227 sum(CASE rule WHEN 1 THEN 1 ELSE 0 END) AS r1, 228 sum(CASE rule WHEN 2 THEN 1 ELSE 0 END) AS r2, 229 sum(CASE rule WHEN 3 THEN 1 ELSE 0 END) AS r3, 230 sum(CASE rule WHEN 4 THEN 1 ELSE 0 END) AS r4, 231 sum(CASE rule WHEN 5 THEN 1 ELSE 0 END) AS r5, 232 sum(CASE rule WHEN 6 THEN 1 ELSE 0 END) AS r6, 233 sum(CASE rule WHEN 7 THEN 1 ELSE 0 END) AS r7, 234 sum(CASE rule WHEN 8 THEN 1 ELSE 0 END) AS r8, 235 sum(CASE rule WHEN 9 THEN 1 ELSE 0 END) AS r9 236 FROM ( 237 -- Get qualifying rule for each message 238 SELECT 239 messages.userID, 240 CASE TRUE 241 WHEN value=total THEN 1 -- First 242 WHEN value%1000=0 THEN 2 -- 1000s 243 WHEN value%1000=1 THEN 3 -- 1001s 244 WHEN value%200=0 THEN 4 -- 200s 245 WHEN value%200=1 THEN 5 -- 201s 246 WHEN value%100=0 THEN 6 -- 100s 247 WHEN value%100=1 THEN 7 -- 101s 248 WHEN value%2=1 THEN 8 -- Odds 249 ELSE 9 -- Evens 250 END AS rule 251 FROM messages 252 WHERE countdownID = _countdownID 253 ) categorizedMessages 254 GROUP BY categorizedMessages.userID 255 ) points 256 ) rankings 257 WHERE rankings.userID = _userID OR _userID IS NULL; 258 END 259 $$; 260 261 -- Get the current progress for each message in a countdown 262 CREATE FUNCTION progressData ( 263 _countdownID BIGINT -- The countdown channel ID 264 ) 265 RETURNS TABLE ( 266 _timestamp TIMESTAMP, -- The timestamp of the message 267 progress INT -- The current countdown progress (0-total) 268 ) 269 LANGUAGE plpgsql AS $$ 270 DECLARE 271 _timezone INTERVAL; 272 BEGIN 273 -- Get timezone 274 SELECT timezone 275 INTO _timezone 276 FROM countdowns 277 WHERE countdownID = _countdownID; 278 279 RETURN QUERY 280 SELECT timestamp AT TIME ZONE _timezone, value 281 FROM messages 282 WHERE countdownID = _countdownID 283 ORDER BY messageID; 284 END 285 $$; 286 287 -- Get general progress-related statistics for a countdown 288 CREATE PROCEDURE progressStats ( 289 _countdownID IN BIGINT, -- The countdown channel ID 290 total OUT INT, -- The starting value 291 current OUT INT, -- The current value 292 progress OUT INT, -- The countdown progress (0-total) 293 percentage OUT DECIMAL, -- The percentage completion 294 startTime OUT TIMESTAMP, -- The start timestamp 295 startAge OUT INTERVAL, -- The time since the start 296 endTime OUT TIMESTAMP, -- The real/predicted finish timestamp 297 endAge OUT INTERVAL, -- The time since/until the finish 298 rate OUT DECIMAL, -- The rate of contributions per day 299 longestBreak OUT INTERVAL, -- The longest break in contributions 300 longestBreakStart OUT TIMESTAMP, -- The start of the longest break 301 longestBreakEnd OUT TIMESTAMP -- The end of the longest break 302 ) 303 LANGUAGE plpgsql AS $$ 304 DECLARE 305 _timezone INTERVAL; 306 _now TIMESTAMP; 307 BEGIN 308 -- Get timezone 309 SELECT timezone 310 INTO _timezone 311 FROM countdowns 312 WHERE countdownID = _countdownID; 313 314 SELECT NOW() AT TIME ZONE _timezone INTO _now; 315 316 -- Get total and startTime from first message 317 SELECT messages.value, messages.timestamp 318 INTO total, startTime AT TIME ZONE _timezone 319 FROM messages 320 WHERE messages.countdownID = _countdownID 321 ORDER BY messageID ASC 322 LIMIT 1; 323 324 -- Get current and endTime from last message 325 SELECT messages.value, messages.timestamp 326 INTO current, endTime AT TIME ZONE _timezone 327 FROM messages 328 WHERE messages.countdownID = _countdownID 329 ORDER BY messageID DESC 330 LIMIT 1; 331 332 -- Calculate progress and percent 333 progress := total - current; 334 percentage := 100.0 * progress / total; 335 336 -- Calculate rate and update endTime 337 IF current = 0 THEN 338 -- Countdown has ended, so endTime is already correct 339 rate := (total - current) / extract(epoch FROM (endTime - startTime)); 340 ELSEIF progress = 0 THEN 341 -- Countdown only has 1 message 342 rate := 0; 343 endTime = NOW(); 344 ELSE 345 rate := progress / extract(epoch FROM (_now - startTime)); 346 endTime := to_timestamp(extract(epoch FROM _now) + (current / rate)) 347 AT TIME ZONE _timezone; 348 END IF; 349 rate := rate * 60 * 60 * 24; -- Adjust rate from per sec to per day 350 351 -- Calculate startAge and endAge 352 startAge := _now - startTime; 353 endAge := _now - endTime; 354 355 -- Calculate longestBreak, longestBreakStart, and longestBreakEnd 356 SELECT 357 timestamp AT TIME ZONE _timezone, 358 CASE 359 WHEN value = 0 THEN '0' 360 ELSE LEAD(timestamp, 1, NOW()) OVER (ORDER BY messageID) - timestamp 361 END AS delta 362 INTO longestBreakStart, longestBreak 363 FROM messages 364 WHERE messages.countdownID = _countdownID 365 ORDER BY delta DESC 366 LIMIT 1; 367 longestBreakEnd := longestBreakStart + longestBreak; 368 END 369 $$; 370 371 -- Calculate the number of contributions per period in a countdown 372 CREATE FUNCTION speedData ( 373 _countdownID BIGINT, -- The countdown channel ID 374 hours INT -- The period size, in hours 375 ) 376 RETURNS TABLE ( 377 periodStart TIMESTAMP, -- The start of the period 378 messages BIGINT -- The number of contributions in the period 379 ) 380 LANGUAGE plpgsql AS $$ 381 DECLARE 382 _timezone INTERVAL; 383 BEGIN 384 -- Get timezone 385 SELECT timezone 386 INTO _timezone 387 FROM countdowns 388 WHERE countdownID = _countdownID; 389 390 RETURN QUERY 391 SELECT 392 to_timestamp( 393 floor(extract(epoch FROM timestamp AT TIME ZONE _timezone) / hours 394 / 3600)::int * hours * 3600 395 ) AT TIME ZONE '0:00' AS periodStart, 396 count(messageID) as messages 397 FROM messages 398 WHERE countdownID = _countdownID 399 GROUP BY periodStart; 400 END 401 $$;