drill-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Raymond Wong (JIRA)" <j...@apache.org>
Subject [jira] [Created] (DRILL-5916) Drill document window function example on LAST_VALUE is incorrect
Date Tue, 31 Oct 2017 14:33:00 GMT
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)

Mime
View raw message