T-SQL:qualify和window 使用（十七）

标准SQL没用定义qualify子句，它是Teredata特有的特性。

2.window

允许我们对窗口进行命名描述；然后在定义其他窗口-即将被串钩函数使用或用来定义另一个命名窗口时，代指这个命名的窗口描述。

如下

SELECT empid, ordermonth, qty,
  SUM(qty) OVER (PARTITION BY empid
                 ORDER BY ordermonth
                 ROWS BETWEEN UNBOUNDED PRECEDING
                          AND CURRENT ROW) AS run_sum_qty,
  AVG(qty) OVER (PARTITION BY empid
                 ORDER BY ordermonth
                 ROWS BETWEEN UNBOUNDED PRECEDING
                          AND CURRENT ROW) AS run_avg_qty,
  MIN(qty) OVER (PARTITION BY empid
                 ORDER BY ordermonth
                 ROWS BETWEEN UNBOUNDED PRECEDING
                          AND CURRENT ROW) AS run_min_qty,
  MAX(qty) OVER (PARTITION BY empid
                 ORDER BY ordermonth
                 ROWS BETWEEN UNBOUNDED PRECEDING
                          AND CURRENT ROW) AS run_max_qty
FROM EmpOrders;

用window缩写前置查询

SELECT empid, ordermonth, qty,
  SUM(qty) OVER W1 AS run_sum_qty,
  AVG(qty) OVER W1 AS run_avg_qty,
  MIN(qty) OVER W1 AS run_min_qty,
  MAX(qty) OVER W1 AS run_max_qty
FROM Sales.EmpOrders
WINDOW W1 AS ( PARTITION BY empid
               ORDER BY ordermonth
               ROWS BETWEEN UNBOUNDED PRECEDING
                        AND CURRENT ROW );

window吧一个带有分区，排序和框架选项的完整的窗口描述为w1  

T-SQL:qualify和window 使用（十七）

标签：row   选项   order   avg   nbsp   rom   win   esc   run