Fabricated numbers of some monks and monks with no writeups are outliers that mess up the numbers. So, the query returns two sets of ratios, with and without exclusions. Perhaps a more complicated rule for outliers can be devised.
// ==UserScript==
// @name Perlmonks Saints in our Book to Oracle
// @namespace http://www.example.com/chacham/
// @description Turn the data in Perlmonks Saints in our Book into an
+Oracle query with CTEs
// @include http://perlmonks.org/?node_id=3559
// @version 1.1.1
// @grant none
// ==/UserScript==
var query = "WITH\
\n\t-- From: http://perlmonks.org/?node_id=509805\
\n\tMonk_Level(Id, XP_Low, XP_High, Title)\
\nAS\
\n\t(\
\n\t SELECT 13, 003000, 03999, 'Curate' FROM Dual UNION ALL\
\n\t SELECT 14, 004000, 05399, 'Priest' FROM Dual UNION ALL\
\n\t SELECT 15, 005400, 06999, 'Vicar' FROM Dual UNION ALL\
\n\t SELECT 16, 007000, 08999, 'Parson' FROM Dual UNION ALL\
\n\t SELECT 17, 009000, 11999, 'Prior' FROM Dual UNION ALL\
\n\t SELECT 18, 012000, 15999, 'Monsignor' FROM Dual UNION ALL\
\n\t SELECT 19, 016000, 21999, 'Abbot' FROM Dual UNION ALL\
\n\t SELECT 20, 022000, 29999, 'Canon' FROM Dual UNION ALL\
\n\t SELECT 21, 030000, 39999, 'Chancellor' FROM Dual UNION ALL\
\n\t SELECT 22, 040000, 49999, 'Bishop' FROM Dual UNION ALL\
\n\t SELECT 23, 050000, 59999, 'Archbishop' FROM Dual UNION ALL\
\n\t SELECT 24, 060000, 69999, 'Cardinal' FROM Dual UNION ALL\
\n\t SELECT 25, 070000, 79999, 'Sage' FROM Dual UNION ALL\
\n\t SELECT 26, 080000, 89999, 'Saint' FROM Dual UNION ALL\
\n\t SELECT 27, 090000, 99999, 'Apostle' FROM Dual UNION ALL\
\n\t SELECT 28, 100000, NULL, 'Pope' FROM Dual\
\n\t),\
\n\tData(Ranking, Name, XP, Writeups)\nAS\n\t(";
var table = document.getElementsByTagName('table')[4];
for(var row = 1; row < table.rows.length; row++)
query += "\n\t SELECT "
+ table.rows[row].cells[0].textContent + ",\t'"
+ table.rows[row].cells[1].textContent + "',\t"
+ table.rows[row].cells[2].textContent + ",\t"
+ table.rows[row].cells[4].textContent.replace('None', 0) + "\t FROM
+ Dual UNION ALL";
// Chop off the extra UNION ALL
query = query.slice(0, -10)
+ "\n\t),\
\n\tData_Level(Monk_Level, Title, Ranking, Name, XP, Writeups)\
\nAS\
\n\t(\
\n\t SELECT\
\n\t\tMonk_Level.Id,\
\n\t\tMonk_Level.Title,\
\n\t\tData.Ranking,\
\n\t\tData.Name,\
\n\t\tData.XP,\
\n\t\tData.Writeups\
\n\t FROM\
\n\t\tData,\
\n\t\tMonk_Level\
\n\t WHERE\
\n\t\tData.XP\tBETWEEN Monk_Level.XP_Low AND Monk_Level.XP_High\
\n\t OR\tData.XP >\tMonk_Level.XP_Low AND Monk_Level.XP_High IS NULL
+\
\n\t),\
\n\tData_Level_Calc(Monk_Level, Title, Ranking, Name, XP, Writeups, XP
+_Ratio, Exclude)\
\nAS\
\n\t(\
\n\t SELECT\
\n\t\tMonk_Level,\
\n\t\tTitle,\
\n\t\tRanking,\
\n\t\tName,\
\n\t\tXP,\
\n\t\tWriteups,\
\n\t\tXP / CASE Writeups WHEN 0 THEN 1 ELSE Writeups END,\
\n\t\t-- Monks with fabricated numbers or 0 Writeups mess up the calcu
+lations.\
\n\t\tCASE WHEN Ranking > 1 AND Writeups > 0 THEN 1 END\
\n\t FROM\
\n\t\tData_Level\
\n\t),\
\n\tXP_Ratio_By_Level(Monk_Level, Title, Min, Max, Avg, Min_X, Max_X,
+Avg_X)\
\nAS\
\n\t(\
\n\t SELECT\
\n\t\tMonk_Level,\
\n\t\tTitle,\
\n\t\tMIN(XP_Ratio),\
\n\t\tMAX(XP_Ratio),\
\n\t\tAVG(XP_Ratio),\
\n\t\t-- Exclude from functions by NULLing out the number.\
\n\t\tMIN(XP_Ratio * Exclude),\
\n\t\tMAX(XP_Ratio * Exclude),\
\n\t\tAVG(XP_Ratio * Exclude)\
\n\t FROM\
\n\t\tData_Level_Calc\
\n\t GROUP BY\
\n\t\tMonk_Level,\
\n\t\tTitle\
\n\t UNION ALL\
\n\t SELECT\
\n\t\tNULL,\
\n\t\t'(All)',\
\n\t\tMIN(XP_Ratio),\
\n\t\tMAX(XP_Ratio),\
\n\t\tAVG(XP_Ratio),\
\n\t\tMIN(XP_Ratio * Exclude),\
\n\t\tMAX(XP_Ratio * Exclude),\
\n\t\tAVG(XP_Ratio * Exclude)\
\n\t FROM\
\n\t\tData_Level_Calc\
\n\t),\
\n\tStep_XP_Writeups(Ranking, Name, XP, Writeups, XP_Diff, Writeups_Di
+ff)\
\nAS\
\n\t(\
\n\t SELECT\
\n\t\tRanking,\
\n\t\tName,\
\n\t\tXP,\
\n\t\tWriteups,\
\n\t\tXP - LEAD(XP) OVER(ORDER BY Ranking),\
\n\t\tWriteups - LEAD(Writeups) OVER(ORDER BY Ranking)\
\n\t FROM\
\n\t\tData\
\n),\
\n\tClose(Ranking, XP_Diff, Writeups_Diff)\
\nAS\
\n\t(\
\n\t SELECT\
\n\t\tRanking,\
\n\t\tXP - LEAD(XP) OVER(ORDER BY Ranking),\
\n\t\tWriteups - LEAD(Writeups) OVER(ORDER BY Ranking)\
\n\t FROM\
\n\t\tStep_XP_Writeups\
\n\t WHERE\
\n\t\tXP_Diff\tBETWEEN -30 AND 30\
\n\t AND\tWriteups_Diff\tBETWEEN -5 AND 5\
\n),\
\n\tMMA(XP_Diff_Min, XP_Diff_Max, XP_Diff_Avg, Writeups_Diff_Min, Writ
+eups_Diff_Max, Writeups_Diff_Avg)\
\nAS\
\n\t(\
\n\t SELECT\
\n\t\tMIN(XP_Diff),\
\n\t\tMAX(XP_Diff),\
\n\t\tAVG(XP_Diff),\
\n\t\tMIN(Writeups_Diff),\
\n\t\tMAX(Writeups_Diff),\
\n\t\tAVG(Writeups_Diff)\
\n\t FROM\
\n\t\tStep_XP_Writeups\
\n\t),\
\n\tStats(Both, Close, XP, XP_Diff_Min, XP_Diff_Max, XP_Diff_Avg, Writ
+eups, Writeups_Diff_Min, Writeups_Diff_Max, Writeups_Diff_Avg)\
\nAS\
\n\t(\
\n\t SELECT\
\n\t\t(SELECT COUNT(COUNT(*)) FROM Data GROUP BY XP, Writeups HAVIN
+G Count(*) > 1),\
\n\t\t(SELECT COUNT(*) FROM Close),\
\n\t\t(SELECT COUNT(COUNT(*)) FROM Data GROUP BY XP HAVING Count(*) >
+1),\
\n\t\tXP_Diff_Min,\
\n\t\tXP_Diff_Max,\
\n\t\tXP_Diff_Avg,\
\n\t\t(SELECT COUNT(COUNT(*)) FROM Data GROUP BY Writeups HAVING Count
+(*) > 1) Writeups,\
\n\t\tWriteups_Diff_Min,\
\n\t\tWriteups_Diff_Max,\
\n\t\tWriteups_Diff_Avg\
\n\t FROM\
\n\t\tMMA\n\t)\
\n--SELECT Monk_Level, Title, ROUND(Min, 2) Min, ROUND(Max, 2) Max, RO
+UND(Avg, 2) Avg, ROUND(Min_X, 2) Min_X, ROUND(Max_X, 2) Max_X, ROUND(
+Avg_X, 2) Avg_X FROM XP_Ratio_By_Level ORDER BY Monk_Level\
\n--SELECT Both, Close, XP, XP_Diff_Min, XP_Diff_Max, XP_Diff_Avg, Wri
+teups, Writeups_Diff_Min, Writeups_Diff_Max, Writeups_Diff_Avg FROM S
+tats\
\nSELECT\
\n\tData.Ranking,\
\n\tData.Name,\
\n\tData.XP,\
\n\tData.Writeups\
\nFROM\
\n\tData,\n\tClose\
\nWHERE\
\n\tData.Ranking BETWEEN Close.Ranking AND Close.Ranking + 1\
\nORDER BY\
\n\tData.Ranking;";
var textarea = document.createElement('textarea');
textarea.cols = 100;
textarea.rows = 20;
textarea.value = query;
document.body.appendChild(textarea);
Notable, perhaps, is the average with exclusions is just over 43. Certainly more outliers can be excluded to "correct" that number. :)
