CODEWITHSUNDEEP
mysqlsqldatabaserelational-databaseanalytics
Zack
Zack

Reputation: 2208

count(distinct(person_id)) not working with window function in MySQL database

So I found that mysql does not support using aggregate function like count(distinct(person_id)) with window function. For example, the below query will not work.

select count(distinct(person_id)) over ([OrderBy clause])
from <table>;

What is the alternative to this problem that works equally fast like window functions?

schema:

create table table1(
check_date date,
person_id  varchar(10)
);

My attempted query:

select person_id,count(distinct(person_id))
over (order by check_date range between interval '20' day preceding and current row)
from table1;

Need to get count of all distinct persons who checked into the system in a window frame of 20 days.

Upvotes: 0

Views: 1038

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270573

THIS ANSWERS THE ORIGINAL VERSION OF THE QUESTION.

In MySQL 8+, you can emulate this using two window functions:

select sum(seqnum = 1) over (order by ?) as num_distinct
from (select t.*,
             row_number() over (partition by person_id order by ?) as seqnum
      from <table> t
     ) t;

Upvotes: 1

Related Questions