Skip to content

Commit 238e179

Browse files
Custom window frame support extended to built-in window functions (#4078)
* refactor running window * remove unnecessary changes * implement suggested changes * Minor refactors to improve readability * Refactor according to reviews * minor changes * Remove unnecessary into/collect calls * convert evaluate_inside_range result to ScalarValue * Simplify evaluate function of BuiltInWindowExpr Co-authored-by: Mehmet Ozan Kabak <[email protected]>
1 parent 6d00bd9 commit 238e179

7 files changed

Lines changed: 567 additions & 423 deletions

File tree

‎datafusion/core/src/physical_plan/windows/mod.rs‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -65,6 +65,7 @@ pub fn create_window_expr(
6565
create_built_in_window_expr(fun, args, input_schema, name)?,
6666
partition_by,
6767
order_by,
68+
window_frame,
6869
)),
6970
})
7071
}

‎datafusion/core/tests/sql/window.rs‎

Lines changed: 182 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -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

Comments
 (0)