sql - Oracle Database Query Design -


i working on program supposed predict outcomes of 1v1 contest. have given each player own elo score , collecting sorts of data in order predict winner be.

for each fighter, want collect current average elo of people defeating current average elo of people defeating them. below sample data , explanations in order better understand data structure.

basic stats snapshot

the picture above shows basic stats view, v_fight_review simplifies fights table stats collection. fid unique fight id , identifies fight. pid player id , identifies each unique player. winner column represents winner of fight. if pid not equal winner, player did not win fight.

player table snapshot

this picture represents players table. left recognize pid each player. right see column named elo.

to rephrase question, having trouble figuring out how can produce current average elo of each player have defeated , current average elo of each player has defeated them. these average elos should change opponents win/lose fights. output should similar below:

pid | avg_elo_def | avg_elo_def_by

i 99% sure there better way here answer came with.

i created new view query:

select  w.fid, w.pid winner, l.pid loser, w.elo winner_elo, l.elo loser_elo    (select * v_fight_review pid = winner , fid = fid) w,     (select * v_fight_review pid <> winner , fid = fid) l w.fid = l.fid;

this query gets elos of every winner , loser each fight.

i created 2 other views. 1 view average elos each opponent has defeated. other view average elos of opponents have defeated them. code below.

--gets average elo of oppenents can beat create view v_avg_elo_winning select p.pid, round(avg(vae.loser_elo), 0) elo players p, v_avg_elo vae p.pid = vae.winner group pid order pid;  --gets average of of people can beat them create view v_avg_elo_losing select p.pid, round(avg(vae.winner_elo), 0) elo players p, v_avg_elo vae p.pid = vae.loser group pid order pid;

suggestions welcome.


-

Comments

Post a Comment

Popular posts from this blog

php - regexp cyrillic filename not matches -

i take list of files php glob(."*.pdf") . files have cyrillic names. example: ООО «Рога и копыта» then load company names database , try find them in file names: preg_match("/$firm_name/ui", $file_name, $match); i have problem: firms if $firm_name visually similar $file_name , preg_match says there no match. mb_detect_encoding both variables return utf-8. there problem in file name, or maybe there encoding or something, can not understand, ask help. upd: $file_name='ООО «Рога и копыта»'; preg_match("/... .Рога и копыта./ui", $file_name, $match); as result, ОО «Рога и копыта» , 1 О missing
Read more

c# - OpenXML hanging while writing elements -

Image
i have program pulls data database, caches file , exports data multiple formats (excel, excel 2003, csv). i'm using openxml sdk 2.0 excel work. these export processes run in parallel (using parallel.foreach ), , amount of data can pretty large - e.g. csvs 800mb. during these larger exports, i've noticed writing of xml documents hang. instance, if have 8 exporting in parallel, @ point "pause". hang around same point: //this.writer openxmlwriter created worksheetpart. this.writer.writeelement(new cell() { cellvalue = new cellvalue(value), datatype = cellvalues.string }); when happens, pause debugger (vs2013 in case) , notice threads blocking around same portion of code - bit deeper in openxml sdk - stem call openxmlwriter.writeelement . i dug through source using justdecompile didn't find answers. appears there intermediary stream in use writing isolated storage , is, reason, blocking. underlying stream each of these filestream . here screenshot ...
Read more

sql - Select Query has unexpected multiple records (MS Access) -

i may tired, can't seem figure out happening. have 2 queries. query 1 has 10 fields. query 2 has 5 fields. fields 1 through 3 same in both queries. trying write select query , simple add field 5 query2 query1 result should query1 additional field (query2.field5). i join first 3 fields both queries , choose select records in query1 , in query2 match. don't know sql, use query designer. expect same number of records have query 1 instead have 3 times or more. can tell me doing wrong. tried reversing joins still seem same records. thanks in advance. here query: the fields in common divisionname , supplierid = vendorid , commodityname . select [part revenue exposed query p1].divisionname, [part revenue exposed query p1].commodityname, [part revenue exposed query p1].suppliername, [part revenue exposed query p1].partnumber, [part revenue exposed query p1].partdescription, [part revenue exposed query p1].bucode, ...
Read more