Clever Geek Handbook

How Hive ql executes a query with multiple COUNT functions and does divisions with them

Here is my question: I have a table with some records like (name, date, type). Suppose I have three types a, b and c. Now I want to count each type as type_count with some constraint and do division with count (a) / count (b) to make a percentage result, and the constraint in and different, how can I deal with this? thank you! my code looks like this:

   SELECT name, count(a), count(a)/count(b)
   from table
   where ...

Is it possible to do some kind of subquery in select? look like this

select name, count(a), count(a)/ (select count(b) from table where restriction_for_b)
from table
where retriction_for_a
+3


source to share


2 answers


If I understand your question correctly, you can replace the counters with sum(if(condition, 1, 0))

. Something like that:



select
  name,
  sum(if(condition_for_a, 1, 0)),
  sum(if(condition_for_a, 1, 0)) / sum(if(condition_for_b, 1, 0))
from table
where ...
+8


source


The same worked for me. Thank you. select product_id, count (*) as cnt, amount (if (action == 'Click', 1,0)) as click_count, amount (if (action == 'Browse', 1,0)) as browse_count from data, where action = 'Browse' or action = 'Click' group by product_id number by cnt desc;



0


source






More articles:


All Articles