abstractdog commented on a change in pull request #2099:
URL: https://github.com/apache/hive/pull/2099#discussion_r616521343
##########
File path: ql/src/test/results/clientpositive/llap/windowing_udaf.q.out
##########
@@ -503,7 +503,7 @@ alice brown 25.258749999999996
alice brown 25.529374999999998
alice brown 25.63012987012987
alice brown 26.472439024390237
-alice brown 27.100638297872322
+alice brown 27.27881720430106
Review comment:
sure, I confirmed this manually, and I found that the new, vectorized
average is correct (27.27881720430106)
here is how I checked:
1. table and original query
```
create table over10k_n4(
t tinyint,
si smallint,
i int,
b bigint,
f float,
d double,
bo boolean,
s string,
ts timestamp,
`dec` decimal,
bin binary)
row format delimited
fields terminated by '|';
load data local inpath '../../data/files/over10k' into table over10k_n4;
select t, f, d, avg(d) over (partition by t order by f) a from over10k_n4
order by s, a limit 100;
```
2. the original query is problematic to represent the problem, doesn't
contain all the important rows due to limit 100, but here is a cleaner scenario
```
select t, f, d, avg(d) over (partition by t order by f) a from over10k_n4
where t = 114;
```
result:
```
| 114 | 95.01 | 13.77 | 27.31472527472526 |
| 114 | 95.09 | 45.37 | 27.510978260869546 |
| 114 | 97.94 | 5.92 | 27.27881720430106 | <--------- this is the
changed value
| 114 | 97.94 | 10.53 | 27.100638297872322 |
+------+--------+--------+---------------------+
```
so we can see the avg in the row before the last row is 27.2788, how can we
check this?
let's calculate the sum and count for this row to get the average:
1. sum (sum of all rows, the subtract the last one)
```
select sum(d) - 10.53 from over10k_n4 where t = 114;
2536.9299999999994
```
2. count (count all, we can subtract 1 while calculating the average in the
next step)
```
select count(d) from over10k_n4 where t = 114;
94
```
3. average:
```
2536.9299999999994 / 93 = 27.2788172043
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
