備忘録:演習履歴を集計する

以下は、個人的な備忘録です。無視してください。

# performed="2011/02/02 00:00:00"

# 履歴からゲストの平均回答率などを求める

select avg(qnum), avg(anum),avg(cnum) 

from history 

where cid='03' and performed > '2011/02/02' 

and uid like 'G%';

# 履歴から演習回数を求める

select count(*) as cnt 

from history 

where cid='03' and performed > '2011/02/02' 

and (uid like 'G%' or uid like 'B%');

# 履歴から年月日ごとの演習回数を求める

select substring(performed,1,10) as date, count(*) as cnt 

from history 

where cid='03' and performed > '2011/02/02' 

and (uid like 'G%' or uid like 'B%') 

group by substring(performed,1,10) 

order by date;

# 履歴から時間帯ごとの演習回数を求める

select substring(performed,12,2) as hour, count(*) as cnt 

from history 

where cid='03' and performed > '2011/02/02' 

and (uid like 'G%' or uid like 'B%') 

group by substring(performed,12,2) 

order by hour;
# メンバに出題された延べ設問数

select count(*) as issued

from score_item

where cid='03' and performed > '2011/02/02' 

and uid like 'B%' and kind=0 and state > 0;

# メンバが誤答した延べ設問数

select count(*) as mistaken

from score_item

where cid='03' and performed > '2011/02/02' 

and uid like 'B%' and kind=0 and state > 0 

and times != corrects;

# メンバが誤答した設問のリスト

select lid, count(lid) as cnt, sum(times) as issued, 

sum(times-corrects) as mistaken

from score_item

where cid='03' and performed > '2011/02/02' 

and uid like 'B%' and kind=0 and state > 0 

and times != corrects

group by lid

order by mistaken desc;

# メンバが誤答した設問のリスト（誤答率つき・誤答した人数ソート）

select lid, count(lid) as cnt, sum(times) as issued, 

sum(times-corrects) as mistaken, 

sum(times-corrects)/sum(times)*100.0 as percentage

from score_item

where cid='03' and performed > '2011/02/02' 

and uid like 'B%' and kind=0 and state > 0 

and times != corrects

group by lid

order by cnt desc;
# 難しすぎる設問のリスト (正答率70％以下)

select lid, count(lid) as cnt, sum(times) as issued, 

sum(times-corrects) as mistaken, 

sum(corrects)/sum(times)*100.0 as corrected

from score_item

where cid='03' and performed > '2011/02/02' 

and uid like 'B%' and kind=0 and state > 0 

and times > 0

group by lid having corrected <= 70.0

order by lid;

# 簡単すぎる設問のリスト (正答率90％以上)

select lid, count(lid) as cnt, sum(times) as issued, 

sum(times-corrects) as mistaken, 

sum(corrects)/sum(times)*100.0 as corrected

from score_item

where cid='03' and performed > '2011/02/02' 

and uid like 'B%' and kind=0 and state > 0 

and times > 0

group by lid having corrected >= 90.0

order by lid;