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 17:42:59 -0000
Message-ID: <20110420174259.37291.92166@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: Fixed reformulation, generalised it, specifi=
ed a case whose reformulation is unknown..
http://wiki.apache.org/hadoop/Hive/LanguageManual/UDF?action=3Ddiff&rev1=3D=
63&rev2=3D64

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

  =

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

- If you would like to GROUP BY or SORT BY a column on which you've applied=
 a function, like this:
+ A typical OLAP pattern is that you have a timestamp column and you want t=
o group by daily or other less granular date windows than by second. So you=
 might want to select concat(year(dt),month(dt)) and then group on that con=
cat(). But if you attempt to GROUP BY or SORT BY a column on which you've a=
pplied a function, like this:
  =

  {{{
  select f(col) as fc, count(*) from table_name group by fc
@@ -341, +341 @@

  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.fc,count(*) from (select f(col) as fc) sq group by sq.fc
+ select sq.fc,count(*) from (select f(col) as fc from table_name) sq group=
 by sq.fc
  }}}
  =

+ You will have to specify all the columns you want along with the f(col) i=
n both the subquery and the outside (which is obvious on retrospect). The g=
eneral formula for the f(col) reformulation is:
+ =

+ {{{
+ select sq.fc,col1,col2,...,colN,count(*) from
+   (select f(col) as fc,col1,col2,...,colN from table_name) sq
+  group by sq.fc,col1,col2,...,colN
+ }}}
+ =

+ Contact Tim Ellis (tellis) at RiotGames dot com if you would like to disc=
uss this in further detail.
+=20