User talk:Kymara: Difference between revisions

-- 0.94

insert into pending_achievement(charname, achievement_id, param, cnt)

select name, 95, param1, 1 from looted_red_items where timedate<'2010-11-24' and name <>'';

== Maze Challenge ==

<source lang = "sql">

-- score board for Maze Challenge, to run on 15th October

select

charname,

points,

@old := (select points from halloffame_archive_recent r1 where r1.charname = r2.charname and fametype = 'M' and day < '2011-10-01' order by day desc limit 1) old,

points - @old as difference

from halloffame_archive_recent r2

where fametype = 'M' and day = date(now())

having difference >0

order by difference desc;

-- experimental procedure to get max maze score for a given player (i.e. the ones in that list) in a certain time period

DROP FUNCTION IF EXISTS getMaxMazeScore //

CREATE FUNCTION getMaxMazeScore(

name VARCHAR(32),

enddate DATE,

ndays SMALLINT

) RETURNS INTEGER

COMMENT 'Get max maze score for player in certain time period'

BEGIN

DECLARE maxscore INT DEFAULT 0;

SET maxscore = 0;

WHILE ndays >= 0 DO

select points,

@old := (select points from halloffame_archive_recent r1 where r1.charname = name and fametype = 'M' and day < date_sub(enddate, interval ndays day) order by day desc limit 1) old,

points - @old as difference

from halloffame_archive_recent r2

where fametype = 'M'

and day = date_sub(enddate, interval ndays day)

and r2.charname = name INTO @p, @o, @d;

IF @d > maxscore THEN

SET maxscore = @d;

END IF;

SET ndays = ndays-1;

END WHILE;

RETURN maxscore;

END //

-- now put it all together in one super amazing query that runs in about 35 seconds!! wow super amazing hey!!

-- (it would be way slower if the function call was not done 'outside' the scores table as the scores table uses a 'having' so is only small after it's completed. incase you wondered why I did that)

select charname, difference as totalscore, getMaxMazeScore(charname, date(now()), 14) as maxscore

from

(select

charname,

points,

@old := (select points from halloffame_archive_recent r1 where r1.charname = r2.charname and fametype = 'M' and day < '2011-10-01' order by day desc limit 1) old,

points - @old as difference

from halloffame_archive_recent r2 where fametype = 'M' and day = date(now())

having difference >0

order by difference desc)

scores;

-- output is name, total score, max score, for all participants

</source>

== making a video ==

<pre>

sudo apt-get install gtk-recordmydesktop

</pre>

Run it. Select area to record, press Record.

Stop recording by pressing the square on the top right of your menu bar where the clock is normally.

Encoding takes a while.

Saves as out.ogv in your home folder by default.

Sound etc was okay on my netbook though it seemed to pick up the mic and you can hear typing.

Couldn't get sound to capture at all on my desktop.

Youtube didn't like the .ogv file. Said it was too long, it was 12 mins, 15 is max? maybe the problem is the format.

Convert to AVI:

<pre>

sudo apt-get install mencoder

</pre>

Use command as per [http://info123.org/tutorials/54-how-to-convert-ogv-file-format-to-avi-or-any-other-format]

<pre>

mencoder -idx out.ogv -ovc lavc -oac mp3lame -o intro_stendhal_201201.avi

</pre>

Weirdly the avi was 36 minutes but the original was 12, nothing happens after 12 minutes, weird? Trim to only keep the first 00:12:12

<pre>

mencoder -endpos 00:12:12 -ovc copy -oac copy intro_stendhal_201201.avi -o intro_stendhal_201201_1.avi

</pre>

following advice at [http://ubuntuliving.blogspot.com/2008/03/splitting-avi-file-into-smaller-parts.html]

Then google accepted it

== Benfords Law fun ==

For a blog post or something, is interesting stats :)

<pre>

-- xp of characters

mysql> select left(xp,1), count(*) from character_stats where xp > 0 group by left(xp,1);

+------------+----------+

| left(xp,1) | count(*) |

+------------+----------+

| 1 | 5555 |

| 2 | 3122 |

| 3 | 2380 |

| 4 | 2344 |

| 5 | 2854 |

| 6 | 996 |

| 7 | 788 |

| 8 | 667 |

| 9 | 633 |

+------------+----------+

-- age of characters

-- max is 2940058

mysql> select left(age,1), count(*) from character_stats where age > 0 group by left(age,1);

+-------------+----------+

| left(age,1) | count(*) |

+-------------+----------+

| 1 | 8810 |

| 2 | 4962 |

| 3 | 3456 |

| 4 | 2417 |

| 5 | 2854 |

| 6 | 1545 |

| 7 | 1402 |

| 8 | 1250 |

| 9 | 971 |

+-------------+----------+

9 rows in set (0.02 sec)

-- number of kills per day

-- 2160 is max

mysql> select left(cnt,1), count(*) from kills group by left(cnt,1);

+-------------+----------+

| left(cnt,1) | count(*) |

+-------------+----------+

| 1 | 527945 |

| 2 | 272803 |

| 3 | 164413 |

| 4 | 116935 |

| 5 | 81453 |

| 6 | 64920 |

| 7 | 49086 |

| 8 | 40612 |

| 9 | 32843 |

+-------------+----------+

9 rows in set (0.61 sec)

-- size of stacks of items added to other items

mysql> select left(param2,1), count(*) from itemlog where event = 'merge in' group by left(param2,1);

+----------------+----------+

| left(param2,1) | count(*) |

+----------------+----------+

| 1 | 10636326 |

| 2 | 3470611 |

| 3 | 2667505 |

| 4 | 1920918 |

| 5 | 1397535 |

| 6 | 972027 |

| 7 | 757029 |

| 8 | 683354 |

| 9 | 667485 |

+----------------+----------+

9 rows in set (5 min 38.02 sec)

</pre>

== paperchase check ==

<source lang="sql">

select source, param2 from gameEvents

where event = 'quest'

and param1 = 'paper_chase_2013'

and param2 like 'done%'

order by round(substring_index(substring_index(param2,';',2),';',-1));

</source>

== sokoban check ==

<source lang = "sql">

select `char`, `round`, score as timetaken, (1000000*round - score) as points from

(SELECT

source AS `char`,

least(max(round(substring_index(substring_index(param2,';',2),';',-1))),59) AS `round`,

max(round(substring_index(substring_index(param2,';',3),';',-1))) AS `score`

FROM gameEvents

WHERE event = 'quest'

AND param1 = 'sokoban'

AND param2 LIKE 'done%'

GROUP BY source ) t

order by points desc

limit 10;

</source>