Return-Path: X-Original-To: apmail-hive-user-archive@www.apache.org Delivered-To: apmail-hive-user-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id C37F19F1F for ; Fri, 13 Jan 2012 04:03:06 +0000 (UTC) Received: (qmail 15356 invoked by uid 500); 13 Jan 2012 04:02:33 -0000 Delivered-To: apmail-hive-user-archive@hive.apache.org Received: (qmail 14952 invoked by uid 500); 13 Jan 2012 04:01:53 -0000 Mailing-List: contact user-help@hive.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user@hive.apache.org Delivered-To: mailing list user@hive.apache.org Received: (qmail 14925 invoked by uid 99); 13 Jan 2012 04:01:46 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 13 Jan 2012 04:01:46 +0000 X-ASF-Spam-Status: No, hits=1.5 required=5.0 tests=HTML_MESSAGE,RCVD_IN_DNSWL_LOW,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (nike.apache.org: domain of xiaobinshe@gmail.com designates 74.125.82.176 as permitted sender) Received: from [74.125.82.176] (HELO mail-we0-f176.google.com) (74.125.82.176) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 13 Jan 2012 04:01:39 +0000 Received: by werm1 with SMTP id m1so96566wer.35 for ; Thu, 12 Jan 2012 20:01:19 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :content-type; bh=wTE2dpHFVBb5rD1fvBkWnXsQnSmj+marpgkYqtHMcqY=; b=W1lKdmirlmDJikazgDDxJ3H+m4NJVm0dCRG8FYSbT2aisNYaE1vyGb+l87xngS4ue4 tjFNd8IujKCWaa/U5BUi/jyVPytDfOH/5dIi4l5V2kmyuxIPfH+hAFyhe2b4eygjQvbv ojX3fdUkvR3DwFyRa0vpzviARpjx6HKsBUhm4= MIME-Version: 1.0 Received: by 10.216.211.97 with SMTP id v75mr1388092weo.29.1326427279445; Thu, 12 Jan 2012 20:01:19 -0800 (PST) Received: by 10.223.101.130 with HTTP; Thu, 12 Jan 2012 20:01:19 -0800 (PST) In-Reply-To: <4F0EEC10.5090509@affinitas.de> References: <4F0EEC10.5090509@affinitas.de> Date: Fri, 13 Jan 2012 12:01:19 +0800 Message-ID: Subject: Re: how to avoid scan the same table multi times? From: Xiaobin She To: user@hive.apache.org, =?GB2312?B?2dzP/rHy?= Content-Type: multipart/alternative; boundary=0016e6d99b2dcfd10504b660ea0e X-Virus-Checked: Checked by ClamAV on apache.org --0016e6d99b2dcfd10504b660ea0e Content-Type: text/plain; charset=GB2312 Content-Transfer-Encoding: quoted-printable to Martin, Mark and Edward, thank you for your advices, I will try it out. And to Martin, by "appropriate data format", do you mean something like "2012011202" ? thanks! xiaobin =D4=DA 2012=C4=EA1=D4=C212=C8=D5 =CF=C2=CE=E710:20=A3=ACMartin Kuhn =D0=B4=B5=C0=A3=BA > Hi there, > > > Select count(*), count(distinct u), type from t group by type where > plat=3D1 and dt=3D=A1=B12012-1-12-02=A1=B1 > > Select count(*), count(distinct u), type from t where (type =3D2 or typ= e > =3D6) and dt=3D=A1=B12012-1-12-02=A1=B1 group by type; > > > Is there a better way to do these queries? > > You could try something like this: > > SELECT > type > , count(*) > , count(DISTINCT u) > , count(CASE WHEN plat=3D1 THEN u ELSE NULL) > , count(DISTINCT CASE WHEN plat=3D1 THEN u ELSE NULL) > , count(CASE WHEN (type=3D2 OR type=3D6) THEN u ELSE NULL) > , count(DISTINCT CASE WHEN (type=3D2 OR type=3D6) THEN u ELSE NULL) > FROM > t > WHERE > dt in ("2012-1-12-02", "2012-1-12-03") > GROUP BY > type > ORDER BY > type > ; > > Good luck :) > Martin Kuhn > > > P.S. You'ge got a strange date format there. For sorting purposes it > would be more appropriate to use something like "2012-01-12-02". > > --0016e6d99b2dcfd10504b660ea0e Content-Type: text/html; charset=GB2312 Content-Transfer-Encoding: quoted-printable to Martin, Mark and Edward,

thank you for your advices, I will try i= t out.

And to Martin, by "appropriate data format", do you= mean something like "2012011202" ?

thanks!

xiaobin=

=D4=DA 2012=C4=EA1=D4=C212=C8=D5 =CF=C2=CE= =E710:20=A3=ACMartin Kuhn <martin.kuhn@affinitas.de>=D0=B4=B5=C0=A3=BA<= br>
Hi there,

> Select count(*), count(distinct u), type from t group by type where pl= at=3D1 and dt=3D”2012-1-12-02”
> Select count(*), count(distinct u), type from = t where (type =3D2 or type =3D6) and dt=3D”2012-1-12-02” group = by type;

> Is there a better way to do these queries?

You could try something like this:

SELECT
   type
 , count(*)
 , count(DISTINCT u)
 , count(CASE WHEN plat=3D1 THEN u ELSE NULL)
 , count(DISTINCT CASE WHEN plat=3D1 THEN u ELSE NULL)
 , count(CASE WHEN (type=3D2 OR type=3D6) THEN u ELSE NULL)
 , count(DISTINCT CASE WHEN (type=3D2 OR type=3D6) THEN u ELSE NULL)<= br> FROM
   t
WHERE
   dt in ("2012-1-12-02", "2012-1-12-03") GROUP BY
   type
ORDER BY
   type
;

Good luck :)
Martin Kuhn


P.S.  You'ge got a strange date format there. For sorting purposes= it would be more appropriate to use something like "2012-01-12-02".


--0016e6d99b2dcfd10504b660ea0e--