Since I am new to bigquery, I am struggling with basic stuffs. The table contains 145 records. How to create array from the table. Here is the downloadable link https://drive.google.com/uc?export=download&id=1SvkAN8sqS7WnsBO-X3UnyXI8sen5TOZ0 . The downloadable file is in csv format. I would like to know highest marks of each student and their subject.
1
-
2what you have tried so far. show us and we can help! and btw, not clear what is expected output - provide example please! Please edit your question to show a Minimal, Complete, and Verifiable example of the code that you are having problems with, then we can try to help with the specific problem. You can also read How to Ask. :o)Mikhail Berlyant– Mikhail Berlyant2018-02-28 16:45:06 +00:00Commented Feb 28, 2018 at 16:45
Add a comment
|
2 Answers
Thanks to Monica's answer I finally got your question :o)
I noticed that data in downloadable link is just bunch of duplicate rows so if dedup them just using simple DISTINCT you end up with just 8 rows. so that part is still not clear
But if to ignore this aspect the way to get the highest mark and respective subject is as below. It answer both questions you asked:
1 - How to create array from the table
2 - how to get highest marks of each student and their subject
#standardSQL
WITH data AS (
SELECT DISTINCT name, Physics, Chemistry, Maths, Biology
FROM `project.dataset.table`
)
SELECT name,
(
SELECT pair
FROM UNNEST([STRUCT<highest_mark INT64, subject STRING>
(Physics, 'Physics'),
(Chemistry, 'Chemistry'),
(Maths, 'Maths'),
(Biology, 'Biology')
]) pair
ORDER BY pair.highest_mark DESC
LIMIT 1
).*
FROM data
If to apply above to table with your data - below is result
Row name highest_mark subject
1 Samuel 96 Maths
2 David 90 Physics
3 Mark 90 Physics
4 John 94 Physics
5 Mathew 94 Physics
6 Andrew 97 Physics
7 James 97 Physics
8 Peter 98 Chemistry
I think this solution is more readable and manageable than using branched CASE WHEN THEN ...
2 Comments
jiki
beautiful, works like a charm. but I have a couple of doubts. 1. what does limit 1 suggests(limit is usually used to reduce records is n't it? here 8 records are displayed.) and 2. what does ) .* suggests?
jiki
The two doubts are clear now. Here the name of the array is pair. Arranging the pair in descending order for each record, so the subject with highest mark comes first, then limiting by 1, so the first set is selected. ).* is used to display the column names highest_mark and subject. For example first record: Samuel --> [(67,'Physics'),(89,'Chemistry'),(96,'Maths'),(67,'Biology')] --> ORDER BY pair.highest_mark DESC --> [(96,'Maths'),(89,'Chemistry'),(67,'Physics'),(67,'Biology')] ---> LIMIT 1 --> [(96,'Maths')] is selected --> ).* --> highest_mark subject
I tried this query and it worked.
WITH data AS (SELECT DISTINCT name, Physics, Chemistry, Maths, Biology
FROM `[Your_Project].[Your_dataset].[Your_table]`)
SELECT
name,
(CASE
WHEN Physics >= Chemistry AND Physics >= Maths AND Physics >= Biology THEN Physics
WHEN Chemistry >= Physics AND Chemistry >= Maths AND Chemistry >= Biology THEN Chemistry
WHEN Maths >= Physics AND Maths >= Chemistry AND Maths >= Biology THEN Maths
WHEN Biology >= Physics AND Biology >= Chemistry AND Biology >= Maths THEN Biology
ELSE Physics
END) AS maxmark,
(CASE
WHEN Physics >= Chemistry AND Physics >= Maths AND Physics >= Biology THEN 'Physics'
WHEN Chemistry >= Physics AND Chemistry >= Maths AND Chemistry >= Biology THEN 'Chemistry'
WHEN Maths >= Physics AND Maths >= Chemistry AND Maths >= Biology THEN 'Maths'
WHEN Biology >= Physics AND Biology >= Chemistry AND Biology >= Maths THEN 'Biology'
ELSE 'Physics'
END) as field
FROM
data
