[ 
https://issues.apache.org/jira/browse/FLINK-5584?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15831133#comment-15831133
 ] 

ASF GitHub Bot commented on FLINK-5584:
---------------------------------------

Github user sunjincheng121 commented on the issue:

    https://github.com/apache/flink/pull/3175
  
    Hi, @hongyuhong , thank your for your job.  Agree with @wuchong 's 
comments. I add same database OVER example for you:
    ```
    Example data:
    select * from  PeopleInfo
    ID         Name                 Gender                 Score
    6   LiHuan          Man             80
    7   LiHuan          Man             90
    8   LiMing          Man             56
    9   LiMing          Woman           60
    10  WangHua         Woman           80
      
    ```
    ```
     --Simple case 
    SELECT name, gender, count(name) OVER () AS num FROM PeopleInfo
    
    name                gender                 num
    LiHuan              Man             5
    LiHuan              Man             5
    LiMing              Man             5
    LiMing              Woman           5
    WangHua        Woman                5
    
    ```
    ```
      --With ORDER BY case
    SELECT name,gender,score ROW_NUMBER() OVER (ORDER BY score ASC) AS num FROM 
PeopleInfo
    
    name                gender                   score   num
    LiMing              Man             56      1
    LiMing              Woman           60      2
    WangHua             Woman           80      3
    LiHuan              Man             80      4
    LiHuan              Man             90      5
    ```
          
       ```
    --With both PARTITION BY and  ORDER BY case
    SELECT [name],gender,score, ROW_NUMBER() OVER(PARTITION BY  Gender ORDER BY 
score ASC) as num
    FROM PeopleInfo;
    
    name                gender                   score   num
    LiMing              Man             56      1
    LiHuan              Man             80      2
    LiHuan              Man             90      3
    LiMing              Woman           60      1
    WangHua             Woman           80      2
    ```
      ```
     --With ROWS  PRECEDING and CURRENT ROW case
    SELECT name, gender, score, sum(score) OVER (PARTITION BY gender ORDER BY
       id ASC  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) as  sum
    FROM PeopleInfo
    
    name                 gender                 score     sum
    LiHuan              Man             80      80
    LiHuan              Man             90      170
    LiMing              Man             56      226
    LiMing              Woman           60      60
    WangHua             Woman           80      140
    
    SELECT name, gender, score, sum(score) OVER (PARTITION BY Gender ORDER BY
       id ASC  ROWS BETWEEN 1 PRECEDING AND CURRENT ROW ) as sum
    FROM PeopleInfo
    
    name                 gender                score     sum
    LiHuan              Man             80      80
    LiHuan              Man             90      170
    LiMing              Man             56      146
    LiMing              Woman           60      60
    WangHua             Woman           80      140
    
    ```                
      ```
    --With ROWS FOLLOWING case   
    
    SELECT id, name, gender, score, sum(score) OVER (PARTITION BY Gender ORDER 
BY
       id ASC  ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) as sum
    FROM dbo.PeopleInfo
    
    id           name                 gender                score      sum
    6   LiHuan          Man             80      170
    7   LiHuan          Man             90      226
    8   LiMing          Man             56      146
    9   LiMing          Woman           60      140
    10  WangHua         Woman           80      140
    
    SELECT id,name, gender, score, sum(score) OVER (PARTITION BY gender ORDER BY
     id ASC  ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING ) as sum
    FROM PeopleInfo
     
    id           name                 gender                 score      sum
    6   LiHuan          Man             80      170
    7   LiHuan          Man             90      226
    8   LiMing          Man             56      226
    9   LiMing          Woman           60      140
    10  WangHua         Woman           80      140
    
    SELECT id, name, gender, score,sum(score) OVER (PARTITION BY gender ORDER BY
       id ASC  ROWS BETWEEN 1 PRECEDING AND UNBOUNDED FOLLOWING ) as sum
    FROM PeopleInfo
    
    id           name                 gender                  score      sum
    8   LiMing          Man             56      146
    7   LiHuan          Man             90      226
    6   LiHuan          Man             80      226
    10  WangHua         Woman           80      140
    9   LiMing          Woman           60      140
    ```
    Thank you , SunJincheng.


> Support Sliding-count row-window on streaming sql
> -------------------------------------------------
>
>                 Key: FLINK-5584
>                 URL: https://issues.apache.org/jira/browse/FLINK-5584
>             Project: Flink
>          Issue Type: New Feature
>          Components: Table API & SQL
>            Reporter: Yuhong Hong
>
> Calcite has already support sliding-count row-window, the grammar look like:
> select sum(amount) over (rows 10 preceding) from Order;
> select sum(amount) over (partition by user rows 10 preceding) from Order;
> And it will parse the sql as a LogicalWindow relnode, the logical Window 
> contains aggregate func info and window info, it's similar to Flink 
> LogicalWIndowAggregate, so we can add an convert rule to directly convert 
> LogicalWindow into DataStreamAggregate relnode, and if Calcite support more 
> grammar, we can extend the convert rule.



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

Reply via email to