Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Add support for "ROWS BETWEEN <expr> PRECEDING AND <expr> FOLLOWING" window frames. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | exp-window-functions |
| Files: | files | file ages | folders |
| SHA3-256: | 3a203660f1e4da3b8d2d605c494f4843 |
| User & Date: | dan 2018-05-23 20:55:37 |
Context
|
2018-05-24
| ||
| 17:49 | Support other frame types that use "<expr> PRECEDING" or "<expr> FOLLOWING" as start or end conditions. check-in: ec7b648c user: dan tags: exp-window-functions | |
|
2018-05-23
| ||
| 20:55 | Add support for "ROWS BETWEEN <expr> PRECEDING AND <expr> FOLLOWING" window frames. check-in: 3a203660 user: dan tags: exp-window-functions | |
|
2018-05-22
| ||
| 20:36 | Merge latest trunk changes into this branch. check-in: cdb68d2c user: dan tags: exp-window-functions | |
Changes
Changes to src/btree.c.
5171 5172 5173 5174 5175 5176 5177 5178 5179 5180 5181 5182 5183 5184 |
}else if( rc==SQLITE_EMPTY ){
assert( pCur->pgnoRoot==0 || pCur->pPage->nCell==0 );
*pRes = 1;
rc = SQLITE_OK;
}
return rc;
}
/* Move the cursor to the last entry in the table. Return SQLITE_OK
** on success. Set *pRes to 0 if the cursor actually points to something
** or set *pRes to 1 if the table is empty.
*/
int sqlite3BtreeLast(BtCursor *pCur, int *pRes){
int rc;
|
> > > > > > > |
5171 5172 5173 5174 5175 5176 5177 5178 5179 5180 5181 5182 5183 5184 5185 5186 5187 5188 5189 5190 5191 |
}else if( rc==SQLITE_EMPTY ){
assert( pCur->pgnoRoot==0 || pCur->pPage->nCell==0 );
*pRes = 1;
rc = SQLITE_OK;
}
return rc;
}
void sqlite3BtreeSkipNext(BtCursor *pCur){
if( pCur->eState==CURSOR_VALID ){
pCur->eState = CURSOR_SKIPNEXT;
pCur->skipNext = 1;
}
}
/* Move the cursor to the last entry in the table. Return SQLITE_OK
** on success. Set *pRes to 0 if the cursor actually points to something
** or set *pRes to 1 if the table is empty.
*/
int sqlite3BtreeLast(BtCursor *pCur, int *pRes){
int rc;
|
Changes to src/btree.h.
297 298 299 300 301 302 303 304 305 306 307 308 309 310 |
int nData; /* Size of pData. 0 if none. */
int nZero; /* Extra zero data appended after pData,nData */
};
int sqlite3BtreeInsert(BtCursor*, const BtreePayload *pPayload,
int flags, int seekResult);
int sqlite3BtreeFirst(BtCursor*, int *pRes);
int sqlite3BtreeLast(BtCursor*, int *pRes);
int sqlite3BtreeNext(BtCursor*, int flags);
int sqlite3BtreeEof(BtCursor*);
int sqlite3BtreePrevious(BtCursor*, int flags);
i64 sqlite3BtreeIntegerKey(BtCursor*);
#ifdef SQLITE_ENABLE_OFFSET_SQL_FUNC
i64 sqlite3BtreeOffset(BtCursor*);
|
> |
297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 |
int nData; /* Size of pData. 0 if none. */
int nZero; /* Extra zero data appended after pData,nData */
};
int sqlite3BtreeInsert(BtCursor*, const BtreePayload *pPayload,
int flags, int seekResult);
int sqlite3BtreeFirst(BtCursor*, int *pRes);
void sqlite3BtreeSkipNext(BtCursor*);
int sqlite3BtreeLast(BtCursor*, int *pRes);
int sqlite3BtreeNext(BtCursor*, int flags);
int sqlite3BtreeEof(BtCursor*);
int sqlite3BtreePrevious(BtCursor*, int flags);
i64 sqlite3BtreeIntegerKey(BtCursor*);
#ifdef SQLITE_ENABLE_OFFSET_SQL_FUNC
i64 sqlite3BtreeOffset(BtCursor*);
|
Changes to src/func.c.
1508
1509
1510
1511
1512
1513
1514
1515
1516
1517
1518
1519
1520
1521
....
1869
1870
1871
1872
1873
1874
1875
1876
1877
1878
1879
1880
1881
1882
1883
1884
1885
1886
1887
1888
|
p->overflow = 1;
}
}else{
p->rSum += sqlite3_value_double(argv[0]);
p->approx = 1;
}
}
}
static void sumFinalize(sqlite3_context *context){
SumCtx *p;
p = sqlite3_aggregate_context(context, 0);
if( p && p->cnt>0 ){
if( p->overflow ){
sqlite3_result_error(context,"integer overflow",-1);
................................................................................
VFUNCTION(last_insert_rowid, 0, 0, 0, last_insert_rowid),
VFUNCTION(changes, 0, 0, 0, changes ),
VFUNCTION(total_changes, 0, 0, 0, total_changes ),
FUNCTION(replace, 3, 0, 0, replaceFunc ),
FUNCTION(zeroblob, 1, 0, 0, zeroblobFunc ),
FUNCTION(substr, 2, 0, 0, substrFunc ),
FUNCTION(substr, 3, 0, 0, substrFunc ),
WAGGREGATE(sum, 1, 0, 0, sumStep, sumFinalize),
WAGGREGATE(total, 1, 0, 0, sumStep, totalFinalize ),
WAGGREGATE(avg, 1, 0, 0, sumStep, avgFinalize ),
AGGREGATE2(count, 0, 0, 0, countStep, countFinalize,
SQLITE_FUNC_COUNT ),
WAGGREGATE(count, 1, 0, 0, countStep, countFinalize ),
AGGREGATE(group_concat, 1, 0, 0, groupConcatStep, groupConcatFinalize,
groupConcatValue),
AGGREGATE(group_concat, 2, 0, 0, groupConcatStep, groupConcatFinalize,
groupConcatValue),
LIKEFUNC(glob, 2, &globInfo, SQLITE_FUNC_LIKE|SQLITE_FUNC_CASE),
#ifdef SQLITE_CASE_SENSITIVE_LIKE
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|
|
|
|
|
1508
1509
1510
1511
1512
1513
1514
1515
1516
1517
1518
1519
1520
1521
1522
1523
1524
1525
1526
1527
1528
1529
1530
1531
1532
1533
1534
1535
1536
1537
1538
1539
1540
1541
1542
....
1890
1891
1892
1893
1894
1895
1896
1897
1898
1899
1900
1901
1902
1903
1904
1905
1906
1907
1908
1909
|
p->overflow = 1;
}
}else{
p->rSum += sqlite3_value_double(argv[0]);
p->approx = 1;
}
}
}
static void sumInverse(sqlite3_context *context, int argc, sqlite3_value**argv){
SumCtx *p;
int type;
assert( argc==1 );
UNUSED_PARAMETER(argc);
p = sqlite3_aggregate_context(context, sizeof(*p));
type = sqlite3_value_numeric_type(argv[0]);
if( p && type!=SQLITE_NULL ){
p->cnt--;
if( type==SQLITE_INTEGER ){
i64 v = sqlite3_value_int64(argv[0]);
p->rSum -= v;
if( (p->approx|p->overflow)==0 && sqlite3AddInt64(&p->iSum, -1*v) ){
p->overflow = 1;
}
}else{
p->rSum += sqlite3_value_double(argv[0]);
p->approx = 1;
}
}
}
static void sumFinalize(sqlite3_context *context){
SumCtx *p;
p = sqlite3_aggregate_context(context, 0);
if( p && p->cnt>0 ){
if( p->overflow ){
sqlite3_result_error(context,"integer overflow",-1);
................................................................................
VFUNCTION(last_insert_rowid, 0, 0, 0, last_insert_rowid),
VFUNCTION(changes, 0, 0, 0, changes ),
VFUNCTION(total_changes, 0, 0, 0, total_changes ),
FUNCTION(replace, 3, 0, 0, replaceFunc ),
FUNCTION(zeroblob, 1, 0, 0, zeroblobFunc ),
FUNCTION(substr, 2, 0, 0, substrFunc ),
FUNCTION(substr, 3, 0, 0, substrFunc ),
WAGGREGATE(sum, 1, 0, 0, sumStep, sumInverse, sumFinalize),
WAGGREGATE(total, 1, 0, 0, sumStep, sumInverse, totalFinalize ),
WAGGREGATE(avg, 1, 0, 0, sumStep, sumInverse, avgFinalize ),
AGGREGATE2(count, 0, 0, 0, countStep, countFinalize,
SQLITE_FUNC_COUNT ),
WAGGREGATE(count, 1, 0, 0, countStep, 0, countFinalize ),
AGGREGATE(group_concat, 1, 0, 0, groupConcatStep, groupConcatFinalize,
groupConcatValue),
AGGREGATE(group_concat, 2, 0, 0, groupConcatStep, groupConcatFinalize,
groupConcatValue),
LIKEFUNC(glob, 2, &globInfo, SQLITE_FUNC_LIKE|SQLITE_FUNC_CASE),
#ifdef SQLITE_CASE_SENSITIVE_LIKE
|
Changes to src/select.c.
5572
5573
5574
5575
5576
5577
5578
5579
5580
5581
5582
5583
5584
5585
5586
....
6083
6084
6085
6086
6087
6088
6089
6090
6091
6092
6093
6094
6095
6096
6097
6098
6099
6100
6101
6102
6103
6104
6105
6106
|
#if SELECTTRACE_ENABLED
if( sqlite3SelectTrace & 0x108 ){
SELECTTRACE(0x104,pParse,p, ("after window rewrite:\n"));
sqlite3TreeViewSelect(0, p, 0);
}
#endif
sqlite3VdbeAddOp2(v, OP_OpenEphemeral, pMWin->iEphCsr, pMWin->nBufferCol);
}
return rc;
}
/*
** Generate code for the SELECT statement given in the p argument.
................................................................................
sqlite3VdbeChangeToNoop(v, sSort.addrSortIndex);
}
assert( p->pEList==pEList );
if( pWin ){
int addrGosub = sqlite3VdbeMakeLabel(v);
int regGosub = ++pParse->nMem;
int addr;
sqlite3WindowCodeStep(pParse, p, pWInfo, regGosub, addrGosub);
sqlite3VdbeAddOp0(v, OP_Goto);
sqlite3VdbeResolveLabel(v, addrGosub);
addr = sqlite3VdbeAddOp1(v, OP_Rewind, pWin->iEphCsr);
selectInnerLoop(pParse, p, -1, &sSort, &sDistinct, pDest, addr+1, 0);
sqlite3VdbeAddOp2(v, OP_Next, pWin->iEphCsr, addr+1);
sqlite3VdbeJumpHere(v, addr);
sqlite3VdbeAddOp1(v, OP_Return, regGosub);
sqlite3VdbeJumpHere(v, addr-1); /* OP_Goto jumps here */
}else{
/* Use the standard inner loop. */
selectInnerLoop(pParse, p, -1, &sSort, &sDistinct, pDest,
sqlite3WhereContinueLabel(pWInfo),
|
|
|
>
|
>
|
>
>
>
>
|
|
>
|
5572
5573
5574
5575
5576
5577
5578
5579
5580
5581
5582
5583
5584
5585
5586
....
6083
6084
6085
6086
6087
6088
6089
6090
6091
6092
6093
6094
6095
6096
6097
6098
6099
6100
6101
6102
6103
6104
6105
6106
6107
6108
6109
6110
6111
6112
6113
|
#if SELECTTRACE_ENABLED
if( sqlite3SelectTrace & 0x108 ){
SELECTTRACE(0x104,pParse,p, ("after window rewrite:\n"));
sqlite3TreeViewSelect(0, p, 0);
}
#endif
sqlite3VdbeAddOp2(v, OP_OpenEphemeral, pMWin->iEphCsr, pSublist->nExpr);
}
return rc;
}
/*
** Generate code for the SELECT statement given in the p argument.
................................................................................
sqlite3VdbeChangeToNoop(v, sSort.addrSortIndex);
}
assert( p->pEList==pEList );
if( pWin ){
int addrGosub = sqlite3VdbeMakeLabel(v);
int regGosub = ++pParse->nMem;
int addr = 0;
int bLoop = 0;
sqlite3WindowCodeStep(pParse, p, pWInfo, regGosub, addrGosub, &bLoop);
sqlite3VdbeAddOp0(v, OP_Goto);
sqlite3VdbeResolveLabel(v, addrGosub);
if( bLoop ){
addr = sqlite3VdbeAddOp1(v, OP_Rewind, pWin->iEphCsr);
}else{
addr = sqlite3VdbeCurrentAddr(v);
}
selectInnerLoop(pParse, p, -1, &sSort, &sDistinct, pDest, addr+1, 0);
if( bLoop ){
sqlite3VdbeAddOp2(v, OP_Next, pWin->iEphCsr, addr+1);
sqlite3VdbeJumpHere(v, addr);
}
sqlite3VdbeAddOp1(v, OP_Return, regGosub);
sqlite3VdbeJumpHere(v, addr-1); /* OP_Goto jumps here */
}else{
/* Use the standard inner loop. */
selectInnerLoop(pParse, p, -1, &sSort, &sDistinct, pDest,
sqlite3WhereContinueLabel(pWInfo),
|
Changes to src/sqliteInt.h.
1719
1720
1721
1722
1723
1724
1725
1726
1727
1728
1729
1730
1731
1732
1733
1734
1735
....
3492
3493
3494
3495
3496
3497
3498
3499
3500
3501
3502
3503
3504
3505
3506
|
#define AGGREGATE(zName, nArg, arg, nc, xStep, xFinal, xValue) \
{nArg, SQLITE_UTF8|(nc*SQLITE_FUNC_NEEDCOLL), \
SQLITE_INT_TO_PTR(arg), 0, xStep,xFinal,xValue,0,#zName, {0}}
#define AGGREGATE2(zName, nArg, arg, nc, xStep, xFinal, extraFlags) \
{nArg, SQLITE_UTF8|(nc*SQLITE_FUNC_NEEDCOLL)|extraFlags, \
SQLITE_INT_TO_PTR(arg), 0, xStep,xFinal,xFinal,0,#zName, {0}}
#define WAGGREGATE(zName, nArg, arg, nc, xStep, xFinal) \
{nArg, SQLITE_UTF8|(nc*SQLITE_FUNC_NEEDCOLL), \
SQLITE_INT_TO_PTR(arg), 0, xStep,xFinal,xFinal,0,#zName, {0}}
/*
** All current savepoints are stored in a linked list starting at
** sqlite3.pSavepoint. The first element in the list is the most recently
** opened savepoint. Savepoints are added to the list by the vdbe
** OP_Savepoint instruction.
*/
................................................................................
};
void sqlite3WindowDelete(sqlite3*, Window*);
Window *sqlite3WindowAlloc(Parse*, int, int, Expr*, int , Expr*);
void sqlite3WindowAttach(Parse*, Expr*, Window*);
int sqlite3WindowCompare(Parse*, Window*, Window*);
void sqlite3WindowCodeInit(Parse*, Window*);
void sqlite3WindowCodeStep(Parse*, Select*, WhereInfo*, int, int);
/*
** Assuming zIn points to the first byte of a UTF-8 character,
** advance zIn to point to the first byte of the next UTF-8 character.
*/
#define SQLITE_SKIP_UTF8(zIn) { \
if( (*(zIn++))>=0xc0 ){ \
|
|
|
|
|
1719
1720
1721
1722
1723
1724
1725
1726
1727
1728
1729
1730
1731
1732
1733
1734
1735
....
3492
3493
3494
3495
3496
3497
3498
3499
3500
3501
3502
3503
3504
3505
3506
|
#define AGGREGATE(zName, nArg, arg, nc, xStep, xFinal, xValue) \
{nArg, SQLITE_UTF8|(nc*SQLITE_FUNC_NEEDCOLL), \
SQLITE_INT_TO_PTR(arg), 0, xStep,xFinal,xValue,0,#zName, {0}}
#define AGGREGATE2(zName, nArg, arg, nc, xStep, xFinal, extraFlags) \
{nArg, SQLITE_UTF8|(nc*SQLITE_FUNC_NEEDCOLL)|extraFlags, \
SQLITE_INT_TO_PTR(arg), 0, xStep,xFinal,xFinal,0,#zName, {0}}
#define WAGGREGATE(zName, nArg, arg, nc, xStep, xInverse, xFinal) \
{nArg, SQLITE_UTF8|(nc*SQLITE_FUNC_NEEDCOLL), \
SQLITE_INT_TO_PTR(arg), 0, xStep,xFinal,xFinal,xInverse,#zName, {0}}
/*
** All current savepoints are stored in a linked list starting at
** sqlite3.pSavepoint. The first element in the list is the most recently
** opened savepoint. Savepoints are added to the list by the vdbe
** OP_Savepoint instruction.
*/
................................................................................
};
void sqlite3WindowDelete(sqlite3*, Window*);
Window *sqlite3WindowAlloc(Parse*, int, int, Expr*, int , Expr*);
void sqlite3WindowAttach(Parse*, Expr*, Window*);
int sqlite3WindowCompare(Parse*, Window*, Window*);
void sqlite3WindowCodeInit(Parse*, Window*);
void sqlite3WindowCodeStep(Parse*, Select*, WhereInfo*, int, int, int*);
/*
** Assuming zIn points to the first byte of a UTF-8 character,
** advance zIn to point to the first byte of the next UTF-8 character.
*/
#define SQLITE_SKIP_UTF8(zIn) { \
if( (*(zIn++))>=0xc0 ){ \
|
Changes to src/vdbe.c.
5014
5015
5016
5017
5018
5019
5020
5021
5022
5023
5024
5025
5026
5027
....
6269
6270
6271
6272
6273
6274
6275
6276
6277
6278
6279
6280
6281
6282
6283
|
if( isSorter(pC) ){
rc = sqlite3VdbeSorterRewind(pC, &res);
}else{
assert( pC->eCurType==CURTYPE_BTREE );
pCrsr = pC->uc.pCursor;
assert( pCrsr );
rc = sqlite3BtreeFirst(pCrsr, &res);
pC->deferredMoveto = 0;
pC->cacheStatus = CACHE_STALE;
}
if( rc ) goto abort_due_to_error;
pC->nullRow = (u8)res;
assert( pOp->p2>0 && pOp->p2<p->nOp );
VdbeBranchTaken(res!=0,2);
................................................................................
}
#endif
pMem->n++;
assert( pCtx->pOut->flags==MEM_Null );
assert( pCtx->isError==0 );
assert( pCtx->skipFlag==0 );
(pCtx->pFunc->xSFunc)(pCtx,pCtx->argc,pCtx->argv); /* IMP: R-24505-23230 */
if( pCtx->isError ){
if( pCtx->isError>0 ){
sqlite3VdbeError(p, "%s", sqlite3_value_text(pCtx->pOut));
rc = pCtx->isError;
}
if( pCtx->skipFlag ){
assert( pOp[-1].opcode==OP_CollSeq );
|
>
>
|
|
5014
5015
5016
5017
5018
5019
5020
5021
5022
5023
5024
5025
5026
5027
5028
....
6270
6271
6272
6273
6274
6275
6276
6277
6278
6279
6280
6281
6282
6283
6284
6285
|
if( isSorter(pC) ){
rc = sqlite3VdbeSorterRewind(pC, &res);
}else{
assert( pC->eCurType==CURTYPE_BTREE );
pCrsr = pC->uc.pCursor;
assert( pCrsr );
rc = sqlite3BtreeFirst(pCrsr, &res);
if( pOp->p5 ) sqlite3BtreeSkipNext(pCrsr);
pC->deferredMoveto = 0;
pC->cacheStatus = CACHE_STALE;
}
if( rc ) goto abort_due_to_error;
pC->nullRow = (u8)res;
assert( pOp->p2>0 && pOp->p2<p->nOp );
VdbeBranchTaken(res!=0,2);
................................................................................
}
#endif
pMem->n++;
assert( pCtx->pOut->flags==MEM_Null );
assert( pCtx->isError==0 );
assert( pCtx->skipFlag==0 );
(pOp->p1 ? (pCtx->pFunc->xInverse) : (pCtx->pFunc->xSFunc))
(pCtx,pCtx->argc,pCtx->argv); /* IMP: R-24505-23230 */
if( pCtx->isError ){
if( pCtx->isError>0 ){
sqlite3VdbeError(p, "%s", sqlite3_value_text(pCtx->pOut));
rc = pCtx->isError;
}
if( pCtx->skipFlag ){
assert( pOp[-1].opcode==OP_CollSeq );
|
Changes to src/window.c.
21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 .. 72 73 74 75 76 77 78 79 80 81 82 83 84 85 ... 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 |
sqlite3DbFree(db, p);
}
}
Window *sqlite3WindowAlloc(
Parse *pParse,
int eType,
int eEnd, Expr *pEnd,
int eStart, Expr *pStart
){
Window *pWin = (Window*)sqlite3DbMallocZero(pParse->db, sizeof(Window));
if( pWin ){
pWin->eType = eType;
pWin->eStart = eStart;
pWin->eEnd = eEnd;
................................................................................
nPart += (pWin->pOrderBy ? pWin->pOrderBy->nExpr : 0);
if( nPart ){
pWin->regPart = pParse->nMem+1;
pParse->nMem += nPart;
sqlite3VdbeAddOp3(v, OP_Null, 0, pWin->regPart, pWin->regPart+nPart-1);
}
}
/*
** RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
**
** ...
** if( new partition ){
** AggFinal (xFinalize)
................................................................................
**
*/
void sqlite3WindowCodeStep(
Parse *pParse,
Select *p,
WhereInfo *pWInfo,
int regGosub,
int addrGosub
){
Vdbe *v = sqlite3GetVdbe(pParse);
Window *pWin;
Window *pMWin = p->pWin;
int k;
int iSubCsr = p->pSrc->a[0].iCursor;
int nSub = p->pSrc->a[0].pTab->nCol;
int reg = pParse->nMem+1;
int regRecord = reg+nSub;
int regRowid = regRecord+1;
int addr;
pParse->nMem += nSub + 2;
/* Martial the row returned by the sub-select into an array of
** registers. */
for(k=0; k<nSub; k++){
sqlite3VdbeAddOp3(v, OP_Column, iSubCsr, k, reg+k);
}
/* Check if this is the start of a new partition or peer group. */
if( pMWin->regPart ){
ExprList *pPart = pMWin->pPartition;
int nPart = (pPart ? pPart->nExpr : 0);
ExprList *pOrderBy = pMWin->pOrderBy;
int nPeer = (pOrderBy ? pOrderBy->nExpr : 0);
int addrGoto = 0;
int addrJump = 0;
if( pPart ){
int regNewPart = reg + pMWin->nBufferCol;
KeyInfo *pKeyInfo = sqlite3KeyInfoFromExprList(pParse, pPart, 0, 0);
addr = sqlite3VdbeAddOp3(v, OP_Compare, regNewPart, pMWin->regPart,nPart);
sqlite3VdbeAppendP4(v, (void*)pKeyInfo, P4_KEYINFO);
addrJump = sqlite3VdbeAddOp3(v, OP_Jump, addr+2, 0, addr+2);
for(pWin=pMWin; pWin; pWin=pWin->pNextWin){
sqlite3VdbeAddOp2(v, OP_AggFinal, pWin->regAccum, pWin->nArg);
sqlite3VdbeAppendP4(v, pWin->pFunc, P4_FUNCDEF);
sqlite3VdbeAddOp2(v, OP_Copy, pWin->regAccum, pWin->regResult);
}
if( pOrderBy ){
addrGoto = sqlite3VdbeAddOp0(v, OP_Goto);
}
}
if( pOrderBy ){
int regNewPeer = reg + pMWin->nBufferCol + nPart;
int regPeer = pMWin->regPart + nPart;
KeyInfo *pKeyInfo = sqlite3KeyInfoFromExprList(pParse, pOrderBy, 0, 0);
if( addrJump ) sqlite3VdbeJumpHere(v, addrJump);
addr = sqlite3VdbeAddOp3(v, OP_Compare, regNewPeer, regPeer, nPeer);
sqlite3VdbeAppendP4(v, (void*)pKeyInfo, P4_KEYINFO);
addrJump = sqlite3VdbeAddOp3(v, OP_Jump, addr+2, 0, addr+2);
for(pWin=pMWin; pWin; pWin=pWin->pNextWin){
sqlite3VdbeAddOp3(v,
OP_AggFinal, pWin->regAccum, pWin->nArg, pWin->regResult
);
sqlite3VdbeAppendP4(v, pWin->pFunc, P4_FUNCDEF);
}
if( addrGoto ) sqlite3VdbeJumpHere(v, addrGoto);
}
sqlite3VdbeAddOp2(v, OP_Gosub, regGosub, addrGosub);
sqlite3VdbeAddOp1(v, OP_ResetSorter, pMWin->iEphCsr);
sqlite3VdbeAddOp3(
v, OP_Copy, reg+pMWin->nBufferCol, pMWin->regPart, nPart+nPeer-1
);
sqlite3VdbeJumpHere(v, addrJump);
}
/* Invoke step function for window functions */
for(pWin=pMWin; pWin; pWin=pWin->pNextWin){
sqlite3VdbeAddOp3(v, OP_AggStep0, 0, reg+pWin->iArgCol, pWin->regAccum);
sqlite3VdbeAppendP4(v, pWin->pFunc, P4_FUNCDEF);
sqlite3VdbeChangeP5(v, (u8)pWin->nArg);
}
/* Buffer the current row in the ephemeral table. */
if( pMWin->nBufferCol>0 ){
sqlite3VdbeAddOp3(v, OP_MakeRecord, reg, pMWin->nBufferCol, regRecord);
}else{
sqlite3VdbeAddOp2(v, OP_Blob, 0, regRecord);
sqlite3VdbeAppendP4(v, (void*)"", 0);
}
sqlite3VdbeAddOp2(v, OP_NewRowid, pMWin->iEphCsr, regRowid);
sqlite3VdbeAddOp3(v, OP_Insert, pMWin->iEphCsr, regRecord, regRowid);
/* End the database scan loop. */
sqlite3WhereEnd(pWInfo);
for(pWin=pMWin; pWin; pWin=pWin->pNextWin){
sqlite3VdbeAddOp2(v, OP_AggFinal, pWin->regAccum, pWin->nArg);
sqlite3VdbeAppendP4(v, pWin->pFunc, P4_FUNCDEF);
sqlite3VdbeAddOp2(v, OP_Copy, pWin->regAccum, pWin->regResult);
}
sqlite3VdbeAddOp2(v, OP_Gosub, regGosub, addrGosub);
}
|
< | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | > < < < < < < < < < < < < < < < < < < | < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < > > | < > > > | < | > > | < < < < < < | < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < |
21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 .. 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 ... 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 |
sqlite3DbFree(db, p);
}
}
Window *sqlite3WindowAlloc(
Parse *pParse,
int eType,
int eStart, Expr *pStart,
int eEnd, Expr *pEnd
){
Window *pWin = (Window*)sqlite3DbMallocZero(pParse->db, sizeof(Window));
if( pWin ){
pWin->eType = eType;
pWin->eStart = eStart;
pWin->eEnd = eEnd;
................................................................................
nPart += (pWin->pOrderBy ? pWin->pOrderBy->nExpr : 0);
if( nPart ){
pWin->regPart = pParse->nMem+1;
pParse->nMem += nPart;
sqlite3VdbeAddOp3(v, OP_Null, 0, pWin->regPart, pWin->regPart+nPart-1);
}
}
static void windowCheckFrameValue(Parse *pParse, int reg, int bEnd){
static const char *azErr[] = {
"frame starting offset must be a non-negative integer",
"frame ending offset must be a non-negative integer"
};
Vdbe *v = sqlite3GetVdbe(pParse);
int regZero = ++pParse->nMem;
sqlite3VdbeAddOp2(v, OP_Integer, 0, regZero);
sqlite3VdbeAddOp2(v, OP_MustBeInt, reg, sqlite3VdbeCurrentAddr(v)+2);
sqlite3VdbeAddOp3(v, OP_Ge, regZero, sqlite3VdbeCurrentAddr(v)+2, reg);
sqlite3VdbeAddOp2(v, OP_Halt, SQLITE_ERROR, OE_Abort);
sqlite3VdbeAppendP4(v, (void*)azErr[bEnd], P4_STATIC);
}
static void windowCodeRowExprStep(
Parse *pParse,
Select *p,
WhereInfo *pWInfo,
int regGosub,
int addrGosub
){
Window *pMWin = p->pWin;
Vdbe *v = sqlite3GetVdbe(pParse);
Window *pWin;
int k;
int iSubCsr = p->pSrc->a[0].iCursor;
int nSub = p->pSrc->a[0].pTab->nCol;
int regFlushPart; /* Register for "Gosub flush_partition" */
int addrFlushPart; /* Label for "Gosub flush_partition" */
int addrDone; /* Label for "Gosub flush_partition_done" */
int reg = pParse->nMem+1;
int regRecord = reg+nSub;
int regRowid = regRecord+1;
int addr;
int csrPrec = pParse->nTab++;
int csrFollow = pParse->nTab++;
int regPrec; /* Value of <expr> PRECEDING */
int regFollow; /* Value of <expr> FOLLOWING */
int addrNext;
int addrGoto;
int addrIfPos1;
int addrIfPos2;
pParse->nMem += nSub + 2;
/* Allocate register and label for the "flush_partition" sub-routine. */
regFlushPart = ++pParse->nMem;
addrFlushPart = sqlite3VdbeMakeLabel(v);
addrDone = sqlite3VdbeMakeLabel(v);
regPrec = ++pParse->nMem;
regFollow = ++pParse->nMem;
/* Martial the row returned by the sub-select into an array of
** registers. */
for(k=0; k<nSub; k++){
sqlite3VdbeAddOp3(v, OP_Column, iSubCsr, k, reg+k);
}
sqlite3VdbeAddOp3(v, OP_MakeRecord, reg, nSub, regRecord);
/* Check if this is the start of a new partition. If so, call the
** flush_partition sub-routine. */
if( pMWin->pPartition ){
ExprList *pPart = pMWin->pPartition;
int nPart = (pPart ? pPart->nExpr : 0);
int addrJump = 0;
int regNewPart = reg + pMWin->nBufferCol;
KeyInfo *pKeyInfo = sqlite3KeyInfoFromExprList(pParse, pPart, 0, 0);
addr = sqlite3VdbeAddOp3(v, OP_Compare, regNewPart, pMWin->regPart,nPart);
sqlite3VdbeAppendP4(v, (void*)pKeyInfo, P4_KEYINFO);
addrJump = sqlite3VdbeAddOp3(v, OP_Jump, addr+2, addr+4, addr+2);
sqlite3VdbeAddOp2(v, OP_Gosub, regFlushPart, addrFlushPart);
sqlite3VdbeAddOp3(v, OP_Copy, regNewPart, pMWin->regPart, nPart);
}
/* Buffer the current row in the ephemeral table. */
sqlite3VdbeAddOp2(v, OP_NewRowid, pMWin->iEphCsr, regRowid);
sqlite3VdbeAddOp3(v, OP_Insert, pMWin->iEphCsr, regRecord, regRowid);
/* End of the input loop */
sqlite3WhereEnd(pWInfo);
/* Invoke "flush_partition" to deal with the final (or only) partition */
sqlite3VdbeAddOp2(v, OP_Gosub, regFlushPart, addrFlushPart);
addrGoto = sqlite3VdbeAddOp0(v, OP_Goto);
/* flush_partition: */
sqlite3VdbeResolveLabel(v, addrFlushPart);
sqlite3VdbeAddOp2(v, OP_Once, 0, sqlite3VdbeCurrentAddr(v)+3);
sqlite3VdbeAddOp2(v, OP_OpenDup, csrPrec, pMWin->iEphCsr);
sqlite3VdbeAddOp2(v, OP_OpenDup, csrFollow, pMWin->iEphCsr);
sqlite3ExprCode(pParse, pMWin->pStart, regPrec);
sqlite3ExprCode(pParse, pMWin->pEnd, regFollow);
sqlite3VdbeAddOp2(v, OP_Null, 0, pMWin->regResult);
sqlite3VdbeAddOp2(v, OP_Null, 0, pMWin->regAccum);
/* If either regPrec or regFollow are not non-negative integers, throw an
** exception. */
windowCheckFrameValue(pParse, regPrec, 0);
windowCheckFrameValue(pParse, regFollow, 1);
sqlite3VdbeAddOp2(v, OP_Rewind, pMWin->iEphCsr, addrDone);
sqlite3VdbeAddOp2(v, OP_Rewind, csrPrec, addrDone);
sqlite3VdbeChangeP5(v, 1);
sqlite3VdbeAddOp2(v, OP_Rewind, csrFollow, addrDone);
sqlite3VdbeChangeP5(v, 1);
/* Invoke AggStep function for each window function using the row that
** csrFollow currently points to. Or, if csrFollow is already at EOF,
** do nothing. */
addrNext = sqlite3VdbeCurrentAddr(v);
sqlite3VdbeAddOp2(v, OP_Next, csrFollow, addrNext+2);
sqlite3VdbeAddOp0(v, OP_Goto);
for(pWin=pMWin; pWin; pWin=pWin->pNextWin){
int i;
for(i=0; i<pWin->nArg; i++){
sqlite3VdbeAddOp3(v, OP_Column, csrFollow, pWin->iArgCol+i, reg+i);
}
sqlite3VdbeAddOp3(v, OP_AggStep0, 0, reg, pWin->regAccum);
sqlite3VdbeAppendP4(v, pWin->pFunc, P4_FUNCDEF);
sqlite3VdbeChangeP5(v, (u8)pWin->nArg);
}
sqlite3VdbeJumpHere(v, addrNext+1);
addrIfPos1 = sqlite3VdbeAddOp3(v, OP_IfPos, regFollow, 0 , 1);
for(pWin=pMWin; pWin; pWin=pWin->pNextWin){
sqlite3VdbeAddOp3(v,
OP_AggFinal, pWin->regAccum, pWin->nArg, pWin->regResult
);
sqlite3VdbeAppendP4(v, pWin->pFunc, P4_FUNCDEF);
}
sqlite3VdbeAddOp2(v, OP_Gosub, regGosub, addrGosub);
sqlite3VdbeAddOp2(v, OP_Next, pMWin->iEphCsr, sqlite3VdbeCurrentAddr(v)+2);
sqlite3VdbeAddOp2(v, OP_Goto, 0, addrDone);
addrIfPos2 = sqlite3VdbeAddOp3(v, OP_IfPos, regPrec, 0 , 1);
sqlite3VdbeAddOp2(v, OP_Next, csrPrec, sqlite3VdbeCurrentAddr(v)+1);
for(pWin=pMWin; pWin; pWin=pWin->pNextWin){
int i;
for(i=0; i<pWin->nArg; i++){
sqlite3VdbeAddOp3(v, OP_Column, csrPrec, pWin->iArgCol+i, reg+i);
}
sqlite3VdbeAddOp3(v, OP_AggStep0, 1, reg, pWin->regAccum);
sqlite3VdbeAppendP4(v, pWin->pFunc, P4_FUNCDEF);
sqlite3VdbeChangeP5(v, (u8)pWin->nArg);
}
sqlite3VdbeJumpHere(v, addrIfPos2);
sqlite3VdbeJumpHere(v, addrIfPos1);
sqlite3VdbeAddOp2(v, OP_Goto, 0, addrNext);
/* flush_partition_done: */
sqlite3VdbeResolveLabel(v, addrDone);
sqlite3VdbeAddOp1(v, OP_ResetSorter, pMWin->iEphCsr);
sqlite3VdbeAddOp1(v, OP_Return, regFlushPart);
/* Jump to here to skip over flush_partition */
sqlite3VdbeJumpHere(v, addrGoto);
}
static void windowCodeDefaultStep(
Parse *pParse,
Select *p,
WhereInfo *pWInfo,
int regGosub,
int addrGosub
){
Window *pMWin = p->pWin;
Vdbe *v = sqlite3GetVdbe(pParse);
Window *pWin;
int k;
int iSubCsr = p->pSrc->a[0].iCursor;
int nSub = p->pSrc->a[0].pTab->nCol;
int reg = pParse->nMem+1;
int regRecord = reg+nSub;
int regRowid = regRecord+1;
int addr;
pParse->nMem += nSub + 2;
/* Martial the row returned by the sub-select into an array of
** registers. */
for(k=0; k<nSub; k++){
sqlite3VdbeAddOp3(v, OP_Column, iSubCsr, k, reg+k);
}
/* Check if this is the start of a new partition or peer group. */
if( pMWin->regPart ){
ExprList *pPart = pMWin->pPartition;
int nPart = (pPart ? pPart->nExpr : 0);
ExprList *pOrderBy = pMWin->pOrderBy;
int nPeer = (pOrderBy ? pOrderBy->nExpr : 0);
int addrGoto = 0;
int addrJump = 0;
if( pPart ){
int regNewPart = reg + pMWin->nBufferCol;
KeyInfo *pKeyInfo = sqlite3KeyInfoFromExprList(pParse, pPart, 0, 0);
addr = sqlite3VdbeAddOp3(v, OP_Compare, regNewPart, pMWin->regPart,nPart);
sqlite3VdbeAppendP4(v, (void*)pKeyInfo, P4_KEYINFO);
addrJump = sqlite3VdbeAddOp3(v, OP_Jump, addr+2, 0, addr+2);
for(pWin=pMWin; pWin; pWin=pWin->pNextWin){
sqlite3VdbeAddOp2(v, OP_AggFinal, pWin->regAccum, pWin->nArg);
sqlite3VdbeAppendP4(v, pWin->pFunc, P4_FUNCDEF);
sqlite3VdbeAddOp2(v, OP_Copy, pWin->regAccum, pWin->regResult);
}
if( pOrderBy ){
addrGoto = sqlite3VdbeAddOp0(v, OP_Goto);
}
}
if( pOrderBy ){
int regNewPeer = reg + pMWin->nBufferCol + nPart;
int regPeer = pMWin->regPart + nPart;
KeyInfo *pKeyInfo = sqlite3KeyInfoFromExprList(pParse, pOrderBy, 0, 0);
if( addrJump ) sqlite3VdbeJumpHere(v, addrJump);
addr = sqlite3VdbeAddOp3(v, OP_Compare, regNewPeer, regPeer, nPeer);
sqlite3VdbeAppendP4(v, (void*)pKeyInfo, P4_KEYINFO);
addrJump = sqlite3VdbeAddOp3(v, OP_Jump, addr+2, 0, addr+2);
for(pWin=pMWin; pWin; pWin=pWin->pNextWin){
sqlite3VdbeAddOp3(v,
OP_AggFinal, pWin->regAccum, pWin->nArg, pWin->regResult
);
sqlite3VdbeAppendP4(v, pWin->pFunc, P4_FUNCDEF);
}
if( addrGoto ) sqlite3VdbeJumpHere(v, addrGoto);
}
sqlite3VdbeAddOp2(v, OP_Gosub, regGosub, addrGosub);
sqlite3VdbeAddOp1(v, OP_ResetSorter, pMWin->iEphCsr);
sqlite3VdbeAddOp3(
v, OP_Copy, reg+pMWin->nBufferCol, pMWin->regPart, nPart+nPeer-1
);
sqlite3VdbeJumpHere(v, addrJump);
}
/* Invoke step function for window functions */
for(pWin=pMWin; pWin; pWin=pWin->pNextWin){
sqlite3VdbeAddOp3(v, OP_AggStep0, 0, reg+pWin->iArgCol, pWin->regAccum);
sqlite3VdbeAppendP4(v, pWin->pFunc, P4_FUNCDEF);
sqlite3VdbeChangeP5(v, (u8)pWin->nArg);
}
/* Buffer the current row in the ephemeral table. */
if( pMWin->nBufferCol>0 ){
sqlite3VdbeAddOp3(v, OP_MakeRecord, reg, pMWin->nBufferCol, regRecord);
}else{
sqlite3VdbeAddOp2(v, OP_Blob, 0, regRecord);
sqlite3VdbeAppendP4(v, (void*)"", 0);
}
sqlite3VdbeAddOp2(v, OP_NewRowid, pMWin->iEphCsr, regRowid);
sqlite3VdbeAddOp3(v, OP_Insert, pMWin->iEphCsr, regRecord, regRowid);
/* End the database scan loop. */
sqlite3WhereEnd(pWInfo);
for(pWin=pMWin; pWin; pWin=pWin->pNextWin){
sqlite3VdbeAddOp2(v, OP_AggFinal, pWin->regAccum, pWin->nArg);
sqlite3VdbeAppendP4(v, pWin->pFunc, P4_FUNCDEF);
sqlite3VdbeAddOp2(v, OP_Copy, pWin->regAccum, pWin->regResult);
}
sqlite3VdbeAddOp2(v, OP_Gosub, regGosub, addrGosub);
}
/*
** RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
**
** ...
** if( new partition ){
** AggFinal (xFinalize)
................................................................................
**
*/
void sqlite3WindowCodeStep(
Parse *pParse,
Select *p,
WhereInfo *pWInfo,
int regGosub,
int addrGosub,
int *pbLoop
){
Window *pMWin = p->pWin;
if( pMWin->eType==TK_ROWS
&& pMWin->eStart==TK_PRECEDING
&& pMWin->eEnd==TK_FOLLOWING
){
*pbLoop = 0;
windowCodeRowExprStep(pParse, p, pWInfo, regGosub, addrGosub);
return;
}
*pbLoop = 1;
windowCodeDefaultStep(pParse, p, pWInfo, regGosub, addrGosub);
}
|
Changes to test/window2.tcl.
132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 |
SELECT sum(d) OVER () FROM t1;
}
execsql_test 1.3 {
SELECT sum(d) OVER (PARTITION BY b) FROM t1;
}
puts $::fd finish_test
==========
execsql_test 2.1 {
SELECT a, sum(d) OVER (
PARTITION BY b ORDER BY d
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
) FROM t1
}
execsql_test 2.2 {
SELECT a, sum(d) OVER (
ORDER BY b
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
) FROM t1
}
execsql_test 2.3 {
SELECT a, sum(d) OVER (
ORDER BY d
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) FROM t1
}
execsql_test 2.4 {
SELECT a, sum(d) OVER (
ORDER BY d
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
) FROM t1
}
execsql_test 2.5 {
SELECT a, sum(d) OVER (
ORDER BY d
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING
) FROM t1
}
finish_test
|
< < | < > < | < > < | < > > > > > | > | > | | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > |
132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 |
SELECT sum(d) OVER () FROM t1;
}
execsql_test 1.3 {
SELECT sum(d) OVER (PARTITION BY b) FROM t1;
}
==========
execsql_test 2.1 {
SELECT a, sum(d) OVER (
ORDER BY d
ROWS BETWEEN 1000 PRECEDING AND 1 FOLLOWING
) FROM t1
}
execsql_test 2.2 {
SELECT a, sum(d) OVER (
ORDER BY d
ROWS BETWEEN 1000 PRECEDING AND 1000 FOLLOWING
) FROM t1
}
execsql_test 2.3 {
SELECT a, sum(d) OVER (
ORDER BY d
ROWS BETWEEN 1 PRECEDING AND 1000 FOLLOWING
) FROM t1
}
execsql_test 2.4 {
SELECT a, sum(d) OVER (
ORDER BY d
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
) FROM t1
}
execsql_test 2.5 {
SELECT a, sum(d) OVER (
ORDER BY d
ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING
) FROM t1
}
execsql_test 2.6 {
SELECT a, sum(d) OVER (
PARTITION BY b
ORDER BY d
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
) FROM t1
}
execsql_test 2.7 {
SELECT a, sum(d) OVER (
PARTITION BY b
ORDER BY d
ROWS BETWEEN 0 PRECEDING AND 0 FOLLOWING
) FROM t1
}
puts $::fd finish_test
==========
execsql_test 3.1 {
SELECT a, sum(d) OVER (
PARTITION BY b ORDER BY d
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
) FROM t1
}
execsql_test 3.2 {
SELECT a, sum(d) OVER (
ORDER BY b
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
) FROM t1
}
execsql_test 3.3 {
SELECT a, sum(d) OVER (
ORDER BY d
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) FROM t1
}
finish_test
|
Changes to test/window2.test.
37 38 39 40 41 42 43 44 45 46 47 48 49 50 |
do_execsql_test 1.2 {
SELECT sum(d) OVER () FROM t1;
} {21 21 21 21 21 21}
do_execsql_test 1.3 {
SELECT sum(d) OVER (PARTITION BY b) FROM t1;
} {12 12 12 9 9 9}
finish_test
#==========================================================================
do_execsql_test 2.1 {
SELECT a, sum(d) OVER (
PARTITION BY b ORDER BY d
|
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > |
37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 |
do_execsql_test 1.2 {
SELECT sum(d) OVER () FROM t1;
} {21 21 21 21 21 21}
do_execsql_test 1.3 {
SELECT sum(d) OVER (PARTITION BY b) FROM t1;
} {12 12 12 9 9 9}
#==========================================================================
do_execsql_test 2.1 {
SELECT a, sum(d) OVER (
ORDER BY d
ROWS BETWEEN 1000 PRECEDING AND 1 FOLLOWING
) FROM t1
} {1 3 2 6 3 10 4 15 5 21 6 21}
do_execsql_test 2.2 {
SELECT a, sum(d) OVER (
ORDER BY d
ROWS BETWEEN 1000 PRECEDING AND 1000 FOLLOWING
) FROM t1
} {1 21 2 21 3 21 4 21 5 21 6 21}
do_execsql_test 2.3 {
SELECT a, sum(d) OVER (
ORDER BY d
ROWS BETWEEN 1 PRECEDING AND 1000 FOLLOWING
) FROM t1
} {1 21 2 21 3 20 4 18 5 15 6 11}
do_execsql_test 2.4 {
SELECT a, sum(d) OVER (
ORDER BY d
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
) FROM t1
} {1 3 2 6 3 9 4 12 5 15 6 11}
do_execsql_test 2.5 {
SELECT a, sum(d) OVER (
ORDER BY d
ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING
) FROM t1
} {1 1 2 3 3 5 4 7 5 9 6 11}
do_execsql_test 2.6 {
SELECT a, sum(d) OVER (
PARTITION BY b
ORDER BY d
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
) FROM t1
} {2 6 4 12 6 10 1 4 3 9 5 8}
do_execsql_test 2.7 {
SELECT a, sum(d) OVER (
PARTITION BY b
ORDER BY d
ROWS BETWEEN 0 PRECEDING AND 0 FOLLOWING
) FROM t1
} {2 2 4 4 6 6 1 1 3 3 5 5}
finish_test
#==========================================================================
do_execsql_test 2.1 {
SELECT a, sum(d) OVER (
PARTITION BY b ORDER BY d
|