cassandra-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "yujunjun (JIRA)" <j...@apache.org>
Subject [jira] [Created] (CASSANDRA-5140) multi group by distinct error
Date Thu, 10 Jan 2013 08:44:12 GMT
yujunjun created CASSANDRA-5140:
-----------------------------------

             Summary: multi group by distinct error 
                 Key: CASSANDRA-5140
                 URL: https://issues.apache.org/jira/browse/CASSANDRA-5140
             Project: Cassandra
          Issue Type: Bug
            Reporter: yujunjun


I hive a hql use "set hive.optimize.multigroupby.common.distincts=true" get a different result
with "set hive.optimize.multigroupby.common.distincts=false",
And the hql is :
set hive.optimize.multigroupby.common.distincts=true;
FROM
(
SELECT
      d.datekey datekey,
      d.`date` dt,
      d.week_num_overall week_num_overall,
      d.yearmo yearmo,
      uc.cityid cityid,
      p.userid userid,
      'all' clienttype,
      du.regdate regdate,
      if (f.orderid = p.orderid, 1, 0) isuserfirstpurchase,
      p.amount revenue
FROM
    fact.orderpayment p
    join dim.user_city uc on uc.userid = p.userid
    join dim.user du on du.userid = p.userid
    join detail.user_firstpurchase f on p.userid=f.userid
    join dim.`date` d on p.datekey = d.datekey

) base
INSERT overwrite TABLE `customer_kpi_periodic` partition (aggrtype = 'day')
    SELECT 
           'day' periodtype,
           base.datekey periodkey,
           'all' clienttype,
           0 cityid,
           count(distinct base.userid) buyer_count,           sum(base.isuserfirstpurchase)
first_buyer_count,
           count(distinct if(base.regdate = base.dt, base.userid, NULL)) regdate_buyer_count,
           count(*) order_count,
           sum(if(base.regdate = base.dt, 1, 0)) regdate_order_count,           sum(base.revenue)
revenue,
           sum(if(base.isuserfirstpurchase = 1, base.revenue, 0)) first_buyer_revenue,
           sum(if(base.regdate = base.dt, base.revenue, 0)) regdate_buyer_revenue

    GROUP BY base.datekey
INSERT overwrite TABLE `customer_kpi_periodic` partition (aggrtype = 'month')
    SELECT 
           'month' periodtype,           base.yearmo periodkey,
           'all' clienttype,           0 cityid,
           count(distinct base.userid) buyer_count,
           sum(base.isuserfirstpurchase) first_buyer_count,           count(distinct if(base.regdate
= base.dt, base.userid, NULL)) regdate_buyer_count,
           count(*) order_count,           sum(if(base.regdate = base.dt, 1, 0)) regdate_order_count,
           sum(base.revenue) revenue,           sum(if(base.isuserfirstpurchase = 1, base.revenue,
0)) first_buyer_revenue,
           sum(if(base.regdate = base.dt, base.revenue, 0)) regdate_buyer_revenue
    GROUP BY base.yearmo

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira

Mime
View raw message