Tanvir Tanvir - 4 days ago 5
SQL Question

Finding out Percentage Value using Hive

I have some tables as:

Table_1:
+------------+--------------+
| Student_ID | Student_Name |
+------------+--------------+
| 000 | Jack |
| 001 | Ron |
| 002 | Nick |
+------------+--------------+

Table_2:
+-----+-------+-------+
| ID | Total | Score |
+-----+-------+-------+
| 000 | 100 | 80 |
| 001 | 100 | 80 |
| 002 | 100 | 80 |
+-----+-------+-------+

Table_3:
+-----+-------+-------+
| ID | Total | Score |
+-----+-------+-------+
| 000 | 100 | 60 |
| 001 | 100 | 80 |
| 002 | 100 | 70 |
+-----+-------+-------+


Expected_Output:

ID percent
000 70
001 80
002 75


I have created a hive table before. Now, I want to come up with a single HiveQL so that, I can get the expected output from these above 3 tables.
What I am thinking to do is, in my query I will:


  1. use the Left outer join using ID

  2. find the sum of "Total" and "Score" for each ID

  3. divide sum of "Score" by sum of "Total" to get percentage.



I came up with this:

INSERT OVERWRITE TABLE expected_output
SELECT t1.Student_ID AS ID, (100*t4.SUM1/t4.SUM2) AS percent
FROM Table_1 t1
LEFT OUTER JOIN(
SELECT (ISNULL(Total,0) + ISNULL(Total,0)) AS ‘SUM2’, (ISNULL(Score,0) + ISNULL(Score,0)) AS ‘SUM1’
FROM t4
)ON (t1.Student_ID=t2.ID) JOIN Table_3 t3 ON (t3.ID=t2.ID);


And, I am stuck at this point. Not sure how to reach to the result.
Any idea please?

vkp vkp
Answer

This is a simple join. Assuming you have one row per id in each of tables t2 and t3, you can do

SELECT t2.Student_ID AS ID, 100.0*(t2.score+t3.score)/(t2.total+t3.total) AS percent
FROM Table_2 t2
JOIN Table_3 t3 ON t3.ID=t2.ID
Comments