Scripting Games 2007 Advanced PowerShell Event 5
As the Answers to the events 5 and 6 in the Scripting Games 2007, Are Posted,
*edit * It's nice to see that more people show there answers, it is nice to compare them also see for example the answers here :
winter scripting games : events 5 and 6 in this excelent blog series about the scripting games by LonerVamp on Terminal23 :
(The whole series you can find at http://www.terminal23.net/ and he also covers the Beginners division !)
I will supply my answer to event 5 here :
PS Answer: When I Say Mean I Mean Average
For my solution, I started here Winter Scripting games Part 8 (Last Day) , for checking my answer to Event 8: Database Derby (for the connection to Access )
If you did follow my series about the Scripting games of last year ( Scripting Games MMVII PowerShell Competition ) to prepare for the Games , this part would be not so difficult.
PoSH> $mdb = "$(pwd)\Scores.mdb"
PoSH> $ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=$mdb"
PoSH> $Conn = new-object System.Data.OleDb.OleDbConnection($connString)
PoSH> $conn.open()
PoSH>
PoSH> $cmd = new-object System.Data.OleDb.OleDbCommand("select * from Results",$Conn)
PoSH> $da = new-object System.Data.OleDb.OleDbDataAdapter($cmd)
PoSH> $dt = new-object System.Data.dataTable
PoSH> [void]$da.fill($dt)
PoSH> $dt | select -First 5
ID Group Score
-- ----- -----
1 Blue 83
2 Blue 74
3 Blue 52
4 Blue 92
5 Blue 73
PoSH>
Note here that in theQuestion was asked for the current path, so I changed it from last version, only in the answer of the Scripting Guys a fixed Path was Used
So here was my data to work with.
Next I started with getting the statistical data measure-Command could provide me :
PoSH> $measure = $dt | measure-object -Property score -Average -min -max
PoSH> $measure
Count : 1115
Average : 48.6251121076233
Sum :
Maximum : 100
Minimum : 1
Property : Score
PoSH> $Mean = [math]::Truncate($measure.Average)
PoSH> $Mean
48
PoSH>
Next step was getting the Mode, the Score that scored most often I started out with using the Group command to group the list by score, to get the counts for each score and worked from there :
PoSH> $dt | group score | select -First 5
Count Name Group
----- ---- -----
11 83 {1, 26, 172, 257...}
11 74 {2, 73, 143, 258...}
11 52 {3, 108, 153, 259...}
10 92 {4, 50, 260, 306...}
7 73 {5, 54, 232, 261...}
PoSH> $dt | group score | sort count -desc | select -First 1
Count Name Group
----- ---- -----
20 94 {48, 82, 304, 338...}
PoSH> ($dt | group score | sort count -desc | select -First 1 ).name
94
PoSH>
Next came the Median (Middle value) this I solved like this :
PoSH> $l = ($dt | sort score)
PoSH> $m = [math]::truncate($l.count / 2)
PoSH> if ($l.count % 2 -ne 0 ) {
>> $median = $l[$m].Score
>> }Else {
>> $median = [math]::truncate(($l[$m -1].Score + $l[$m].score) / 2)
>> }
>>
PoSH> $median
47
PoSH> . {
>> "Mean: $Mean"
>> "Mode: $mode"
>> "Median: $median"
>> "Highest score: $Maximum"
>> "Lowest score: $minimum"
>> }
>>
Mean: 48
Mode: 94
Median: 47
Highest score: 100
Lowest score: 1
PoSH>
Note here that as I Like to this code by pasting it in the the Console session and play with it, to test the output I used the following trick:
I start with typing a dot '.' a Space ' ' and Opening Curly Braces '{' and hit Enter,
PoSH> . {
>>
Then I get a Nested Prompt, as PowerShell sees the Opening Curly Braces { and no ending Curly Braces } it will ask for more input
I then paste in a couple lines of code, and end with the Closing Curly Braces '}' to execute that code as a Block.
I actualy create a lambada here ( Nameless Function ) by creating a scriptblock that I execute in the current scope by Dotsourcing it, this is very handy to test as otherwise each line is executed one be one and the results are mixed with the lines of code I paste in.
this is only needed for testing like this so you will not find this in the final script :
$mdb = "$(pwd)\Scores.mdb"
$ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=$mdb"
$Conn = new-object System.Data.OleDb.OleDbConnection($connString)
$conn.open()
$cmd = new-object System.Data.OleDb.OleDbCommand("select * from Results",$Conn)
$da = new-object System.Data.OleDb.OleDbDataAdapter($cmd)
$dt = new-object System.Data.dataTable
[void]$da.fill($dt)
$measure = $dt | measure-object -Property score -Average -min -max
$Mean = [math]::Truncate($measure.Average)
$Maximum = $measure.Maximum
$minimum = $measure.Minimum
$mode = ($dt | group score | sort count -desc | select -First 1 ).name
$l = ($dt | sort score)
$m = [math]::truncate($l.count / 2)
if ($l.count % 2 -ne 0 ) {
$median = $l[$m].Score
}Else {
$median = [math]::truncate(($l[$m -1].Score + $l[$m].score) / 2)
}
"Mean: $Mean"
"Mode: $mode"
"Median: $median"
"Highest score: $Maximum"
"Lowest score: $minimum"
Enjoy,
Greetings /\/\o\/\/