Hive Explode / Lateral View Table

Question

Table : jd1 (Comaparison Table)

Table : data1 (New Values Table)

I have write this query in sql server it's working but in hive it shows me an error

select * from data1;

1   siva    hadoop
1   siva    hive
1   siva    spark
1   siva    hbase
1   siva    mapreduce
1   siva    hdfs
2   kumar   hadoop
2   kumar   hive
2   kumar   python
2   kumar   spark
3   naveen  hive
3   naveen  hadoop
3   naveen  flume
3   naveen  kafka

select * from jd1;

1   hadoop
1   hive
1   spark
1   hbase
1   mapreduce
1   hdfs
1   python
1   java  

Expected output

1   siva    6   85.71428571428571
2   kumar   4   57.142857142857146
3   naveen  2   28.571428571428573

My Query

select id, name, count(*), ((count(*)*100)/(select count(skills)from jd1))avg
from (select n.id, n.name, n.skills
      from data1 n join jd1 t on (n.skills=t.skills))a
group by id,name;

Error

FAILED: ParseException line 1:44 cannot recognize input near 'select' 'count' '(' in expression specification

Answer 1

select id, name, count(*) cnt, count(*)*100/skill_cnt cnt_pct
from (select n.id, n.name, n.skills, t.skill_cnt 
      from data1 n 
           inner join (select skills, count(*) over() skill_cnt from jd1) t 
                      on n.skills=t.skills
     ) a
group by id,name;

Answer 2

You can try below query -

SELECT n.id, n.name, COUNT(n.skills), COUNT(n.skills)/skill_cnt.total_skill
FROM data1 n
JOIN jd1 t ON n.skills=t.skills
CROSS JOIN (SELECT COUNT(*) total_skill FROM jd1) skill_cnt
GROUP BY n.id, n.name, total_skill

Answer 3

Create one more table for jd skill as skill_count and join this tables.

SELECT n.Id, n.Job_Id, n.Name, n.Email, n.Mobile_Number, n.Education, n.Total_Experiance,((count(n.skills)*100)/s.skill_count) Average
FROM new_resume n
JOIN new_jd t ON n.skills=t.skills
JOIN skill_count s ON n.job_id = s.job_id
GROUP BY n.Id, n.Job_Id, n.Name, n.Email, n.Mobile_Number, n.Education, n.Total_Experiance,s.skill_count;