T-SQL：qualify和window使⽤（⼗七）
1.qualify
是⼀个潜在的额外筛选器
主要⽤于对开窗函数的数据筛选
SELECT orderid, orderdate, val,
RANK() OVER(ORDER BY val DESC) AS rnk
FROM Sales.OrderValues
QUALIFY rnk <=5;
标准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,rows函数的使用方法及实例
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

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。