The SqlWindow example involves populating tables thru a form that is derived by the Dataphor frontend server. The fact that the form is derived by the system services is a key concept in Dataphors ability to be a vehicle of rapid application development. The example covers a wide range of Dataphor capabilities that are used in application development. Concepts covered include the presentation layer, metadata, forms, event handlers, references, constraints, cursors and views as well as many other programming aspects of D4. What does it do Based on user input several tables are populated and can be viewed thru a form. Complete editing is available. The example uses Sql Server 2005 as the data respository (device). Requirements The application was developed using Dataphor v2677 and MS Sql Server 2005 sp1. Except for an sql stored procedure (which is not required) the example should run on any version of Sql Server or any other database for that matter. Download the demo Everything needed to run the demo including screenshots and setup scripts for tables, views, constraints, references, operators etc. can be download @ http://www.geocities.com/d4tosql/D4/sqlwindowexample.zip All scripts for Dataphor objects are documented with comments that should help explain the what and why of the code. Screenshots You can view screenshots of the demo here and here. View a D4 operator used here. What is the context of the example Two different methods are used to compute cumulative aggregates that are based on the concept of an sql window as outlined in the Sql-99 standard. The sql window is intended to simplify and make more efficient the computing of cumulative sums. While an sql window is available in DB2 and Oracle, it is not in Sql Server. The two methods used in the example simulate two different conceptual approaches to deriving cumulative/running sums. Who is this intended for For anyone interested in exploring in greater detail key programming aspects of Dataphor used in application development. There are many operators and other Dataphor objects to go thru. The code is relatively straightforward and easy to follow. The example can also be used as a demo for those not familiar with Dataphor. The setup script will do everything required to run the example. It is only necessary to have a library using Sql Server as the device. The form in this example is 'completely' derived by the frontend server using metadata supplied in various places. Even without a customized form entering appropriate data is very easy and straightforward. If an entry is invalid and violates a constraint the custom error message(s) fully describe how to correct the entry. A modified form with menus will be available in the future. Sql background for computing cumulative/running sums Traditionally computing a running sum involved a subquery with an inequality comparision. Given the sample table Stocks, the following sql query will compute running sums for each Stock in ascending order of QTime. The query can be run from Dataphor using the SQLQuery operator to pass it directly to Sql Server. The use of the row_number() function allows the ranks to be easily compared. select SQLQuery (' select A.Stock,A.PRank,A.QTime,A.Quote, (select Sum(B.Quote) from (select Stock,QTime,Quote, row_number()over(Partition by Stock Order by QTime,Quote) as PRank from Stocks) as B where B.Stock=A.Stock and B.PRank<=A.PRank) as YSum from (select Stock,QTime,Quote, row_number()over(Partition by Stock Order by QTime,Quote) as PRank from Stocks) as A '); Stock PRank QTime Quote YSum -------- ----- ---------------------- ----- ---- IBM 1 4/3/2006 2:47:00 PM 107 107 IBM 2 7/16/2006 7:39:00 AM 157 264 IBM 3 8/26/2006 6:24:00 PM 125 389 IBM 4 9/8/2006 1:11:00 PM 171 560 IBM 5 11/22/2006 12:01:00 PM 155 715 IBM 6 2/2/2007 12:03:00 AM 171 886 IBM 7 4/13/2007 3:30:00 AM 219 1105 IBM 8 10/18/2007 12:12:00 AM 133 1238 IBM 9 12/25/2007 10:48:00 AM 139 1377 MS 1 3/17/2006 7:12:00 PM 257 257 . This method is similar to computing ranks as discussed here and is also just as inefficient. The impact of the subquery is more compelling when it is expressed as a table. The following query gives the counts for the stocks: select Stocks group by {Stock} add{Count() Cnt}; Stock Cnt -------- --- IBM 9 MS 11 MySQL_AB 12 Oracle 11 Sybase 9 For a table to represent the subquery it must be able to accommodate the maximum number of rows in a Stock. Based on MySQL_AB, we need at least 12 rows. Consider the following D4 batch which populates a table assuming the current row is the 6th row (PRank=6) in the 'IBM' Stock partition. The table represents all rows up to and including the 6th row. Non-existent rows, thru the use of IfNil, are given a PRank and nil (null) value for Quote. A sum of the Quote values from the table would represent the running sum up thru the 6th rank (PRank) for the 'IBM' Stock. //Shows table based on table var S for a particular Stock and PRank. var S:=SQLQuery('select Stock,QTime,Quote, row_number()over(Partition by Stock Order by QTime,Quote) as PRank from Stocks') {Stock,PRank,Quote} ; var Stock:='IBM'; var PRank:=6; select table { IfNil((S adorn{key{Stock,PRank}})[Stock,PRank-11],row{-11 PRank,nil Stock,nil Quote}), IfNil((S adorn{key{Stock,PRank}})[Stock,PRank-10],row{-10 PRank,nil Stock,nil Quote}), IfNil((S adorn{key{Stock,PRank}})[Stock,PRank-9], row{ -9 PRank,nil Stock,nil Quote}), IfNil((S adorn{key{Stock,PRank}})[Stock,PRank-8], row{ -8 PRank,nil Stock,nil Quote}), IfNil((S adorn{key{Stock,PRank}})[Stock,PRank-7], row{ -7 PRank,nil Stock,nil Quote}), IfNil((S adorn{key{Stock,PRank}})[Stock,PRank-6], row{ -6 PRank,nil Stock,nil Quote}), IfNil((S adorn{key{Stock,PRank}})[Stock,PRank-5], row{ -5 PRank,nil Stock,nil Quote}), IfNil((S adorn{key{Stock,PRank}})[Stock,PRank-4], row{ -4 PRank,nil Stock,nil Quote}), IfNil((S adorn{key{Stock,PRank}})[Stock,PRank-3], row{ -3 PRank,nil Stock,nil Quote}), IfNil((S adorn{key{Stock,PRank}})[Stock,PRank-2], row{ -2 PRank,nil Stock,nil Quote}), IfNil((S adorn{key{Stock,PRank}})[Stock,PRank-1], row{ -1 PRank,nil Stock,nil Quote}), (S adorn{key{Stock,PRank}})[Stock,PRank] } order by {PRank desc}; Stock PRank Quote ---------- ----- ---------- IBM 6 171 IBM 5 155 IBM 4 171 IBM 3 125 IBM 2 157 IBM 1 107 <No Value> -6 <No Value> <No Value> -7 <No Value> <No Value> -8 <No Value> <No Value> -9 <No Value> <No Value> -10 <No Value> <No Value> -11 <No Value> The fact that the table has to be repopulated for each rank underscores the inefficiency of the subquery. There is no reuse of information from one rank to the next. When we compute the running sum for 'IBM': var S:=SQLQuery('select Stock,QTime,Quote, row_number()over(Partition by Stock Order by QTime,Quote) as PRank from Stocks') {Stock,PRank,Quote} ; select S add { Sum ( Quote from ( table { IfNil((S adorn{key{Stock,PRank}})[Stock,PRank-11],row{-11 PRank,nil Stock,nil Quote}), IfNil((S adorn{key{Stock,PRank}})[Stock,PRank-10],row{-10 PRank,nil Stock,nil Quote}), IfNil((S adorn{key{Stock,PRank}})[Stock,PRank-9], row{ -9 PRank,nil Stock,nil Quote}), IfNil((S adorn{key{Stock,PRank}})[Stock,PRank-8], row{ -8 PRank,nil Stock,nil Quote}), IfNil((S adorn{key{Stock,PRank}})[Stock,PRank-7], row{ -7 PRank,nil Stock,nil Quote}), IfNil((S adorn{key{Stock,PRank}})[Stock,PRank-6], row{ -6 PRank,nil Stock,nil Quote}), IfNil((S adorn{key{Stock,PRank}})[Stock,PRank-5], row{ -5 PRank,nil Stock,nil Quote}), IfNil((S adorn{key{Stock,PRank}})[Stock,PRank-4], row{ -4 PRank,nil Stock,nil Quote}), IfNil((S adorn{key{Stock,PRank}})[Stock,PRank-3], row{ -3 PRank,nil Stock,nil Quote}), IfNil((S adorn{key{Stock,PRank}})[Stock,PRank-2], row{ -2 PRank,nil Stock,nil Quote}), IfNil((S adorn{key{Stock,PRank}})[Stock,PRank-1], row{ -1 PRank,nil Stock,nil Quote}), (S adorn{key{Stock,PRank}})[Stock,PRank] } ) ) SumQte } ; Stock PRank Quote SumQte -------- ----- ----- ------ IBM 1 107 107 IBM 2 157 264 IBM 3 125 389 IBM 4 171 560 IBM 5 155 715 IBM 6 171 886 IBM 7 219 1105 IBM 8 133 1238 IBM 9 139 1377 MS 1 257 257 . We are making 45 inserts into the table since we are starting over from the 1st rank for each row of 'IBM': 1+2+3+4+5+6+7+8+9=45 inserts. In the example operators D4ApplyforForm (used on the before insert event) and D4ApplyforFormUpdate (used on the before update event) represent the the idea of having to build a solution independently for each row. It therefore represents an inefficient solution just as a subquery does. Obviously it would make a lot more sense to simply add each new row to the table which had all the previous rows and then get the sum. We could represent the addition of a single row to the table as follows: //Shows reusing information from one PRank to another. var S:=SQLQuery('select Stock,QTime,Quote, row_number()over(Partition by Stock Order by QTime,Quote) as PRank from Stocks') {Stock,PRank,Quote} ; var Stock:='IBM'; var PRank:=6; var T1:= table { IfNil((S adorn{key{Stock,PRank}})[Stock,PRank-11],row{-11 PRank,nil Stock,nil Quote}), IfNil((S adorn{key{Stock,PRank}})[Stock,PRank-10],row{-10 PRank,nil Stock,nil Quote}), IfNil((S adorn{key{Stock,PRank}})[Stock,PRank-9], row{ -9 PRank,nil Stock,nil Quote}), IfNil((S adorn{key{Stock,PRank}})[Stock,PRank-8], row{ -8 PRank,nil Stock,nil Quote}), IfNil((S adorn{key{Stock,PRank}})[Stock,PRank-7], row{ -7 PRank,nil Stock,nil Quote}), IfNil((S adorn{key{Stock,PRank}})[Stock,PRank-6], row{ -6 PRank,nil Stock,nil Quote}), IfNil((S adorn{key{Stock,PRank}})[Stock,PRank-5], row{ -5 PRank,nil Stock,nil Quote}), IfNil((S adorn{key{Stock,PRank}})[Stock,PRank-4], row{ -4 PRank,nil Stock,nil Quote}), IfNil((S adorn{key{Stock,PRank}})[Stock,PRank-3], row{ -3 PRank,nil Stock,nil Quote}), IfNil((S adorn{key{Stock,PRank}})[Stock,PRank-2], row{ -2 PRank,nil Stock,nil Quote}), IfNil((S adorn{key{Stock,PRank}})[Stock,PRank-1], row{ -1 PRank,nil Stock,nil Quote}), (S adorn{key{Stock,PRank}})[Stock,PRank] } ; select T1 union table { IfNil((S adorn{key{Stock,PRank}})[Stock,PRank+1],row{-99 PRank,nil Stock,nil Quote}) } where IsNotNil(Quote) order by {PRank desc}; Stock PRank Quote ----- ----- ----- IBM 7 219 IBM 6 171 IBM 5 155 IBM 4 171 IBM 3 125 IBM 2 157 IBM 1 107 In this way there would be as many inserts into the table as rows for the Stock. In other words we would be scanning the data just once for the nine rows vs. the multiple scans for the subquery (table). We can show the idea of accumulating sums incrementally. For example the running sum for row 6, adding the next row to the previously stored table and then getting the sum for row 7: var S:=SQLQuery('select Stock,QTime,Quote, row_number()over(Partition by Stock Order by QTime,Quote) as PRank from Stocks') {Stock,PRank,Quote} ; var Stock:='IBM'; var PRank:=6; var T1:= table { IfNil((S adorn{key{Stock,PRank}})[Stock,PRank-11],row{-11 PRank,nil Stock,nil Quote}), IfNil((S adorn{key{Stock,PRank}})[Stock,PRank-10],row{-10 PRank,nil Stock,nil Quote}), IfNil((S adorn{key{Stock,PRank}})[Stock,PRank-9], row{ -9 PRank,nil Stock,nil Quote}), IfNil((S adorn{key{Stock,PRank}})[Stock,PRank-8], row{ -8 PRank,nil Stock,nil Quote}), IfNil((S adorn{key{Stock,PRank}})[Stock,PRank-7], row{ -7 PRank,nil Stock,nil Quote}), IfNil((S adorn{key{Stock,PRank}})[Stock,PRank-6], row{ -6 PRank,nil Stock,nil Quote}), IfNil((S adorn{key{Stock,PRank}})[Stock,PRank-5], row{ -5 PRank,nil Stock,nil Quote}), IfNil((S adorn{key{Stock,PRank}})[Stock,PRank-4], row{ -4 PRank,nil Stock,nil Quote}), IfNil((S adorn{key{Stock,PRank}})[Stock,PRank-3], row{ -3 PRank,nil Stock,nil Quote}), IfNil((S adorn{key{Stock,PRank}})[Stock,PRank-2], row{ -2 PRank,nil Stock,nil Quote}), IfNil((S adorn{key{Stock,PRank}})[Stock,PRank-1], row{ -1 PRank,nil Stock,nil Quote}), (S adorn{key{Stock,PRank}})[Stock,PRank] } ; select table { row{'Accumulation of Sum for row 6' Desc,Sum(Quote from T1) SumQte}, row{'Accumulation of Sum for row 7', Sum(Quote from ( T1 union table { IfNil((S adorn{key{Stock,PRank}})[Stock,PRank+1],row{-99 PRank,nil Stock,nil Quote}) } where IsNotNil(Quote) )) } }; Desc SumQte ----------------------------- ------ Accumulation of Sum for row 6 886 Accumulation of Sum for row 7 1105 In the example operators D4WindowforForm (used on the before insert event) and D4WindowforFormUpdate (used on the before update event) represent the the idea of reusing prior information when appropriate by incrementally adding (and when appropriate deleting) rows from a work table that is used to obtain cumulative aggregates. View operator D4WindowforForm here. The Sql window The idea of accumulating running sums based on a new construct was introduced in the sql-99 standard: ISO/ANSI: Introduction to OLAP functions http://tinyurl.com/2taahc 'An <OLAP function> is defined using a window. A window may specify a partitioning, an ordering of rows within partitions, and an aggregation group. The aggregation group specifies which rows of a partition, relative to the current row, should participate in the calculation of an aggregate. Through aggregation groups, windows support such important OLAP capabilities as cumulative sums and moving averages. Windows may be specified either in the new WINDOW clause, or in-line in the SELECT list.' The running sums for Stock is expressed by: SELECT Stock,QTime,Quote, Sum(Quote) OVER (PARTITION BY Stock ORDER BY QTime,Quote 'ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW') AS SumQte FROM Stocks; where the window is: Sum(Quote) OVER (PARTITION BY Stock ORDER BY QTime,Quote 'ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW') AS SumQte The 'ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW' is called the aggregate group clause/statement. The definition of the terms is as follows: window-aggregation-group-clause The aggregation group of a row R is a set of rows, defined relative to R in the ordering of the rows of R's partition. This clause specifies the aggregation group. ROWS Indicates the aggregation group is defined by counting rows. group-BETWEEN Specifies the aggregation group start and end based on ROWS. UNBOUNDED PRECEDING Includes the entire partition preceding the current row. UNBOUNDED FOLLOWING Includes the entire partition following the current row. CURRENT ROW Specifies the start or end of the aggregation group as the current row. PRECEDING Specifies the number of rows preceding the current row.as a positive integer indicating a number of rows. FOLLOWING Specifies the number of rows following the current row. as a positive integer indicating a number of rows. The definition 'ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW' or its abbreviated form 'ROWS UNBOUNDED PRECEDING' means include all the rows in the partition prior to and including the current row in the computation of the sum. The sql-99 window does not introduce anything that couldn't be done previously. What it does is simplify the way it is done and most importantly make accumulation much more efficient. We can express the idea of the sql window in Sql Server with the following procedure: create procedure SqlWindow @From INT, @To INT as SELECT A.PRank,A.Stock,A.QTime,A.Quote, WCnt,SumQte,MinQte,MaxQte,AvgQte FROM (SELECT Stock,QTime,Quote, ROW_NUMBER() OVER(PARTITION BY Stock ORDER BY QTime) AS PRank FROM Stocks) AS A CROSS APPLY ( SELECT COUNT(*) AS WCnt,SUM(Quote) AS SumQte,MIN(Quote) AS MinQte, MAX(Quote) AS MaxQte,CAST(AVG(1.*Quote) AS DECIMAL(6,1)) AS AvgQte FROM (SELECT Stock,Quote,ROW_NUMBER() OVER(ORDER BY QTime) AS PRank FROM Stocks AS B WHERE B.Stock=A.Stock) AS C WHERE C.PRank BETWEEN A.PRank+@From AND A.PRank+@To ) AS D The @From and @To parameters are the bound of rows relative to the current row that the running/cumulative sum is obtained for. To obtain the running sums (and other aggregates) we can execute the procedure using an aggregate group definition that includes all prior rows in the sum: select SQLQuery ( 'Exec SqlWindow @From,@To', WList('ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW') {Wfrom From,Wto To} ); PRank Stock QTime Quote WCnt SumQte MinQte MaxQte AvgQte ----- -------- ---------------------- ----- ---- ------ ------ ------ ------ 1 IBM 4/3/2006 2:47:00 PM 107 1 107 107 107 107.0 2 IBM 7/16/2006 7:39:00 AM 157 2 264 107 157 132.0 3 IBM 8/26/2006 6:24:00 PM 125 3 389 107 157 129.7 4 IBM 9/8/2006 1:11:00 PM 171 4 560 107 171 140.0 5 IBM 11/22/2006 12:01:00 PM 155 5 715 107 171 143.0 6 IBM 2/2/2007 12:03:00 AM 171 6 886 107 171 147.7 7 IBM 4/13/2007 3:30:00 AM 219 7 1105 107 219 157.9 8 IBM 10/18/2007 12:12:00 AM 133 8 1238 107 219 154.8 9 IBM 12/25/2007 10:48:00 AM 139 9 1377 107 219 153.0 1 MS 3/17/2006 7:12:00 PM 257 1 257 257 257 257.0 . . The D4 operator WList parses the aggregate definition to obtain the @From and @To values. Executing the WList operator with the definition we obtain the bounds of the cumulative sum. WList returns a row with the From and To values: select WList('ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW'); Wfrom Wto ----------- --- -1000000000 0 The Wfrom value of -1000000000 simply represents a large negative number guaranteed to capture all prior rows. The value of 0 for Wto represents the current row. The specify construct '{Wfrom From,Wto To}' simply renames the columns of the row returned by operator WList to the names of the parameters expected by the sql procedure SqlWindow. If we wanted cumulative sums from the current row thru all rows available for a Stock we can use either of the following: select WList('ROWS UNBOUNDED FOLLOWING'); select WList('ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING'); Wfrom Wto ----- ---------- 0 1000000000 The Wfrom value of 0 is the current row and the Wto value of 1000000000 guarantees we will include all available 'following' rows in the partition for the cumulative sum. Executing the SqlWindow procedure: select SQLQuery ( 'Exec SqlWindow @From,@To', WList('ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING') {Wfrom From,Wto To} ); PRank Stock QTime Quote WCnt SumQte MinQte MaxQte AvgQte ----- -------- ---------------------- ----- ---- ------ ------ ------ ------ 1 IBM 4/3/2006 2:47:00 PM 107 9 1377 107 219 153.0 2 IBM 7/16/2006 7:39:00 AM 157 8 1270 125 219 158.8 3 IBM 8/26/2006 6:24:00 PM 125 7 1113 125 219 159.0 4 IBM 9/8/2006 1:11:00 PM 171 6 988 133 219 164.7 5 IBM 11/22/2006 12:01:00 PM 155 5 817 133 219 163.4 6 IBM 2/2/2007 12:03:00 AM 171 4 662 133 219 165.5 7 IBM 4/13/2007 3:30:00 AM 219 3 491 133 219 163.7 8 IBM 10/18/2007 12:12:00 AM 133 2 272 133 139 136.0 9 IBM 12/25/2007 10:48:00 AM 139 1 139 139 139 139.0 <- Last row 1 MS 3/17/2006 7:12:00 PM 257 11 2254 100 290 204.9 . . You can see that it is the opposite of 'ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW'. For the last row in the partition we are left with only the current row being used for the cumulative sum. Suppose we want sums for the current row, 2 prior rows and 1 following row. The aggregate group definition would be: select WList('ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING'); Wfrom Wto ----- --- -2 1 This definition therefore can use 4 rows, the 2 prior rows from the current row, the current row and the next row. select SQLQuery ( 'Exec SqlWindow @From,@To', WList('ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING') {Wfrom From,Wto To} ); PRank Stock QTime Quote WCnt SumQte MinQte MaxQte AvgQte ----- -------- ---------------------- ----- ---- ------ ------ ------ ------ 1 IBM 4/3/2006 2:47:00 PM 107 2 264 107 157 132.0 <- First row 2 IBM 7/16/2006 7:39:00 AM 157 3 389 107 157 129.7 3 IBM 8/26/2006 6:24:00 PM 125 4 560 107 171 140.0 4 IBM 9/8/2006 1:11:00 PM 171 4 608 125 171 152.0 5 IBM 11/22/2006 12:01:00 PM 155 4 622 125 171 155.5 6 IBM 2/2/2007 12:03:00 AM 171 4 716 155 219 179.0 7 IBM 4/13/2007 3:30:00 AM 219 4 678 133 219 169.5 8 IBM 10/18/2007 12:12:00 AM 133 4 662 133 219 165.5 9 IBM 12/25/2007 10:48:00 AM 139 3 491 133 219 163.7 1 MS 3/17/2006 7:12:00 PM 257 2 424 167 257 212.0 . . Note that not all rows have 4 values. For the 1st row (PRank=1) there are no prior rows, there is only the current row and the one following. Therefore the aggregates for the 1st row are based on cumulative values from only 2 rows. Note that a cumulative sum need not include the current row. We can base the accumulation on prior or following rows. For example we can use the prior rows between 4 and 2: select WList('ROWS BETWEEN 4 PRECEDING AND 2 PRECEDING') Wfrom Wto ----- --- -4 -2 select SQLQuery ( 'Exec SqlWindow @From,@To', WList('ROWS BETWEEN 4 PRECEDING AND 2 PRECEDING') {Wfrom From,Wto To} ); PRank Stock QTime Quote WCnt SumQte MinQte MaxQte AvgQte ----- -------- ---------------------- ----- ---- ---------- ---------- ---------- ---------- 1 IBM 4/3/2006 2:47:00 PM 107 0 <No Value> <No Value> <No Value> <No Value> 2 IBM 7/16/2006 7:39:00 AM 157 0 <No Value> <No Value> <No Value> <No Value> 3 IBM 8/26/2006 6:24:00 PM 125 1 107 107 107 107.0 <-First value 4 IBM 9/8/2006 1:11:00 PM 171 2 264 107 157 132.0 5 IBM 11/22/2006 12:01:00 PM 155 3 389 107 157 129.7 6 IBM 2/2/2007 12:03:00 AM 171 3 453 125 171 151.0 7 IBM 4/13/2007 3:30:00 AM 219 3 451 125 171 150.3 8 IBM 10/18/2007 12:12:00 AM 133 3 497 155 171 165.7 9 IBM 12/25/2007 10:48:00 AM 139 3 545 155 219 181.7 1 MS 3/17/2006 7:12:00 PM 257 0 <No Value> <No Value> <No Value> <No Value> 2 MS 5/9/2006 3:12:00 PM 167 0 <No Value> <No Value> <No Value> <No Value> 3 MS 6/24/2006 1:51:00 AM 277 1 257 257 257 257.0 . . It's not until the 3rd row of a partition that a row in included. We can lead or lag by using the same value from both From and To. For example to lead by 1 row we can use: select SQLQuery ( 'Exec SqlWindow @From,@To', WList('ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING') {Wfrom From,Wto To} ); PRank Stock QTime Quote WCnt SumQte MinQte MaxQte AvgQte ----- -------- ---------------------- ----- ---- ---------- ---------- ---------- ---------- 1 IBM 4/3/2006 2:47:00 PM 107 1 157 157 157 157.0 2 IBM 7/16/2006 7:39:00 AM 157 1 125 125 125 125.0 3 IBM 8/26/2006 6:24:00 PM 125 1 171 171 171 171.0 4 IBM 9/8/2006 1:11:00 PM 171 1 155 155 155 155.0 5 IBM 11/22/2006 12:01:00 PM 155 1 171 171 171 171.0 6 IBM 2/2/2007 12:03:00 AM 171 1 219 219 219 219.0 7 IBM 4/13/2007 3:30:00 AM 219 1 133 133 133 133.0 8 IBM 10/18/2007 12:12:00 AM 133 1 139 139 139 139.0 9 IBM 12/25/2007 10:48:00 AM 139 0 <No Value> <No Value> <No Value> <No Value> <-Last row 1 MS 3/17/2006 7:12:00 PM 257 1 167 167 167 167.0 2 MS 5/9/2006 3:12:00 PM 167 1 277 277 277 277.0 3 MS 6/24/2006 1:51:00 AM 277 1 250 250 250 250.0 4 MS 8/18/2006 6:46:00 PM 250 1 290 290 290 290.0 5 MS 1/16/2007 5:56:00 PM 290 1 177 177 177 177.0 6 MS 1/22/2007 1:28:00 AM 177 1 150 150 150 150.0 7 MS 5/30/2007 4:30:00 PM 150 1 263 263 263 263.0 8 MS 6/21/2007 1:54:00 PM 263 1 153 153 153 153.0 9 MS 7/17/2007 12:20:00 PM 153 1 170 170 170 170.0 10 MS 11/26/2007 1:45:00 AM 170 1 100 100 100 100.0 11 MS 12/25/2007 3:54:00 PM 100 0 <No Value> <No Value> <No Value> <No Value> <-Last row 1 MySQL_AB 2/8/2006 3:47:00 PM 21 1 25 25 25 25.0 . . The last row for each partition (Stock) has no data in the accumulation. Note that the lead precludes the use of previous information. Modeling the Sql Window The D4 operators that compute cumulative aggregates, (D4ApplyforForm, D4ApplyforFormUpdate, D4WindowforForm and D4WindowforFormUpdate) take the sql procedure SqlWindow as their logical model. The operators implement the logic of the procedure using cursors as a way to illustrate their use. As stated previously the D4Apply operators follow the sql procedure more faithfully and reflect the same inefficiency as the procedure. The D4Window operators follow the same basic logic but make use of previous rows. This interdependency reflects a much more efficient methodology and hence is more faithful to the idea of the efficient sql window.
Friday, July 13, 2007
Dataphor - An example of rapid application development
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment