Teradata的OLAP函数使用（3）

5，剩余视窗聚合（就是除去选中的）

SELECT  
    salesdate
    ,itemid
    ,sales
    ,SUM(sales)   OVER (ORDER BY salesdate ASC ROWS BETWEEN CURRENT ROW AND unbounded following)    AS "AMsum" 
    ,CAST((AMsum - sales)AS DECIMAL(6,2)) AS "excl. current" 
    ,SUM(sales)   OVER (ORDER BY salesdate ASC ROWS BETWEEN 1 following AND unbounded following)            AS "AM1sum" 
FROM daily_sales 
ORDER BY salesdate ASC 

 结果如下：

salesdate              itemid       sales      AMsum        excl. current     AM1sum
========           =====   =====   ======       =========   ==========
1997-01-01             10       350.00      4100.00         3750.00      3750.00
1997-01-02             10       100.00      3750.00         3650.00      3650.00
1997-01-03             10       250.00      3650.00         3400.00      3400.00
1997-01-05             10       350.00      3400.00         3050.00      3050.00
1997-01-10             10       450.00      3050.00         2600.00      2600.00
1997-01-21             10       250.00      2600.00         2350.00      2350.00
1997-01-25             10       300.00      2350.00         2050.00      2050.00
1997-01-31             10       100.00      2050.00         1950.00      1950.00
1997-02-01             10       550.00      1950.00         1400.00      1400.00
1997-02-03             10       350.00      1400.00         1050.00      1050.00
1997-02-06             10       150.00      1050.00          900.00       900.00
1997-02-17             10       250.00       900.00          650.00       650.00
1997-02-20             10       500.00       650.00          150.00       150.00
1997-02-27             10       150.00       150.00             .00           ?    

 BETWEEN CURRENT ROW AND unbounded following：当前行到记录结束的所以行

 BETWEEN 1 following AND unbounded following      ：从下面一条到最后一条记录的

unbounded ：不限制（后一般跟着 PRECEDING：前面的记录 following：接下来的记录 ）

6，排队

SELECT 
    itemid
    ,salesdate
    ,sales
    ,RANK() OVER (ORDER BY sales DESC) 
FROM daily_sales_2004
WHERE salesdate BETWEEN DATE '2004-01-01‘ AND DATE '2004-03-01’

 结果如下

     itemid salesdate        sales       Rank(sales)
     -------- -------------    -----------    -----------------
         10   04/01/10       550.00             	     1
         10   04/02/17       550.00             	     1
         10   04/02/20       450.00             	     3
         10   04/02/06       350.00             	     4
         10   04/02/27       350.00             	     4
         10   04/01/05       350.00             	     4
         10   04/01/03       250.00             	     7
         10   04/02/03       250.00             	     7
         10   04/01/25       200.00             	     9
         10   04/01/02       200.00        	     9
         10   04/01/21       150.00            	   11
         10   04/02/01       150.00            	   11
         10   04/01/01       150.00            	   11
         10   04/01/31       100.00            	   14

 Rank()的如果重复记录的队列会相同。接下来会奖行号，这个不会重复。

 

7.行号

SELECT itemid, salesdate, sales,
       ROW_NUMBER() OVER (ORDER BY sales DESC) 
  FROM daily_sales_2004 
 WHERE salesdate BETWEEN DATE '2004-01-01' AND DATE '2004-03-01’

 

结果如下

    itemid  salesdate       sales    Row_Number()
-----------  -------------    -----------    ---------------------
         10   04/01/10       550.00             	 1
         10   04/02/17       550.00             	 2
         10   04/02/20       450.00             	 3
         10   04/02/06       350.00             	 4
         10   04/02/27       350.00             	 5
         10   04/01/05       350.00             	 6
         10   04/01/03       250.00             	 7
         10   04/02/03       250.00             	 8
         10   04/01/25       200.00             	 9
         10   04/01/02       200.00        	10
         10   04/01/21       150.00            	11
         10   04/02/01       150.00            	12
         10   04/01/01       150.00            	13
         10   04/01/31       100.00            	14

 看见了吧，行号是连续的。