Hi Jing, Thanks for chiming in. This sounds great. Any chance this will work for Flink 1.13 as well, as I am using AWS KDA.
Thanks, -Guoqin On Wed, Dec 8, 2021 at 7:47 PM Jing Zhang <beyond1...@gmail.com> wrote: > Hi Guoqin, > I guess you have misunderstood Martijn's response. > Martijn suggest you use Window TopN. Besides, Window TopN does not need to > follow a Window Aggregate, it could followed with Window TVF directly since > Flink 1.14. Please see document [1] attached. > You could try the following SQL to get the record with the max gauge > because you use ORDER BY gauge desc and fetch the first one. > > SELECT deviceId, locationId, gauge, window_start, window_end > FROM ( > SELECT *, ROW_NUMBER() OVER (PARTITION BY window_start, window_end ORDER > BY gauge DESC) as rownum > FROM TABLE( > TUMBLE(TABLE MyTable, DESCRIPTOR(readtime), INTERVAL '5' > MINUTES)) > ) WHERE rownum <= 1; > > [1] > https://nightlies.apache.org/flink/flink-docs-release-1.14/docs/dev/table/sql/queries/window-topn/#window-top-n-follows-after-windowing-tvf > > Best, > Jing Zhang > > > > > Guoqin Zheng <lanson.zh...@gmail.com> 于2021年12月9日周四 10:30写道： > >> Hi Martijn, >> >> Thanks for your quick response. I tried it, but it does not seem to work. >> >> The problem is that I want to select fields that are not in the `GROUP >> BY`. So in my example, I can have a tumble window on `readtime`, and select >> max(gauge), but I also want both `deviceId` and `locationId` of the max >> record included in the selected result. With Top-N, it does not seem to >> allow that. >> >> -Guoqin >> >> On Wed, Dec 8, 2021 at 1:22 PM Martijn Visser <mart...@ververica.com> >> wrote: >> >>> Hi Guoqin, >>> >>> I think you could use the Window Top-N. There's a recipe in the Flink >>> SQL Cookbook [1]. The example uses a SUM which you should change to MAX and >>> of course you change the rownum to 1 instead of 3. >>> >>> Best regards, >>> >>> Martijn >>> >>> [1] >>> >>> https://github.com/ververica/flink-sql-cookbook/blob/main/aggregations-and-analytics/11_window_top_n/11_window_top_n.md >>> >>> Op wo 8 dec. 2021 om 19:54 schreef Guoqin Zheng <lanson.zh...@gmail.com> >>> >>>> Hi Flink Community, >>>> >>>> I am curious what the recommended way is to select the event with a max >>>> attribute value with SQL api. >>>> >>>> For example, I have an event stream like: >>>> >>>> { >>>> deviceId, >>>> locationId >>>> gauge, >>>> readtime, <-- eventTime >>>> } >>>> >>>> I want to figure out which device and location has the max gauge over a >>>> 5-mins window. >>>> >>>> Any advice would be greatly appreciated! >>>> >>>> Thanks! >>>> -Guoqin >>>> >>> -- >>> >>> Martijn Visser | Product Manager >>> >>> mart...@ververica.com >>> >>> <https://www.ververica.com/> >>> >>> >>> Follow us @VervericaData >>> >>> -- >>> >>> Join Flink Forward <https://flink-forward.org/> - The Apache Flink >>> Conference >>> >>> Stream Processing | Event Driven | Real Time >>> >>>
