Hive中COUNT的高级用法(条件过滤等)

在HIVE中,除了COUNT(*)外,COUNT还可以有很多高级用法。

转载自:http://jmeagher.net/blog/2012/3/28/complex-counts-in-hive.html

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 ("2012-1-12-02", "2012-1-12-03")
GROUP BY
    type
ORDER BY
    type
;

Leave a Reply

Your email address will not be published.