Skip to content

Commit c3d2768

Browse files
taozhi8833998taozhi8833998
committed
refactor: windows function ignore nulls in all db
1 parent 331427a commit c3d2768

9 files changed

Lines changed: 24 additions & 26 deletions

File tree

‎pegjs/noql.pegjs‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -4180,7 +4180,7 @@ window_fun_laglead
41804180
}
41814181

41824182
window_fun_firstlast
4183-
= name:KW_FIRST_LAST_VALUE __ LPAREN __ l:expr __ cn:consider_nulls_clause? __ RPAREN __ over:over_partition {
4183+
= name:KW_FIRST_LAST_VALUE __ LPAREN __ l:expr __ RPAREN __ cn:consider_nulls_clause? __ over:over_partition {
41844184
// => window_fun_laglead
41854185
return {
41864186
type: 'window_func',

‎pegjs/postgresql.pegjs‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -4790,7 +4790,7 @@ window_fun_laglead
47904790
}
47914791

47924792
window_fun_firstlast
4793-
= name:KW_FIRST_LAST_VALUE __ LPAREN __ l:expr __ cn:consider_nulls_clause? __ RPAREN __ over:over_partition {
4793+
= name:KW_FIRST_LAST_VALUE __ LPAREN __ l:expr __ RPAREN __ cn:consider_nulls_clause? __ over:over_partition {
47944794
// => window_fun_laglead
47954795
return {
47964796
type: 'window_func',

‎pegjs/redshift.pegjs‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -4239,7 +4239,7 @@ window_fun_laglead
42394239
}
42404240

42414241
window_fun_firstlast
4242-
= name:KW_FIRST_LAST_VALUE __ LPAREN __ l:expr __ cn:consider_nulls_clause? __ RPAREN __ over:over_partition {
4242+
= name:KW_FIRST_LAST_VALUE __ LPAREN __ l:expr __ RPAREN __ cn:consider_nulls_clause? __ over:over_partition {
42434243
// => window_fun_laglead
42444244
return {
42454245
type: 'window_func',

‎pegjs/snowflake.pegjs‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -3666,7 +3666,7 @@ window_fun_laglead
36663666
}
36673667

36683668
window_fun_firstlast
3669-
= name:KW_FIRST_LAST_VALUE __ LPAREN __ l:expr __ cn:consider_nulls_clause? __ RPAREN __ over:over_partition {
3669+
= name:KW_FIRST_LAST_VALUE __ LPAREN __ l:expr __ RPAREN __ cn:consider_nulls_clause? __ over:over_partition {
36703670
// => window_fun_laglead
36713671
return {
36723672
type: 'window_func',

‎pegjs/trino.pegjs‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -3636,7 +3636,7 @@ window_fun_laglead
36363636
}
36373637

36383638
window_fun_firstlast
3639-
= name:KW_FIRST_LAST_VALUE __ LPAREN __ l:expr __ cn:consider_nulls_clause? __ RPAREN __ over:over_partition {
3639+
= name:KW_FIRST_LAST_VALUE __ LPAREN __ l:expr __ RPAREN __ cn:consider_nulls_clause? __ over:over_partition {
36403640
// => window_fun_laglead
36413641
return {
36423642
type: 'window_func',

‎src/window.js‎

Lines changed: 3 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -42,24 +42,11 @@ function namedWindowExprListToSQL(namedWindowExprInfo) {
4242
return expr.map(namedWindowExprToSQL).join(', ')
4343
}
4444

45-
function isConsiderNullsInArgs(fnName) {
46-
// position of IGNORE/RESPECT NULLS varies by function
47-
switch (toUpper(fnName)) {
48-
case 'NTH_VALUE':
49-
case 'LEAD':
50-
case 'LAG':
51-
return false
52-
default:
53-
return true
54-
}
55-
}
56-
5745
function constructArgsList(expr) {
58-
const { args, name, consider_nulls = '' } = expr
59-
const argsList = args ? exprToSQL(args).join(', ') : ''
46+
const { args, name, consider_nulls = '', separator = ', ' } = expr
47+
const argsList = args ? exprToSQL(args).join(separator) : ''
6048
// cover Syntax from FN_NAME(...args [RESPECT NULLS]) [RESPECT NULLS]
61-
const isConsidernulls = isConsiderNullsInArgs(name)
62-
const result = [name, '(', argsList, !isConsidernulls && ')', consider_nulls && ' ', consider_nulls, isConsidernulls && ')']
49+
const result = [name, '(', argsList, ')', consider_nulls && ' ', consider_nulls]
6350
return result.filter(hasVal).join('')
6451
}
6552

‎test/postgres.spec.js‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -249,12 +249,12 @@ describe('Postgres', () => {
249249
title: 'Window Fns + FIRST_VALUE',
250250
sql: [
251251
`SELECT
252-
FIRST_VALUE(user_name ignore NULLS) OVER (
252+
FIRST_VALUE(user_name) ignore NULLS OVER (
253253
PARTITION BY user_city
254254
ORDER BY created_at, ranking
255255
) AS age_window
256256
FROM roster`,
257-
'SELECT FIRST_VALUE(user_name IGNORE NULLS) OVER (PARTITION BY user_city ORDER BY created_at ASC, ranking ASC) AS "age_window" FROM "roster"'
257+
'SELECT FIRST_VALUE(user_name) IGNORE NULLS OVER (PARTITION BY user_city ORDER BY created_at ASC, ranking ASC) AS "age_window" FROM "roster"'
258258
]
259259
},
260260
{

‎test/redshift.spec.js‎

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -11,10 +11,10 @@ describe('redshift', () => {
1111
}
1212

1313
it('should support qualify condition', () => {
14-
let sql = `SELECT sf_account_id, date_trunc('week', date_) as week_id, date_trunc('month', date_) as month_id, Last_VALUE(hermes_health_score IGNORE NULLS) OVER ( partition by sf_account_id, week_id ) as hermes_health_score,
15-
Last_VALUE(hermes_health_score IGNORE NULLS) OVER ( partition by sf_account_id, month_id ) as hermes_health_score_monthly, row_number() OVER ( PARTITION BY sf_account_id, date_trunc('week', date_)
14+
let sql = `SELECT sf_account_id, date_trunc('week', date_) as week_id, date_trunc('month', date_) as month_id, Last_VALUE(hermes_health_score) IGNORE NULLS OVER ( partition by sf_account_id, week_id ) as hermes_health_score,
15+
Last_VALUE(hermes_health_score) IGNORE NULLS OVER ( partition by sf_account_id, month_id ) as hermes_health_score_monthly, row_number() OVER ( PARTITION BY sf_account_id, date_trunc('week', date_)
1616
ORDER BY date_ desc ) AS o_key_week FROM dwh.dwh_health_score_hermes WHERE date_trunc('month', date_) >= '2023-01-01' Qualify o_key_week = 1`
17-
expect(getParsedSql(sql)).to.be.equal(`SELECT sf_account_id, date_trunc('week', date_) AS "week_id", date_trunc('month', date_) AS "month_id", Last_VALUE(hermes_health_score IGNORE NULLS) OVER (PARTITION BY sf_account_id, week_id) AS "hermes_health_score", Last_VALUE(hermes_health_score IGNORE NULLS) OVER (PARTITION BY sf_account_id, month_id) AS "hermes_health_score_monthly", row_number() OVER (PARTITION BY sf_account_id, date_trunc('week', date_) ORDER BY date_ DESC) AS "o_key_week" FROM "dwh"."dwh_health_score_hermes" WHERE date_trunc('month', date_) >= '2023-01-01' QUALIFY o_key_week = 1`)
17+
expect(getParsedSql(sql)).to.be.equal(`SELECT sf_account_id, date_trunc('week', date_) AS "week_id", date_trunc('month', date_) AS "month_id", Last_VALUE(hermes_health_score) IGNORE NULLS OVER (PARTITION BY sf_account_id, week_id) AS "hermes_health_score", Last_VALUE(hermes_health_score) IGNORE NULLS OVER (PARTITION BY sf_account_id, month_id) AS "hermes_health_score_monthly", row_number() OVER (PARTITION BY sf_account_id, date_trunc('week', date_) ORDER BY date_ DESC) AS "o_key_week" FROM "dwh"."dwh_health_score_hermes" WHERE date_trunc('month', date_) >= '2023-01-01' QUALIFY o_key_week = 1`)
1818
sql = `with pv as (
1919
select
2020
action_date,

‎test/snowflake.spec.js‎

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -500,6 +500,17 @@ describe('snowflake', () => {
500500
`SELECT SUM("id") OVER (PARTITION BY "name" ORDER BY "created_at" ASC RANGE BETWEEN INTERVAL '9 DAYS' PRECEDING AND CURRENT ROW) AS "last10Dmatches" FROM "model1"`
501501
]
502502
},
503+
{
504+
title: 'window function ignore null',
505+
sql: [
506+
`SELECT
507+
LAST_VALUE(ac_install_date)
508+
IGNORE NULLS
509+
OVER (PARTITION BY player_id ORDER BY date DESC) AS ac_install_date
510+
FROM some_table;`,
511+
'SELECT LAST_VALUE("ac_install_date") IGNORE NULLS OVER (PARTITION BY "player_id" ORDER BY "date" DESC) AS "ac_install_date" FROM "some_table"'
512+
]
513+
},
503514
]
504515
SQL_LIST.forEach(sqlInfo => {
505516
const { title, sql } = sqlInfo

0 commit comments

Comments
 (0)