AryaWu/sqlite
0
1# 2013-05-282#3# The author disclaims copyright to this source code. In place of4# a legal notice, here is a blessing:5#6# May you do good and not evil.7# May you find forgiveness for yourself and forgive others.8# May you share freely, never taking more than you give.9#10#***********************************************************************11# This file implements regression tests for SQLite library. The12# focus of this file is percentile.c extension. This also tests13# the SQLITE_ENABLE_ORDERED_SET_AGGREGATES compile-time option.14#15 16set testdir [file dirname $argv0]17source $testdir/tester.tcl18 19# Basic test of the percentile() function.20#21do_test percentile-1.0 {22 execsql {23 CREATE TABLE t1(x);24 INSERT INTO t1 VALUES(1),(4),(6),(7),(8),(9),(11),(11),(11);25 }26 execsql {SELECT percentile(x,0) FROM t1}27} {1.0}28foreach {in out disc} {29 100 11.0 11.030 50 8.0 8.031 12.5 4.0 4.032 15 4.4 4.033 20 5.2 4.034 80 11.0 11.035 89 11.0 11.036} {37 do_test percentile-1.1.$in.1 {38 execsql {SELECT percentile(x,$in) FROM t1}39 } $out40 do_test percentile-1.1.$in.2 {41 execsql {SELECT percentile_cont(x,$in*0.01) FROM t1}42 } $out43 do_test percentile-1.1.$in.3 {44 execsql {SELECT percentile_disc(x,$in*0.01) FROM t1}45 } $disc46 if {$in==50} {47 do_test percentile-1.1.$in.4 {48 execsql {SELECT median(x) FROM t1}49 } $out50 }51 ifcapable ordered_set_aggregates {52 do_test percentile-1.1.$in.5 {53 execsql {SELECT percentile($in)WITHIN GROUP(ORDER BY x) FROM t1}54 } $out55 do_test percentile-1.1.$in.6 {56 execsql {SELECT percentile_cont($in*0.01) WITHIN GROUP(ORDER BY x)57 FROM t1}58 } $out59 do_test percentile-1.1.$in.7 {60 execsql {SELECT percentile_disc($in*0.01) WITHIN GROUP(ORDER BY x)61 FROM t1}62 } $disc63 if {$in==50} {64 do_test percentile-1.1.$in.8 {65 execsql {SELECT median() WITHIN GROUP (ORDER BY x) FROM t1}66 } $out67 }68 }69}70do_execsql_test percentile-1.1.median {71 SELECT median(x) FROM t1;72} 8.073ifcapable ordered_set_aggregates {74 do_execsql_test percentile-1.1.median {75 SELECT median() WITHIN GROUP (ORDER BY x) FROM t1;76 } 8.077 do_execsql_test percentile-1.1.distinct.1 {78 SELECT median(DISTINCT x) FROM t1;79 } 7.080 do_catchsql_test percentile-1.1.distinct.2 {81 SELECT percentile(DISTINCT 50) WITHIN GROUP (ORDER BY x) FROM t1;82 } {1 {DISTINCT not allowed on ordered-set aggregate percentile()}}83} else {84 do_catchsql_test percentile-1.1.median {85 SELECT median() WITHIN GROUP (ORDER BY x) FROM t1;86 } {1 {near "(": syntax error}}87}88 89# Add some NULL values.90#91do_test percentile-1.2 {92 execsql {INSERT INTO t1 VALUES(NULL),(NULL);}93} {}94foreach {in out disc} {95 100 11.0 11.096 50 8.0 8.097 12.5 4.0 4.098 15 4.4 4.099 20 5.2 4.0100 80 11.0 11.0101 89 11.0 11.0102} {103 do_test percentile-1.3.$in.1 {104 execsql {SELECT percentile(x,$in) FROM t1}105 } $out106 do_test percentile-1.3.$in.2 {107 execsql {SELECT percentile_cont(x,$in*0.01) FROM t1}108 } $out109 do_test percentile-1.3.$in.3 {110 execsql {SELECT percentile_disc(x,$in*0.01) FROM t1}111 } $disc112 if {$in==50} {113 do_test percentile-1.3.$in.4 {114 execsql {SELECT median(x) FROM t1}115 } $out116 }117 ifcapable ordered_set_aggregates {118 do_test percentile-1.3.$in.5 {119 execsql {SELECT percentile($in)WITHIN GROUP(ORDER BY x) FROM t1}120 } $out121 do_test percentile-1.3.$in.6 {122 execsql {SELECT percentile_cont($in*0.01) WITHIN GROUP(ORDER BY x)123 FROM t1}124 } $out125 do_test percentile-1.3.$in.7 {126 execsql {SELECT percentile_disc($in*0.01) WITHIN GROUP(ORDER BY x)127 FROM t1}128 } $disc129 if {$in==50} {130 do_test percentile-1.3.$in.8 {131 execsql {SELECT median() WITHIN GROUP (ORDER BY x) FROM t1}132 } $out133 }134 }135}136 137# The second argument to percentile can change some, but not much.138#139do_test percentile-1.4.1 {140 catchsql {SELECT round(percentile(x, 15+0.000001*rowid),1) FROM t1}141} {0 4.4}142do_test percentile-1.4.2 {143 catchsql {SELECT round(percentile_cont(x,(15+0.000001*rowid)*0.01),1) FROM t1}144} {0 4.4}145do_test percentile-1.4.3 {146 catchsql {SELECT percentile_disc(x, (15+0.000001*rowid)*0.01) FROM t1}147} {0 4.0}148do_test percentile-1.5.1 {149 catchsql {SELECT percentile(x, 15+0.1*rowid) FROM t1}150} {1 {the fraction argument to percentile() is not the same for all input rows}}151do_test percentile-1.5.2 {152 catchsql {SELECT percentile_cont(x, (15+0.1*rowid)*0.01) FROM t1}153} {1 {the fraction argument to percentile_cont() is not the same for all input rows}}154do_test percentile-1.5.3 {155 catchsql {SELECT percentile_disc(x, (15+0.1*rowid)*0.01) FROM t1}156} {1 {the fraction argument to percentile_disc() is not the same for all input rows}}157 158# Input values in a random order159#160do_test percentile-1.6 {161 execsql {162 CREATE TABLE t2(x);163 INSERT INTO t2 SELECT x+0.0 FROM t1 ORDER BY random();164 }165} {}166foreach {in out disc} {167 100 11.0 11.0168 50 8.0 8.0169 12.5 4.0 4.0170 15 4.4 4.0171 20 5.2 4.0172 80 11.0 11.0173 89 11.0 11.0174} {175 do_test percentile-1.7.$in.1 {176 execsql {SELECT percentile(x,$in) FROM t2}177 } $out178 do_test percentile-1.7.$in.2 {179 execsql {SELECT percentile_cont(x,$in*0.01) FROM t2}180 } $out181 do_test percentile-1.7.$in.3 {182 execsql {SELECT percentile_disc(x,$in*0.01) FROM t2}183 } $disc184 if {$in==50} {185 do_test percentile-1.7.$in.4 {186 execsql {SELECT median(x) FROM t2}187 } $out188 }189 ifcapable ordered_set_aggregates {190 do_test percentile-1.7.$in.5 {191 execsql {SELECT percentile($in)WITHIN GROUP(ORDER BY x) FROM t2}192 } $out193 do_test percentile-1.7.$in.6 {194 execsql {SELECT percentile_cont($in*0.01) WITHIN GROUP(ORDER BY x)195 FROM t2}196 } $out197 do_test percentile-1.7.$in.7 {198 execsql {SELECT percentile_disc($in*0.01) WITHIN GROUP(ORDER BY x)199 FROM t2}200 } $disc201 if {$in==50} {202 do_test percentile-1.7.$in.8 {203 execsql {SELECT median() WITHIN GROUP (ORDER BY x) FROM t2}204 } $out205 }206 }207}208 209# Wrong number of arguments210#211do_test percentile-1.8.1 {212 catchsql {SELECT percentile(x,0,1) FROM t1}213} {1 {wrong number of arguments to function percentile()}}214do_test percentile-1.8.2 {215 catchsql {SELECT percentile_cont(x,0,1) FROM t1}216} {1 {wrong number of arguments to function percentile_cont()}}217do_test percentile-1.8.3 {218 catchsql {SELECT percentile_disc(x,0,1) FROM t1}219} {1 {wrong number of arguments to function percentile_disc()}}220do_test percentile-1.8.4 {221 catchsql {SELECT median(x,0) FROM t1}222} {1 {wrong number of arguments to function median()}}223ifcapable ordered_set_aggregates {224 do_test percentile-1.8.5 {225 catchsql {SELECT percentile(0,1) WITHIN GROUP(ORDER BY x) FROM t1}226 } {1 {wrong number of arguments to function percentile()}}227 do_test percentile-1.8.2 {228 catchsql {SELECT percentile_cont(0,1)WITHIN GROUP (ORDER BY x) FROM t1}229 } {1 {wrong number of arguments to function percentile_cont()}}230 do_test percentile-1.8.3 {231 catchsql {SELECT percentile_disc(0,1)WITHIN GROUP (ORDER BY x) FROM t1}232 } {1 {wrong number of arguments to function percentile_disc()}}233 do_test percentile-1.8.4 {234 catchsql {SELECT median(x) WITHIN GROUP (ORDER BY x) FROM t1}235 } {1 {wrong number of arguments to function median()}}236}237do_test percentile-1.9.1 {238 catchsql {SELECT percentile(x) FROM t1}239} {1 {wrong number of arguments to function percentile()}}240do_test percentile-1.9.2 {241 catchsql {SELECT percentile_cont(x) FROM t1}242} {1 {wrong number of arguments to function percentile_cont()}}243do_test percentile-1.9.3 {244 catchsql {SELECT percentile_disc(x) FROM t1}245} {1 {wrong number of arguments to function percentile_disc()}}246do_test percentile-1.9.4 {247 catchsql {SELECT median() FROM t1}248} {1 {wrong number of arguments to function median()}}249ifcapable ordered_set_aggregates {250 do_test percentile-1.9.5 {251 catchsql {SELECT percentile() WITHIN GROUP(ORDER BY x) FROM t1}252 } {1 {wrong number of arguments to function percentile()}}253 do_test percentile-1.9.6 {254 catchsql {SELECT percentile_cont()WITHIN GROUP (ORDER BY x) FROM t1}255 } {1 {wrong number of arguments to function percentile_cont()}}256 do_test percentile-1.9.7 {257 catchsql {SELECT percentile_disc()WITHIN GROUP (ORDER BY x) FROM t1}258 } {1 {wrong number of arguments to function percentile_disc()}}259}260 261# Second argument must be numeric262#263do_test percentile-1.10 {264 catchsql {SELECT percentile(x,null) FROM t1}265} {1 {the fraction argument to percentile() is not between 0.0 and 100.0}}266do_test percentile-1.11 {267 catchsql {SELECT percentile(x,'fifty') FROM t1}268} {1 {the fraction argument to percentile() is not between 0.0 and 100.0}}269do_test percentile-1.12 {270 catchsql {SELECT percentile(x,x'3530') FROM t1}271} {1 {the fraction argument to percentile() is not between 0.0 and 100.0}}272 273# Second argument is out of range274#275do_test percentile-1.13 {276 catchsql {SELECT percentile(x,-0.0000001) FROM t1}277} {1 {the fraction argument to percentile() is not between 0.0 and 100.0}}278do_test percentile-1.14 {279 catchsql {SELECT percentile(x,100.0000001) FROM t1}280} {1 {the fraction argument to percentile() is not between 0.0 and 100.0}}281do_test percentile-1.14.2 {282 catchsql {SELECT percentile_cont(x,1.0000001) FROM t1}283} {1 {the fraction argument to percentile_cont() is not between 0.0 and 1.0}}284do_test percentile-1.14.3 {285 catchsql {SELECT percentile_disc(x,1.0000001) FROM t1}286} {1 {the fraction argument to percentile_disc() is not between 0.0 and 1.0}}287 288# First argument is not NULL and is not NUMERIC289#290do_test percentile-1.15.1 {291 catchsql {292 BEGIN;293 UPDATE t1 SET x='50' WHERE x IS NULL;294 SELECT percentile(x, 50) FROM t1;295 }296} {1 {input to percentile() is not numeric}}297do_test percentile-1.15.2 {298 catchsql {299 SELECT percentile_cont(x, 0.50) FROM t1;300 }301} {1 {input to percentile_cont() is not numeric}}302do_test percentile-1.15.3 {303 catchsql {304 SELECT percentile_disc(x, 0.50) FROM t1;305 }306} {1 {input to percentile_disc() is not numeric}}307do_test percentile-1.15.4 {308 catchsql {309 SELECT median(x) FROM t1;310 }311} {1 {input to median() is not numeric}}312do_test percentile-1.16 {313 catchsql {314 ROLLBACK;315 BEGIN;316 UPDATE t1 SET x=x'3530' WHERE x IS NULL;317 SELECT percentile(x, 50) FROM t1;318 }319} {1 {input to percentile() is not numeric}}320do_test percentile-1.17 {321 catchsql {322 ROLLBACK;323 SELECT percentile(x, 50) FROM t1;324 }325} {0 8.0}326 327# No non-NULL entries.328#329do_test percentile-1.18 {330 execsql {331 UPDATE t1 SET x=NULL;332 SELECT ifnull(percentile(x, 50),'NULL') FROM t1333 } 334} {NULL}335 336# Exactly one non-NULL entry337#338do_test percentile-1.19 {339 execsql {340 UPDATE t1 SET x=12345 WHERE rowid=5;341 SELECT percentile(x, 0), percentile(x, 50), percentile(x,100) FROM t1342 } 343} {12345.0 12345.0 12345.0}344 345# Infinity as an input346#347do_test percentile-1.20.1 {348 catchsql {349 DELETE FROM t1;350 INSERT INTO t1 SELECT x+0.0 FROM t2;351 UPDATE t1 SET x=1.0e300*1.0e300 WHERE rowid=5;352 SELECT percentile(x,50) from t1;353 }354} {1 {Inf input to percentile()}}355do_test percentile-1.20.2 {356 catchsql {357 SELECT percentile_cont(x,0.50) from t1;358 }359} {1 {Inf input to percentile_cont()}}360do_test percentile-1.20.3 {361 catchsql {362 SELECT percentile_disc(x,0.50) from t1;363 }364} {1 {Inf input to percentile_disc()}}365do_test percentile-1.20.4 {366 catchsql {367 SELECT median(x) from t1;368 }369} {1 {Inf input to median()}}370ifcapable ordered_set_aggregates {371 do_test percentile-1.20.5 {372 catchsql {373 SELECT percentile(50) WITHIN GROUP (ORDER BY x) from t1;374 }375 } {1 {Inf input to percentile()}}376 do_test percentile-1.20.6 {377 catchsql {378 SELECT percentile_cont(0.50) WITHIN GROUP (ORDER BY x) from t1;379 }380 } {1 {Inf input to percentile_cont()}}381 do_test percentile-1.20.7 {382 catchsql {383 SELECT percentile_disc(0.50) WITHIN GROUP(ORDER BY X) from t1;384 }385 } {1 {Inf input to percentile_disc()}}386 do_test percentile-1.20.8 {387 catchsql {388 SELECT median() WITHIN GROUP (ORDER BY x) from t1;389 }390 } {1 {Inf input to median()}}391}392do_test percentile-1.21 {393 catchsql {394 UPDATE t1 SET x=-1.0e300*1.0e300 WHERE rowid=5;395 SELECT percentile(x,50) from t1;396 }397} {1 {Inf input to percentile()}}398 399# Million-row Inputs400#401ifcapable vtab {402 do_test percentile-2.0 {403 load_static_extension db wholenumber404 execsql {405 CREATE VIRTUAL TABLE nums USING wholenumber;406 CREATE TABLE t3(x);407 INSERT INTO t3 SELECT value-1 FROM nums WHERE value BETWEEN 1 AND 500000;408 INSERT INTO t3 SELECT value*10 FROM nums409 WHERE value BETWEEN 500000 AND 999999;410 SELECT count(*) FROM t3;411 }412 } {1000000}413 foreach {in out} {414 0 0.0415 100 9999990.0416 50 2749999.5417 10 99999.9418 } {419 do_test percentile-2.1.$in {420 execsql {421 SELECT round(percentile(x, $in),1) from t3;422 }423 } $out424 }425}426 427# median() as a window function. (2024-08-31)428#429do_execsql_test percentile-3.0 {430 DROP TABLE IF EXISTS t1;431 CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, d);432 INSERT INTO t1 VALUES (1, 'A', 'one', 8.4),433 (2, 'B', 'two', 7.1),434 (3, 'C', 'three', 5.9),435 (4, 'D', 'one', 11.0),436 (5, 'E', 'two', 12.5),437 (6, 'F', 'three', 0.0),438 (7, 'G', 'one', 2.7);439}440foreach {id oba expr} {441 1 0 "median(d)"442 2 0 "percentile(d,50)"443 3 0 "percentile_cont(d,0.5)"444 4 1 "median() WITHIN GROUP (ORDER BY d)"445 5 1 "percentile(50) WITHIN GROUP (ORDER BY d)"446 6 1 "percentile_cont(0.5) WITHIN GROUP (ORDER BY d)"447} {448 if {$oba} {449 ifcapable !ordered_set_aggregates break450 }451 set sql "SELECT a, b, c, d, \452 group_concat(b,'.') OVER w1 AS 'elements', \453 $expr OVER w1 AS 'median' \454 FROM t1 \455 WINDOW w1 AS (ORDER BY c, a ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)"456 do_execsql_test percentile-3.$id.1 $sql {457 1 A one 8.4 A.D 9.7 458 4 D one 11.0 A.D.G 8.4 459 7 G one 2.7 D.G.C 5.9 460 3 C three 5.9 G.C.F 2.7 461 6 F three 0.0 C.F.B 5.9 462 2 B two 7.1 F.B.E 7.1 463 5 E two 12.5 B.E 9.8 464 }465 466 set sql "SELECT a, b, c, d, \467 group_concat(b,'.') OVER w1 AS 'elements', \468 $expr OVER w1 AS 'median' \469 FROM t1 \470 WINDOW w1 AS (ORDER BY c, a \471 ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING)"472 do_execsql_test percentile-3.$id.2 $sql {473 1 A one 8.4 A.D 9.7 474 4 D one 11.0 A.D.G 8.4 475 7 G one 2.7 A.D.G.C 7.15 476 3 C three 5.9 A.D.G.C.F 5.9 477 6 F three 0.0 A.D.G.C.F.B 6.5 478 2 B two 7.1 A.D.G.C.F.B.E 7.1 479 5 E two 12.5 A.D.G.C.F.B.E 7.1 480 }481 482 set sql "SELECT a, b, c, d, \483 group_concat(b,'.') OVER w1 AS 'elements', \484 $expr OVER w1 AS 'median' \485 FROM t1 \486 WINDOW w1 AS (ORDER BY c, a \487 ROWS BETWEEN 1 PRECEDING AND UNBOUNDED FOLLOWING)"488 do_execsql_test percentile-3.$id.3 $sql {489 1 A one 8.4 A.D.G.C.F.B.E 7.1 490 4 D one 11.0 A.D.G.C.F.B.E 7.1 491 7 G one 2.7 D.G.C.F.B.E 6.5 492 3 C three 5.9 G.C.F.B.E 5.9 493 6 F three 0.0 C.F.B.E 6.5 494 2 B two 7.1 F.B.E 7.1 495 5 E two 12.5 B.E 9.8 496 }497}498 499# Test case adapted from examples shown at500# https://database.guide/3-functions-to-calculate-the-median-in-sql/501#502do_execsql_test percential-4.0 {503 CREATE TABLE products(504 vendorId INT,505 productId INTEGER PRIMARY KEY,506 productName REAL,507 price REAL508 );509 INSERT INTO products VALUES510 (1001, 17, 'Left-handed screwdriver', 25.99),511 (1001, 49, 'Right-handed screwdriver', 25.99),512 (1001, 216, 'Long weight (blue)', 14.75),513 (1001, 31, 'Long weight (green)', 11.99),514 (1002, 37, 'Sledge hammer', 33.49),515 (1003, 7, 'Chainsaw', 245.00),516 (1003, 8, 'Straw dog box', 55.99),517 (1003, 12, 'Hammock', 11.01),518 (1004, 113, 'Teapot', 12.45),519 (1004, 117, 'Bottomless coffee mug', 9.99);520}521do_execsql_test percentile-4.1 {522 SELECT VendorId, ProductId, /* ProductName,*/ Price,523 avg(price) OVER (PARTITION BY vendorId) AS "Average",524 median(price) OVER (PARTITION BY vendorId) AS "Median"525 FROM products526 ORDER BY vendorId, productId;527} {528 1001 17 25.99 19.68 20.37 529 1001 31 11.99 19.68 20.37 530 1001 49 25.99 19.68 20.37 531 1001 216 14.75 19.68 20.37 532 1002 37 33.49 33.49 33.49 533 1003 7 245.0 104.0 55.99 534 1003 8 55.99 104.0 55.99 535 1003 12 11.01 104.0 55.99 536 1004 113 12.45 11.22 11.22 537 1004 117 9.99 11.22 11.22 538}539do_execsql_test percentile-4.2 {540 SELECT vendorId, median(price) FROM products541 GROUP BY 1 ORDER BY 1;542} {1001 20.37 1002 33.49 1003 55.99 1004 11.22}543 544do_execsql_test percentile-5.0 {545 CREATE TABLE user(name TEXT, class TEXT, cost REAL);546 INSERT INTO user VALUES547 ('Alice', 'Y', 3578.27),548 ('Bob', 'X', 3399.99),549 ('Cindy', 'Z', 699.10),550 ('Dave', 'Y', 3078.27),551 ('Emma', 'Z', 2319.99),552 ('Fred', 'Y', 539.99),553 ('Gina', 'X', 2320.49),554 ('Hank', 'W', 24.99),555 ('Irma', 'W', 24.99),556 ('Jake', 'X', 2234.99),557 ('Kim', 'Y', 4319.99),558 ('Liam', 'X', 4968.59),559 ('Mia', 'W', 59.53),560 ('Nate', 'W', 23.50);561}562do_execsql_test percentile-5.1 {563 SELECT name, class, cost,564 percentile(cost, 0) OVER w1 AS 'P0',565 percentile(cost, 25) OVER w1 AS 'P1',566 percentile(cost, 50) OVER w1 AS 'P2',567 percentile(cost, 75) OVER w1 AS 'P3',568 percentile(cost, 100) OVER w1 AS 'P4'569 FROM user570 WINDOW w1 AS (PARTITION BY class)571 ORDER BY class, cost;572} {573 Nate W 23.5 23.5 24.6175 24.99 33.625 59.53 574 Hank W 24.99 23.5 24.6175 24.99 33.625 59.53 575 Irma W 24.99 23.5 24.6175 24.99 33.625 59.53 576 Mia W 59.53 23.5 24.6175 24.99 33.625 59.53 577 Jake X 2234.99 2234.99 2299.115 2860.24 3792.14 4968.59578 Gina X 2320.49 2234.99 2299.115 2860.24 3792.14 4968.59579 Bob X 3399.99 2234.99 2299.115 2860.24 3792.14 4968.59580 Liam X 4968.59 2234.99 2299.115 2860.24 3792.14 4968.59581 Fred Y 539.99 539.99 2443.7 3328.27 3763.7 4319.99582 Dave Y 3078.27 539.99 2443.7 3328.27 3763.7 4319.99583 Alice Y 3578.27 539.99 2443.7 3328.27 3763.7 4319.99584 Kim Y 4319.99 539.99 2443.7 3328.27 3763.7 4319.99585 Cindy Z 699.1 699.1 1104.3225 1509.545 1914.7675 2319.99586 Emma Z 2319.99 699.1 1104.3225 1509.545 1914.7675 2319.99587}588 589# Fuzzer find.590do_execsql_test percentile-6.0 {591 WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<12)592 SELECT median(iif(n%2,0.1,1.0)) FROM c;593} 0.55594 595finish_test596