SQLite

Check-in [b84fbf16ea]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Fix a problem with handling single row partitions in the percent_rank() window function.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | exp-window-functions
Files: files | file ages | folders
SHA3-256: b84fbf16eac718c151731e2b2dcc73f2f2a144e3670f8566a30793f1e4e6a3ec
User & Date: dan 2018-06-21 19:20:39.296
Context
2018-06-22
17:57
Merge latest trunk changes. (check-in: ebe65b2386 user: dan tags: exp-window-functions)
2018-06-21
19:20
Fix a problem with handling single row partitions in the percent_rank() window function. (check-in: b84fbf16ea user: dan tags: exp-window-functions)
2018-06-20
09:23
Add tests to improve coverage of code in window.c. Fix a problem with "SELECT row_number() OVER ()". (check-in: f41b6b7317 user: dan tags: exp-window-functions)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/window.c.
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
  struct CallCount *p;
  p = (struct CallCount*)sqlite3_aggregate_context(pCtx, sizeof(*p));
  if( p ){
    if( p->nTotal>1 ){
      double r = (double)(p->nValue-1) / (double)(p->nTotal-1);
      sqlite3_result_double(pCtx, r);
    }else{
      sqlite3_result_double(pCtx, 100.0);
    }
    p->nValue = 0;
  }
}

/*
** Implementation of built-in window function cume_dist(). Assumes that







|







275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
  struct CallCount *p;
  p = (struct CallCount*)sqlite3_aggregate_context(pCtx, sizeof(*p));
  if( p ){
    if( p->nTotal>1 ){
      double r = (double)(p->nValue-1) / (double)(p->nTotal-1);
      sqlite3_result_double(pCtx, r);
    }else{
      sqlite3_result_double(pCtx, 0.0);
    }
    p->nValue = 0;
  }
}

/*
** Implementation of built-in window function cume_dist(). Assumes that
Changes to test/window4.tcl.
300
301
302
303
304
305
306


















307
308
309
           lead(d) OVER win,
           lag(d) OVER win,
           max(d) OVER win,
           min(d) OVER win
    FROM t1
    WINDOW win AS (ORDER BY a)
}



















finish_test








>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>



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
           lead(d) OVER win,
           lag(d) OVER win,
           max(d) OVER win,
           min(d) OVER win
    FROM t1
    WINDOW win AS (ORDER BY a)
}

==========

execsql_test 9.0 {
  DROP TABLE IF EXISTS t2;
  CREATE TABLE t2(x INTEGER);
  INSERT INTO t2 VALUES(1), (1), (1), (4), (4), (6), (7);
}

execsql_test 9.1 {
  SELECT rank() OVER () FROM t2
}
execsql_test 9.2 {
  SELECT dense_rank() OVER (PARTITION BY x) FROM t2
}
execsql_float_test 9.3 {
  SELECT x, percent_rank() OVER (PARTITION BY x ORDER BY x) FROM t2
}

finish_test

Changes to test/window4.test.
1195
1196
1197
1198
1199
1200
1201
























1202
1203
           lead(d) OVER win,
           lag(d) OVER win,
           max(d) OVER win,
           min(d) OVER win
    FROM t1
    WINDOW win AS (ORDER BY a)
} {1 1 1 1 4 4 {} 8 {} 4 4   2 2 2 1 4 8 8 12 4 8 4   3 3 3 2 4 12 8 {} 8 12 4}

























finish_test







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>


1195
1196
1197
1198
1199
1200
1201
1202
1203
1204
1205
1206
1207
1208
1209
1210
1211
1212
1213
1214
1215
1216
1217
1218
1219
1220
1221
1222
1223
1224
1225
1226
1227
           lead(d) OVER win,
           lag(d) OVER win,
           max(d) OVER win,
           min(d) OVER win
    FROM t1
    WINDOW win AS (ORDER BY a)
} {1 1 1 1 4 4 {} 8 {} 4 4   2 2 2 1 4 8 8 12 4 8 4   3 3 3 2 4 12 8 {} 8 12 4}

#==========================================================================

do_execsql_test 9.0 {
  DROP TABLE IF EXISTS t2;
  CREATE TABLE t2(x INTEGER);
  INSERT INTO t2 VALUES(1), (1), (1), (4), (4), (6), (7);
} {}

do_execsql_test 9.1 {
  SELECT rank() OVER () FROM t2
} {1   1   1   1   1   1   1}

do_execsql_test 9.2 {
  SELECT dense_rank() OVER (PARTITION BY x) FROM t2
} {1   1   1   1   1   1   1}

do_test 9.3 {
  set myres {}
  foreach r [db eval {SELECT x, percent_rank() OVER (PARTITION BY x ORDER BY x) FROM t2}] {
    lappend myres [format %.2f [set r]]
  }
  set myres
} {1.00 0.00 1.00 0.00 1.00 0.00 4.00 0.00 4.00 0.00 6.00 0.00 7.00 0.00}

finish_test