Home > Net >  Another way of using COUNT(DISTINCT column) OVER(...)
Another way of using COUNT(DISTINCT column) OVER(...)

Time:02-26

My desired output is something like:

"16924595"  "35"
"16924595"  "40"
"16924595"  "45"
"16924610"  "35"
"16924610"  "40"
"16924610"  "45"
"16924644"  "35"
"16924644"  "40"
"16924644"  "45"
"16924648"  "35"
"16924648"  "40"
"16924648"  "45"
"16924666"  "35"
"16924666"  "40"
"16924666"  "45"
"16924697"  "45"
"16924705"  "35"
"16924705"  "40"
"16924705"  "45"
"16924716"  "35"
"16924716"  "40"
"16924716"  "45"
"16924773"  "35"
"16924773"  "40"
"16924773"  "45"
"16924797"  "35"
"16924816"  "35"

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?

CodePudding user response:

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

CodePudding user response:

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

  • Related