Efficient SQL to count an occurrence in the latest X rows

Question

For example I have:

create table a (i int);

Assume there are 10k rows.

I want to count 0's in the last 20 rows.

Something like:

select count(*) from (select i from a limit 20) where i = 0;

Is that possible to make it more efficient? Like a single SQL statement or something?

PS. DB is SQLite3 if that matters at all...

UPDATE

PPS. No need to group by anything in this instance, assume the table that is literally 1 column (and presumably the internal DB row_ID or something). I'm just curious if this is possible to do without the nested selects?

Answer 1

You'll need to order by something in order to determine the last 20 rows. When you say last, do you mean by date, by ID, ...?

Something like this should work:

select count(*) 
from (
    select i 
    from a 
    order by j desc
    limit 20
) where i = 0; 

Answer 2

You need to remember to order by when you use limit, otherwise the result is indeterminate. To get the latest rows added, you need to include a column with the insertion date, then you can use that. Without this column you cannot guarantee that you will get the latest rows.

To make it efficient you should ensure that there is an index on the column you order by, possibly even a clustered index.

Answer 3

I'm afraid that you need a nested select to be able to count and restrict to last X rows at a time, because something like this

SELECT count(*) FROM a GROUP BY i HAVING i = 0

will count 0's, but in ALL table records, because a LIMIT in this query will basically have no effect.

However, you can optimize making COUNT(i) as it is faster to COUNT only one field than 2 or more (in this case your table will have 2 fields, i and rowid, that is automatically created by SQLite in PKless tables)

Answer 4

If you do not remove rows from the table, you may try the following hacky query:

SELECT COUNT(*) as cnt
FROM A
WHERE
    ROWID > (SELECT MAX(ROWID)-20 FROM A)
    AND i=0;

It operates with ROWIDs only. As the documentation says: Rows are stored in rowid order.