Skip to content

Commit e1269fa

Browse files
taozhi8833998taozhi8833998
authored
Merge pull request taozhi8833998#179 from taozhi8833998/feature-select-over
Feature select over
2 parents d9ed0a5 + ddc0de2 commit e1269fa

4 files changed

Lines changed: 27 additions & 5 deletions

File tree

‎package.json‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,6 +1,6 @@
11
{
22
"name": "node-sql-parser",
3-
"version": "1.6.19",
3+
"version": "1.6.20",
44
"description": "simple node sql parser",
55
"main": "index.js",
66
"types": "index.d.ts",

‎pegjs/mysql.pegjs‎

Lines changed: 13 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1535,14 +1535,25 @@ KW_SUM_MAX_MIN_AVG
15351535
= KW_SUM / KW_MAX / KW_MIN / KW_AVG
15361536

15371537
aggr_fun_count
1538-
= name:KW_COUNT __ LPAREN __ arg:count_arg __ RPAREN {
1538+
= name:KW_COUNT __ LPAREN __ arg:count_arg __ RPAREN __ KW_OVER __ LPAREN __ KW_PARTITION __ KW_BY __ bc: column_list __ RPAREN __ {
1539+
console.log(bc)
1540+
if (bc) bc.forEach(c => columnList.add(`select::null::${c}`))
1541+
return {
1542+
type: 'aggr_func',
1543+
name: name,
1544+
args: arg,
1545+
over: bc
1546+
};
1547+
}
1548+
/ name:KW_COUNT __ LPAREN __ arg:count_arg __ RPAREN {
15391549
return {
15401550
type: 'aggr_func',
15411551
name: name,
15421552
args: arg
15431553
};
15441554
}
15451555

1556+
15461557
count_arg
15471558
= e:star_expr { return { expr: e }; }
15481559
/ d:KW_DISTINCT? __ c:column_ref { return { distinct: d, expr: c }; }
@@ -1804,6 +1815,7 @@ KW_FULL = "FULL"i !ident_start
18041815
KW_INNER = "INNER"i !ident_start
18051816
KW_JOIN = "JOIN"i !ident_start
18061817
KW_OUTER = "OUTER"i !ident_start
1818+
KW_OVER = "OVER"i !ident_start
18071819
KW_UNION = "UNION"i !ident_start
18081820
KW_VALUES = "VALUES"i !ident_start
18091821
KW_USING = "USING"i !ident_start

‎src/aggregation.js‎

Lines changed: 4 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1,17 +1,18 @@
11
import has from 'has'
22
import { exprToSQL } from './expr'
3+
import { hasVal, identifierToSql } from './util'
34

45
function aggrToSQL(expr) {
56
/** @type {Object} */
6-
const { args } = expr
7+
const { args, over } = expr
78
let str = exprToSQL(args.expr)
89
const fnName = expr.name
10+
const overStr = over && `OVER (PARTITION BY ${over.map(col => identifierToSql(col)).join(', ')})`
911

1012
if (fnName === 'COUNT') {
1113
if (has(args, 'distinct') && args.distinct !== null) str = `DISTINCT ${str}`
1214
}
13-
14-
return `${fnName}(${str})`
15+
return [`${fnName}(${str})`, overStr].filter(hasVal).join(' ')
1516
}
1617

1718
export {

‎test/select.spec.js‎

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -996,6 +996,15 @@ describe('select', () => {
996996
})
997997
})
998998

999+
describe('select over', () => {
1000+
it('should support select over', () => {
1001+
const sql = 'SELECT id, name,gender, COUNT(gender) OVER (PARTITION BY gender) AS Total_students FROM student'
1002+
const ast = parser.astify(sql)
1003+
const backSQL = parser.sqlify(ast)
1004+
expect(backSQL).to.equal("SELECT `id`, `name`, `gender`, COUNT(`gender`) OVER (PARTITION BY `gender`) AS `Total_students` FROM `student`")
1005+
})
1006+
})
1007+
9991008
describe('pg json column', () => {
10001009
it('should support pg json column query', () => {
10011010
const sql = `SELECT id,

0 commit comments

Comments
 (0)