You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@kylin.apache.org by "shengxiaopeng (JIRA)" <ji...@apache.org> on 2018/11/26 08:05:00 UTC
[jira] [Created] (KYLIN-3703) get negative value when query kylin
shengxiaopeng created KYLIN-3703:
------------------------------------
Summary: get negative value when query kylin
Key: KYLIN-3703
URL: https://issues.apache.org/jira/browse/KYLIN-3703
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
|Name|Expression|Parameters|Return Type|
|SUM_FS|SUM|Value:APM_FIRSTSCREEN_WEB.PRF_PL_FS, Type:column|bigint|
#
## 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 >= '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|
#
## 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|
|...|略|...|
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|
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)