1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
|
# name: test/sql/window/test_tpcds_q49.test
# description: TPC-DS Q49 bug fix for multi-sort window functions
# group: [window]
statement ok
PRAGMA enable_verification
statement ok
BEGIN TRANSACTION
statement ok
create table wintest( item integer, return_ratio numeric, currency_ratio numeric)
statement ok
insert into wintest values (7539 ,0.590000 , 0.590000), (3337 ,0.626506 , 0.626506), (15597 ,0.661972 , 0.661972), (2915 ,0.698630 , 0.698630), (11933 ,0.717172 , 0.717172), (483 ,0.800000 , 0.800000), (85 ,0.857143 , 0.857143), (97 ,0.903614 , 0.903614), (117 ,0.925000 , 0.925000), (5299 ,0.927083 , 0.927083), (10055 ,0.945652 , 0.945652), (4231 ,0.977778 , 0.977778), (5647 ,0.987805 , 0.987805), (8679 ,0.988764 , 0.988764), (10323 ,0.977778 , 1.111111), (3305 ,0.737500 , 1.293860)
query III
SELECT item, rank() OVER (ORDER BY return_ratio) AS return_rank, rank() OVER (ORDER BY currency_ratio) AS currency_rank FROM wintest order by item
----
85 8 7
97 9 8
117 10 9
483 7 6
2915 4 4
3305 6 16
3337 2 2
4231 13 12
5299 11 10
5647 15 13
7539 1 1
8679 16 14
10055 12 11
10323 13 15
11933 5 5
15597 3 3
statement ok
ROLLBACK
|
