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

     1  # 2018 May 19
     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  #
    12  
    13  source [file join [file dirname $argv0] pg_common.tcl]
    14  
    15  #=========================================================================
    16  
    17  start_test window7 "2019 March 01"
    18  ifcapable !windowfunc
    19  
    20  execsql_test 1.0 {
    21    DROP TABLE IF EXISTS t3;
    22    CREATE TABLE t3(a INTEGER, b INTEGER);
    23    INSERT INTO t3 VALUES
    24      (1, 1), (2, 2), (3, 3), (4, 4), (5, 5), (6, 6), (7, 7), (8, 8), 
    25      (9, 9), (0, 10), (1, 11), (2, 12), (3, 13), (4, 14), (5, 15), (6, 16), 
    26      (7, 17), (8, 18), (9, 19), (0, 20), (1, 21), (2, 22), (3, 23), (4, 24), 
    27      (5, 25), (6, 26), (7, 27), (8, 28), (9, 29), (0, 30), (1, 31), (2, 32), 
    28      (3, 33), (4, 34), (5, 35), (6, 36), (7, 37), (8, 38), (9, 39), (0, 40), 
    29      (1, 41), (2, 42), (3, 43), (4, 44), (5, 45), (6, 46), (7, 47), (8, 48), 
    30      (9, 49), (0, 50), (1, 51), (2, 52), (3, 53), (4, 54), (5, 55), (6, 56), 
    31      (7, 57), (8, 58), (9, 59), (0, 60), (1, 61), (2, 62), (3, 63), (4, 64), 
    32      (5, 65), (6, 66), (7, 67), (8, 68), (9, 69), (0, 70), (1, 71), (2, 72), 
    33      (3, 73), (4, 74), (5, 75), (6, 76), (7, 77), (8, 78), (9, 79), (0, 80), 
    34      (1, 81), (2, 82), (3, 83), (4, 84), (5, 85), (6, 86), (7, 87), (8, 88), 
    35      (9, 89), (0, 90), (1, 91), (2, 92), (3, 93), (4, 94), (5, 95), (6, 96), 
    36      (7, 97), (8, 98), (9, 99), (0, 100);
    37  }
    38  
    39  execsql_test 1.1 {
    40    SELECT a, sum(b) FROM t3 GROUP BY a ORDER BY 1;
    41  }
    42  
    43  execsql_test 1.2 {
    44    SELECT a, sum(b) OVER (
    45      ORDER BY a GROUPS BETWEEN CURRENT ROW AND CURRENT ROW
    46    ) FROM t3 ORDER BY 1;
    47  }
    48  
    49  execsql_test 1.3 {
    50    SELECT a, sum(b) OVER (
    51      ORDER BY a GROUPS BETWEEN 0 PRECEDING AND 0 FOLLOWING
    52    ) FROM t3 ORDER BY 1;
    53  }
    54  
    55  execsql_test 1.4 {
    56    SELECT a, sum(b) OVER (
    57      ORDER BY a GROUPS BETWEEN 2 PRECEDING AND 2 FOLLOWING
    58    ) FROM t3 ORDER BY 1;
    59  }
    60  
    61  execsql_test 1.5 {
    62    SELECT a, sum(b) OVER (
    63      ORDER BY a RANGE BETWEEN 0 PRECEDING AND 0 FOLLOWING
    64    ) FROM t3 ORDER BY 1;
    65  }
    66  
    67  execsql_test 1.6 {
    68    SELECT a, sum(b) OVER (
    69      ORDER BY a RANGE BETWEEN 2 PRECEDING AND 2 FOLLOWING
    70    ) FROM t3 ORDER BY 1;
    71  }
    72  
    73  execsql_test 1.7 {
    74    SELECT a, sum(b) OVER (
    75      ORDER BY a RANGE BETWEEN 2 PRECEDING AND 1 FOLLOWING
    76    ) FROM t3 ORDER BY 1;
    77  }
    78  
    79  execsql_test 1.8.1 {
    80    SELECT a, sum(b) OVER (
    81      ORDER BY a RANGE BETWEEN 0 PRECEDING AND 1 FOLLOWING
    82    ) FROM t3 ORDER BY 1;
    83  }
    84  execsql_test 1.8.2 {
    85    SELECT a, sum(b) OVER (
    86      ORDER BY a DESC RANGE BETWEEN 0 PRECEDING AND 1 FOLLOWING
    87    ) FROM t3 ORDER BY 1;
    88  }
    89  
    90  finish_test
    91