FlinkSQL中的JOIN操作有哪几种类型？每种类型适用于什么场景？

维表关联​：如订单流与动态用户信息表关联。
​历史数据回溯​：按事件时间查询维表的历史状态。

-- 定义动态维表（需实现upsert模式）
CREATE TABLE user_dim (
  user_id STRING,
  name STRING,
  valid_from TIMESTAMP(3),
  valid_to TIMESTAMP(3),
  PRIMARY KEY (user_id) NOT ENFORCED
) WITH (...);
-- 时态表JOIN
SELECT o.order_id, u.name
FROM orders o
JOIN user_dim FOR SYSTEM_TIME AS OF o.event_time AS u
ON o.user_id = u.user_id;

时间范围匹配​：如检测用户登录后1小时内的支付行为。
​因果关系分析​：如订单创建与支付的时间关联性。

SELECT o.order_id, p.payment_id
FROM orders o
JOIN payments p ON o.user_id = p.user_id
WHERE 
  p.payment_time BETWEEN o.order_time - INTERVAL '1' HOUR AND o.order_time
WATERMARK FOR o.order_time AS o.order_time - INTERVAL '5' SECOND
WATERMARK FOR p.payment_time AS p.payment_time - INTERVAL '5' SECOND;

​时间窗口聚合​：如统计过去1小时内用户的订单金额。
​延迟容忍​：允许窗口内数据延迟到达。

-- 滚动窗口JOIN（每5分钟统计）
SELECT 
  TUMBLE_START(o.event_time, INTERVAL '5' MINUTE) AS window_start,
  SUM(o.amount) AS total_amount,
  COUNT(u.user_id) AS user_count
FROM orders o
JOIN user_info u ON o.user_id = u.user_id
GROUP BY 
  TUMBLE(o.event_time, INTERVAL '5' MINUTE),
  u.user_id;

实时关联​：如实时订单与用户维表关联，补充用户信息。
​动态过滤​：实时过滤不符合条件的数据。

-- 使用处理时间JOIN
SELECT o.order_id, u.name
FROM orders o
JOIN user_info u ON o.user_id = u.user_id
WHERE o.proctime BETWEEN u.proctime - INTERVAL '5' SECOND AND u.proctime;
-- 使用事件时间JOIN（需定义水印）
SELECT o.order_id, u.name
FROM orders o
JOIN user_info u ON o.user_id = u.user_id
WHERE o.event_time BETWEEN u.event_time - INTERVAL '5' SECOND AND u.event_time
WATERMARK FOR o.event_time AS o.event_time - INTERVAL '5' SECOND
WATERMARK FOR u.event_time AS u.event_time - INTERVAL '5' SECOND;