在HIVE中,除了COUNT(*)外,COUNT还可以有很多高级用法。
SELECT
type
, count(*)
, count(DISTINCT u)
, count(CASE WHEN plat=1 THEN u ELSE NULL END)
, count(DISTINCT CASE WHEN plat=1 THEN u ELSE NULL END)
, count(CASE WHEN (type=2 OR type=6) THEN u ELSE NULL END)
, count(DISTINCT CASE WHEN (type=2 OR type=6) THEN u ELSE NULL END)
FROM
t
WHERE
dt in ("2018-05-20", "2018-05-21")
GROUP BY
type
ORDER BY
type
;
例如根据消费记录要求计算三餐消费次数
select xh,jyrq,
COUNT(distinct(
case when xfzl ='其他' then null
else concat(jyrq,xfzl) end) )
from (select xh,jyrq,
case when hour(jysj) >= 6 and hour(jysj)<10 then '早餐'
when hour(jysj) >= 10 and hour(jysj)<16 then '午餐'
when hour(jysj) >= 16 and hour(jysj)<22 then '晚餐'
else '其他' end as xfzl
from gdm.gdm_ykt_jy_log
order by xh,jyrq ) t
group by xh,jyrq;