Some Songfight statistics

[thehipcola]

These stats are sweet. Great job!

Here's an idea that may or may not be simple to implement: Create a webform (forgive me now for trying to parlez about stuff I know little about..) that allows a SFighter to enter their username, and check off boxes (modifiers?) to create a statistic the form would return to the user.

Like,

fighter: redcar [x] # wins [x] when [x] fighting with [x] fighter: Frontalot

= 0 obviously. But you get what I mean....select the bits that make up the stat you want.

Or maybe that's really stupid....dunno... anyways..great thread!

[fluffy]

It'd be easier to just provide an SQL command line which is only allowed to SELECT * FROM songfight_table and link to an SQL tutorial. :D

[thehipcola]

uhh..yah... so I don't know anything about programming at all, but I'd have to guess you are saying the idea is either a) dumb or b) difficult to do. Or both, I admit it may be both. Though if you are half serious at all, what I understand of your idea is cool..if it's that easy. Tutorials are good.

[Southwest_Statistic]

uhh..yah... so I don't know anything about programming at all, but I'd have to guess you are saying the idea is either a) dumb or b) difficult to do. Or both, I admit it may be both. Though if you are half serious at all, what I understand of your idea is cool..if it's that easy. Tutorials are good.

He's serious enough. It would be cool to have a public SQL command line.

[fluffy]

I'm just saying it'd be difficult to do in any way which would give any reasonable amount of flexibility.

[deshead]

All you need is one Zombie Son (with 5 votes) or one Romantic Cheapskate (with 700+) to throw a whole bunch of stats out of whack. Can you do a median instead of a mean? And I guess eliminate anyone with fewer than 3 fights...

I can: here's the complete list of Songfighters with their median '+/-'. It looks more balanced, though you and I both fared worse.

It'd be easier to just provide an SQL command line which is only allowed to SELECT * FROM songfight_table and link to an SQL tutorial.

I like THC's idea too, but with some obvious caveats, fluffy's idea actually did seem easier.

So here's a (hastily thrown together) public SQL Interface.

There's a single table, SongfightData, described on the schema page. And there are a few sample queries linked, too. (I probably overlooked some security issue or other. So .. uh .. be gentle.)

[fluffy]

That table schema seems a bit sub-optimal. It'd have been better to have two separate tables, Fights and Entries, just to make things more efficient and so that each entry wouldn't have to have the complete data for the fight it was in.

Oh well. Still a useful tool for people who know the sorts of things they want to search for.

[deshead]

That table schema seems a bit sub-optimal.

It's a view ... Trust me, the underlying tables are in 3NF.

Oh well. Still a useful tool for people who know the sorts of things they want to search for.

Ya, I think any tool of this nature is going to be a compromise between flexibility and give-me-enough-rope-to-hang-myself-ibility

[fluffy]

That table schema seems a bit sub-optimal.

It's a view ... Trust me, the underlying tables are in 3NF. :wink:

Ah, okay.

Personally I'd have found the normal form to be easier to work with but I'm also kinda weird that way.

By the way, what the hell are first and second normal form? I've never understood why the (to me) dead-obvious concept of normalized indices is called third normal form. :)

[fluffy]

Personally I'd have found the normal form to be easier to work with but I'm also kinda weird that way.

You could have left off everything before "I'm" and after "weird."

[thehipcola]

So here's a (hastily thrown together) public SQL Interface.

That's awesome Des! Very very cool. With the samples, even I can look up stuff....

Nice job. (from someone who knows less than nothing about how this is accomplished, so tifwiw)

[Southwest_Statistic]

Personally I'd have found the normal form to be easier to work with but I'm also kinda weird that way.

You could have left off everything before "I'm" and after "weird."

Look, he's talking to himself again.

[fluffy]

Personally I'd have found the normal form to be easier to work with but I'm also kinda weird that way.

You could have left off everything before "I'm" and after "weird."

Look, he's talking to himself again.

Who is?

[LMNOP]

It looks more balanced, though you and I both fared worse.

That depends on whether you interpret the bottom of the list as "repulsive" or "unquestionably honest".

And hey, thanks for all the work. This is big fun.

[deshead]

By the way, what the hell are first and second normal form? I've never understood why the (to me) dead-obvious concept of normalized indices is called third normal form.

Generally, they're compromises for the sake of performance. Every 'real world' database I've ever worked with required some kind of denormalization to keep the users happy. (And ya, I cringe every time I have to do it.)

That depends on whether you interpret the bottom of the list as "repulsive" or "unquestionably honest".

Heh, for some reason that reminded me of the FM's comment in this fight. "No, mom, you can't vote for me. It wouldn't be honest."

With the samples, even I can look up stuff....

I added another sample that'll help you get at the type of data you referenced in a previous post, specifically: how one fighter fared against another.

Code: Select all

SELECT
    sf1.fightTitle,
    sf1.artistName as Fighter1,
    sf1.VoteCount as Fighter1Votes,
    sf1.VoteRank as Fighter1Rank,
    sf2.artistName as Fighter2,
    sf2.VoteCount as Fighter2Votes,
    sf2.VoteRank as Fighter2Rank
FROM
    songFightData sf1
    join songFightData sf2 on sf1.fightid = sf2.fightid
WHERE
    sf1.artistName = 'deshead'
    and sf2.artistName LIKE %hostess%'

That's some advanced SQL right there. Add some grouping and a HAVING clause, and you can put it on your resume.

[HeuristicsInc]

I can: here's the complete list of Songfighters with their median '+/-'. It looks more balanced, though you and I both fared worse.

I'm not sure if this means anything, really, because I'm WAY near the top but I've never gotten more than 4 votes. Don't think this means I'm friend-flooding

This SQL thing is great. I was able to see all the bands I'm in:

Code: Select all

SELECT 
    *
FROM
    SongFightData
WHERE
    artistName = 'Heuristics Inc.'
    OR artistName = 'Brainpipe'
    OR artistName = 'Dollar Bill and the Inkpoints'
    OR artistName = 'Mechanical Mayhem'
ORDER BY 
    fightStartDate

Cool!
-bill

[fluffy]

I added another sample that'll help you get at the type of data you referenced in a previous post, specifically: how one fighter fared against another.

Self-joins are ever so much fun! I particularly like this little gem from Project 2:

Code: Select all

SELECT a.* FROM project2_clip AS a
    LEFT JOIN project2_clip AS b
      ON (b.accompany = a.clip_id and b.user_id = $uid)
WHERE
    a.user_id != $uid AND b.clip_id IS NULL
ORDER BY num_accompany ASC

which is just to find all clips in the database where the user didn't submit it and hasn't submitted an accompaniment for it.

[deshead]

I'm not sure if this means anything, really, because I'm WAY near the top but I've never gotten more than 4 votes. Don't think this means I'm friend-flooding

If could also mean that when people see your name in a fight, they're more inclined to listen and vote!!

I was able to see all the bands I'm in:

OK, based on your list, I added the artistName "Heuristics Inc. - COMBINED", so you can also see all your results grouped together.

[Southwest_Statistic]

Is there a thread somewhere with the MySQL access info? This is the second time I've seen a cool little SF-DB-Accessing web app.

[fluffy]

SongFight itself doesn't run on an SQL database of any kind. These little dbs are all built by random people by scraping the HTML and similar.

[Southwest_Statistic]

SongFight itself doesn't run on an SQL database of any kind. These little dbs are all built by random people by scraping the HTML and similar.

ouch...

[fluffy]

Eh, Songfight doesn't really need any sort of SQL anyway. The understanding I get from Spud is that it's got a very simple file-based database which works well enough, and is a lot more robust in terms of reliability and backups and so on. It's also inherently more secure since there's no need to worry about SQL injection holes or whatever.

Personally I only use SQL when there's a lot of data which needs to have interesting relations described in a runtime manner. Flat files are a lot simpler for a lot of things, and simplicity is a virtue.