How to calculate one day's uv every minute by SQL

Posted by liujiangang on
URL: http://deprecated-apache-flink-user-mailing-list-archive.369.s1.nabble.com/How-to-calculate-one-day-s-uv-every-minute-by-SQL-tp29789.html

      We want to calculate one day's uv and show the result every minute . We have implemented this by java code:

      dataStream.keyBy(dimension)
                        .incrementWindow(Time.days(1), Time.minutes(1))
                        .uv(userId)

      The input data is big. So we use ValueState<Bitmap> to store all the distinct userIds from 00:00:00 to last minute. For current minute, we union the minute's data with ValueState<Bitmap> to obtain a new ValueState<Bitmap> and output the current uv.

      The problem is how to translate the java code to sql? We expect the sql to be like this:

    

       select incrementWindow_end, dimension, distinct(userId) from table group by incrementWindow(Time.days(1), Time.minutes(1)), dimension

      Anyone can give me some suggestions? Thank you very much.