Raymond Wong created DRILL-5916:
-----------------------------------
Summary: Drill document window function example on LAST_VALUE is
incorrect
Key: DRILL-5916
URL: https://issues.apache.org/jira/browse/DRILL-5916
Project: Apache Drill
Issue Type: Bug
Components: Documentation
Affects Versions: 1.11.0
Reporter: Raymond Wong
Priority: Minor
The top and bottom review count example query result is showing incorrect
values for the LAST_VALUE column.
([https://drill.apache.org/docs/analyzing-data-using-window-functions/] )
The LAST_VALUE column should have the same value as the review count of each
row because the default Window Frame is RANGE BETWEEN UNBOUNDED PRECEDING AND
*CURRENT ROW*.
Query result using 2017 yelp data set.
{quote}
SELECT name, city, review_count,
FIRST_VALUE(review_count)
OVER(PARTITION BY city ORDER BY review_count DESC) AS top_review_count,
LAST_VALUE(review_count)
OVER(PARTITION BY city ORDER BY review_count DESC) AS bottom_review_count
FROM dfs.yelp.`yelp_academic_dataset_business.json`
LIMIT 30
||name ||city
||review_count ||top_review_count||bottom_review_count ||
|Lululemon Athletica | |5
|5 |5 |
|Aberdour Castle |Aberdour |4
|4 |4 |
|Cupz N' Crepes |Ahwatukee |236
|236 |236 |
|My Wine Cellar |Ahwatukee |158
|236 |158 |
|Florencia Pizza Bistro |Ahwatukee |129
|236 |129 |
|Barro's Pizza |Ahwatukee |62
|236 |62 |
|Kathy's Alterations |Ahwatukee |30
|236 |30 |
|Hertz Rent A Car |Ahwatukee |26
|236 |26 |
|Active Kids Pediatrics |Ahwatukee |18
|236 |18 |
|Dental by Design |Ahwatukee |18
|236 |18 |
|Desert Dog Pet Care |Ahwatukee |10
|236 |10 |
|McDonald's |Ahwatukee |7
|236 |7 |
|U-Haul |Ahwatukee |6
|236 |6 |
|Sprinkler Detective |Ahwatukee |5
|236 |5 |
|Hi-Health |Ahwatukee |4
|236 |4 |
|Healthy and Clean Living Environments |Ahwatukee |4
|236 |4 |
|Designs By Christa |Ahwatukee |4
|236 |4 |
{quote}
Changing the LAST_VAULE's Window Frame to RANGE BETWEEN UNBOUNDED PRECEDING AND
*UNBOUNDED FOLLOWING*.
{quote}
SELECT name, city, review_count,
FIRST_VALUE(review_count)
OVER(PARTITION BY city ORDER BY review_count DESC) AS top_review_count,
LAST_VALUE(review_count)
OVER(PARTITION BY city ORDER BY review_count DESC RANGE BETWEEN UNBOUNDED
PRECEDING AND UNBOUNDED FOLLOWING) AS bottom_review_count
FROM dfs.yelp.`yelp_academic_dataset_business.json`
LIMIT 30
;
||name ||city
||review_count ||top_review_count ||bottom_review_count ||
|Lululemon Athletica | |5
|5 |5 |
|Aberdour Castle |Aberdour |4
|4 |4 |
|Cupz N' Crepes |Ahwatukee |236
|236 |4 |
|My Wine Cellar |Ahwatukee |158
|236 |4 |
|Florencia Pizza Bistro |Ahwatukee |129
|236 |4 |
|Barro's Pizza |Ahwatukee |62
|236 |4 |
|Kathy's Alterations |Ahwatukee |30
|236 |4 |
|Hertz Rent A Car |Ahwatukee |26
|236 |4 |
|Active Kids Pediatrics |Ahwatukee |18
|236 |4 |
|Dental by Design |Ahwatukee |18
|236 |4 |
|Desert Dog Pet Care |Ahwatukee |10
|236 |4 |
|McDonald's |Ahwatukee |7
|236 |4 |
|U-Haul |Ahwatukee |6
|236 |4 |
|Sprinkler Detective |Ahwatukee |5
|236 |4 |
|Hi-Health |Ahwatukee |4
|236 |4 |
|Healthy and Clean Living Environments |Ahwatukee |4
|236 |4 |
|Designs By Christa |Ahwatukee |4
|236 |4 |
{quote}
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
