[jira] [Commented] (KYLIN-3703) get negative value when query kylin

["ASF GitHub Bot (JIRA)" <ji...@apache.org>]

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

ASF GitHub Bot commented on KYLIN-3703:
---------------------------------------

TempleZhou commented on pull request #448: KYLIN-3703 get negative value when query kylin
URL: https://github.com/apache/kylin/pull/448
 
 
   
 
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on GitHub and use the
URL above to go to the specific comment.
 
For queries about this service, please contact Infrastructure at:
users@infra.apache.org


> get negative value when query  kylin
> ------------------------------------
>
>                 Key: KYLIN-3703
>                 URL: https://issues.apache.org/jira/browse/KYLIN-3703
>             Project: Kylin
>          Issue Type: Bug
>    Affects Versions: v2.5.1
>            Reporter: shengxiaopeng
>            Assignee: Temple Zhou
>            Priority: Major
>         Attachments: KYLIN-3703.master.001.patch
>
>
> version: Kylin 2.5.1 
> There is a cube built in real time, one metric is average time consuming, *the source data does not have a negative value, but the negative value occurs by hourly aggregation*, and the aggregation is normal by minute
>  
> *1. Metric definition*
> |Name|Expression|Parameters|Return Type|
> |SUM_FS|SUM|Value:APM_FIRSTSCREEN_WEB.PRF_PL_FS, Type:column|bigint|
>  
>  *2.Aggregate sql and results by hour，then negative measure value occurs*
>  ```
>  select 
>  hour_start
>  ,count(*) as amount
>  ,sum(prf_pl_fs) as sum_pl_fs
>  from DY_APM.APM_FIRSTSCREEN_WEB
>  where 1=1 and minute_start >= '2018-11-23 20:00:00' and minute_start < '2018-11-23 21:00:00' 
>  group by hour_start 
>  order by hour_start asc 
>  limit 15000
>  ```
>   
> |hour_start|amount|sum_pl_fs|
> |2018-11-23 20:00:00|974466|-1317398890|
> *3.Aggregate sql and results by minute*
>  ```
>  select 
>  minute_start
>  ,count(*) as amount
>  ,sum(prf_pl_fs) as sum_pl_fs
>  from DY_APM.APM_FIRSTSCREEN_WEB
>  where 1=1 and minute_start >= '2018-11-23 20:00:00' and minute_start < '2018-11-23 21:00:00' 
>  group by minute_start 
>  order by minute_start asc 
>  limit 15000
>  ```
> |hour_start|amount|sum_pl_fs|
> |2018-11-23 20:21:00|2629|8107124|
> |2018-11-23 20:22:00|12216|36558869|
> |2018-11-23 20:23:00|12800|38401450|
> |...|略|...|
>  
> 4. I guess this should be caused by crossing the border. 
> I use the data of each minute to get the hourly data, and the total time is *2977568406*, which is far from the scope of bigint.
>  ```
>  select 
>  sum(t.amount) amount
>  ,sum(t.sum_pl_fs) sum_pl_fs
>  ,sum(t.sum_pl_fs) / sum(t.amount) as avg_pl_fs
>  from 
>  (
>  select 
>  minute_start
>  ,count(*) as amount
>  ,sum(prf_pl_fs) as sum_pl_fs
>  from DY_APM.APM_FIRSTSCREEN_WEB
>  where 1=1 and minute_start >= '2018-11-23 20:00:00' and minute_start < '2018-11-23 21:00:00' 
>  group by minute_start 
>  order by minute_start asc 
>  limit 15000
>  ) as t
>  ```
> |amount|sum_pl_fs|avg_pl_fs|
> |974466|2977568406|3055|
>  
> After verification, when cast 2977568406 to int ,then value is -1317398890
>  
>  
> *Excuse me, is this question a problem with my configuration or query? How should it be solved?*
>  
>  



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)