hive-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Zoltan Haindrich (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (HIVE-17454) Hive - MAX Window function does not accept more than 1 sort key and does not work as expected with rows window clause
Date Mon, 18 Sep 2017 14:35:00 GMT

    [ https://issues.apache.org/jira/browse/HIVE-17454?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16170092#comment-16170092
] 

Zoltan Haindrich commented on HIVE-17454:
-----------------------------------------

Hello, I was planning to look into this...but I'm not yet understand the goal of the query;
could you give a smaller example possibly?
By using "unbounded preceeding" you would like to get the maximum so-far - am I correct?

hmm...you've written "rows unbounded preceding and unbounded following" but your query only
contains unbounded preceding...

Could you give a sample which can be executed? 
{code}
create table t (a int,b int);
insert into t values (1,1),(1,2),(1,3),(2,4),(2,5),(2,6),(3,7),(3,8),(3,9);
select a,b,sum(b) over (partition by a order by b rows unbounded preceding) from t;
{code}


> Hive - MAX Window function does not accept more than 1 sort key and does not work as
expected with rows window clause
> ---------------------------------------------------------------------------------------------------------------------
>
>                 Key: HIVE-17454
>                 URL: https://issues.apache.org/jira/browse/HIVE-17454
>             Project: Hive
>          Issue Type: Bug
>          Components: Hive
>    Affects Versions: 1.2.0
>            Reporter: Krish B
>            Priority: Minor
>
> Hi,
> I see MAX window function is throwing error if I use more than one order by clause. But
when I use window clause (rows), it works fine but the results are not as expected as shown
below.
> From the data shown below, I was expecting max_individual_id should be 42562 for all
the individuals under same HICN_NBR but I am getting different results. I tried "rows unbounded
preceding and unbounded following" but results are still not as expected. Please let me know
if this is an issue?
> Query:
> select individual_id, MAX(individual_id) over (partition by HICN_NBR order by eff_dt
desc, cncl_dt desc, individual_id desc rows unbounded preceding) as max_individual_id,
>        hicn_nbr, eff_dt, cncl_dt
> from MFW_MeasureMembership_BOTH MM 
> where Remove_ASH <> 1 and Remove_AGB <> 1 and Remove_FARM_151 <> 1
and Remove_JCA <> 1 and Remove_CVTY <> 1
>       and trim(HICN_NBR)='15248461314T';



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

Mime
View raw message