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.
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.
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.