Re: [sqlite] Find the user in the sorted table and retrieve a certain number of users above and below him
I did try to do something with rank() and row_number() but it slowed the query a lot bringing it up to 26 ms. > On 14 Feb 2019, at 16:33, Shawn Wagner wrote: > > Are you using a new enough version of sqlite (3.25 or better) that you can > use window functions? > >> On Thu, Feb 14, 2019, 7:29 AM Dobrila Šunde > >> Hi! >> >> So I have been trying to write a query that needs to be really fast (under >> 1 ms would be ideal) to a very large database (around 20 000 rows and 20 >> columns). The problem is that I need to find the user inside the sorted >> database and retrieve a number of users better and worse than him as to >> make a ranking table and show the user in the middle of it (the ranking >> table consists of 7 players). >> Currently this query takes about 3.5 ms and has a lot of issues. Some of >> them include the fact that I can't retrieve the users that have the same >> amount of points as my user. I also need to know the exact rank of the >> person inside the whole database that's why I need the count (*). It would >> be ideal to have the ranks of every player in the database but I couldn't >> find a way that isn't ridiculously slow. Is there any fast way I can >> retrieve 3 players better or equal to my player with points and worse or >> equal to my player with points so there aren't any duplicates? >> >> select player.user_profile_id, (select count(*) + 1 from event_stats >> player2 where player2.points > player.points order by player2.points desc) >> as 'rank', 'player' as 'stats_group', player.name, player.points from >> event_stats player where player.user_profile_id=202 >> union all >> SELECT * >> FROM ( >> select stats.user_profile_id, 0 as 'rank', 'ahead' as 'stats_group', >> stats.name, stats.points from event_stats user >> left join event_stats stats on stats.points > user.points >> where user.user_profile_id=202 order by stats.points ASC limit 3 >> ) q1 >> union all >> SELECT * >> FROM ( >> select stats.user_profile_id, 0 as 'rank', 'below' as 'stats_group', >> stats.name, stats.fame from event_stats user >> left join event_stats stats on stats.points < user.points >> where user.user_profile_id=202 order by stats.points DESC limit 3 >> ) q2 order by points desc >> >> Thanks for your help! >> ___ >> sqlite-users mailing list >> sqlite-users@mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Find the user in the sorted table and retrieve a certain number of users above and below him
Yes, sqlite 3.26. On Thu, Feb 14, 2019 at 4:33 PM Shawn Wagner wrote: > Are you using a new enough version of sqlite (3.25 or better) that you can > use window functions? > > On Thu, Feb 14, 2019, 7:29 AM Dobrila Šunde wrote: > > > Hi! > > > > So I have been trying to write a query that needs to be really fast > (under > > 1 ms would be ideal) to a very large database (around 20 000 rows and 20 > > columns). The problem is that I need to find the user inside the sorted > > database and retrieve a number of users better and worse than him as to > > make a ranking table and show the user in the middle of it (the ranking > > table consists of 7 players). > > Currently this query takes about 3.5 ms and has a lot of issues. Some of > > them include the fact that I can't retrieve the users that have the same > > amount of points as my user. I also need to know the exact rank of the > > person inside the whole database that's why I need the count (*). It > would > > be ideal to have the ranks of every player in the database but I couldn't > > find a way that isn't ridiculously slow. Is there any fast way I can > > retrieve 3 players better or equal to my player with points and worse or > > equal to my player with points so there aren't any duplicates? > > > > select player.user_profile_id, (select count(*) + 1 from event_stats > > player2 where player2.points > player.points order by player2.points > desc) > > as 'rank', 'player' as 'stats_group', player.name, player.points from > > event_stats player where player.user_profile_id=202 > > union all > > SELECT * > > FROM ( > > select stats.user_profile_id, 0 as 'rank', 'ahead' as 'stats_group', > > stats.name, stats.points from event_stats user > > left join event_stats stats on stats.points > user.points > > where user.user_profile_id=202 order by stats.points ASC limit 3 > > ) q1 > > union all > > SELECT * > > FROM ( > > select stats.user_profile_id, 0 as 'rank', 'below' as 'stats_group', > > stats.name, stats.fame from event_stats user > > left join event_stats stats on stats.points < user.points > > where user.user_profile_id=202 order by stats.points DESC limit 3 > > ) q2 order by points desc > > > > Thanks for your help! > > ___ > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Find the user in the sorted table and retrieve a certain number of users above and below him
Are you using a new enough version of sqlite (3.25 or better) that you can use window functions? On Thu, Feb 14, 2019, 7:29 AM Dobrila Šunde Hi! > > So I have been trying to write a query that needs to be really fast (under > 1 ms would be ideal) to a very large database (around 20 000 rows and 20 > columns). The problem is that I need to find the user inside the sorted > database and retrieve a number of users better and worse than him as to > make a ranking table and show the user in the middle of it (the ranking > table consists of 7 players). > Currently this query takes about 3.5 ms and has a lot of issues. Some of > them include the fact that I can't retrieve the users that have the same > amount of points as my user. I also need to know the exact rank of the > person inside the whole database that's why I need the count (*). It would > be ideal to have the ranks of every player in the database but I couldn't > find a way that isn't ridiculously slow. Is there any fast way I can > retrieve 3 players better or equal to my player with points and worse or > equal to my player with points so there aren't any duplicates? > > select player.user_profile_id, (select count(*) + 1 from event_stats > player2 where player2.points > player.points order by player2.points desc) > as 'rank', 'player' as 'stats_group', player.name, player.points from > event_stats player where player.user_profile_id=202 > union all > SELECT * > FROM ( > select stats.user_profile_id, 0 as 'rank', 'ahead' as 'stats_group', > stats.name, stats.points from event_stats user > left join event_stats stats on stats.points > user.points > where user.user_profile_id=202 order by stats.points ASC limit 3 > ) q1 > union all > SELECT * > FROM ( > select stats.user_profile_id, 0 as 'rank', 'below' as 'stats_group', > stats.name, stats.fame from event_stats user > left join event_stats stats on stats.points < user.points > where user.user_profile_id=202 order by stats.points DESC limit 3 > ) q2 order by points desc > > Thanks for your help! > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Find the user in the sorted table and retrieve a certain number of users above and below him
Hi! So I have been trying to write a query that needs to be really fast (under 1 ms would be ideal) to a very large database (around 20 000 rows and 20 columns). The problem is that I need to find the user inside the sorted database and retrieve a number of users better and worse than him as to make a ranking table and show the user in the middle of it (the ranking table consists of 7 players). Currently this query takes about 3.5 ms and has a lot of issues. Some of them include the fact that I can't retrieve the users that have the same amount of points as my user. I also need to know the exact rank of the person inside the whole database that's why I need the count (*). It would be ideal to have the ranks of every player in the database but I couldn't find a way that isn't ridiculously slow. Is there any fast way I can retrieve 3 players better or equal to my player with points and worse or equal to my player with points so there aren't any duplicates? select player.user_profile_id, (select count(*) + 1 from event_stats player2 where player2.points > player.points order by player2.points desc) as 'rank', 'player' as 'stats_group', player.name, player.points from event_stats player where player.user_profile_id=202 union all SELECT * FROM ( select stats.user_profile_id, 0 as 'rank', 'ahead' as 'stats_group', stats.name, stats.points from event_stats user left join event_stats stats on stats.points > user.points where user.user_profile_id=202 order by stats.points ASC limit 3 ) q1 union all SELECT * FROM ( select stats.user_profile_id, 0 as 'rank', 'below' as 'stats_group', stats.name, stats.fame from event_stats user left join event_stats stats on stats.points < user.points where user.user_profile_id=202 order by stats.points DESC limit 3 ) q2 order by points desc Thanks for your help! ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
