@@ -1276,3 +1276,185 @@ async fn window_frame_creation() -> Result<()> {
12761276
12771277 Ok ( ( ) )
12781278}
1279+
1280+ #[ tokio:: test]
1281+ async fn test_window_row_number_aggregate ( ) -> Result < ( ) > {
1282+ let config = SessionConfig :: new ( ) ;
1283+ let ctx = SessionContext :: with_config ( config) ;
1284+ register_aggregate_csv ( & ctx) . await ?;
1285+ let sql = "SELECT
1286+ c8,
1287+ ROW_NUMBER() OVER(ORDER BY c9) AS rn1,
1288+ ROW_NUMBER() OVER(ORDER BY c9 ROWS BETWEEN 10 PRECEDING and 1 FOLLOWING) as rn2
1289+ FROM aggregate_test_100
1290+ ORDER BY c8
1291+ LIMIT 5" ;
1292+
1293+ let actual = execute_to_batches ( & ctx, sql) . await ;
1294+ let expected = vec ! [
1295+ "+-----+-----+-----+" ,
1296+ "| c8 | rn1 | rn2 |" ,
1297+ "+-----+-----+-----+" ,
1298+ "| 102 | 73 | 73 |" ,
1299+ "| 299 | 1 | 1 |" ,
1300+ "| 363 | 41 | 41 |" ,
1301+ "| 417 | 14 | 14 |" ,
1302+ "| 794 | 95 | 95 |" ,
1303+ "+-----+-----+-----+" ,
1304+ ] ;
1305+ assert_batches_eq ! ( expected, & actual) ;
1306+ Ok ( ( ) )
1307+ }
1308+
1309+ #[ tokio:: test]
1310+ async fn test_window_cume_dist ( ) -> Result < ( ) > {
1311+ let config = SessionConfig :: new ( ) ;
1312+ let ctx = SessionContext :: with_config ( config) ;
1313+ register_aggregate_csv ( & ctx) . await ?;
1314+ let sql = "SELECT
1315+ c8,
1316+ CUME_DIST() OVER(ORDER BY c9) as cd1,
1317+ CUME_DIST() OVER(ORDER BY c9 ROWS BETWEEN 10 PRECEDING and 1 FOLLOWING) as cd2
1318+ FROM aggregate_test_100
1319+ ORDER BY c8
1320+ LIMIT 5" ;
1321+
1322+ let actual = execute_to_batches ( & ctx, sql) . await ;
1323+ let expected = vec ! [
1324+ "+-----+------+------+" ,
1325+ "| c8 | cd1 | cd2 |" ,
1326+ "+-----+------+------+" ,
1327+ "| 102 | 0.73 | 0.73 |" ,
1328+ "| 299 | 0.01 | 0.01 |" ,
1329+ "| 363 | 0.41 | 0.41 |" ,
1330+ "| 417 | 0.14 | 0.14 |" ,
1331+ "| 794 | 0.95 | 0.95 |" ,
1332+ "+-----+------+------+" ,
1333+ ] ;
1334+ assert_batches_eq ! ( expected, & actual) ;
1335+ Ok ( ( ) )
1336+ }
1337+
1338+ #[ tokio:: test]
1339+ async fn test_window_rank ( ) -> Result < ( ) > {
1340+ let config = SessionConfig :: new ( ) ;
1341+ let ctx = SessionContext :: with_config ( config) ;
1342+ register_aggregate_csv ( & ctx) . await ?;
1343+ let sql = "SELECT
1344+ c9,
1345+ RANK() OVER(ORDER BY c1) AS rank1,
1346+ RANK() OVER(ORDER BY c1 ROWS BETWEEN 10 PRECEDING and 1 FOLLOWING) as rank2,
1347+ DENSE_RANK() OVER(ORDER BY c1) as dense_rank1,
1348+ DENSE_RANK() OVER(ORDER BY c1 ROWS BETWEEN 10 PRECEDING and 1 FOLLOWING) as dense_rank2,
1349+ PERCENT_RANK() OVER(ORDER BY c1) as percent_rank1,
1350+ PERCENT_RANK() OVER(ORDER BY c1 ROWS BETWEEN 10 PRECEDING and 1 FOLLOWING) as percent_rank2
1351+ FROM aggregate_test_100
1352+ ORDER BY c9
1353+ LIMIT 5" ;
1354+
1355+ let actual = execute_to_batches ( & ctx, sql) . await ;
1356+ let expected = vec ! [
1357+ "+-----------+-------+-------+-------------+-------------+---------------------+---------------------+" ,
1358+ "| c9 | rank1 | rank2 | dense_rank1 | dense_rank2 | percent_rank1 | percent_rank2 |" ,
1359+ "+-----------+-------+-------+-------------+-------------+---------------------+---------------------+" ,
1360+ "| 28774375 | 80 | 80 | 5 | 5 | 0.797979797979798 | 0.797979797979798 |" ,
1361+ "| 63044568 | 62 | 62 | 4 | 4 | 0.6161616161616161 | 0.6161616161616161 |" ,
1362+ "| 141047417 | 1 | 1 | 1 | 1 | 0 | 0 |" ,
1363+ "| 141680161 | 41 | 41 | 3 | 3 | 0.40404040404040403 | 0.40404040404040403 |" ,
1364+ "| 145294611 | 1 | 1 | 1 | 1 | 0 | 0 |" ,
1365+ "+-----------+-------+-------+-------------+-------------+---------------------+---------------------+" ,
1366+ ] ;
1367+ assert_batches_eq ! ( expected, & actual) ;
1368+ Ok ( ( ) )
1369+ }
1370+
1371+ #[ tokio:: test]
1372+ async fn test_lag_lead ( ) -> Result < ( ) > {
1373+ let config = SessionConfig :: new ( ) ;
1374+ let ctx = SessionContext :: with_config ( config) ;
1375+ register_aggregate_csv ( & ctx) . await ?;
1376+ let sql = "SELECT
1377+ c9,
1378+ LAG(c9, 2, 10101) OVER(ORDER BY c9) as lag1,
1379+ LAG(c9, 2, 10101) OVER(ORDER BY c9 ROWS BETWEEN 10 PRECEDING and 1 FOLLOWING) as lag2,
1380+ LEAD(c9, 2, 10101) OVER(ORDER BY c9) as lead1,
1381+ LEAD(c9, 2, 10101) OVER(ORDER BY c9 ROWS BETWEEN 10 PRECEDING and 1 FOLLOWING) as lead2
1382+ FROM aggregate_test_100
1383+ ORDER BY c9
1384+ LIMIT 5" ;
1385+
1386+ let actual = execute_to_batches ( & ctx, sql) . await ;
1387+ let expected = vec ! [
1388+ "+-----------+-----------+-----------+-----------+-----------+" ,
1389+ "| c9 | lag1 | lag2 | lead1 | lead2 |" ,
1390+ "+-----------+-----------+-----------+-----------+-----------+" ,
1391+ "| 28774375 | 10101 | 10101 | 141047417 | 141047417 |" ,
1392+ "| 63044568 | 10101 | 10101 | 141680161 | 141680161 |" ,
1393+ "| 141047417 | 28774375 | 28774375 | 145294611 | 145294611 |" ,
1394+ "| 141680161 | 63044568 | 63044568 | 225513085 | 225513085 |" ,
1395+ "| 145294611 | 141047417 | 141047417 | 243203849 | 243203849 |" ,
1396+ "+-----------+-----------+-----------+-----------+-----------+" ,
1397+ ] ;
1398+ assert_batches_eq ! ( expected, & actual) ;
1399+ Ok ( ( ) )
1400+ }
1401+
1402+ #[ tokio:: test]
1403+ async fn test_window_frame_first_value_last_value_aggregate ( ) -> Result < ( ) > {
1404+ let config = SessionConfig :: new ( ) ;
1405+ let ctx = SessionContext :: with_config ( config) ;
1406+ register_aggregate_csv ( & ctx) . await ?;
1407+
1408+ let sql = "SELECT
1409+ FIRST_VALUE(c4) OVER(ORDER BY c9 ASC ROWS BETWEEN 10 PRECEDING AND 1 FOLLOWING) as first_value1,
1410+ FIRST_VALUE(c4) OVER(ORDER BY c9 ASC ROWS BETWEEN 2 PRECEDING AND 3 FOLLOWING) as first_value2,
1411+ LAST_VALUE(c4) OVER(ORDER BY c9 ASC ROWS BETWEEN 10 PRECEDING AND 1 FOLLOWING) as last_value1,
1412+ LAST_VALUE(c4) OVER(ORDER BY c9 ASC ROWS BETWEEN 2 PRECEDING AND 3 FOLLOWING) as last_value2
1413+ FROM aggregate_test_100
1414+ ORDER BY c9
1415+ LIMIT 5" ;
1416+
1417+ let actual = execute_to_batches ( & ctx, sql) . await ;
1418+ let expected = vec ! [
1419+ "+--------------+--------------+-------------+-------------+" ,
1420+ "| first_value1 | first_value2 | last_value1 | last_value2 |" ,
1421+ "+--------------+--------------+-------------+-------------+" ,
1422+ "| -16110 | -16110 | 3917 | -1114 |" ,
1423+ "| -16110 | -16110 | -16974 | 15673 |" ,
1424+ "| -16110 | -16110 | -1114 | 13630 |" ,
1425+ "| -16110 | 3917 | 15673 | -13217 |" ,
1426+ "| -16110 | -16974 | 13630 | 20690 |" ,
1427+ "+--------------+--------------+-------------+-------------+" ,
1428+ ] ;
1429+ assert_batches_eq ! ( expected, & actual) ;
1430+ Ok ( ( ) )
1431+ }
1432+
1433+ #[ tokio:: test]
1434+ async fn test_window_frame_nth_value_aggregate ( ) -> Result < ( ) > {
1435+ let config = SessionConfig :: new ( ) ;
1436+ let ctx = SessionContext :: with_config ( config) ;
1437+ register_aggregate_csv ( & ctx) . await ?;
1438+
1439+ let sql = "SELECT
1440+ NTH_VALUE(c4, 3) OVER(ORDER BY c9 ASC ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING) as nth_value1,
1441+ NTH_VALUE(c4, 2) OVER(ORDER BY c9 ASC ROWS BETWEEN 1 PRECEDING AND 3 FOLLOWING) as nth_value2
1442+ FROM aggregate_test_100
1443+ ORDER BY c9
1444+ LIMIT 5" ;
1445+
1446+ let actual = execute_to_batches ( & ctx, sql) . await ;
1447+ let expected = vec ! [
1448+ "+------------+------------+" ,
1449+ "| nth_value1 | nth_value2 |" ,
1450+ "+------------+------------+" ,
1451+ "| | 3917 |" ,
1452+ "| -16974 | 3917 |" ,
1453+ "| -16974 | -16974 |" ,
1454+ "| -1114 | -1114 |" ,
1455+ "| 15673 | 15673 |" ,
1456+ "+------------+------------+" ,
1457+ ] ;
1458+ assert_batches_eq ! ( expected, & actual) ;
1459+ Ok ( ( ) )
1460+ }
0 commit comments