shengxiaopeng created KYLIN3703:

Summary: get negative value when query kylin
Key: KYLIN3703
URL: https://issues.apache.org/jira/browse/KYLIN3703
Project: Kylin
Issue Type: Bug
Reporter: shengxiaopeng
Fix For: v2.5.1
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
Metric definition
NameExpressionParametersReturn Type
SUM_FSSUMValue:APM_FIRSTSCREEN_WEB.PRF_PL_FS, Type:columnbigint
#
## Aggregate sql and results by hour
```
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 >= '20181123 20:00:00' and minute_start < '20181123
21:00:00'
group by hour_start
order by hour_start asc
limit 15000
```
hour_startamountsum_pl_fs
20181123 20:00:009744661317398890
#
## 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 >= '20181123 20:00:00' and minute_start < '20181123
21:00:00'
group by minute_start
order by minute_start asc
limit 15000
```
hour_startamountsum_pl_fs
20181123 20:21:0026298107124
20181123 20:22:001221636558869
20181123 20:23:001280038401450
...略...
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 >= '20181123 20:00:00' and minute_start < '20181123
21:00:00'
group by minute_start
order by minute_start asc
limit 15000
) as t
```
amountsum_pl_fsavg_pl_fs
97446629775684063055
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)
