Snowflake中是否可一次性声明WINDOW子句实现DRY查询？

可以，Snowflake完全支持一次性声明共享的WINDOW子句，你给出的示例代码本身就是正确的实现方式，刚好能帮你实现DRY（Don't Repeat Yourself）的查询原则，避免重复编写相同的窗口定义。

核心用法说明 #

你只需要在查询的FROM子句之后添加WINDOW子句，给你的窗口定义起一个别名，然后在需要使用该窗口的分析函数中，用OVER 窗口别名代替重复的OVER (PARTITION BY ... ORDER BY ...)语句即可。

完整可运行示例 #

SELECT
  customer_id,
  order_date,
  order_amount,
  FIRST_VALUE(order_amount) OVER customer_window AS first_order_amount,
  LAST_VALUE(order_amount) OVER customer_window AS last_order_amount,
  AVG(order_amount) OVER customer_window AS avg_order_amount
FROM orders
WINDOW customer_window AS (PARTITION BY customer_id ORDER BY order_date);

进阶：基于已有窗口扩展定义 #

你还可以基于已声明的窗口别名，进一步调整窗口的帧范围等属性，无需重复写完整的分区和排序逻辑：

SELECT
  customer_id,
  order_date,
  order_amount,
  AVG(order_amount) OVER customer_window AS avg_all_orders,
  -- 在已有窗口基础上，只调整帧范围为最近3笔订单
  AVG(order_amount) OVER (customer_window ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS avg_last_3_orders
FROM orders
WINDOW customer_window AS (PARTITION BY customer_id ORDER BY order_date);

内容的提问来源于stack exchange，提问作者mateusz_zdrenka