全部面试刷题微服务数据库消息队列搜索引擎大数据运维 go语言人工智能

FlinkSQL中的TopN查询如何实现？有哪些优化技巧？

提问者：帅平问题分类：面试刷题

FlinkSQL面试题 Flink 性能调优

发布于：4天前 IP属地：

3 个回答

旧梦拾遗べ忆往昔

结合ReduceFunction或AggregateFunction实现增量聚合，减少状态开销。

CREATE TABLE top3_products (
    product_id STRING,
    click_count BIGINT,
    window_start TIMESTAMP(3)
) WITH (...);
INSERT INTO top3_products
SELECT 
    product_id, 
    SUM(click_count) AS total_clicks,
    TUMBLE_START(event_time, INTERVAL '1' MINUTE) AS window_start
FROM clickstream
GROUP BY 
    product_id, 
    TUMBLE(event_time, INTERVAL '1' MINUTE)
HAVING SUM(click_count) IN (
    SELECT TOP3_CLICKS(product_id, SUM(click_count)) 
    FROM clickstream 
    GROUP BY product_id
);

发布于：4天前 IP属地：

我是白云

对全量数据排序后取前N条，适用于实时性要求低但需全局一致性的场景。

SELECT 
    product_id, 
    click_count
FROM (
    SELECT 
        product_id, 
        COUNT(*) AS click_count,
        ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) AS rn
    FROM clickstream
    GROUP BY product_id
) 
WHERE rn <= 5;

发布于：4天前 IP属地：

勿忘心安

通过TUMBLE或HOP窗口定义时间范围，结合ROW_NUMBER()函数排序并筛选前N条记录。

SELECT 
    product_id, 
    click_count,
    window_start
FROM (
    SELECT 
        product_id, 
        COUNT(*) AS click_count,
        TUMBLE_START(event_time, INTERVAL '1' HOUR) AS window_start,
        ROW_NUMBER() OVER (PARTITION BY TUMBLE_START(event_time, INTERVAL '1' HOUR) 
                          ORDER BY COUNT(*) DESC) AS rn
    FROM clickstream
    GROUP BY 
        product_id, 
        TUMBLE(event_time, INTERVAL '1' HOUR)
) 
WHERE rn <= 3;

发布于：4天前 IP属地：

我来回答