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
>>
>>
