How to aggregate data group by week in FlinkSQL

Question

If I want aggregate data group by day, sql:

select
DATE_FORMAT(ctime, 'yyyyMMdd'),
count(*) as num
from event
group by DATE_FORMAT(ctime, 'yyyyMMdd');

how to aggregate data group by week?

Answer 1

You can aggregate by week using:

SELECT date_format(ctime,'YYYYw'),
       count(*) AS num
FROM event
GROUP BY date_format(ctime,'YYYYw')

Any format string that is compatible with Java's SimpleDateFormat is supported in the date_format function.