Another way of using COUNT(DISTINCT column) OVER(...)

Question

My desired output is something like:



My attempt is as follows:

SELECT id, g_minute, COUNT(DISTINCT id) OVER(ORDER BY f_datetime DESC) AS counter FROM tbl_X 

When I try this I get:

This version of MariaDB doesn't yet support 'COUNT(DISTINCT) aggregate as window function' */

Is there another 'simple' way of doing this? I want a cumulative counter of unique id's. Is there another window function that can do the same thing?

Answer 1

Try a nested window function instead:

SELECT id
    ,g_minute 
    ,sum(seqno = 1) OVER (
        ORDER BY f_datetime DESC
        ) AS counter
FROM (
    SELECT t.*
        ,row_number() OVER (
            PARTITION BY id ORDER BY f_datetime DESC
            ) AS seqno
    FROM tbl_X t
    ) r

Answer 2

This is the way I get the count of values when I'm testing

SELECT
     ID,
     G_MINUTE,
     COUNT(ID)
FROM tbl_X
GROUP BY ID, G_MINUTE

This will give you the distinct count of the ID and g_minute