gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/window5.test (about)

     1  # 2018 May 8
     2  #
     3  # The author disclaims copyright to this source code.  In place of
     4  # 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. Specifically,
    12  # it tests the sqlite3_create_window_function() API.
    13  #
    14  
    15  set testdir [file dirname $argv0]
    16  source $testdir/tester.tcl
    17  set testprefix window5
    18  
    19  ifcapable !windowfunc {
    20    finish_test
    21    return
    22  }
    23  
    24  proc m_step {ctx val} {
    25    lappend ctx $val
    26    return $ctx
    27  }
    28  proc m_value {ctx} {
    29    set lSort [lsort $ctx]
    30  
    31    set nVal [llength $lSort]
    32    set n [expr $nVal/2]
    33    
    34    if {($nVal % 2)==0 && $nVal>0} {
    35      set a [lindex $lSort $n]
    36      set b [lindex $lSort $n-1]
    37      if {($a+$b) % 2} {
    38        set ret [expr ($a+$b)/2.0]
    39      } else {
    40        set ret [expr ($a+$b)/2]
    41      }
    42    } else {
    43      set ret [lindex $lSort $n]
    44    }
    45    return $ret
    46  }
    47  proc m_inverse {ctx val} {
    48    set ctx [lrange $ctx 1 end]
    49    return $ctx
    50  }
    51  proc w_value {ctx} {
    52    lsort $ctx
    53  }
    54  
    55  sqlite3_create_window_function db median m_step m_value m_value m_inverse
    56  sqlite3_create_window_function db win m_step w_value w_value m_inverse
    57  
    58  do_test 0.0 {
    59    test_create_window_function_misuse db
    60  } {}
    61  
    62  do_execsql_test 1.0 {
    63    CREATE TABLE t1(a, b);
    64    INSERT INTO t1 VALUES(4, 'a');
    65    INSERT INTO t1 VALUES(6, 'b');
    66    INSERT INTO t1 VALUES(1, 'c');
    67    INSERT INTO t1 VALUES(5, 'd');
    68    INSERT INTO t1 VALUES(2, 'e');
    69    INSERT INTO t1 VALUES(3, 'f');
    70  }
    71  
    72  do_execsql_test 1.1 {
    73    SELECT win(a) OVER (ORDER BY b), median(a) OVER (ORDER BY b) FROM t1;
    74  } {4 4  {4 6} 5  {1 4 6} 4  {1 4 5 6} 4.5  {1 2 4 5 6} 4 {1 2 3 4 5 6} 3.5}
    75  
    76  test_create_sumint db
    77  do_execsql_test 2.0 {
    78    SELECT sumint(a) OVER (ORDER BY rowid) FROM t1 ORDER BY rowid;
    79  } {4 10 11 16 18 21}
    80  
    81  do_execsql_test 2.1 {
    82    SELECT sumint(a) OVER (ORDER BY rowid ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) FROM t1 ORDER BY rowid;
    83  } {10 11 12 8 10 5}
    84  
    85  test_override_sum db
    86  do_catchsql_test 3.0 {
    87    SELECT sum(a) OVER 
    88    (ORDER BY b ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) 
    89    FROM t1;
    90  } {1 {sum() may not be used as a window function}}
    91  do_execsql_test 3.1 {
    92    SELECT sum(a) FROM t1;
    93  } {21}
    94  
    95  
    96  finish_test