{"action":"created","issue":{"url":"https://api.github.com/repos/apache/spark/issues/12147","repository_url":"https://api.github.com/repos/apache/spark","labels_url":"https://api.github.com/repos/apache/spark/issues/12147/labels{/name}","comments_url":"https://api.github.com/repos/apache/spark/issues/12147/comments","events_url":"https://api.github.com/repos/apache/spark/issues/12147/events","html_url":"https://github.com/apache/spark/pull/12147","id":145593410,"number":12147,"title":"[SPARK-14361][SQL]Window function exclude clause ","user":{"login":"xwu0226","id":14225992,"avatar_url":"https://avatars.githubusercontent.com/u/14225992?v=3","gravatar_id":"","url":"https://api.github.com/users/xwu0226","html_url":"https://github.com/xwu0226","followers_url":"https://api.github.com/users/xwu0226/followers","following_url":"https://api.github.com/users/xwu0226/following{/other_user}","gists_url":"https://api.github.com/users/xwu0226/gists{/gist_id}","starred_url":"https://api.github.com/users/xwu0226/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/xwu0226/subscriptions","organizations_url":"https://api.github.com/users/xwu0226/orgs","repos_url":"https://api.github.com/users/xwu0226/repos","events_url":"https://api.github.com/users/xwu0226/events{/privacy}","received_events_url":"https://api.github.com/users/xwu0226/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":4,"created_at":"2016-04-04T06:09:10Z","updated_at":"2016-04-04T15:08:20Z","closed_at":null,"pull_request":{"url":"https://api.github.com/repos/apache/spark/pulls/12147","html_url":"https://github.com/apache/spark/pull/12147","diff_url":"https://github.com/apache/spark/pull/12147.diff","patch_url":"https://github.com/apache/spark/pull/12147.patch"},"body":"#### What changes were proposed in this pull request?\r\n\r\nThe current Spark SQL does not support the `exclusion` clause, which is part of ANSI SQL2003’s `Window` syntax. For example, IBM Netezza fully supports it as shown in the [document web link](https://www.ibm.com/support/knowledgecenter/SSULQD_7.1.0/com.ibm.nz.dbu.doc/c_dbuser_window_aggregation_family_syntax.html). This PR is to fill the gap. \r\n\r\n\r\n\r\n##### Introduction\r\n\r\nBelow is the ANSI SQL2003’s `Window` syntax:\r\n```\r\nFUNCTION_NAME(expr) OVER {window_name | (window_specification)}\r\nwindow_specification ::= [window_name] [partitioning] [ordering] [framing]\r\npartitioning ::= PARTITION BY value[, value...] [COLLATE collation_name]\r\nordering ::= ORDER [SIBLINGS] BY rule[, rule...]\r\nrule ::= {value | position | alias} [ASC | DESC] [NULLS {FIRST | LAST}]\r\nframing ::= {ROWS | RANGE} {start | between} [exclusion]\r\nstart ::= {UNBOUNDED PRECEDING | unsigned-integer PRECEDING | CURRENT ROW}\r\nbetween ::= BETWEEN bound AND bound\r\nbound ::= {start | UNBOUNDED FOLLOWING | unsigned-integer FOLLOWING}\r\nexclusion ::= {EXCLUDE CURRENT ROW | EXCLUDE GROUP | EXCLUDE TIES | EXCLUDE NO OTHERS}\r\n```\r\nExclusion clause can be used to exclude certain rows from the window framing when calculating window aggregation function (e.g. AVG, SUM, MAX, MIN, COUNT, etc)  related to current row. Types of window functions that are not supported are listed below:\r\n\r\n1. Offset functions, such as lead(), lag()\r\n2. Ranking functions, such as rank(), dense_rank(), percent_rank(), cume_dist, ntile()\r\n3. Row number function, such as row_number()\r\n\r\n##### Definition\r\nSyntax | Description\r\n------------ | -------------\r\nEXCLUDE CURRENT ROW | Specifies excluding the current row.\r\nEXCLUDE GROUP | Specifies excluding the current row and all rows that are tied with it. Ties occur when there is a match on the order column or columns.\r\nEXCLUDE NO OTHERS | Specifies not excluding any rows. This value is the default if you specify no exclusion.\r\nEXCLUDE TIES | Specifies excluding all rows that are tied with the current row (peer rows), but retaining the current row.\r\n\r\n##### Use-case Examples:\r\n\r\n- Let's say you want to find out for every employee, where is his/her salary at compared to the average salary of those within the same department and whose ages are within 5 years younger and older. The query could be: \r\n```SQL\r\nSELECT NAME, DEPT_ID, SALARY, AGE, AVG(SALARY) AS AVG_WITHIN_5_YEAR\r\n    OVER(PARTITION BY DEPT_ID \r\n         ORDER BY AGE \r\n         RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING \r\n         EXCLUDE CURRENT ROW) \r\nFROM EMPLOYEE \r\n```\r\n\r\n- Let's say you want to compare every customer's yearly purchase with other customers' average yearly purchase who are at different age group from the current customer. The query could be: \r\n```SQL\r\nSELECT CUST_NAME, AGE, PROD_CATEGORY, YEARLY_PURCHASE, AVG(YEARLY_PURCHASE) \r\n    OVER(PARTITION BY PROD_CATEGORY \r\n         ORDER BY AGE \r\n         RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUND FOLLOWING \r\n         EXCLUDE GROUP) \r\nFROM CUSTOMER_PURCHASE_SUM \r\n```\r\n\r\n##### Implementation \r\n**catalyst.parser.SqlBase.g4**\r\n\r\n- add excludeClause definition and append to each case in windowFrame\r\n\r\n- add excludeType definition for CURRENT ROW,GOUP, TIES, NO OTHERS\r\n\r\n**catalyst.parser.AstBuilder.scala**\r\n\r\n- parse excludeClause to generate `ExcludeClause `object to set into `SpecifiedWindowFrame`\r\n\r\n**catalyst.expressions.windowExpressions.scala**\r\n\r\n- add `ExcludeClause `and `ExcludeType`\r\n\r\n- add `ExcludeClause `object as a parameter to `SpecifiedWindowFrame`\r\n\r\n- validate EXCLUDE GROUP/TIES requires ordered window frame\r\n\r\n**catalyst.analysis.Analyzer.scala**\r\n\r\n- validate exclude clause does not support `RowNumberLike`, `RankLike `and `OffsetWindowFunction`\r\n\r\n**execution.Window.scala**\r\n\r\n- implement the exclusion logic in sliding frame, expanding frame, shrinking frame and whole partition frame\r\n\r\n**Some Test case examples:**\r\n**DDL and DML:**\r\nsql(\"create table table1 (col1 int, col2 int, col3 int)\")\r\n      sql(\"insert into table1 select 6, 12, 10\")\r\n      sql(\"insert into table1 select 6, 11, 4\")\r\n      sql(\"insert into table1 select 6, 13, 11\")\r\n      sql(\"insert into table1 select 6, 9, 10\")\r\n      sql(\"insert into table1 select 6, 15, 8\")\r\n      sql(\"insert into table1 select 6, 10, 1\")\r\n      sql(\"insert into table1 select 6, 15, 8\")\r\n      sql(\"insert into table1 select 6, 7, 4\")\r\n      sql(\"insert into table1 select 6, 7, 8\")\r\n\r\n**Queries:**\r\n```\r\n// sliding frame with exclude current row\r\nselect col1, col2, col3, sum(col2) \r\n    over (partition by col1 \r\n       order by col3 \r\n       rows between 2 preceding and 2 following \r\n       exclude current row) \r\nfrom table1 where col1 = 6\r\n```\r\n##### Results\r\ncol1 | col2 | col3 | sum(col2)\r\n----- | ------ | ----- | -------\r\n6 | 10 | 1 | 18\r\n6 | 11 | 4 | 32\r\n**6** | **7** | **4** | **51**\r\n6 | 15 | 8 | 40\r\n6 | 15 | 8 | 40\r\n6 | 7 | 8 | 51\r\n6 | 12 | 10 | 44\r\n6 | 9 | 10 | 32\r\n6 | 6 | 13 | 11 | 21\r\n\r\nIn this case, for example, if we are looking at the (6, 7, 4) row, the frame around this current row has 5 rows (6, 10, 1), (6,11, 4), **(6, 7, 4)**, (6, 15, 8), (6, 15, 8), including the current row. With the `exclude current row` clause, then the calculation of sum(col2) is on the other 4 rows, which is 10 + 11 + 15 + 15 = 51. \r\n\r\n```\r\n//expanding frame with exclude current row\r\nselect col1, col2, col3, sum(col2) \r\n    over (partition by col1 \r\n            order by col3 \r\n            rows between unbounded preceding and current row \r\n            exclude group) \r\nfrom table1 where col1 = 6\r\n```\r\n##### Results\r\ncol1 | col2 | col3 | sum(col2)\r\n----- | ------ | ----- | -------\r\n6 | 10 | 1 | null\r\n6 | 11 | 4 | 10\r\n**6** | **7** | **4** | **10**\r\n6 | 15 | 8 | 28\r\n6 | 15 | 8 | 28\r\n6 | 7 | 8 | 28\r\n6 | 12 | 10 | 65\r\n6 | 9 | 10 | 65\r\n6 | 13 | 11 | 86\r\n\r\nIn this case, for example, if we are looking at row (6, 7, 4), the frame around this current row has 3 rows (6, 10, 1), (6, 11, 4) and (6, 7, 4). Then, with `exclude group` clause, rows (6, 11, 4) and (6, 7, 4) will be excluded because the values of order-by column `col3` on these 2 rows are the same, thus in the same group. Therefore, the sum(col2) at current frame is 10. \r\n\r\nFor testcases are located in hive/execution/**WindowFunctionExcludeSuite.scala**\r\n\r\n#### How was this patch tested?\r\n1. created unit tests to cover all framing cases, each with different exclude types. \r\n2. run regtest over catalyst, sql, and hive tests under SQL component."},"comment":{"url":"https://api.github.com/repos/apache/spark/issues/comments/205341062","html_url":"https://github.com/apache/spark/pull/12147#issuecomment-205341062","issue_url":"https://api.github.com/repos/apache/spark/issues/12147","id":205341062,"user":{"login":"xwu0226","id":14225992,"avatar_url":"https://avatars.githubusercontent.com/u/14225992?v=3","gravatar_id":"","url":"https://api.github.com/users/xwu0226","html_url":"https://github.com/xwu0226","followers_url":"https://api.github.com/users/xwu0226/followers","following_url":"https://api.github.com/users/xwu0226/following{/other_user}","gists_url":"https://api.github.com/users/xwu0226/gists{/gist_id}","starred_url":"https://api.github.com/users/xwu0226/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/xwu0226/subscriptions","organizations_url":"https://api.github.com/users/xwu0226/orgs","repos_url":"https://api.github.com/users/xwu0226/repos","events_url":"https://api.github.com/users/xwu0226/events{/privacy}","received_events_url":"https://api.github.com/users/xwu0226/received_events","type":"User","site_admin":false},"created_at":"2016-04-04T15:08:20Z","updated_at":"2016-04-04T15:08:20Z","body":"@hvanhovell , Thanks for the suggestions! I will look into this today.. "}}