Mailing-List: contact common-commits-help@hadoop.apache.org; run by ezmlm
Precedence: bulk
Reply-To: common-dev@hadoop.apache.org
MIME-Version: 1.0
Content-Type: text/plain; charset="utf-8"
Content-Transfer-Encoding: quoted-printable
From: Apache Wiki <wikidiffs@apache.org>
To: Apache Wiki <wikidiffs@apache.org>
Date: Wed, 20 Apr 2011 00:05:47 -0000
Message-ID: <20110420000547.52313.68338@eos.apache.org>
Subject: 
 =?utf-8?q?=5BHadoop_Wiki=5D_Update_of_=22Hive/LanguageManual/UDF=22_by_Ph?=
 =?utf-8?q?iloVivero?=

Dear Wiki user,

You have subscribed to a wiki page or wiki category on "Hadoop Wiki" for ch=
ange notification.

The "Hive/LanguageManual/UDF" page has been changed by PhiloVivero.
The comment on this change is: How to reformulate aggregation functions usi=
ng subqueries.
http://wiki.apache.org/hadoop/Hive/LanguageManual/UDF?action=3Ddiff&rev1=3D=
60&rev2=3D61

--------------------------------------------------

  from log a lateral view json_tuple(a.appevent, 'eventid', 'eventname') b =
as f1, f2;
  }}}
  =

+ =3D=3D=3D GROUPing and SORTing on f(column) =3D=3D=3D
+ =

+ If you would like to GROUP BY or SORT BY a column on which you've applied=
 a function, like this:
+ =

+ {{{
+ select f(col) as fc, count(*) from table_name group by fc
+ }}}
+ =

+ You will get an error:
+ =

+ {{{
+ FAILED: Error in semantic analysis: line 1:86 Invalid Table Alias or Colu=
mn Reference fc
+ }}}
+ =

+ Because you are not able to GROUP BY or SORT BY a column on which a funct=
ion has been applied. However, you can reformulate this query with subqueri=
es:
+ =

+ {{{
+ select sq.col,count(*) from (select f(column) as col) sq group by sq.col
+ }}}
+=20