如表A
姓名 竞猜类型 比赛时间 结果(输0/赢1)
a1 NBA 2008-12-15 12:00:00 1
a2 NBA 2008-12-15 12:00:00 0
a3 NBA 2008-12-15 12:00:00 0
a4 NBA 2008-12-15 12:00:00 1
a5 英超 2008-12-15 12:00:00 0
a6 NBA 2008-12-15 12:00:00 1
a1 NBA 2008-12-16 12:00:00 0
a2 NBA 2008-12-16 12:00:00 1
a3 NBA 2008-12-16 12:00:00 0
a6 NBA 2008-12-16 12:00:00 1
a1 NBA 2008-12-16 13:00:00 1
a2 NBA 2008-12-16 13:00:00 0
a6 NBA 2008-12-16 13:00:00 1根据比赛时间和类型可以计算某用户的参加某类比赛的场次,而我要达到70%的人进行胜率统计,结果如下:
姓名 竞猜类型 参加次数 胜率
a1 NBA 3 0.6666
a2 NBA 3 0.3333
a5 英超 1 0
a6 NBA 3 1
姓名 竞猜类型 比赛时间 结果(输0/赢1)
a1 NBA 2008-12-15 12:00:00 1
a2 NBA 2008-12-15 12:00:00 0
a3 NBA 2008-12-15 12:00:00 0
a4 NBA 2008-12-15 12:00:00 1
a5 英超 2008-12-15 12:00:00 0
a6 NBA 2008-12-15 12:00:00 1
a1 NBA 2008-12-16 12:00:00 0
a2 NBA 2008-12-16 12:00:00 1
a3 NBA 2008-12-16 12:00:00 0
a6 NBA 2008-12-16 12:00:00 1
a1 NBA 2008-12-16 13:00:00 1
a2 NBA 2008-12-16 13:00:00 0
a6 NBA 2008-12-16 13:00:00 1根据比赛时间和类型可以计算某用户的参加某类比赛的场次,而我要达到70%的人进行胜率统计,结果如下:
姓名 竞猜类型 参加次数 胜率
a1 NBA 3 0.6666
a2 NBA 3 0.3333
a5 英超 1 0
a6 NBA 3 1
-------------------- -------------------- ------------------- ----------
a1 NBA 2008-12-15 12:00:00 1
a2 NBA 2008-12-15 12:00:00 0
a3 NBA 2008-12-15 12:00:00 0
a4 NBA 2008-12-15 12:00:00 1
a5 英超 2008-12-15 12:00:00 0
a6 NBA 2008-12-15 12:00:00 1
a1 NBA 2008-12-16 12:00:00 0
a2 NBA 2008-12-16 12:00:00 1
a3 NBA 2008-12-16 12:00:00 0
a6 NBA 2008-12-16 12:00:00 1
a1 NBA 2008-12-16 13:00:00 1
a2 NBA 2008-12-16 13:00:00 0
a6 NBA 2008-12-16 13:00:00 1已选择13行。scott@STUDY> select
2 name
3 ,bet_type
4 ,bet_cnt
5 ,win_cnt
6 ,win_cnt/(case when bet_cnt > 0 then bet_cnt else 1 end) perc
7 from
8 (
9 select name, bet_type
10 ,count(1) bet_cnt
11 ,sum(case when bet_result=1 then 1 else 0 end) win_cnt
12 from aa
13 group by name, bet_type
14 );NAME BET_TYPE BET_CNT WIN_CNT PERC
-------------------- -------------------- ---------- ---------- ----------
a1 NBA 3 2 .666666667
a2 NBA 3 1 .333333333
a3 NBA 2 0 0
a6 NBA 3 3 1
a5 英超 1 0 0
a4 NBA 1 1 1已选择6行。
FROM ( SELECT temp_code,temp_type,
COUNT(temp_type) AS temp_cnt,
SUM(DECODE(temp_result,'1',1,0)) AS temp_sl
FROM temp
GROUP BY temp_code,temp_type )
create table TABLEA(NAME varchar2(10),BET_TYPE varchar2(10),MATCH_DT date,BET_RESULT int)
insert into TABLEA values('a1','NBA',sysdate,'1')
insert into TABLEA values('a1','NBA',sysdate,'1') SELECT NAME,count(BET_RESULT),
sum(decode(BET_RESULT,1,0,0)) as lost,
sum(decode(BET_RESULT,0,1,1)) as win,
sum(decode(BET_RESULT,0,1,1))/( sum(decode(BET_RESULT,1,0,0))+sum(decode(BET_RESULT,0,1,1))) rate
FROM TABLEA
GROUP BY NAME
HAVING count(BET_RESULT) >= (SELECT round(COUNT(*)*0.7) from TABLEA )
FROM ( SELECT temp_code,temp_type,MATCH_DT
COUNT(temp_type) AS temp_cnt,
SUM(DECODE(temp_result,'1',1,0)) AS temp_sl
FROM temp
GROUP BY temp_code,temp_type,MATCH_DT )
where AA>70%
而我要的是参加次数相对于所有场次要达到70%
create table test(name varchar2(4),
type varchar2(4),
time varchar(50),
result number(1));insert into test values ('a1', 'NBA', '2008-12-15 12:00:00', 1);
insert into test values ('a2', 'NBA', '2008-12-15 12:00:00', 0);
insert into test values ('a3', 'NBA', '2008-12-15 12:00:00', 0);
insert into test values ('a4', 'NBA', '2008-12-15 12:00:00', 1);
insert into test values ('a5', '英超', '2008-12-15 12:00:00 ', 0);
insert into test values ('a6', 'NBA', '2008-12-15 12:00:00', 1);
insert into test values ('a1', 'NBA', '2008-12-16 12:00:00', 0);
insert into test values ('a2', 'NBA', '2008-12-16 12:00:00', 1);
insert into test values ('a3', 'NBA', '2008-12-16 12:00:00', 0);
insert into test values ('a6', 'NBA', '2008-12-16 12:00:00', 1);
insert into test values ('a1', 'NBA', '2008-12-16 13:00:00', 1);
insert into test values ('a2', 'NBA', '2008-12-16 13:00:00', 0);
insert into test values ('a6', 'NBA', '2008-12-16 13:00:00', 1);
truncate table test;
select t.name,
t.type,
count(1) bet_cnt,
sum(case
when t.result = 1 then
1
else
0
end) / count(1) win_cnt
from test t
where exists (select 1
from (select name, type, count(1) cnt
from test
group by test.name, test.type) temp1,
(select type, count(1) cnt1 from test group by type) temp
where temp1.type = temp.type
and temp1.cnt / temp.cnt1 >= 1 / 4(参赛比例值)
and t.name = temp1.name
and t.type = temp1.type)
group by t.name, t.type
order by t.name期待高人出现
1 a1 NBA 3 0.666666666666667
2 a2 NBA 3 0.333333333333333
3 a5 英超 1 0
4 a6 NBA 3 1