You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by ch...@apache.org on 2023/07/24 08:15:12 UTC
[shardingsphere] branch master updated: Support oracle NTILE,PERCENTILE_CONT,PERCENTILE_DISC functions parsing (#27418)
This is an automated email from the ASF dual-hosted git repository.
chengzhang pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/shardingsphere.git
The following commit(s) were added to refs/heads/master by this push:
new 18681f7d563 Support oracle NTILE,PERCENTILE_CONT,PERCENTILE_DISC functions parsing (#27418)
18681f7d563 is described below
commit 18681f7d5639c232c006a9c31347e3d9b7e45aa8
Author: ZhangCheng <ch...@apache.org>
AuthorDate: Mon Jul 24 16:15:06 2023 +0800
Support oracle NTILE,PERCENTILE_CONT,PERCENTILE_DISC functions parsing (#27418)
* Support oracle NTILE,PERCENTILE_CONT,PERCENTILE_DISC functions parsing
* fix
* fix
---
.../src/main/antlr4/imports/oracle/BaseRule.g4 | 2 +
.../parser/src/main/resources/case/dml/select.xml | 43 ++++++++++++++++++++++
.../main/resources/sql/supported/dml/select.xml | 2 +
3 files changed, 47 insertions(+)
diff --git a/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/BaseRule.g4 b/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/BaseRule.g4
index e871ec72cdd..f1e55d54017 100644
--- a/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/BaseRule.g4
+++ b/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/BaseRule.g4
@@ -712,6 +712,8 @@ windowingClause
analyticFunction
: specifiedAnalyticFunctionName = (LEAD | LAG) ((LP_ expr leadLagInfo? RP_ respectOrIgnoreNulls?) | (LP_ expr respectOrIgnoreNulls? leadLagInfo? RP_)) overClause
+ | specifiedAnalyticFunctionName = NTILE LP_ expr RP_ overClause
+ | specifiedAnalyticFunctionName = (PERCENTILE_CONT | PERCENTILE_DISC) LP_ expr RP_ WITHIN GROUP LP_ orderByClause RP_ overClause
| analyticFunctionName LP_ dataType* RP_ overClause
;
diff --git a/test/it/parser/src/main/resources/case/dml/select.xml b/test/it/parser/src/main/resources/case/dml/select.xml
index e470975292f..1e2041309a2 100644
--- a/test/it/parser/src/main/resources/case/dml/select.xml
+++ b/test/it/parser/src/main/resources/case/dml/select.xml
@@ -5683,4 +5683,47 @@
<simple-table name="employees" start-index="48" stop-index="56" literal-start-index="48" literal-stop-index="56"/>
</from>
</select>
+
+ <select sql-case-id="select_with_ntile_function">
+ <projections start-index="7" stop-index="54" literal-start-index="7" literal-stop-index="54">
+ <expression-projection text="NTILE(4) OVER (ORDER BY salary DESC)" alias="quartile" start-index="7" stop-index="54" literal-start-index="7" literal-stop-index="54">
+ <expr>
+ <function function-name="NTILE" text="NTILE(4) OVER (ORDER BY salary DESC)" start-index="7" stop-index="42" literal-start-index="7" literal-stop-index="42" />
+ </expr>
+ </expression-projection>
+ </projections>
+ <from>
+ <simple-table name="employees" start-index="61" stop-index="69" literal-start-index="61" literal-stop-index="69" />
+ </from>
+ <where start-index="71" stop-index="95" literal-start-index="71" literal-stop-index="95">
+ <expr>
+ <binary-operation-expression start-index="77" stop-index="95" literal-start-index="77" literal-stop-index="95">
+ <left>
+ <column name="department_id" start-index="77" stop-index="89" literal-start-index="77" literal-stop-index="89" />
+ </left>
+ <operator>=</operator>
+ <right>
+ <literal-expression value="100" start-index="93" stop-index="95" literal-start-index="93" literal-stop-index="95" />
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ <order-by>
+ <column-item name="last_name" order-direction="ASC" start-index="106" stop-index="114" literal-start-index="106" literal-stop-index="114" />
+ </order-by>
+ </select>
+
+ <select sql-case-id="select_with_percentile_functions">
+ <projections start-index="7" stop-index="163" literal-start-index="7" literal-stop-index="163">
+ <column-projection name="department_id" start-index="7" stop-index="19" literal-start-index="7" literal-stop-index="19" />
+ <expression-projection text="PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary DESC)" alias="Median cont" start-index="22" stop-index="91" literal-start-index="22" literal-stop-index="91" />
+ <expression-projection text="PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY salary DESC)" alias="Median disc" start-index="94" stop-index="163" literal-start-index="94" literal-stop-index="163" />
+ </projections>
+ <from>
+ <simple-table name="employees" start-index="170" stop-index="178" literal-start-index="170" literal-stop-index="178" />
+ </from>
+ <group-by>
+ <column-item name="department_id" order-direction="ASC" start-index="189" stop-index="201" literal-start-index="189" literal-stop-index="201" />
+ </group-by>
+ </select>
</sql-parser-test-cases>
diff --git a/test/it/parser/src/main/resources/sql/supported/dml/select.xml b/test/it/parser/src/main/resources/sql/supported/dml/select.xml
index 1d336a068cf..12a934ea074 100644
--- a/test/it/parser/src/main/resources/sql/supported/dml/select.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dml/select.xml
@@ -191,4 +191,6 @@
<sql-case id="select_with_last_value_function" value="SELECT LAST_VALUE(AGE IGNORE NULLS) OVER (PARTITION BY AGE ORDER BY AGE) from TEST;" db-types="Oracle" />
<sql-case id="select_with_lead_and_lag_function" value="SELECT hire_date, LAG(hire_date, 1) OVER (ORDER BY hire_date) AS LAG1, LEAD(hire_date, 1) OVER (ORDER BY hire_date) AS LEAD1 FROM employees WHERE department_id = 30 ORDER BY hire_date;" db-types="Oracle" />
<sql-case id="select_with_connect_by_root" value="SELECT CONNECT_BY_ROOT last_name 'Manager' FROM employees CONNECT BY PRIOR employee_id = manager_id" db-types="Oracle" />
+ <sql-case id="select_with_ntile_function" value="SELECT NTILE(4) OVER (ORDER BY salary DESC) AS quartile FROM employees WHERE department_id = 100 ORDER BY last_name" db-types="Oracle" />
+ <sql-case id="select_with_percentile_functions" value="SELECT department_id, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary DESC) 'Median cont', PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY salary DESC) 'Median disc' FROM employees GROUP BY department_id" db-types="Oracle" />
</sql-cases>