sql – rowsBetween和rangeBetween之间有什么区别？

来自PySpark docs rangeBetween： @H_403_2@

rangeBetween(start,end)

Defines the frame boundaries,from start (inclusive) to end (inclusive).

Both start and end are relative from the current row. For example,“0” means “current row”,while “-1” means one off before the current row,and “5” means the five off after the current row.

Parameters:

start – boundary start,inclusive. The frame is unbounded if this is -sys.maxsize (or lower).
end – boundary end,inclusive. The frame is unbounded if this is sys.maxsize (or higher).
New in version 1.4.

而rowsBetween

@H_403_2@

rowsBetween(start,from start (inclusive) to end (inclusive).

 
  Both start and end are relative positions from the current row. For example,while “-1” means the row before the current row,and “5” means the fifth row after the current row. 
  Parameters:  
   
   start – boundary start,inclusive. The frame is unbounded if this is sys.maxsize (or higher).
 New in version 1.4. 
   
  
 对于rangeBetween例如,“1 off”与“1行”有何不同？


解决方法

 很简单： 
  
 > ROWS BETWEEN并不关心确切的值.它只关心计算帧时的行顺序.
> RANGE BETWEEN在计算帧时考虑值. 
 让我们使用两个窗口定义的示例： 
 >在前2行和当前行之间排序x行
>在2前进和当前行之间按x排序 
 和数据为 
  
 +---+
|  x|
+---+
| 10|
| 20|
| 30|
| 31|
+---+ 
 假设当前行是第一个窗口的值为31的行,将包含以下行(当前一个,前两个)： 
  
 +---+----------------------------------------------------+
|  x|ORDER BY x ROWS BETWEEN 2  PRECEDING AND CURRENT ROW|
+---+----------------------------------------------------+
| 10|                                               false|
| 20|                                                true|
| 30|                                                true|
| 31|                                                true|
+---+----------------------------------------------------+ 
 并且对于下面的第二个(当前的一个,以及前面的所有,其中x> = 31 – 2)： 
  
 +---+-----------------------------------------------------+
|  x|ORDER BY x RANGE BETWEEN 2  PRECEDING AND CURRENT ROW|
+---+-----------------------------------------------------+
| 10|                                                false|
| 20|                                                false|
| 30|                                                 true|
| 31|                                                 true|
+---+-----------------------------------------------------+

原文链接：https://www.f2er.com/mssql/77189.html

sql – rowsBetween和rangeBetween之间有什么区别？

解决方法

猜你在找的MsSQL相关文章