===========================================================
===========================================================
用oracle分析itpub徽章(2) -- 分析数据
作者: oldwain(http://oldwain.itpub.net)发表于: 2006.09.12 21:44
分类: Oracle
出处: http://oldwain.itpub.net/post/6/207555
---------------------------------------------------------------
有了数据，进行分析就比较容易了。我们可以分析每个品种的最大持有人、前n大持有人等信息。
我们可以先得到一个每个品种每个人当前持有的数目:
select item, username, count(*) cnt, count(*)/(select count(*) from medals where item = a.item)*100 ratio from medals a where snaptime = (select max(snaptime) from medals where item = a.item) group by a.item, username order by item, count(*) desc / 1742 505 ITPUB拍卖行 17 4.7887323943662 1743 505 多少年来 15 4.22535211267606 1744 505 水鬼 14 3.94366197183099 1745 505 Great.Z 12 3.38028169014085 1746 505 月明 11 3.09859154929577 .......
然后，以上面的结果为基础，可以进行进一步的查询, 比如可以查询每个品种的最大持有人, 以及他持有的数量和占总数的比例:
column ratio format 99.99 column username format a20 column medalname format a20 with v as (select item, username, count(*) cnt, count(*)/(select count(*) from medals where item = a.item)*100 ratio from medals a where snaptime = (select max(snaptime) from medals where item = a.item) group by a.item, username order by item, count(*) desc ) select n.medalname, username, cnt, ratio from v vo, medalname n where vo.item = n.medalid and n.medalclass = 'animals' and cnt = (select max(cnt) from v where item=vo.item ) MEDALNAME USERNAME CNT RATIO -------------------- -------------------- ---------- ------ 鼠 ITPUB拍卖行 17 4.79 牛 binss 66 18.59 虎 多少年来 30 8.52 兔 ITPUB拍卖行 21 5.93 龙 flashcopy 14 3.95 蛇 ITPUB拍卖行 21 5.93 马 ITPUB拍卖行 21 5.93 羊 ITPUB拍卖行 22 6.21 猴 oldwain 35 9.92 鸡 多少年来 13 3.67 狗 多少年来 14 3.93 MEDALNAME USERNAME CNT RATIO -------------------- -------------------- ---------- ------ 猪 ITPUB拍卖行 20 5.70 已选择12行。
我们还可以选择查询每个品种的前n位持有人的持有情况:
break on medalname
compute sum of cnt on medalname
compute sum of ratio on medalname
column ratio format 99.99
column username format a20
column medalname format a20
with v as (select item, username, count(*) cnt, count(*)/(select count(*) from medals where item = a.item)*100 ratio
from medals a
where snaptime = (select max(snaptime) from medals where item = a.item)
group by a.item, username
order by item, count(*) desc
)
select n.medalname, username, cnt, ratio
from v vo, medalname n
where vo.item = n.medalid
and n.medalclass = 'animals'
and (select count(*) from v where item=vo.item and cnt>vo.cnt ) < 3
MEDALNAME USERNAME CNT RATIO
-------------------- -------------------- ---------- ------
鼠 ITPUB拍卖行 17 4.79
多少年来 15 4.23
水鬼 14 3.94
******************** ---------- ------
sum 46 12.96
牛 binss 66 18.59
Great.Z 12 3.38
tang2049 12 3.38
******************** ---------- ------
sum 90 25.35
虎 多少年来 30 8.52
ITPUB拍卖行 15 4.26
Great.Z 12 3.41
******************** ---------- ------
sum 57 16.19
兔 ITPUB拍卖行 21 5.93
多少年来 14 3.95
Great.Z 12 3.39
******************** ---------- ------
sum 47 13.28
龙 flashcopy 14 3.95
月明 12 3.39
lodge 12 3.39
Great.Z 12 3.39
******************** ---------- ------
sum 50 14.12
蛇 ITPUB拍卖行 21 5.93
Great.Z 12 3.39
月明 10 2.82
bankit 10 2.82
******************** ---------- ------
sum 53 14.97
马 ITPUB拍卖行 21 5.93
Great.Z 12 3.39
lodge 11 3.11
******************** ---------- ------
sum 44 12.43
羊 ITPUB拍卖行 22 6.21
lodge 18 5.08
月明 13 3.67
******************** ---------- ------
sum 53 14.97
猴 oldwain 35 9.92
Great.Z 12 3.40
醉眼看世界 12 3.40
******************** ---------- ------
sum 59 16.71
鸡 多少年来 13 3.67
Great.Z 12 3.39
vecentli 12 3.39
******************** ---------- ------
sum 37 10.45
狗 多少年来 14 3.93
wanggi 12 3.37
Great.Z 12 3.37
******************** ---------- ------
sum 38 10.67
猪 ITPUB拍卖行 20 5.70
Great.Z 12 3.42
月明 10 2.85
******************** ---------- ------
sum 42 11.97
已选择38行。
另外，当我们积攒了一定时期的数据后，还可以按照时间对数据的趋势进行进一步的分析，同时可以配合GUI程序进行图表分析。
(需要引用, 请注明出处: http://oldwain.itpub.net)
re: 用oracle分析itpub徽章(2) -- 分析数据 
继续继续……
