You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@kylin.apache.org by sh...@apache.org on 2019/01/30 01:07:27 UTC
[kylin] branch document updated: KYLIN-3766 Add docs for Query SQL
Grammar, Function and Data Type
This is an automated email from the ASF dual-hosted git repository.
shaofengshi pushed a commit to branch document
in repository https://gitbox.apache.org/repos/asf/kylin.git
The following commit(s) were added to refs/heads/document by this push:
new 09860ef KYLIN-3766 Add docs for Query SQL Grammar, Function and Data Type
09860ef is described below
commit 09860ef4102892d43f378ac5dd78b2586ff937e2
Author: GinaZhai <na...@kyligence.io>
AuthorDate: Mon Jan 28 21:05:10 2019 +0800
KYLIN-3766 Add docs for Query SQL Grammar, Function and Data Type
---
website/_data/docs-cn.yml | 3 +
website/_data/docs.yml | 3 +
website/_docs/tutorial/kylin_datatype.cn.md | 16 ++
website/_docs/tutorial/kylin_datatype.md | 15 ++
website/_docs/tutorial/kylin_function.cn.md | 219 ++++++++++++++++++++++++++++
website/_docs/tutorial/kylin_function.md | 218 +++++++++++++++++++++++++++
website/_docs/tutorial/kylin_grammar.cn.md | 188 ++++++++++++++++++++++++
website/_docs/tutorial/kylin_grammar.md | 190 ++++++++++++++++++++++++
website/assets/css/docs.css | 1 +
9 files changed, 853 insertions(+)
diff --git a/website/_data/docs-cn.yml b/website/_data/docs-cn.yml
index b494469..cdc4698 100644
--- a/website/_data/docs-cn.yml
+++ b/website/_data/docs-cn.yml
@@ -27,6 +27,9 @@
- title: 教程
docs:
- tutorial/kylin_sample
+ - tutorial/kylin_grammar
+ - tutorial/kylin_function
+ - tutorial/kylin_datatype
- tutorial/web
- tutorial/create_cube
- tutorial/cube_build_job
diff --git a/website/_data/docs.yml b/website/_data/docs.yml
index c17d4b5..924a8c3 100644
--- a/website/_data/docs.yml
+++ b/website/_data/docs.yml
@@ -36,6 +36,9 @@
- title: Tutorial
docs:
- tutorial/kylin_sample
+ - tutorial/kylin_grammar
+ - tutorial/kylin_function
+ - tutorial/kylin_datatype
- tutorial/web
- tutorial/create_cube
- tutorial/cube_build_job
diff --git a/website/_docs/tutorial/kylin_datatype.cn.md b/website/_docs/tutorial/kylin_datatype.cn.md
new file mode 100644
index 0000000..4467655
--- /dev/null
+++ b/website/_docs/tutorial/kylin_datatype.cn.md
@@ -0,0 +1,16 @@
+---
+layout: docs-cn
+title: "数据类型"
+categories: tutorial
+permalink: /cn/docs/tutorial/kylin_datatype.html
+---
+
+## 数据类型
+
+| :--------- | :--------- | :--------- | :--------- | :------------------- |
+| ANY | CHAR | VARCHAR | STRING | BOOLEAN |
+| BYTE | BINARY | INT | SHORT | LONG |
+| INTEGER | TINYINT | SMALLINT | BIGINT | INT4 (for test only) |
+| FLOAT | REAL | DOUBLE | DECIMAL | LONG8 (for test only)|
+| NUMERIC | DATE | TIME | DATETIME | TIMESTAMP |
+
diff --git a/website/_docs/tutorial/kylin_datatype.md b/website/_docs/tutorial/kylin_datatype.md
new file mode 100644
index 0000000..f19994e
--- /dev/null
+++ b/website/_docs/tutorial/kylin_datatype.md
@@ -0,0 +1,15 @@
+---
+layout: docs
+title: Data Type
+categories: tutorial
+permalink: /docs/tutorial/kylin_datatype.html
+---
+
+## Data Type
+
+| :--------- | :--------- | :--------- | :--------- | :------------------- |
+| ANY | CHAR | VARCHAR | STRING | BOOLEAN |
+| BYTE | BINARY | INT | SHORT | LONG |
+| INTEGER | TINYINT | SMALLINT | BIGINT | INT4 (for test only) |
+| FLOAT | REAL | DOUBLE | DECIMAL | LONG8 (for test only)|
+| NUMERIC | DATE | TIME | DATETIME | TIMESTAMP |
\ No newline at end of file
diff --git a/website/_docs/tutorial/kylin_function.cn.md b/website/_docs/tutorial/kylin_function.cn.md
new file mode 100644
index 0000000..ce798d7
--- /dev/null
+++ b/website/_docs/tutorial/kylin_function.cn.md
@@ -0,0 +1,219 @@
+---
+layout: docs-cn
+title: "SQL 函数"
+categories: tutorial
+permalink: /cn/docs/tutorial/kylin_function.html
+---
+
+## 函数
+
+[COUNT](#COUNT)
+ [COUNT(COLUMN)](#COUNT_COLUMN)
+ [COUNT(*)](#COUNT_)
+[COUNT_DISTINCT](#COUNT_DISTINCT)
+[EXTENDED_COLUMN](#EXTENDED_COLUMN)
+[MAX](#MAX)
+[MIN](#MIN)
+[PERCENTILE](#PERCENTILE)
+[SUM](#SUM)
+[TOP_N](#TOP_N)
+
+[WINDOW](#WINDOW)
+ [ROW_NUMBER](#ROW_NUMBER)
+ [AVG](#AVG)
+ [RANK](#RANK)
+ [DENSE_RANK](#DENSE_RANK)
+ [FIRST_VALUE](#FIRST_VALUE)
+ [LAST_VALUE](#LAST_VALUE)
+ [LAG](#LAG)
+ [LEAD](#LEAD)
+ [NTILE](#NTILE)
+ [CASE WHEN](#CASEWHEN)
+ [CAST](#CAST)
+
+[SUSTRING](#SUBSTRING)
+[COALESCE](#COALESCE)
+
+
+## COUNT {#COUNT}
+用于返回与指定条件匹配的行数。
+
+### COUNT(COLUMN) {#COUNT_COLUMN}
+
+例子：
+{% highlight Groff markup %}
+SELECT COUNT(seller_id) FROM kylin_sales;
+{% endhighlight %}
+
+### COUNT(*) {#COUNT_}
+
+例子：
+{% highlight Groff markup %}
+SELECT COUNT(*) FROM kylin_sales;
+{% endhighlight %}
+
+
+## COUNT_DISTINCT {#COUNT_DISTINCT}
+
+例子：
+{% highlight Groff markup %}
+SELECT COUNT(DISTINCT seller_id) AS DIST_SELLER FROM kylin_sales;
+{% endhighlight %}
+
+## EXTENDED_COLUMN {#EXTENDED_COLUMN}
+
+例子：
+{% highlight Groff markup %}
+SELECT test_order.order_id, test_extended_column FROM test_kylin_fact as test_kylin_fact INNER JOIN test_order as test_order ON test_kylin_fact.order_id = test_order.order_id INNER JOIN test_category_groupings as test_category_groupings ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id GROUP BY test_order.order_id, test_extended_column;
+{% endhighlight %}
+
+## MAX {#MAX}
+返回一列中的最大值。NULL 值不包括在计算中。
+例子：
+{% highlight Groff markup %}
+SELECT MAX(lstg_site_id) FROM kylin_sales;
+{% endhighlight %}
+
+
+## MIN {#MIN}
+返回一列中的最小值。NULL 值不包括在计算中。
+例子：
+{% highlight Groff markup %}
+SELECT MIN(lstg_site_id) FROM kylin_sales;
+{% endhighlight %}
+
+
+## PERCENTILE {#PERCENTILE}
+
+例子：
+{% highlight Groff markup %}
+SELECT seller_id, PERCENTILE(price, 0.5) FROM kylin_sales GROUP BY seller_id;
+
+SELECT seller_id, PERCENTILE_APPROX(price, 0.5) FROM kylin_sales GROUP BY seller_id;
+{% endhighlight %}
+
+
+## SUM {#SUM}
+返回数值列的总数。
+例子：
+{% highlight Groff markup %}
+SELECT SUM(price) FROM kylin_sales;
+{% endhighlight %}
+
+## TOP_N {#TOP_N}
+
+例子：
+{% highlight Groff markup %}
+SELECT SUM(price) AS gmv
+ FROM kylin_sales
+INNER JOIN kylin_cal_dt AS kylin_cal_dt
+ ON kylin_sales.part_dt = kylin_cal_dt.cal_dt
+ INNER JOIN kylin_category_groupings
+ ON kylin_sales.leaf_categ_id = kylin_category_groupings.leaf_categ_id AND kylin_sales.lstg_site_id = kylin_category_groupings.site_id
+ WHERE kylin_cal_dt.cal_dt between DATE '2013-09-01' AND DATE '2013-10-01' AND (lstg_format_name='FP-GTC' OR 'a' = 'b')
+ GROUP BY kylin_cal_dt.cal_dt;
+
+SELECT kylin_sales.part_dt, seller_id
+FROM kylin_sales
+INNER JOIN kylin_cal_dt AS kylin_cal_dt
+ON kylin_sales.part_dt = kylin_cal_dt.cal_dt
+INNER JOIN kylin_category_groupings
+ON kylin_sales.leaf_categ_id = kylin_category_groupings.leaf_categ_id
+AND kylin_sales.lstg_site_id = kylin_category_groupings.site_id
+GROUP BY
+kylin_sales.part_dt, kylin_sales.seller_id ORDER BY SUM(kylin_sales.price) DESC LIMIT 20;
+{% endhighlight %}
+
+## WINDOW {#WINDOW}
+```WINDOW``` 函数在和当前行相关的一组表行上执行计算。
+*注意*：```WINDOW``` 函数中必须有 ```OVER``` 子句
+
+### ROW_NUMBER {#ROW_NUMBER}
+
+例子：
+{% highlight Groff markup %}
+SELECT lstg_format_name, SUM(price) AS gmv, ROW_NUMBER() OVER() FROM kylin_sales GROUP BY lstg_format_name;
+{% endhighlight %}
+
+### AVG {#AVG}
+返回数值列的平均值。NULL 值不包括在计算中。
+例子：
+{% highlight Groff markup %}
+SELECT lstg_format_name, AVG(SUM(price)) OVER(PARTITION BY lstg_format_name) FROM kylin_sales GROUP BY part_dt, lstg_format_name;
+{% endhighlight %}
+
+### RANK {#RANK}
+
+例子：
+{% highlight Groff markup %}
+SELECT part_dt, lstg_format_name, SUM(price), RANK() OVER(PARTITION BY lstg_format_name ORDER BY part_dt) AS "rank" FROM kylin_sales GROUP BY part_dt, lstg_format_name;
+{% endhighlight %}
+
+### DENSE_RANK {#DENSE_RANK}
+
+例子：
+{% highlight Groff markup %}
+SELECT part_dt, lstg_format_name, SUM(price), DENSE_RANK() OVER(PARTITION BY lstg_format_name ORDER BY part_dt) AS "dense_rank" FROM kylin_sales GROUP BY part_dt, lstg_format_name;
+{% endhighlight %}
+
+### FIRST_VALUE {#FIRST_VALUE}
+
+例子：
+{% highlight Groff markup %}
+SELECT part_dt, lstg_format_name, SUM(price) AS gmv, FIRST_VALUE(SUM(price)) OVER(PARTITION BY lstg_format_name ORDER BY part_dt) AS "first" FROM kylin_sales WHERE part_dt < '2012-02-01' GROUP BY part_dt, lstg_format_name;
+{% endhighlight %}
+
+### LAST_VALUE {#LAST_VALUE}
+
+例子：
+{% highlight Groff markup %}
+SELECT part_dt, lstg_format_name, SUM(price) AS gmv, LAST_VALUE(SUM(price)) OVER(PARTITION BY lstg_format_name ORDER BY part_dt) AS "current" FROM kylin_sales WHERE part_dt < '2012-02-01' GROUP BY part_dt, lstg_format_name;
+{% endhighlight %}
+
+### LAG {#LAG}
+
+例子：
+{% highlight Groff markup %}
+SELECT part_dt, lstg_format_name, SUM(price) AS gmv, LAG(SUM(price), 1, 0.0) OVER(PARTITION BY lstg_format_name ORDER BY part_dt) AS "prev" FROM kylin_sales WHERE part_dt < '2012-02-01' GROUP BY part_dt, lstg_format_name;
+{% endhighlight %}
+
+### LEAD {#LEAD}
+
+例子：
+{% highlight Groff markup %}
+SELECT part_dt, lstg_format_name, SUM(price) AS gmv, LEAD(SUM(price), 1, 0.0) OVER(PARTITION BY lstg_format_name ORDER BY part_dt) AS "next" FROM kylin_sales WHERE part_dt < '2012-02-01' GROUP BY part_dt, lstg_format_name;
+{% endhighlight %}
+
+### NTILE {#NTILE}
+
+例子：
+{% highlight Groff markup %}
+SELECT part_dt, lstg_format_name, SUM(price) AS gmv, NTILE(4) OVER (PARTITION BY lstg_format_name ORDER BY part_dt) AS "quarter" FROM kylin_sales WHERE part_dt < '2012-02-01' GROUP BY part_dt, lstg_format_name;
+{% endhighlight %}
+
+### CASE WHEN {#CASEWHEN}
+
+例子：
+{% highlight Groff markup %}
+SELECT part_dt, lstg_format_name, SUM(price) AS gmv, (CASE LAG(SUM(price), 1, 0.0) OVER(PARTITION BY lstg_format_name ORDER BY part_dt) WHEN 0.0 THEN 0 ELSE SUM(price)/LAG(SUM(price), 1, 0.0) OVER(PARTITION BY lstg_format_name ORDER BY part_dt) END) AS "prev" FROM kylin_sales WHERE part_dt < '2012-02-01' GROUP BY part_dt, lstg_format_name;
+{% endhighlight %}
+
+### CAST {#CAST}
+```RANGE```，```INTERVAL``` 关键字指明了范围。```PRECEDING``` 表示前几天（秒/分/时/月/年）。```FOLLOWING``` 表示后几天（秒/分/时/月/年）。
+例子：
+{% highlight Groff markup %}
+SELECT part_dt, lstg_format_name, SUM(price) AS gmv, FIRST_VALUE(SUM(price)) OVER (PARTITION BY lstg_format_name ORDER BY CAST(part_dt AS timestamp) RANGE INTERVAL '3' DAY PRECEDING) AS "prev 3 days", LAST_VALUE(SUM(price)) OVER (PARTITION BY lstg_format_name ORDER BY CAST(part_dt AS timestamp) RANGE INTERVAL '3' DAY FOLLOWING) AS "next 3 days" FROM kylin_sales WHERE part_dt < '2012-02-01' GROUP BY part_dt, lstg_format_name;
+{% endhighlight %}
+
+## SUBSTRING {#SUBSTRING}
+例子：
+{% highlight Groff markup %}
+SELECT SUBSTRING(lstg_format_name, 1) FROM kylin_sales;
+{% endhighlight %}
+
+## COALESCE {#COALESCE}
+例子：
+{% highlight Groff markup %}
+SELECT COALESCE(lstg_format_name, '888888888888') FROM kylin_sales;
+{% endhighlight %}
+
diff --git a/website/_docs/tutorial/kylin_function.md b/website/_docs/tutorial/kylin_function.md
new file mode 100644
index 0000000..8a56976
--- /dev/null
+++ b/website/_docs/tutorial/kylin_function.md
@@ -0,0 +1,218 @@
+---
+layout: docs
+title: SQL Function
+categories: tutorial
+permalink: /docs/tutorial/kylin_function.html
+---
+
+## Function
+
+[COUNT](#COUNT)
+ [COUNT(COLUMN)](#COUNT_COLUMN)
+ [COUNT(*)](#COUNT_)
+[COUNT_DISTINCT](#COUNT_DISTINCT)
+[EXTENDED_COLUMN](#EXTENDED_COLUMN)
+[MAX](#MAX)
+[MIN](#MIN)
+[PERCENTILE](#PERCENTILE)
+[SUM](#SUM)
+[TOP_N](#TOP_N)
+
+[WINDOW](#WINDOW)
+ [ROW_NUMBER](#ROW_NUMBER)
+ [AVG](#AVG)
+ [RANK](#RANK)
+ [DENSE_RANK](#DENSE_RANK)
+ [FIRST_VALUE](#FIRST_VALUE)
+ [LAST_VALUE](#LAST_VALUE)
+ [LAG](#LAG)
+ [LEAD](#LEAD)
+ [NTILE](#NTILE)
+ [CASE WHEN](#CASEWHEN)
+ [CAST](#CAST)
+
+[SUSTRING](#SUBSTRING)
+[COALESCE](#COALESCE)
+
+## COUNT {#COUNT}
+Returns the number of rows matching the specified criteria.
+
+### COUNT(COLUMN) {#COUNT_COLUMN}
+
+Example:
+{% highlight Groff markup %}
+SELECT COUNT(seller_id) FROM kylin_sales;
+{% endhighlight %}
+
+### COUNT(*) {#COUNT_}
+
+Example:
+{% highlight Groff markup %}
+SELECT COUNT(*) FROM kylin_sales;
+{% endhighlight %}
+
+
+## COUNT_DISTINCT {#COUNT_DISTINCT}
+
+Example:
+{% highlight Groff markup %}
+SELECT COUNT(DISTINCT seller_id) AS DIST_SELLER FROM kylin_sales;
+{% endhighlight %}
+
+## EXTENDED_COLUMN {#EXTENDED_COLUMN}
+
+Example:
+{% highlight Groff markup %}
+SELECT test_order.order_id, test_extended_column FROM test_kylin_fact as test_kylin_fact INNER JOIN test_order as test_order ON test_kylin_fact.order_id = test_order.order_id INNER JOIN test_category_groupings as test_category_groupings ON test_kylin_fact.leaf_categ_id = test_category_groupings.leaf_categ_id AND test_kylin_fact.lstg_site_id = test_category_groupings.site_id GROUP BY test_order.order_id, test_extended_column;
+{% endhighlight %}
+
+## MAX {#MAX}
+Returns the maximum value in a column. NULL values are not included in the calculation.
+Example:
+{% highlight Groff markup %}
+SELECT MAX(lstg_site_id) FROM kylin_sales;
+{% endhighlight %}
+
+
+## MIN {#MIN}
+Returns the minimum value in a column. NULL values are not included in the calculation.
+Example:
+{% highlight Groff markup %}
+SELECT MIN(lstg_site_id) FROM kylin_sales;
+{% endhighlight %}
+
+
+## PERCENTILE {#PERCENTILE}
+
+Example:
+{% highlight Groff markup %}
+SELECT seller_id, PERCENTILE(price, 0.5) FROM kylin_sales GROUP BY seller_id;
+
+SELECT seller_id, PERCENTILE_APPROX(price, 0.5) FROM kylin_sales GROUP BY seller_id;
+{% endhighlight %}
+
+
+## SUM {#SUM}
+Returns the total number of numeric columns.
+Example:
+{% highlight Groff markup %}
+SELECT SUM(price) FROM kylin_sales;
+{% endhighlight %}
+
+## TOP_N {#TOP_N}
+
+Example:
+{% highlight Groff markup %}
+SELECT SUM(price) AS gmv
+ FROM kylin_sales
+INNER JOIN kylin_cal_dt AS kylin_cal_dt
+ ON kylin_sales.part_dt = kylin_cal_dt.cal_dt
+ INNER JOIN kylin_category_groupings
+ ON kylin_sales.leaf_categ_id = kylin_category_groupings.leaf_categ_id AND kylin_sales.lstg_site_id = kylin_category_groupings.site_id
+ WHERE kylin_cal_dt.cal_dt between DATE '2013-09-01' AND DATE '2013-10-01' AND (lstg_format_name='FP-GTC' OR 'a' = 'b')
+ GROUP BY kylin_cal_dt.cal_dt;
+
+SELECT kylin_sales.part_dt, seller_id
+FROM kylin_sales
+INNER JOIN kylin_cal_dt AS kylin_cal_dt
+ON kylin_sales.part_dt = kylin_cal_dt.cal_dt
+INNER JOIN kylin_category_groupings
+ON kylin_sales.leaf_categ_id = kylin_category_groupings.leaf_categ_id
+AND kylin_sales.lstg_site_id = kylin_category_groupings.site_id
+GROUP BY
+kylin_sales.part_dt, kylin_sales.seller_id ORDER BY SUM(kylin_sales.price) DESC LIMIT 20;
+{% endhighlight %}
+
+## WINDOW {#WINDOW}
+
+The ```WINDOW``` function performs the calculation on a set of table rows associated with the current row.
+*NOTE*: ```OVER``` clause is necessary for window functions.
+
+### ROW_NUMBER {#ROW_NUMBER}
+
+Example:
+{% highlight Groff markup %}
+SELECT lstg_format_name, SUM(price) AS gmv, ROW_NUMBER() OVER() FROM kylin_sales GROUP BY lstg_format_name;
+{% endhighlight %}
+
+### AVG {#AVG}
+Returns the average of the numeric columns. NULL values are not included in the calculation.
+Example:
+{% highlight Groff markup %}
+SELECT lstg_format_name, AVG(SUM(price)) OVER(PARTITION BY lstg_format_name) FROM kylin_sales GROUP BY part_dt, lstg_format_name;
+{% endhighlight %}
+
+### RANK {#RANK}
+
+Example:
+{% highlight Groff markup %}
+SELECT part_dt, lstg_format_name, SUM(price), RANK() OVER(PARTITION BY lstg_format_name ORDER BY part_dt) AS "rank" FROM kylin_sales GROUP BY part_dt, lstg_format_name;
+{% endhighlight %}
+
+### DENSE_RANK {#DENSE_RANK}
+
+Example:
+{% highlight Groff markup %}
+SELECT part_dt, lstg_format_name, SUM(price), DENSE_RANK() OVER(PARTITION BY lstg_format_name ORDER BY part_dt) AS "dense_rank" FROM kylin_sales GROUP BY part_dt, lstg_format_name;
+{% endhighlight %}
+
+### FIRST_VALUE {#FIRST_VALUE}
+
+Example:
+{% highlight Groff markup %}
+SELECT part_dt, lstg_format_name, SUM(price) AS gmv, FIRST_VALUE(SUM(price)) OVER(PARTITION BY lstg_format_name ORDER BY part_dt) AS "first" FROM kylin_sales WHERE part_dt < '2012-02-01' GROUP BY part_dt, lstg_format_name;
+{% endhighlight %}
+
+### LAST_VALUE {#LAST_VALUE}
+
+Example:
+{% highlight Groff markup %}
+SELECT part_dt, lstg_format_name, SUM(price) AS gmv, LAST_VALUE(SUM(price)) OVER(PARTITION BY lstg_format_name ORDER BY part_dt) AS "current" FROM kylin_sales WHERE part_dt < '2012-02-01' GROUP BY part_dt, lstg_format_name;
+{% endhighlight %}
+
+### LAG {#LAG}
+
+Example:
+{% highlight Groff markup %}
+SELECT part_dt, lstg_format_name, SUM(price) AS gmv, LAG(SUM(price), 1, 0.0) OVER(PARTITION BY lstg_format_name ORDER BY part_dt) AS "prev" FROM kylin_sales WHERE part_dt < '2012-02-01' GROUP BY part_dt, lstg_format_name;
+{% endhighlight %}
+
+### LEAD {#LEAD}
+
+Example:
+{% highlight Groff markup %}
+SELECT part_dt, lstg_format_name, SUM(price) AS gmv, LEAD(SUM(price), 1, 0.0) OVER(PARTITION BY lstg_format_name ORDER BY part_dt) AS "next" FROM kylin_sales WHERE part_dt < '2012-02-01' GROUP BY part_dt, lstg_format_name;
+{% endhighlight %}
+
+### NTILE {#NTILE}
+
+Example:
+{% highlight Groff markup %}
+SELECT part_dt, lstg_format_name, SUM(price) AS gmv, NTILE(4) OVER (PARTITION BY lstg_format_name ORDER BY part_dt) AS "quarter" FROM kylin_sales WHERE part_dt < '2012-02-01' GROUP BY part_dt, lstg_format_name;
+{% endhighlight %}
+
+### CASE WHEN {#CASEWHEN}
+
+Example:
+{% highlight Groff markup %}
+SELECT part_dt, lstg_format_name, SUM(price) AS gmv, (CASE LAG(SUM(price), 1, 0.0) OVER(PARTITION BY lstg_format_name ORDER BY part_dt) WHEN 0.0 THEN 0 ELSE SUM(price)/LAG(SUM(price), 1, 0.0) OVER(PARTITION BY lstg_format_name ORDER BY part_dt) END) AS "prev" FROM kylin_sales WHERE part_dt < '2012-02-01' GROUP BY part_dt, lstg_format_name;
+{% endhighlight %}
+
+### CAST {#CAST}
+The keyword ```RANGE```, ```INTERVAL``` to specify a range. ```PRECEDING``` means the first few days (second/minute/hour/month/year). ```FOLLOWING``` means the next few days (second/minute/hour/month/year).
+Example:
+{% highlight Groff markup %}
+SELECT part_dt, lstg_format_name, SUM(price) AS gmv, FIRST_VALUE(SUM(price)) OVER (PARTITION BY lstg_format_name ORDER BY CAST(part_dt AS timestamp) RANGE INTERVAL '3' DAY PRECEDING) AS "prev 3 days", LAST_VALUE(SUM(price)) OVER (PARTITION BY lstg_format_name ORDER BY CAST(part_dt AS timestamp) RANGE INTERVAL '3' DAY FOLLOWING) AS "next 3 days" FROM kylin_sales WHERE part_dt < '2012-02-01' GROUP BY part_dt, lstg_format_name;
+{% endhighlight %}
+
+## SUBSTRING {#SUBSTRING}
+Example:
+{% highlight Groff markup %}
+SELECT SUBSTRING(lstg_format_name, 1) FROM kylin_sales;
+{% endhighlight %}
+
+## COALESCE {#COALESCE}
+Example:
+{% highlight Groff markup %}
+SELECT COALESCE(lstg_format_name, '888888888888') FROM kylin_sales;
+{% endhighlight %}
diff --git a/website/_docs/tutorial/kylin_grammar.cn.md b/website/_docs/tutorial/kylin_grammar.cn.md
new file mode 100644
index 0000000..087d465
--- /dev/null
+++ b/website/_docs/tutorial/kylin_grammar.cn.md
@@ -0,0 +1,188 @@
+---
+layout: docs-cn
+title: "SQL 语法"
+categories: tutorial
+permalink: /cn/docs/tutorial/kylin_grammar.html
+---
+
+## 语法
+
+[SELECT](#SELECT)
+ [STATEMENT](#STATEMENT)
+ [EXPRESSION](#EXPRESSION)
+[SUBQUERY](#SUBQUERY)
+[JOIN](#JOIN)
+ [INNER JOIN](#INNERJOIN)
+ [LEFT JOIN](#LEFTJOIN)
+ [CROSS JOIN](#CROSSJOIN)
+[UNION](#UNION)
+[UNION ALL](#UNIONALL)
+
+{% highlight Groff markup %}
+statement:
+| query
+
+query:
+ values
+ | WITH withItem [ , withItem ]* query
+ | {
+ select
+ | selectWithoutFrom
+ | query UNION [ ALL | DISTINCT ] query
+ | query INTERSECT [ ALL | DISTINCT ] query
+ }
+ [ ORDER BY orderItem [, orderItem ]* ]
+ [ LIMIT { count | ALL } ]
+ [ OFFSET start { ROW | ROWS } ]
+ [ FETCH { FIRST | NEXT } [ count ] { ROW| ROWS } ]
+
+withItem:
+ name
+ ['(' column [, column ]* ')' ]
+ AS '(' query ')'
+
+orderItem:
+ expression [ ASC | DESC ]［ NULLS FIRST |NULLS LAST ］
+
+select:
+ SELECT [ ALL | DISTINCT]
+ { * | projectItem [, projectItem ]* }
+ FROM tableExpression
+ [ WHERE booleanExpression ]
+ [ GROUP BY { groupItem [, groupItem ]* }]
+ [ HAVING booleanExpression ]
+ [ WINDOW windowName AS windowSpec [,windowName AS windowSpec ]* ]
+
+selectWithoutFrom:
+ SELECT [ ALL | DISTINCT ]
+ { * | projectItem [, projectItem ]* }
+
+projectItem:
+ expression [ [ AS ] columnAlias ]
+ | tableAlias . *
+
+tableExpression:
+ tableReference [, tableReference ]*
+ | tableExpression [ NATURAL ]［( LEFT | RIGHT | FULL ) [ OUTER ] ］ JOIN tableExpression [ joinCondition ]
+
+joinCondition:
+ ON booleanExpression
+ | USING '(' column [, column ]* ')'
+
+tableReference:
+ tablePrimary
+ [ matchRecognize ]
+ [ [ AS ] alias [ '(' columnAlias [,columnAlias ]* ')' ] ]
+
+tablePrimary:
+ [ [ catalogName . ] schemaName . ] tableName
+ '(' TABLE [ [ catalogName . ] schemaName. ] tableName ')'
+ | [ LATERAL ] '(' query ')'
+ | UNNEST '(' expression ')' [ WITH ORDINALITY ]
+ | [ LATERAL ] TABLE '(' [ SPECIFIC ] functionName '(' expression [, expression ]*')' ')'
+
+values:
+ VALUES expression [, expression ]*
+
+groupItem:
+ expression
+ | '('')'
+ | '('expression [, expression ]* ')'
+ | GROUPING SETS '(' groupItem [, groupItem ]* ')'
+
+windowRef:
+ windowName
+ | windowSpec
+
+windowSpec:
+ [windowName ]
+ '('
+ [ ORDER BY orderItem [, orderItem ]* ]
+ [ PARTITION BY expression [, expression]* ]
+ [
+ RANGE numericOrIntervalExpression { PRECEDING | FOLLOWING }
+ | ROWS numericExpression { PRECEDING | FOLLOWING }
+ ]
+ ')'
+
+{% endhighlight %}
+
+## SELECT {#SELECT}
+
+### STATEMENT {#STATEMENT}
+```SELECT``` 用于从表中选取数据。```COUNT``` 用于统计数据。```DISTINCT``` 过滤掉重复的结果。```AS``` 用于给表或列起别名。```FROM``` 指定要查询的表。```JOIN``` 用于连接两个表以获取所需的数据。```WHERE``` 用于规定选择的标准。```LIKE``` 用于在 ```WHERE``` 子句中搜索列中的指定模式。```BETWEEN ... AND``` 选取介于两个值之间的数据范围。```AND``` 和 ```OR``` 用于基于一个以上的条件对记录进行过滤。```GROUP BY``` 按给定表达式对结果进行分组。```HAVING``` 用于分组后过滤行。```ORDER BY``` 用于对结果集进行排序，通常和 ```TOPN``` 一起使用。```LIMIT``` 用来限制查询返回的行数。
+
+例子：
+{% highlight Groff markup %}
+SELECT COUNT(*) FROM kylin_sales;
+
+SELECT COUNT(DISTINCT seller_id) FROM kylin_sales;
+
+SELECT seller_id, COUNT(1) FROM kylin_sales GROUP BY seller_id;
+
+SELECT lstg_format_name, SUM(price) AS gmv, COUNT(DISTINCT seller_id) AS dist_seller FROM kylin_sales WHERE lstg_format_name='FP-GTC' GROUP BY lstg_format_name HAVING COUNT(DISTINCT seller_id) > 50;
+
+SELECT lstg_format_name FROM kylin_sales INNER JOIN kylin_cal_dt AS kylin_cal_dt ON kylin_sales.part_dt = kylin_cal_dt.cal_dt WHERE NOT(lstg_format_name NOT LIKE '%ab%') GROUP BY lstg_format_name;
+
+SELECT kylin_cal_dt.cal_dt FROM kylin_sales INNER JOIN kylin_cal_dt AS kylin_cal_dt ON kylin_sales.part_dt = kylin_cal_dt.cal_dt WHERE kylin_cal_dt.cal_dt BETWEEN DATE '2013-01-01' AND DATE '2013-06-04';
+
+SELECT kylin_sales.lstg_format_name, SUM(kylin_sales.price) AS gmv, COUNT(*) AS trans_cnt FROM kylin_sales WHERE kylin_sales.lstg_format_name IS NULL GROUP BY kylin_sales.lstg_format_name HAVING SUM(price)>5000 AND COUNT(*)>72;
+
+SELECT kylin_sales.lstg_format_name, SUM(kylin_sales.price) AS gmv, COUNT(*) AS trans_cnt FROM kylin_sales WHERE kylin_sales.lstg_format_name IS NOT NULL GROUP BY kylin_sales.lstg_format_name HAVING SUM(price)>5000 OR COUNT(*)>20;
+
+SELECT lstg_format_name, SUM(price) AS gmv, COUNT(1) AS trans_cnt FROM kylin_sales WHERE lstg_format_name='FP-GTC' GROUP BY lstg_format_name ORDER BY lstg_format_name LIMIT 10;
+{% endhighlight %}
+
+### EXPRESSION {#EXPRESSION}
+在 ```SELECT``` 语句中的表达式。 可以使用 * 选择表中的所有列。
+例子：
+1. *
+2. 将 ID 作为值
+3. 值 + 1
+
+## SUBQUERY {#SUBQUERY}
+
+例子：
+{% highlight Groff markup %}
+SELECT cal_dt ,sum(price) AS sum_price FROM (SELECT kylin_cal_dt.cal_dt, kylin_sales.price FROM kylin_sales INNER JOIN kylin_cal_dt AS kylin_cal_dt ON kylin_sales.part_dt = kylin_cal_dt.cal_dt INNER JOIN kylin_category_groupings ON kylin_sales.leaf_categ_id = kylin_category_groupings.leaf_categ_id AND kylin_sales.lstg_site_id = kylin_category_groupings.site_id) t GROUP BY cal_dt;
+{% endhighlight %}
+
+## JOIN {#JOIN}
+
+### INNER JOIN {#INNERJOIN}
+在表中存在至少一个匹配时，```INNER JOIN``` 关键字返回行。
+例子：
+{% highlight Groff markup %}
+SELECT kylin_cal_dt.cal_dt, kylin_sales.price FROM kylin_sales INNER JOIN kylin_cal_dt AS kylin_cal_dt ON kylin_sales.part_dt**** = kylin_cal_dt.cal_dt;
+{% endhighlight %}
+
+### LEFT JOIN {#LEFTJOIN}
+使用 ```LEFT JOIN``` 关键字会从左表 (kylin_sales) 那里返回所有的行，即使在右表 (kylin_category_groupings) 中没有匹配的行。
+例子：
+{% highlight Groff markup %}
+SELECT seller_id FROM kylin_sales LEFT JOIN kylin_category_groupings AS kylin_category_groupings ON kylin_sales.leaf_categ_id = kylin_category_groupings.leaf_categ_id AND kylin_sales.lstg_site_id = kylin_category_groupings.site_id WHERE lstg_format_name='FP-GTC' GROUP BY seller_id LIMIT 20;
+{% endhighlight %}
+
+### CROSS JOIN {#CROSSJOIN}
+例子：
+{% highlight Groff markup %}
+SELECT * FROM (SELECT leaf_categ_id, SUM(price) AS sum_price FROM kylin_sales GROUP BY leaf_categ_id UNION ALL SELECT leaf_categ_id, SUM(price) AS sum_price FROM kylin_sales GROUP BY leaf_categ_id) CROSS JOIN (SELECT SUM(price) AS sum_price_2 FROM kylin_sales GROUP BY leaf_categ_id) UNION ALL SELECT CAST(1999 AS bigint) AS leaf_categ_id, 11.2 AS sum_price, 21.2 AS sum_price2 UNION ALL SELECT * FROM (SELECT leaf_categ_id, SUM(price) AS sum_price FROM kylin_sales GROUP BY leaf_categ_id UNI [...]
+{% endhighlight %}
+
+## UNION {#UNION}
+```UNION``` 操作符用于合并两个或多个 ```SELECT``` 语句的结果集。
+*注意* ```UNION``` 内部的 ```SELECT``` 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 ```SELECT``` 语句中的列的顺序必须相同。
+默认地，```UNION``` 操作符选取不同的值。如果允许重复的值，请使用 ```UNION ALL```。
+
+例子：
+{% highlight Groff markup %}
+SELECT SUM(price) AS x, seller_id, COUNT(*) AS y FROM kylin_sales WHERE part_dt < DATE '2012-08-01' GROUP BY seller_id UNION (SELECT SUM(price) AS x, seller_id, COUNT(*) AS y FROM kylin_sales WHERE part_dt > DATE '2012-12-01' GROUP BY seller_id);
+{% endhighlight %}
+
+## UNION ALL {#UNIONALL}
+```UNION ALL``` 命令和 ```UNION``` 命令几乎是等效的，不过 ```UNION ALL``` 命令会列出所有的值。
+
+例子：
+{% highlight Groff markup %}
+SELECT COUNT(trans_id) AS trans_id FROM kylin_sales AS test_a WHERE trans_id <> 1 UNION ALL SELECT COUNT(trans_id) AS trans_id FROM kylin_sales AS test_b;
+{% endhighlight %}
diff --git a/website/_docs/tutorial/kylin_grammar.md b/website/_docs/tutorial/kylin_grammar.md
new file mode 100644
index 0000000..4efec46
--- /dev/null
+++ b/website/_docs/tutorial/kylin_grammar.md
@@ -0,0 +1,190 @@
+---
+layout: docs
+title: SQL Grammar
+categories: tutorial
+permalink: /docs/tutorial/kylin_grammar.html
+---
+
+## Grammar
+
+[SELECT](#SELECT)
+ [STATEMENT](#STATEMENT)
+ [EXPRESSION](#EXPRESSION)
+[SUBQUERY](#SUBQUERY)
+[JOIN](#JOIN)
+ [INNER JOIN](#INNERJOIN)
+ [LEFT JOIN](#LEFTJOIN)
+ [CROSS JOIN](#CROSSJOIN)
+[UNION](#UNION)
+[UNION ALL](#UNIONALL)
+
+{% highlight Groff markup %}
+statement:
+| query
+
+query:
+ values
+ | WITH withItem [ , withItem ]* query
+ | {
+ select
+ | selectWithoutFrom
+ | query UNION [ ALL | DISTINCT ] query
+ | query INTERSECT [ ALL | DISTINCT ] query
+ }
+ [ ORDER BY orderItem [, orderItem ]* ]
+ [ LIMIT { count | ALL } ]
+ [ OFFSET start { ROW | ROWS } ]
+ [ FETCH { FIRST | NEXT } [ count ] { ROW| ROWS } ]
+
+withItem:
+ name
+ ['(' column [, column ]* ')' ]
+ AS '(' query ')'
+
+orderItem:
+ expression [ ASC | DESC ]［ NULLS FIRST |NULLS LAST ］
+
+select:
+ SELECT [ ALL | DISTINCT]
+ { * | projectItem [, projectItem ]* }
+ FROM tableExpression
+ [ WHERE booleanExpression ]
+ [ GROUP BY { groupItem [, groupItem ]* }]
+ [ HAVING booleanExpression ]
+ [ WINDOW windowName AS windowSpec [,windowName AS windowSpec ]* ]
+
+selectWithoutFrom:
+ SELECT [ ALL | DISTINCT ]
+ { * | projectItem [, projectItem ]* }
+
+projectItem:
+ expression [ [ AS ] columnAlias ]
+ | tableAlias . *
+
+tableExpression:
+ tableReference [, tableReference ]*
+ | tableExpression [ NATURAL ]［( LEFT | RIGHT | FULL ) [ OUTER ] ］ JOIN tableExpression [ joinCondition ]
+
+joinCondition:
+ ON booleanExpression
+ | USING '(' column [, column ]* ')'
+
+tableReference:
+ tablePrimary
+ [ matchRecognize ]
+ [ [ AS ] alias [ '(' columnAlias [,columnAlias ]* ')' ] ]
+
+tablePrimary:
+ [ [ catalogName . ] schemaName . ] tableName
+ '(' TABLE [ [ catalogName . ] schemaName. ] tableName ')'
+ | [ LATERAL ] '(' query ')'
+ | UNNEST '(' expression ')' [ WITH ORDINALITY ]
+ | [ LATERAL ] TABLE '(' [ SPECIFIC ] functionName '(' expression [, expression ]*')' ')'
+
+values:
+ VALUES expression [, expression ]*
+
+groupItem:
+ expression
+ | '('')'
+ | '('expression [, expression ]* ')'
+ | GROUPING SETS '(' groupItem [, groupItem ]* ')'
+
+windowRef:
+ windowName
+ | windowSpec
+
+windowSpec:
+ [windowName ]
+ '('
+ [ ORDER BY orderItem [, orderItem ]* ]
+ [ PARTITION BY expression [, expression]* ]
+ [
+ RANGE numericOrIntervalExpression { PRECEDING | FOLLOWING }
+ | ROWS numericExpression { PRECEDING | FOLLOWING }
+ ]
+ ')'
+
+{% endhighlight %}
+
+## SELECT {#SELECT}
+
+### STATEMENT {#STATEMENT}
+```SELECT``` chooses the data from the table. ```COUNT``` is used for quantitative statistics. ```DISTINCT``` filters out duplicate results. ```AS``` is used to alias tables or columns. ```FROM``` identifies the table being queried. ```JOIN``` is used to connect two tables to get the desired data. ```WHERE``` is used to specify the standard of selection. ```LIKE``` is used to search for a specified pattern in a column in a ```WHERE``` clause. ```BETWEEN ... AND``` is used to select a ran [...]
+
+Example:
+{% highlight Groff markup %}
+SELECT COUNT(*) FROM kylin_sales;
+
+SELECT COUNT(DISTINCT seller_id) FROM kylin_sales;
+
+SELECT seller_id, COUNT(1) FROM kylin_sales GROUP BY seller_id;
+
+SELECT lstg_format_name, SUM(price) AS gmv, COUNT(DISTINCT seller_id) AS dist_seller FROM kylin_sales WHERE lstg_format_name='FP-GTC' GROUP BY lstg_format_name HAVING COUNT(DISTINCT seller_id) > 50;
+
+SELECT lstg_format_name FROM kylin_sales INNER JOIN kylin_cal_dt AS kylin_cal_dt ON kylin_sales.part_dt = kylin_cal_dt.cal_dt WHERE NOT(lstg_format_name NOT LIKE '%ab%') GROUP BY lstg_format_name;
+
+SELECT kylin_cal_dt.cal_dt FROM kylin_sales INNER JOIN kylin_cal_dt AS kylin_cal_dt ON kylin_sales.part_dt = kylin_cal_dt.cal_dt WHERE kylin_cal_dt.cal_dt BETWEEN DATE '2013-01-01' AND DATE '2013-06-04';
+
+SELECT kylin_sales.lstg_format_name, SUM(kylin_sales.price) AS gmv, COUNT(*) AS trans_cnt FROM kylin_sales WHERE kylin_sales.lstg_format_name IS NULL GROUP BY kylin_sales.lstg_format_name HAVING SUM(price)>5000 AND COUNT(*)>72;
+
+SELECT kylin_sales.lstg_format_name, SUM(kylin_sales.price) AS gmv, COUNT(*) AS trans_cnt FROM kylin_sales WHERE kylin_sales.lstg_format_name IS NOT NULL GROUP BY kylin_sales.lstg_format_name HAVING SUM(price)>5000 OR COUNT(*)>20;
+
+SELECT lstg_format_name, SUM(price) AS gmv, COUNT(1) AS trans_cnt FROM kylin_sales WHERE lstg_format_name='FP-GTC' GROUP BY lstg_format_name ORDER BY lstg_format_name LIMIT 10;
+{% endhighlight %}
+
+### EXPRESSION {#EXPRESSION}
+An expression in a ```SELECT``` statement. All columns in a table may be selected using *.
+Example:
+1. *
+2. ID AS VALUE
+3. VALUE + 1
+
+## SUBQUERY {#SUBQUERY}
+
+Example:
+{% highlight Groff markup %}
+SELECT cal_dt ,sum(price) AS sum_price FROM (SELECT kylin_cal_dt.cal_dt, kylin_sales.price FROM kylin_sales INNER JOIN kylin_cal_dt AS kylin_cal_dt ON kylin_sales.part_dt = kylin_cal_dt.cal_dt INNER JOIN kylin_category_groupings ON kylin_sales.leaf_categ_id = kylin_category_groupings.leaf_categ_id AND kylin_sales.lstg_site_id = kylin_category_groupings.site_id) t GROUP BY cal_dt;
+{% endhighlight %}
+
+## JOIN {#JOIN}
+
+### INNER JOIN {#INNERJOIN}
+The ```INNER JOIN``` keyword returns rows when there is at least one match in the table.
+
+Example:
+{% highlight Groff markup %}
+SELECT kylin_cal_dt.cal_dt, kylin_sales.price FROM kylin_sales INNER JOIN kylin_cal_dt AS kylin_cal_dt ON kylin_sales.part_dt = kylin_cal_dt.cal_dt;
+{% endhighlight %}
+
+### LEFT JOIN {#LEFTJOIN}
+The ```LEFT JOIN``` keyword returns all rows from the left table (kylin_sales), even if there are no matching rows in the right table (kylin_category_groupings).
+
+Example:
+{% highlight Groff markup %}
+SELECT seller_id FROM kylin_sales LEFT JOIN kylin_category_groupings AS kylin_category_groupings ON kylin_sales.leaf_categ_id = kylin_category_groupings.leaf_categ_id AND kylin_sales.lstg_site_id = kylin_category_groupings.site_id WHERE lstg_format_name='FP-GTC' GROUP BY seller_id LIMIT 20;
+{% endhighlight %}
+
+### CROSS JOIN {#CROSSJOIN}
+Example:
+{% highlight Groff markup %}
+SELECT * FROM (SELECT leaf_categ_id, SUM(price) AS sum_price FROM kylin_sales GROUP BY leaf_categ_id UNION ALL SELECT leaf_categ_id, SUM(price) AS sum_price FROM kylin_sales GROUP BY leaf_categ_id) CROSS JOIN (SELECT SUM(price) AS sum_price_2 FROM kylin_sales GROUP BY leaf_categ_id) UNION ALL SELECT CAST(1999 AS bigint) AS leaf_categ_id, 11.2 AS sum_price, 21.2 AS sum_price2 UNION ALL SELECT * FROM (SELECT leaf_categ_id, SUM(price) AS sum_price FROM kylin_sales GROUP BY leaf_categ_id UNI [...]
+{% endhighlight %}
+
+## UNION {#UNION}
+The ```UNION``` operator is used to combine the result sets of two or more ```SELECT``` statements.
+*Note that* the ```SELECT``` statement inside ```UNION``` must have the same number of columns. Columns must also have similar data types. At the same time, the order of the columns in each ```SELECT``` statement must be the same.
+By default, the ```UNION``` operator picks a different value. If you allow duplicate values, use ```UNION ALL```.
+
+Example:
+{% highlight Groff markup %}
+SELECT SUM(price) AS x, seller_id, COUNT(*) AS y FROM kylin_sales WHERE part_dt < DATE '2012-08-01' GROUP BY seller_id UNION (SELECT SUM(price) AS x, seller_id, COUNT(*) AS y FROM kylin_sales WHERE part_dt > DATE '2012-12-01' GROUP BY seller_id);
+{% endhighlight %}
+
+## UNION ALL {#UNIONALL}
+The ```UNION ALL``` command is almost equivalent to the ```UNION``` command, but the ```UNION ALL``` command lists all values.
+
+Example:
+{% highlight Groff markup %}
+SELECT COUNT(trans_id) AS trans_id FROM kylin_sales AS test_a WHERE trans_id <> 1 UNION ALL SELECT COUNT(trans_id) AS trans_id FROM kylin_sales AS test_b;
+{% endhighlight %}
\ No newline at end of file
diff --git a/website/assets/css/docs.css b/website/assets/css/docs.css
index 917d825..9bdd776 100755
--- a/website/assets/css/docs.css
+++ b/website/assets/css/docs.css
@@ -152,6 +152,7 @@
}
#pjax > article > table td {
border: 1px solid black;
+ width: 200px;
}
#pjax > article > table tr {
height: 60px;