Hello all,
I have a toy problem which is analogous to a real problem I am working
on. The toy problem is, given a set of teams each with a roster of
100 possible players, create a set of teams each with 6 players chosen
randomly from the roster of 100 players. I can model the sets in
sqlite3 and pick the 6 random players per team in SQL (see below). I
am just curious to know of alternate ways which may be faster as this
method below seems to crawl as the number of teams gets big
(>200,000).
For example, the initial data could be displayed like so:
Team: players
aaa : 1,2,3..100
bbb : 1,2,3..100
ccc : 1,2,3..100
...
The final data should look something like this:
Team: players
aaa : 5, 20, 38, 75, 98, 100
bbb : 10, 23, 24, 87, 92, 95
ccc : 2, 5, 7, 12, 35, 67
...
I can model the initial dataset in sqlite3 like so:
$ echo {a..c}{a..c}{a..c}$'\t'{1..100} | tr ' ' '\n' |
sqlite3 -init <( echo '
drop table if exists pool
;
create table pool (team TEXT, player INTEGER)
;
.mode tabs
.imp "/dev/stdin" "pool"
select count(*) from pool
;
.mode column
.head on
select team, count(player) from pool group by team limit 10
;
') sample.db .quit
2700
team count(player)
---------- -------------
aaa 100
aab 100
aac 100
aba 100
abb 100
abc 100
aca 100
acb 100
acc 100
baa 100
I can pick the first six using this SQL construct:
$ sqlite3 -init <( echo '
.mode column
.head on
select team, player from pool p1 where rowid in (
select rowid from pool p2 where
p1.team=p2.team
limit 6
)
limit 10
;
') sample.db .quit
-- Loading resources from /dev/fd/63
team player
---------- ----------
aaa 1
aaa 2
aaa 3
aaa 4
aaa 5
aaa 6
aab 1
aab 2
aab 3
aab 4
And I can adjust the table to pick 6 random players by including
another column that holds a random integer:
$ sqlite3 -init <( echo '
alter table pool add tag INTEGER
;
update pool set tag=random()
;
.mode column
.head on
select team, player from pool p1 where rowid in (
select rowid from pool p2 where
p1.team=p2.team
order by tag
limit 6
)
limit 10
;
') sample.db .quit
-- Loading resources from /dev/fd/63
team player
---------- ----------
aaa 4
aaa 8
aaa 10
aaa 12
aaa 73
aaa 76
aab 2
aab 43
aab 48
aab 49
I'm curious to know if there is a faster way of select 6 random
players per team?
Regards,
- Robert
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
