Skip to content
Tutorials Center
My learning

Topics

Lesson 27 of 37 ยท Table API and SQL

SQL Windows, Joins and Top-N

Use Flink SQL window table functions (TUMBLE, HOP, CUMULATE), interval and temporal joins, Top-N and deduplication patterns, with runnable portable SQL equivalents.

The previous lesson showed that plain GROUP BY and regular joins keep state forever. Production SQL jobs therefore lean on four bounded patterns: window aggregation, time-bounded joins, Top-N and deduplication. Each is expressed in standard-looking SQL that Flink's planner recognizes and executes with efficient, cleaned-up state.

Flink-specific syntax is shown read-only, since the site cannot run Flink. For each pattern the runnable block computes the same answer with portable SQL on a fixed table, so you can see what the result means. This lesson follows Dynamic Tables and Changelogs.

Window aggregation with table-valued functions

Flink's windows are table-valued functions (TVFs). They take a table and a time column and add window_start, window_end and window_time columns to every row. You then GROUP BY window_start, window_end.

SQL
SELECT window_start, window_end, page, COUNT(*) AS views
FROM TABLE(
  TUMBLE(TABLE clicks, DESCRIPTOR(ts), INTERVAL '1' MINUTE))
GROUP BY window_start, window_end, page;

The window types:

Function Shape Example use
TUMBLE(data, time, size) fixed size, no overlap per-minute counts
HOP(data, time, slide, size) fixed size, overlapping 10-minute average every minute
CUMULATE(data, time, step, max_size) growing windows inside a max period running total per day, emitted hourly
SESSION(data, time, gap) closes after inactivity (partition by a key) user sessions in recent Flink versions

Event time must have a watermark declared on the table. When the watermark passes a window's end, the result is emitted once and the window's state is dropped, so a windowed aggregation is append-only and cheap. Windows are covered in more depth in the time and windows chapter of the Apache Flink track.

A tumbling window is simply integer division of the timestamp. This runnable query reproduces the answer on a fixed table where ts is in seconds.

SQL Tumbling-window counts (portable SQL equivalent)

Press Run (or Ctrl + Enter) to see the result here.

Joins: bound them in time

A regular join remembers everything. Two patterns bound the state.

Interval join matches rows whose timestamps are close to each other. Flink only keeps rows that can still match, and drops older ones as watermarks advance.

SQL
SELECT o.order_id, s.ship_id, s.ship_ts
FROM orders o
JOIN shipments s
  ON o.order_id = s.order_id
 AND s.ship_ts BETWEEN o.order_ts AND o.order_ts + INTERVAL '4' HOUR;

Temporal join looks up the value of a changing table (versioned table) as of the event's time, for example the exchange rate that was valid when the order was placed. The right side needs a primary key and a watermark.

SQL
SELECT o.order_id, o.amount * r.rate AS amount_eur
FROM orders AS o
JOIN rates FOR SYSTEM_TIME AS OF o.order_ts AS r
  ON o.currency = r.currency;

A lookup join is similar, but queries an external database at processing time with FOR SYSTEM_TIME AS OF o.proc_time. It is the way to enrich events with slowly changing dimension data kept in MySQL or HBase (the connector can cache results).

The runnable block imitates both: an interval join with a BETWEEN condition, and an "as of" lookup that picks the latest rate at or before the order time.

SQL Interval join and as-of lookup (portable SQL equivalent)

Press Run (or Ctrl + Enter) to see the result here.

Order 2 is shipped too late for the interval join and is dropped; order 2 also uses the newer rate 1.20 because it was placed after time 30.

Top-N

"Top 3 products per category" is a streaming favourite. In Flink SQL you write it with ROW_NUMBER() and filter on the rank. The planner recognizes the pattern and keeps only the N best rows per partition.

SQL
SELECT *
FROM (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) AS rownum
  FROM product_sales
)
WHERE rownum <= 3;

The result is updating: when a new row displaces another in the top 3, Flink retracts the old one. Combine it with a window aggregation to get a "top 5 pages every hour" board that is append-only.

Deduplication

The same ROW_NUMBER() form removes duplicate events: keep the first row per key by processing or event time and discard the rest.

SQL
SELECT event_id, payload, ts
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY event_id ORDER BY ts ASC) AS rn
  FROM events
)
WHERE rn = 1;

Ordering by a time attribute lets Flink run an optimized operator that keeps only the key and does not buffer all duplicates. Use DESC to keep the latest row instead. Run both shapes below.

SQL Top-N per group and deduplication

Press Run (or Ctrl + Enter) to see the result here.

Pattern matching with MATCH_RECOGNIZE

When you need to find sequences (a price that rises three times, a login failure followed by a success), Flink SQL supports MATCH_RECOGNIZE, a row pattern matching clause in the SQL standard. It is the SQL face of Flink's CEP library, shown in Patterns: Deduplication, Enrichment and CEP.

SQL
SELECT *
FROM ticker
MATCH_RECOGNIZE (
  PARTITION BY symbol
  ORDER BY ts
  MEASURES FIRST(A.price) AS start_price, LAST(B.price) AS end_price
  ONE ROW PER MATCH
  PATTERN (A B+)
  DEFINE B AS B.price > PREV(B.price)
) AS m;

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What columns do the window TVFs (TUMBLE, HOP, CUMULATE) add to each row?

    You then group by window_start and window_end to aggregate per window.
  2. 2. How does an interval join keep its state bounded?

    The time condition tells Flink how long a row can still find a partner.
  3. 3. What is a temporal join used for?

    FOR SYSTEM_TIME AS OF the event time fetches the right historical value, such as an exchange rate.
  4. 4. In the dedup pattern, why ORDER BY a time attribute?

    The ordering on a time attribute with rn = 1 is recognized as deduplication.

Summary

  • Window TVFs (TUMBLE, HOP, CUMULATE, SESSION) add window columns; finished windows emit once and free their state.
  • Bound joins in time: interval joins, temporal joins (as of event time) and lookup joins (external dimension data).
  • ROW_NUMBER() with a rank filter gives Top-N per group; with rn = 1 ordered by time it deduplicates.
  • MATCH_RECOGNIZE detects event sequences.
  • Prefer these patterns over plain GROUP BY and regular joins when the key or time space is unbounded.

Next: Connectors: Sources and Sinks