I have an SQLite database that contains a list of user messages in a group. And I want to get a user's "rank" by counting the number of messages they had sent.
Currently I'm doing this
SELECT user_id, COUNT(*) as count
FROM message
group by user_id
ORDER BY count DESC
It'd return something like this:
| - | user_id | count |
|---|---|---|
| 1 | 2072040132 | 61877 |
| 2 | 1609505732 | 40514 |
| 3 | 1543287045 | 34735 |
| 4 | 203349203 | 30570 |
| 5 | 842634673 | 29651 |
| 6 | 1702633101 | 29185 |
| 7 | 1978947042 | 27728 |
| 8 | 1929648593 | 27025 |
| 9 | 1069841429 | 17944 |
| 10 | 1437208364 | 17344 |
| 11 | ... | ... |
Like user 1609505732 is top 2, and 1702633101 is top 6.
But my database has more than 2 million rows, and this is too slow having to fetch all of the list. I was wondering if there are any way that I can fetch only the order of it.
Like this:
| - | user_id | order | count |
|---|---|---|---|
| 1 | 1702633101 | 6 | 61877 |
And the user with id=1702633101 is top 6. That'd be a lot faster.
Thanks for spending time on my question, I can't seem to find the answer anywhere on the internet.
CodePudding user response：
To improve query speed, I'd consider physicalising the aggregate view, example below:
CREATE Table as tbl_aggregate()
Id INTEGER PRIMARY KEY AUTOINCREMENT
, user_id NVARCHAR
, count INT;
INSERT INTO tbl_aggregate
SELECT user_id, COUNT(*) as count
FROM message
group by user_id
ORDER BY count DESC;
Select * from tbl_aggregate
Where Id = 6
Select top 10 * from tbl_aggregate