Statbus II: Statnarok

(Mainly the wiki)
User avatar
Atlanta-Ned
In-Game Game Master
Joined: Fri Apr 18, 2014 2:11 pm
Byond Username: Atlanta-ned

Re: Statbus II: Statnarok

Post by Atlanta-Ned » #391234

Bottom post of the previous page:

Polls have returned https://atlantaned.space/statbus/poll.php
Your me page now has a role time chart again https://atlantaned.space/statbus/me.php
Jordie gave me a better/faster solution for finding durations of stuff from the DB. <3 u jordie
Statbus! | Admin Feedback
OOC: Pizzatiger: God damn Atlanta, how are you so fucking smart and charming. It fucking pisses me off how perfect you are
User avatar
pubby
Github User
Joined: Sun Jul 24, 2016 3:45 pm
Byond Username: Pubby
Github Username: pubby

Re: Statbus II: Statnarok

Post by pubby » #391852

I don't think traitor_uplink_items_bought is working correctly. It's not listing every item and the values seem off.
User avatar
Atlanta-Ned
In-Game Game Master
Joined: Fri Apr 18, 2014 2:11 pm
Byond Username: Atlanta-ned

Re: Statbus II: Statnarok

Post by Atlanta-Ned » #405498

The round end report json, which tells you who survived and who didn't, is available now: https://atlantaned.space/statbus/round. ... _data.json
I also tweaked some stat views so they work better.
Statbus! | Admin Feedback
OOC: Pizzatiger: God damn Atlanta, how are you so fucking smart and charming. It fucking pisses me off how perfect you are
User avatar
Atlanta-Ned
In-Game Game Master
Joined: Fri Apr 18, 2014 2:11 pm
Byond Username: Atlanta-ned

Re: Statbus II: Statnarok

Post by Atlanta-Ned » #410849

I just pushed an update to the admin activity page: https://atlantaned.space/statbus/info.php?admins
Note that some admins(me) won't show up due to inconsistencies between the admin table and the rest of the database. Unfortunately resolving that is out of my hands. nevermind mysql supports regex and this is probably not optimal.

Ghost minutes are (assumed) time spent actively adminning, while living minutes are (assumed) spent playing the game. This data goes back to since we started tracking time spent playing roles. As with connection numbers, this data should only be used to get a rough idea of how active an admin is; this is not the definitive authority on whether or not they're dead.

Also! Poly is back on the homepage if you're logged in! Hooray!
Statbus! | Admin Feedback
OOC: Pizzatiger: God damn Atlanta, how are you so fucking smart and charming. It fucking pisses me off how perfect you are
User avatar
Qbopper
Joined: Fri Jul 10, 2015 6:34 pm
Byond Username: Qbopper
Github Username: Qbopper
Location: Canada

Re: Statbus II: Statnarok

Post by Qbopper » #410913

I think the GBP page is still busted
Limey wrote:its too late.
User avatar
Atlanta-Ned
In-Game Game Master
Joined: Fri Apr 18, 2014 2:11 pm
Byond Username: Atlanta-ned

Re: Statbus II: Statnarok

Post by Atlanta-Ned » #410931

Qbopper wrote:I think the GBP page is still busted
it is in fact, still busted. I will fix it tomorrow.
Statbus! | Admin Feedback
OOC: Pizzatiger: God damn Atlanta, how are you so fucking smart and charming. It fucking pisses me off how perfect you are
User avatar
Atlanta-Ned
In-Game Game Master
Joined: Fri Apr 18, 2014 2:11 pm
Byond Username: Atlanta-ned

Re: Statbus II: Statnarok

Post by Atlanta-Ned » #411082

Atlanta-Ned wrote:
Qbopper wrote:I think the GBP page is still busted
it is in fact, still busted. I will fix it tomorrow.
Fixed it and rolled it out into its own microservice.

Please update your bookmarks: https://atlantaned.space/coderbus/
Statbus! | Admin Feedback
OOC: Pizzatiger: God damn Atlanta, how are you so fucking smart and charming. It fucking pisses me off how perfect you are
User avatar
Atlanta-Ned
In-Game Game Master
Joined: Fri Apr 18, 2014 2:11 pm
Byond Username: Atlanta-ned

Re: Statbus II: Statnarok

Post by Atlanta-Ned » #412108

Admin ranks & flags viewer: https://atlantaned.space/statbus/info.php?ranks
Green indicates they have that flag, user+pencil icon indicates that users can edit those flags.
Statbus! | Admin Feedback
OOC: Pizzatiger: God damn Atlanta, how are you so fucking smart and charming. It fucking pisses me off how perfect you are
User avatar
iamgoofball
Github User
Joined: Fri Apr 18, 2014 5:50 pm
Byond Username: Iamgoofball
Github Username: Iamgoofball

Re: Statbus II: Statnarok

Post by iamgoofball » #416838

reposting from discord
Image
User avatar
DemonFiren
Joined: Sat Dec 13, 2014 9:15 pm
Byond Username: DemonFiren

Re: Statbus II: Statnarok

Post by DemonFiren » #416886

how does kekhi not know what the energy shotgun is

remember the space hotel
Image
Image
Image
ImageImageImageImageImage

non-lizard things:
Spoiler:
Image
User avatar
Denton
Joined: Wed Aug 23, 2017 3:53 pm
Byond Username: Denton-30
Github Username: 81Denton

Re: Statbus II: Statnarok

Post by Denton » #431484

traitor_uplink_items_bought seems to leave out most items since December 2017.
For example, compare July 2018 with October 2017:

https://atlantaned.space/statbus/stat.p ... 18&month=7
https://atlantaned.space/statbus/stat.p ... 7&month=10
Image
User avatar
Mickyan
Github User
Joined: Tue Oct 14, 2014 11:59 pm
Byond Username: Mickyan
Github Username: Mickyan

Re: Statbus II: Statnarok

Post by Mickyan » #433550

Logs appear to be broken, the last available logs are from the 12th of August
ImageI play on Manuel as Swanni, the brain-damaged moth.
Be nice to each other.
Image
Image
Image
Image
Image
Kingfish
Joined: Sun Dec 10, 2017 5:36 am
Byond Username: Enkment

Re: Statbus II: Statnarok

Post by Kingfish » #434829

parsed logs fucked again
User avatar
Atlanta-Ned
In-Game Game Master
Joined: Fri Apr 18, 2014 2:11 pm
Byond Username: Atlanta-ned

Re: Statbus II: Statnarok

Post by Atlanta-Ned » #446025

I want to post about the filter I developed for the cat people poll. Here are the unfiltered results.

When I asked, MSO's requirements for the filter were:
1 hour of play time (time spent in living job role) in the 30 days leading up to the vote being created (by summing the role_time_log table for the time period)
...and the query below was submitted for his approval before it was posted publicly.

Here's the raw SQL query that I came up with:

Code: Select all

SELECT
count(DISTINCT tbl_poll_vote.id) AS votes,
tbl_poll_option.text AS `option`
FROM tbl_poll_vote
LEFT JOIN tbl_poll_option ON tbl_poll_vote.optionid = tbl_poll_option.id
LEFT JOIN tbl_role_time_log ON tbl_poll_vote.ckey = tbl_role_time_log.ckey AND (tbl_role_time_log.job = 'Living')
LEFT JOIN tbl_poll_question ON tbl_poll_vote.pollid = tbl_poll_question.id
WHERE tbl_poll_vote.pollid = ?
AND tbl_role_time_log.datetime BETWEEN tbl_poll_question.starttime - INTERVAL 30 DAY AND tbl_poll_question.starttime
GROUP BY tbl_poll_vote.optionid
HAVING sum(tbl_role_time_log.delta) >= 60
ORDER BY votes DESC;
First off, every instance of `tbl_` is replaced with the actual table prefix used by the database. This allows statbus to be used regardless of what database prefix is being used.

Every vote cast gets a unique, automatically incrementing ID associated with it. This allows the database to keep track of everything, and speeds up queries. This is a standard practice across relational databases. Everything in tgdb polls is referenced by an ID. `count(DISTINCT tbl_poll_vote.id) AS votes` simply tallies up the number of unique IDs and provides it in the result as a column titled 'votes'.

In tgdb, polls are split across a few different tables:
  • `ss13poll_question` contains the basic information about the poll, like what the question is, who started the poll, how long it runs for, etc.
  • `ss13poll_option` are the options associated with the poll. So answers like 'yes', 'no', 'atlantaned is the best admin' are stored in this table. These answers are associated with, and tied to, the ID column from `ss13poll_question`.
  • `ss13poll_vote` is a record of the actual vote cast by a player. This records the poll ID, and the ID of the vote option that the player selected. The player's ckey is also saved as well.
It seems crazy that this complex query only returns two columns, the vote option text and how many votes were cast for it. The actual filtering part starts with `LEFT JOIN tbl_role_time_log ON tbl_poll_vote.ckey = tbl_role_time_log.ckey AND (tbl_role_time_log.job = 'Living')`:

This line takes the ckey that gets saved when a player casts a vote, and references it with EVERY entry they have in `ss13role_time_log`. This is a really useful table that stores how long someone spent as a role, saved every 10 minutes. Some 'broader' data is also stored in `ss13role_time_log`, such as how long someone spends living (not as a ghost). So if I spend ten minutes playing captain, I also spent ten minutes living. Make sense?

So we're only collecting how much time a player has spent living when we join `ss13role_time_log`. The second part of the filter is this line: `AND tbl_role_time_log.datetime BETWEEN tbl_poll_question.starttime - INTERVAL 30 DAY AND tbl_poll_question.starttime`

`ss13role_time_log` also includes a timestamp of when the data was recorded. This line above says "only look at entries for this ckey in `ss13role_time_log` that were entered within 30 days before the start of the poll". So we're only looking at time spent playing during that period.

`WHERE tbl_poll_vote.pollid = ?` simply selects the poll data based on the poll ID. The `?` is a placeholder for a the poll ID, the replacement/securing of which is handled by PHP.

Without `GROUP BY tbl_poll_vote.optionid`, we'd get a result of almost 600 rows. This line tells the query to count based on votes that share the same optionid.

Finally, the last part of the filter, `HAVING sum(tbl_role_time_log.delta) >= 60`. This lets me use an aggregate function, `sum()`, to further restrict the query. In this case, we're taking all those minutes we found from `ss13role_time_log` and summing them up. And if the sum is greater than or equal to 60 (one hour of playtime within 30 days of the poll starting), we count it!

Lastly, `ORDER BY votes DESC;` simply orders the results by the `votes` column, in descending order.

I hope this has been illuminating for you.If there are any questions feel free to ask them here, or ping me on discord.
Statbus! | Admin Feedback
OOC: Pizzatiger: God damn Atlanta, how are you so fucking smart and charming. It fucking pisses me off how perfect you are
User avatar
DemonFiren
Joined: Sat Dec 13, 2014 9:15 pm
Byond Username: DemonFiren

Re: Statbus II: Statnarok

Post by DemonFiren » #446058

brb, playing two rounds every month so I can vote on this shit next time
Image
Image
Image
ImageImageImageImageImage

non-lizard things:
Spoiler:
Image
User avatar
Atlanta-Ned
In-Game Game Master
Joined: Fri Apr 18, 2014 2:11 pm
Byond Username: Atlanta-ned

Re: Statbus II: Statnarok

Post by Atlanta-Ned » #446367

I redesigned the round view pages: https://atlantaned.space/statbus/round.php?round=95561
And I'm updating the icons to a newer version of the file. So everything is in fact horribly broken.
Statbus! | Admin Feedback
OOC: Pizzatiger: God damn Atlanta, how are you so fucking smart and charming. It fucking pisses me off how perfect you are
User avatar
Atlanta-Ned
In-Game Game Master
Joined: Fri Apr 18, 2014 2:11 pm
Byond Username: Atlanta-ned

Re: Statbus II: Statnarok

Post by Atlanta-Ned » #446406

Parsed logs are BACK: https://atlantaned.space/statbus/round. ... &file=game
Please note that parsed logs are NOT currently qualified for use in ban appeals! As soon as MSO signs off on them though, you'll be good to go.

Please report any errors to me here, or on discord.
Statbus! | Admin Feedback
OOC: Pizzatiger: God damn Atlanta, how are you so fucking smart and charming. It fucking pisses me off how perfect you are
User avatar
Atlanta-Ned
In-Game Game Master
Joined: Fri Apr 18, 2014 2:11 pm
Byond Username: Atlanta-ned

Re: Statbus II: Statnarok

Post by Atlanta-Ned » #446943

I've got some pretty groundbreaking news to share.

As it turns out, the filter query I came up with wasn't giving the correct results. This, in spite of me going through it line-by-line, in public, after MSO went over it.

Due to some misgivings with the results, I pulled down the raw data and filtered it by hand. It didn't match up with what I was seeing on Statbus' filtered results. Or the unfiltered results. So I reached out to MSO and he sent me a query he uses for the IRV poll filtering. I adapted it to match the current cat people poll, and these results matched the manually filtered results. We're working to determine what went wrong.

The accurate poll results are now live here.

Needless to say, I am deeply sorry that my results were incorrect, and I apologize for contributing to what is already a stressful situation.

Note that this does error does NOT affect any previous polls available on Statbus.

That being said, let's review the newer, better query!

Code: Select all

SELECT
COUNT(o.id) AS votes,
o.text AS `option`
FROM ss13poll_vote AS v
LEFT JOIN ss13poll_option AS o ON (v.optionid = o.id)
LEFT JOIN ss13player AS p ON (v.ckey = p.ckey)
LEFT JOIN ss13poll_question AS q ON (v.pollid = q.id) 
WHERE v.pollid = ?
AND
  (SELECT SUM(j.delta)
  FROM ss13role_time_log AS j
  WHERE j.job IN ('Living')
  AND j.datetime BETWEEN q.starttime - INTERVAL 30 DAY AND q.starttime
  AND j.ckey = v.ckey) >= 60
  GROUP BY o.text
ORDER BY votes DESC;
I'll split this up into three distinct chunks:

Code: Select all

SELECT
  COUNT(o.id) AS votes,
  o.text AS `option`
  FROM ss13poll_vote AS v
For starters, this looks a lot different from the last query. That's because this one makes heavy uses of aliases. This is more of a personal preference in regards to readability. MSO prefers his queries this way. The `AS v` after the `FROM` simply means we can type `v.<column>` instead of `ss13poll_vote.<column`. Note that I'm also aliasing `o.text` to `option` so that I don't have to change a bunch of frontend display code on statbus.

Code: Select all

LEFT JOIN ss13poll_option AS o ON (v.optionid = o.id)
LEFT JOIN ss13player AS p ON (v.ckey = p.ckey)
LEFT JOIN ss13poll_question AS q ON (v.pollid = q.id) 
More aliases! If you'll recall from the last explainer, ids and ckeys can be used to reference columns on other tables. Here, we're grabbing the poll option column as o, the player table as p, and the poll question table as q.

Code: Select all

  WHERE v.pollid = ? 
  AND
    (SELECT SUM(j.delta)
    FROM ss13role_time_log AS j
    WHERE j.job IN ('Living')
    AND j.datetime BETWEEN q.starttime - INTERVAL 30 DAY AND q.starttime
    AND j.ckey = v.ckey) >= 60
    GROUP BY o.text
  ORDER BY votes DESC;
This is the real magic, and the reason this query works much better than the first one. As it turns out, MySQL will let you use another SELECT (a subquery) for the WHERE clause. This lets us create a complicated WHERE clause with more conditions than we'd get normally.

As from before, we're summing the ss13_role_time_log.delta column to get the number of minutes played.

If I wanted to, the IN operator would let me specify a list of jobs to look for. We're only interested in the Living role right now though.

From the previous query, I'm only looking at entries in ss13_role_time_log that were made within 30 days of the poll starting.

Finally, we're using the vote ckey to match against the role_time_log ckey.

All of this outputs the number of minutes played. If that number is greater than or equal to 60, we count the vote!

`GROUP BY o.text` tells MySQL to group everything by the value of the option text, and `ORDER BY votes DESC;` simply arranges the results by the number of votes, in descending order (so the most-voted option is first).
Statbus! | Admin Feedback
OOC: Pizzatiger: God damn Atlanta, how are you so fucking smart and charming. It fucking pisses me off how perfect you are
User avatar
MrStonedOne
Host
Joined: Mon Apr 14, 2014 10:56 pm
Byond Username: MrStonedOne
Github Username: MrStonedOne

Re: Statbus II: Statnarok

Post by MrStonedOne » #446945

I just wanted to apologize for not catching this error.

It seems (i haven't been able to confirm why the old query is producing incorrect results, just that it is, and that the irv based query is written correctly) to stem from how the tables were merged in the old query (and part of the reason I use subqueries for things like this, as its harder to mess up).

Before filtering went live on Ned's site, I was given a chance to validate the query, and I had only skimmed it, not the kind of review fitting for something as integral to how /tg/ engages with the playerbase.
Forum/Wiki Administrator, Server host, Database King, Master Coder
MrStonedOne on digg(banned), Steam, IRC, Skype Discord. (!vAKvpFcksg)
Image
User avatar
oranges
Code Maintainer
Joined: Tue Apr 15, 2014 9:16 pm
Byond Username: Optimumtact
Github Username: optimumtact
Location: #CHATSHITGETBANGED

Re: Statbus II: Statnarok

Post by oranges » #447085

Atlanta-Ned wrote:I've got some pretty groundbreaking news to share.

As it turns out, the filter query I came up with wasn't giving the correct results. This, in spite of me going through it line-by-line, in public, after MSO went over it.

Due to some misgivings with the results, I pulled down the raw data and filtered it by hand. It didn't match up with what I was seeing on Statbus' filtered results. Or the unfiltered results. So I reached out to MSO and he sent me a query he uses for the IRV poll filtering. I adapted it to match the current cat people poll, and these results matched the manually filtered results. We're working to determine what went wrong.

The accurate poll results are now live here.

Needless to say, I am deeply sorry that my results were incorrect, and I apologize for contributing to what is already a stressful situation.

Note that this does error does NOT affect any previous polls available on Statbus.
nowhere do you say why the previous filter was incorrectly returning results, why was it incorrect and what part of it was wrong?
User avatar
DemonFiren
Joined: Sat Dec 13, 2014 9:15 pm
Byond Username: DemonFiren

Re: Statbus II: Statnarok

Post by DemonFiren » #447106

so whatever the conspiracy does they'll be accused of falsifying data now

revote now or in two years?
Image
Image
Image
ImageImageImageImageImage

non-lizard things:
Spoiler:
Image
User avatar
Atlanta-Ned
In-Game Game Master
Joined: Fri Apr 18, 2014 2:11 pm
Byond Username: Atlanta-ned

Re: Statbus II: Statnarok

Post by Atlanta-Ned » #447236

oranges wrote: nowhere do you say why the previous filter was incorrectly returning results, why was it incorrect and what part of it was wrong?
I don't know enough about SQL to nail down the issue.

The previously incorrect query:

Code: Select all

SELECT
count(DISTINCT tbl_poll_vote.id) AS votes,
tbl_poll_option.text AS `option`
FROM tbl_poll_vote
LEFT JOIN tbl_poll_option ON tbl_poll_vote.optionid = tbl_poll_option.id
LEFT JOIN tbl_role_time_log ON tbl_poll_vote.ckey = tbl_role_time_log.ckey AND (tbl_role_time_log.job = 'Living')
LEFT JOIN tbl_poll_question ON tbl_poll_vote.pollid = tbl_poll_question.id
WHERE tbl_poll_vote.pollid = 200
AND tbl_role_time_log.datetime BETWEEN tbl_poll_question.starttime - INTERVAL 30 DAY AND tbl_poll_question.starttime
GROUP BY tbl_poll_vote.optionid
HAVING sum(tbl_role_time_log.delta) >= 60
ORDER BY votes DESC;
...when modified to:

Code: Select all

SELECT
sum(tbl_role_time_log.delta) AS minutes,
tbl_poll_option.text AS `option`
FROM tbl_poll_vote
LEFT JOIN tbl_poll_option ON tbl_poll_vote.optionid = tbl_poll_option.id
LEFT JOIN tbl_role_time_log ON tbl_poll_vote.ckey = tbl_role_time_log.ckey AND (tbl_role_time_log.job = 'Living')
LEFT JOIN tbl_poll_question ON tbl_poll_vote.pollid = tbl_poll_question.id
WHERE tbl_poll_vote.pollid = 200
AND tbl_role_time_log.datetime BETWEEN tbl_poll_question.starttime - INTERVAL 30 DAY AND tbl_poll_question.starttime
GROUP BY tbl_poll_vote.id
HAVING sum(tbl_role_time_log.delta) >= 60;
...returns every vote with 60 minutes or more playtime. Ordering this data by hand in a spreadsheet returns the same results currently being returned by the correct, working filter. I suspect the issue lies with this line:

Code: Select all

count(DISTINCT tbl_poll_vote.id) AS votes,
But again, I'm not sure if this is the culprit, or why it would be returning incorrect data.

In other news, the admin activity page is back! https://atlantaned.space/statbus/info.php?admins
Statbus! | Admin Feedback
OOC: Pizzatiger: God damn Atlanta, how are you so fucking smart and charming. It fucking pisses me off how perfect you are
User avatar
MrStonedOne
Host
Joined: Mon Apr 14, 2014 10:56 pm
Byond Username: MrStonedOne
Github Username: MrStonedOne

Re: Statbus II: Statnarok

Post by MrStonedOne » #447258

oranges wrote:
Atlanta-Ned wrote:I've got some pretty groundbreaking news to share.

As it turns out, the filter query I came up with wasn't giving the correct results. This, in spite of me going through it line-by-line, in public, after MSO went over it.

Due to some misgivings with the results, I pulled down the raw data and filtered it by hand. It didn't match up with what I was seeing on Statbus' filtered results. Or the unfiltered results. So I reached out to MSO and he sent me a query he uses for the IRV poll filtering. I adapted it to match the current cat people poll, and these results matched the manually filtered results. We're working to determine what went wrong.

The accurate poll results are now live here.

Needless to say, I am deeply sorry that my results were incorrect, and I apologize for contributing to what is already a stressful situation.

Note that this does error does NOT affect any previous polls available on Statbus.
nowhere do you say why the previous filter was incorrectly returning results, why was it incorrect and what part of it was wrong?
Quite simply neither of don't even know for sure.

I was just able to confirm the "having" does nothing, removing it returns the same results, and making the irv style query only filter by people with 1 minute or more of play time also returns the same results as the having query, suggesting it was just filtering for people with living play time entries in the time window.

Next step is to figure out why the having was doing nothing, and what would be needed to make it do the right things (if it can be done). Having queries are one of my few blind spots in sql, I played with them in my TSQL course in college, but never really used them after that. likely why I glossed over it during review.
Forum/Wiki Administrator, Server host, Database King, Master Coder
MrStonedOne on digg(banned), Steam, IRC, Skype Discord. (!vAKvpFcksg)
Image
User avatar
iamgoofball
Github User
Joined: Fri Apr 18, 2014 5:50 pm
Byond Username: Iamgoofball
Github Username: Iamgoofball

Re: Statbus II: Statnarok

Post by iamgoofball » #447268

So if you don't know what was wrong how do you know the filter is right this time? Or that the original was wrong?
User avatar
MrStonedOne
Host
Joined: Mon Apr 14, 2014 10:56 pm
Byond Username: MrStonedOne
Github Username: MrStonedOne

Re: Statbus II: Statnarok

Post by MrStonedOne » #447284

The new one is based off of the IRV filter. The IRV filter was tested by me using test data where I knew what the correct outcome was when I implemented it because we use it for elections and something like that better be well tested.


Also I do know why it wasn't working, at least now I do, after further research.

Code: Select all

SELECT
count(DISTINCT ss13poll_vote.id) AS votes,
ss13poll_option.text
FROM ss13poll_vote
LEFT JOIN ss13poll_option ON (ss13poll_vote.optionid = ss13poll_option.id)
**LEFT JOIN ss13role_time_log ON ss13poll_vote.ckey = ss13role_time_log.ckey AND (ss13role_time_log.job = 'Living')**
LEFT JOIN ss13poll_question ON (ss13poll_vote.pollid = ss13poll_question.id)
WHERE ss13poll_vote.pollid = 200
AND ss13role_time_log.datetime BETWEEN ss13poll_question.starttime - INTERVAL 30 DAY AND ss13poll_question.starttime
**GROUP BY ss13poll_vote.optionid**
**HAVING sum(ss13role_time_log.delta) >= 60;**
The three key lines are marked with ** around them.

Code: Select all

LEFT JOIN ss13role_time_log ON ss13poll_vote.ckey = ss13role_time_log.ckey AND (ss13role_time_log.job = 'Living')
This part joins in the play time table, linking the entries up by ckey. (it also filters out entries related to jobs other than 'Living')

Code: Select all

HAVING sum(ss13role_time_log.delta) >= 60;
Having handles filtering out groups that do not have 60 minutes of combined playtime.

So what makes a group?

Code: Select all

GROUP BY ss13poll_vote.optionid
This line groups all rows by rows with the same optionid (the optionid is the id given to each option you can pick in a given poll, so ie it groups all votes by what they voted for) This allows us to count the yes votes and no votes mysql side, rather then shit out every vote over the wire to ned's server we only send 2 rows, the yes row and the no row.

So lets keep that in mind while we return to that having line:

Code: Select all

HAVING sum(ss13role_time_log.delta) >= 60;
Remember, it's applied after the group by, so at this point there are 2 groups, the yes group, and the no group. it goes to the yes group, adds up all of their role time, and if its over 60, keeps the yes group, then it does the same to the no group.

Visualization:
Image

This is not what was intended, and why a sub-query was needed instead, so it could filter votes by the criteria, rather then filter the results by the criteria.
Forum/Wiki Administrator, Server host, Database King, Master Coder
MrStonedOne on digg(banned), Steam, IRC, Skype Discord. (!vAKvpFcksg)
Image
User avatar
GuyonBroadway
Joined: Wed Aug 30, 2017 12:38 pm
Byond Username: GuyonBroadway

Re: Statbus II: Statnarok

Post by GuyonBroadway » #447300

MrStonedOne wrote:
So lets keep that in mind while we return to that having line:

Code: Select all

HAVING sum(ss13role_time_log.delta) >= 60;
Remember, it's applied after the group by, so at this point there are 2 groups, the yes group, and the no group. it goes to the yes group, adds up all of their role time, and if its over 60, keeps the yes group, then it does the same to the no group.
So if I understand correctly, it was keeping all the votes of yes or no if the total playtime of all the yes or no votes was >= 60?
User avatar
Grazyn
Joined: Tue Nov 04, 2014 11:01 am
Byond Username: Grazyn

Re: Statbus II: Statnarok

Post by Grazyn » #447310

I have a question. The poll filtered players according to "1 hour of play time (time spent in living job role) in the 30 days leading up to the vote". I assume this was to prevent people from logging in after the vote had already started to go afk for an hour and get their vote validated.

Now oranges is going to make a new almost identical poll with a better wording. Will the filter count those people who only logged in after the first vote, but before the start of the new vote? Or will it still count time played in the month leading up to the first vote?
User avatar
MrStonedOne
Host
Joined: Mon Apr 14, 2014 10:56 pm
Byond Username: MrStonedOne
Github Username: MrStonedOne

Re: Statbus II: Statnarok

Post by MrStonedOne » #447344

The second poll will be IRV, which already has a system for custom selecting filter parameters (we base headmin election filtering on the date the headmin elections thread is posted to global announcements), so we plan to base it on the time of the first poll being started, yes.
Forum/Wiki Administrator, Server host, Database King, Master Coder
MrStonedOne on digg(banned), Steam, IRC, Skype Discord. (!vAKvpFcksg)
Image
User avatar
MrStonedOne
Host
Joined: Mon Apr 14, 2014 10:56 pm
Byond Username: MrStonedOne
Github Username: MrStonedOne

Re: Statbus II: Statnarok

Post by MrStonedOne » #447347

GuyonBroadway wrote:
MrStonedOne wrote:
So lets keep that in mind while we return to that having line:

Code: Select all

HAVING sum(ss13role_time_log.delta) >= 60;
Remember, it's applied after the group by, so at this point there are 2 groups, the yes group, and the no group. it goes to the yes group, adds up all of their role time, and if its over 60, keeps the yes group, then it does the same to the no group.
So if I understand correctly, it was keeping all the votes of yes or no if the total playtime of all the yes or no votes was >= 60?
Correct.

The reason the vote even changed at all was the WHERE clause (that happens before grouping) that filtered entries without a linked job role table entry within the 30 day time window, so it basically filtered out people without at least 1 minute of living role time.
Forum/Wiki Administrator, Server host, Database King, Master Coder
MrStonedOne on digg(banned), Steam, IRC, Skype Discord. (!vAKvpFcksg)
Image
User avatar
iamgoofball
Github User
Joined: Fri Apr 18, 2014 5:50 pm
Byond Username: Iamgoofball
Github Username: Iamgoofball

Re: Statbus II: Statnarok

Post by iamgoofball » #447387

MrStonedOne wrote:The second poll will be IRV, which already has a system for custom selecting filter parameters (we base headmin election filtering on the date the headmin elections thread is posted to global announcements), so we plan to base it on the time of the first poll being started, yes.
It's still going to be yes/no, right?
The_Dreamweaver
Joined: Sun Oct 18, 2015 3:40 am
Byond Username: The Dreamweaver
Github Username: TheDreamweaver

Re: Statbus II: Statnarok

Post by The_Dreamweaver » #447724

I think it definitely important to make the most of every experience and so at least we can say this was a major learning experience both from a technical and social standpoint. It also goes to show that there's a lot of great people willing to spend so much of their free time on a "2d atmos sim" in order to give others a posittive experience. It's things like this that make this place so amazing in the end, so thanks again for everyone who contributes in any way to this place. It does make a difference.
I've escaped this hell community.
User avatar
Oldman Robustin
Joined: Tue May 13, 2014 2:18 pm
Byond Username: ForcefulCJS

Re: Statbus II: Statnarok

Post by Oldman Robustin » #447952

Just came to say that having Win/Loss data for modes moves from monthly data to its own "All Time" page completely defeats the point of tracking win-rates.

How much Revs, Cultists, Nukeops, etc. won from 2012-2016 is largely irrelevant. I used that data to understand the state of the game and how certain changes affected win rates, now all I can do is scroll through the round list and get a very rough idea.

P.S. whenever someone gets around to posting the mode ranking poll, can it PLEASE be done using linear weighting?
Image
User avatar
Atlanta-Ned
In-Game Game Master
Joined: Fri Apr 18, 2014 2:11 pm
Byond Username: Atlanta-ned

Re: Statbus II: Statnarok

Post by Atlanta-Ned » #448270

Oldman Robustin wrote:[Long-winded feature request]
https://atlantaned.space/statbus/info.php?winloss
Go by the Current Range displayed in pink. There's an annoying timezone issue with the javascript slider.
Statbus! | Admin Feedback
OOC: Pizzatiger: God damn Atlanta, how are you so fucking smart and charming. It fucking pisses me off how perfect you are
User avatar
Oldman Robustin
Joined: Tue May 13, 2014 2:18 pm
Byond Username: ForcefulCJS

Re: Statbus II: Statnarok

Post by Oldman Robustin » #448312

That customized date range is extremely helpful, thank you.
Image
User avatar
Atlanta-Ned
In-Game Game Master
Joined: Fri Apr 18, 2014 2:11 pm
Byond Username: Atlanta-ned

Re: Statbus II: Statnarok

Post by Atlanta-Ned » #448342

I'm working on YET ANOTHER version of Statbus that I call Slimbus!
You can preview it at: https://sb.atlantaned.space

The primary features of Slimbus are:
- Really damn simple URLs. A round is at https://sb.atlantaned.space/rounds/95887 , a single stat for a round is at https://sb.atlantaned.space/rounds/95887/handcuffs and so on. I'm so excited for pretty URLs and they were SUCH A GODDAMN PAIN IN THE ASS TO SET UP HOLY SHIT YOU DON'T EVEN UNDERSTAND
- A tighter codebase, thanks to the Pretty URLs

So far only Rounds, their stats, and deaths are working, but I'll be adding more to it as I go along.
Statbus! | Admin Feedback
OOC: Pizzatiger: God damn Atlanta, how are you so fucking smart and charming. It fucking pisses me off how perfect you are
User avatar
Atlanta-Ned
In-Game Game Master
Joined: Fri Apr 18, 2014 2:11 pm
Byond Username: Atlanta-ned

Re: Statbus II: Statnarok

Post by Atlanta-Ned » #448448

God this is SO MUCH MORE ENJOYABLE TO WORK ON I AM SO EXCITED

The me page is back and now lives at https://sb.atlantaned.space/me

EDIT: Please report any errors you get with regards to using the authentication tool.
Statbus! | Admin Feedback
OOC: Pizzatiger: God damn Atlanta, how are you so fucking smart and charming. It fucking pisses me off how perfect you are
User avatar
Floiven
Joined: Fri Dec 01, 2017 11:20 pm
Byond Username: Brak7000

Re: Statbus II: Statnarok

Post by Floiven » #448451

Looks and reads great, I love well presented statistics like these. Thanks for your hard work.
User avatar
Atlanta-Ned
In-Game Game Master
Joined: Fri Apr 18, 2014 2:11 pm
Byond Username: Atlanta-ned

Re: Statbus II: Statnarok

Post by Atlanta-Ned » #448667

There's now a listing for admin rank changes (ranks being added/remove, rank permissions being changed, admins being promoted/demoted) here: https://sb.atlantaned.space/info/adminlogs

EDIT: Code is available here: https://github.com/nfreader/slimbus
I amNOT offering support to downstreams at this time. Not because I don't love you, but because I don't have enough time.
Statbus! | Admin Feedback
OOC: Pizzatiger: God damn Atlanta, how are you so fucking smart and charming. It fucking pisses me off how perfect you are
User avatar
Atlanta-Ned
In-Game Game Master
Joined: Fri Apr 18, 2014 2:11 pm
Byond Username: Atlanta-ned

Re: Statbus II: Statnarok

Post by Atlanta-Ned » #450869

I've updated the map again:
- Deaths were displaying one tile NE of where they actually took place. I corrected that. Fuckin' coordinate logic man...
- Explosions are now displaying as well. I tweaked them from the previous statbus, so that the epicenters are now correctly displayed centered on the tile where they occurred, and effect radiuses now extend to the furthest edge of the tile at the end of their range.

https://sb.atlantaned.space/rounds/96331/map

A caveat: "Kill" tracking in deaths isn't accurate. Take this death for example, wherein I shot this guy:

Code: Select all

Atlanta-ned/(Sloan Cox) has shot Timothytea/(Timothy Teakettle) with [10mm bullet] (NEWHP: -21.2) 
Atlanta-ned/(Sloan Cox) has fired at Timothytea/(Timothy Teakettle) with [10mm bullet] from Engineering Foyer (NEWHP: -22.1) 
Atlanta-ned/(Sloan Cox) has shot Timothytea/(Timothy Teakettle) with [10mm bullet] (NEWHP: -52.1) 
Atlanta-ned/(Sloan Cox) has fired at Timothytea/(Timothy Teakettle) with [10mm bullet] from Engineering Foyer (NEWHP: -52.1) 
Atlanta-ned/(Sloan Cox) has shot Timothytea/(Timothy Teakettle) with [10mm bullet] (NEWHP: -82.1) 
Atlanta-ned/(Sloan Cox) has fired at Timothytea/(Timothy Teakettle) with [10mm bullet] from Engineering Foyer (NEWHP: -82.1) 
Atlanta-ned/(Sloan Cox) has shot Timothytea/(Timothy Teakettle) with [10mm bullet] (NEWHP: -112.1) 
Atlanta-ned/(Sloan Cox) has fired at Timothytea/(Timothy Teakettle) with [10mm bullet] from Engineering Foyer (NEWHP: -112.1) 
Atlanta-ned/(Sloan Cox) has shot Timothytea/(Timothy Teakettle) with [10mm bullet] (NEWHP: -142.1) 
Atlanta-ned/(Sloan Cox) has fired at Timothytea/(Timothy Teakettle) with [10mm bullet] from Engineering Foyer (NEWHP: -142.1) 
Atlanta-ned/(Sloan Cox) has shot Timothytea/(Timothy Teakettle) with [10mm bullet] (NEWHP: -173) 
...and didn't receive credit for it: https://sb.atlantaned.space/deaths/2996177
Statbus! | Admin Feedback
OOC: Pizzatiger: God damn Atlanta, how are you so fucking smart and charming. It fucking pisses me off how perfect you are
User avatar
Atlanta-Ned
In-Game Game Master
Joined: Fri Apr 18, 2014 2:11 pm
Byond Username: Atlanta-ned

Re: Statbus II: Statnarok

Post by Atlanta-Ned » #453913

At some point I'm going to move statbus to a new server, so expect some downtime. Dunno when, dunno for how long. Don't freak out if it's suddenly down for a bit.
Statbus! | Admin Feedback
OOC: Pizzatiger: God damn Atlanta, how are you so fucking smart and charming. It fucking pisses me off how perfect you are
User avatar
Atlanta-Ned
In-Game Game Master
Joined: Fri Apr 18, 2014 2:11 pm
Byond Username: Atlanta-ned

Re: Statbus II: Statnarok

Post by Atlanta-Ned » #455036

Atlanta-Ned wrote:At some point I'm going to move statbus to a new server, so expect some downtime. Dunno when, dunno for how long. Don't freak out if it's suddenly down for a bit.
I'll probably do this this weekend, during tg's maintenance window.
In other news, I just pushed a huge update to !NEW! Statbus that brings back some log parsing. Check it out: https://sb.atlantaned.space/rounds/97170/logs
There's still a lot of work to do on parsing, but it's a step in the right direction. The code behind it is MUCH more straightforward than the previous iteration. If you like regular expressions (and see anything that can be done better), check out the code: https://github.com/nfreader/slimbus/blo ... r.php#L170
Statbus! | Admin Feedback
OOC: Pizzatiger: God damn Atlanta, how are you so fucking smart and charming. It fucking pisses me off how perfect you are
User avatar
Atlanta-Ned
In-Game Game Master
Joined: Fri Apr 18, 2014 2:11 pm
Byond Username: Atlanta-ned

Re: Statbus II: Statnarok

Post by Atlanta-Ned » #455265

Atlanta-Ned wrote:
Atlanta-Ned wrote:At some point I'm going to move statbus to a new server, so expect some downtime. Dunno when, dunno for how long. Don't freak out if it's suddenly down for a bit.
I'll probably do this this weekend, during tg's maintenance window.
I did this, everything should be working now.
Statbus! | Admin Feedback
OOC: Pizzatiger: God damn Atlanta, how are you so fucking smart and charming. It fucking pisses me off how perfect you are
User avatar
Atlanta-Ned
In-Game Game Master
Joined: Fri Apr 18, 2014 2:11 pm
Byond Username: Atlanta-ned

Re: Statbus II: Statnarok

Post by Atlanta-Ned » #455505

Even though the library is/was/will always be a mistake, I have restored it: https://sb.atlantaned.space/library
Statbus! | Admin Feedback
OOC: Pizzatiger: God damn Atlanta, how are you so fucking smart and charming. It fucking pisses me off how perfect you are
User avatar
Atlanta-Ned
In-Game Game Master
Joined: Fri Apr 18, 2014 2:11 pm
Byond Username: Atlanta-ned

Re: Statbus II: Statnarok

Post by Atlanta-Ned » #455794

Server population graph is back. https://sb.atlantaned.space/info/population
Avg # of players, and admins, per server, per hour, going back for the last two years.

Note that this data is cached locally for 24 hours to avoid hammering the database, so you won't always see the most up-to-date information.
If you hit that page and don't see a blue banner at the top, congrats, you reset the cache!
Statbus! | Admin Feedback
OOC: Pizzatiger: God damn Atlanta, how are you so fucking smart and charming. It fucking pisses me off how perfect you are
User avatar
Atlanta-Ned
In-Game Game Master
Joined: Fri Apr 18, 2014 2:11 pm
Byond Username: Atlanta-ned

Re: Statbus II: Statnarok

Post by Atlanta-Ned » #457163

I just finished and pushed an overhaul for stat parsing and display. The new system is leaner, meaner, and probably still needs a ton of work so if you find errors feel free to @ me on discord, post here or open an issue on the github project: https://github.com/nfreader/slimbus/
Statbus! | Admin Feedback
OOC: Pizzatiger: God damn Atlanta, how are you so fucking smart and charming. It fucking pisses me off how perfect you are
User avatar
Atlanta-Ned
In-Game Game Master
Joined: Fri Apr 18, 2014 2:11 pm
Byond Username: Atlanta-ned

Re: Statbus II: Statnarok

Post by Atlanta-Ned » #458345

Pushed some updates:
https://sb.atlantaned.space/me now shows your last words
https://sb.atlantaned.space/rounds/97911/testmerged_prs stat was updated to show more information, and now that data is reflected on statbus. This also comes with an overhaul to how stats with different versions are shown as well, which will give us more flexibility going forward.
Statbus! | Admin Feedback
OOC: Pizzatiger: God damn Atlanta, how are you so fucking smart and charming. It fucking pisses me off how perfect you are
User avatar
Atlanta-Ned
In-Game Game Master
Joined: Fri Apr 18, 2014 2:11 pm
Byond Username: Atlanta-ned

Re: Statbus II: Statnarok

Post by Atlanta-Ned » #461385

You can now find rounds with newscaster stories at: https://sb.atlantaned.space/stats/newsc ... ies/rounds (and other stats by changing 'newscaster_stories' to something else ^_^
Clicking on the timestamp on the far-right will give you a permalink that you can share with your friends.
Statbus! | Admin Feedback
OOC: Pizzatiger: God damn Atlanta, how are you so fucking smart and charming. It fucking pisses me off how perfect you are
User avatar
Ayy Lemoh
Joined: Mon Jun 05, 2017 5:58 pm
Byond Username: Jerry Derpington

Re: Statbus II: Statnarok

Post by Ayy Lemoh » #461986

Atlanta-Ned wrote:Even though the library is/was/will always be a mistake, I have restored it: https://sb.atlantaned.space/library
It's missing the search bar now, it seems.
User avatar
Atlanta-Ned
In-Game Game Master
Joined: Fri Apr 18, 2014 2:11 pm
Byond Username: Atlanta-ned

Re: Statbus II: Statnarok

Post by Atlanta-Ned » #462067

Ayy Lemoh wrote:
Atlanta-Ned wrote:Even though the library is/was/will always be a mistake, I have restored it: https://sb.atlantaned.space/library
It's missing the search bar now, it seems.
I'll see about restoring that. I've never been satisfied with how search was implemented.
Statbus! | Admin Feedback
OOC: Pizzatiger: God damn Atlanta, how are you so fucking smart and charming. It fucking pisses me off how perfect you are
User avatar
Atlanta-Ned
In-Game Game Master
Joined: Fri Apr 18, 2014 2:11 pm
Byond Username: Atlanta-ned

Re: Statbus II: Statnarok

Post by Atlanta-Ned » #462829

I made a goofy toy: https://sb.atlantaned.space/names
Please read and understand the warnings/disclaimers
Statbus! | Admin Feedback
OOC: Pizzatiger: God damn Atlanta, how are you so fucking smart and charming. It fucking pisses me off how perfect you are
somerandomguy
Joined: Sun Nov 05, 2017 7:41 pm
Byond Username: Astatineguy12

Re: Statbus II: Statnarok

Post by somerandomguy » #462857

Atlanta-Ned wrote:I made a goofy toy: https://sb.atlantaned.space/names
Please read and understand the warnings/disclaimers
calling it now: someone wont read the warnings/disclaimers and they'll post a salt thread on the forums
Post Reply

Who is online

Users browsing this forum: No registered users