select  count(case when pos=0 and count_distinct_ID>1 then 1 end) as different_cityid
       ,count(case when pos=1 and count_distinct_ID>1 then 1 end) as different_countryid
       ,count(case when pos=2 and count_distinct_ID>1 then 1 end) as different_tagid

from   (select      pe.pos
                   ,count (distinct pe.ID) as count_distinct_ID
        from        mytable t
                    lateral view posexplode (array(CityID,CountryID,TagID)) pe as pos,ID

        group by    t.UserID
                   ,pe.pos        
        ) t          
;

+------------------+---------------------+-----------------+
| different_cityid | different_countryid | different_tagid |
+------------------+---------------------+-----------------+
|                1 |                   3 |               2 |
+------------------+---------------------+-----------------+

这是另一个避免 count(distinct ...) ```
select count (case when pos=0 and not is_distinct_ID then 1 end) as different_cityid
,count (case when pos=1 and not is_distinct_ID then 1 end) as different_countryid
,count (case when pos=2 and not is_distinct_ID then 1 end) as different_tagid

from (select pe.pos
,min(pe.ID)<=>max(pe.ID) as is_distinct_ID
from mytable t
lateral view posexplode (array(CityID,CountryID,TagID)) pe as pos,ID

    group by    t.UserID
               ,pe.pos        
    ) t          

;

…还有另一个变种

select count (case when not is_distinct_CityID then 1 end) as different_cityid
,count (case when not is_distinct_CountryID then 1 end) as different_countryid
,count (case when not is_distinct_TagID then 1 end) as different_tagid

from (select min (CityID) <=> max (CityID) as is_distinct_CityID
,min (CountryID) <=> max (CountryID) as is_distinct_CountryID
,min (TagID) <=> max (TagID) as is_distinct_TagID

    from        mytable

    group by    UserID     
    ) t          

;

select uid,cid,count(c),count(g) from(select cid,uid,count(coid) over(partition by cid,uid) as c,count(tagid) over(partition by cid,tagid) as g from citydata)e group by cid,uid; 这里uid=userid，cid=cityid，coid=countryid，tagid Total MapReduce CPU Time Spent: 0 msec OK uid cid coid tagid 100000 1 1 1 100001 1 2 2 100002 1 2 2 100000 2 2 2 Time taken: 3.865 seconds, Fetched: 4 row(s) 基于 userid 我希望这会有帮助

使用下面的代码我想它可以帮助你，

SELECT COUNT(DISTINCT (CountryID)) AS CountryID,
COUNT(DISTINCT(CityID)) AS CityID,
COUNT(DISTINCT(TagID)) AS TagID
FROM test GROUP BY UserID

结果是这样的，

CountryID   CityID  TagID
2   3   3
1   2   1
1   2   2

你好，维努