0
Student Id | marks
101        | 93,63,74,
102        | 63,66,34,
103        | 93,64,54,
104        | 43,93,34,
105        | 95,83,54,

I have this data available in a table "Students" with two columns " student_ID" and "marks".

the marks are for 3 subjects maths, english and science but are in Comma within a single table. How to use windows functions to find the student with highest average among all?

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Sachin Attri
  • 27
  • 1
  • 7

2 Answers2

1

You can use a recursive CTE to split your list of marks into individual marks, then take AVG of those marks using a window function, order by that value descending and take only the first result:

WITH RECURSIVE CTE AS (
  SELECT student_id, SUBSTRING_INDEX(marks, ',', 1) AS mark,
         SUBSTRING(marks, LOCATE(',', marks) + 1) AS balance
  FROM data
  UNION ALL
  SELECT student_id, SUBSTRING_INDEX(balance, ',', 1),
         SUBSTRING(balance, LOCATE(',', balance) + 1)
  FROM CTE
  WHERE LOCATE(',', balance) > 0
)
SELECT student_id, 
       ROUND(AVG(mark) OVER(PARTITION BY student_id), 2) AS avg_mark
FROM CTE
ORDER BY avg_mark DESC
LIMIT 1

Output:

student_id  avg_mark
105         77.33

Demo on dbfiddle

Nick
  • 138,499
  • 22
  • 57
  • 95
  • You could just use a second CTE e.g. https://www.db-fiddle.com/f/bo5srCB8ZkyM6Qx5VKDgq1/2 – Nick Jun 07 '20 at 01:43
  • if i remove the limit 1, i get bit peculier response. – Sachin Attri Jun 07 '20 at 02:02
  • You will get multiple rows if you remove the `limit`. It's there so that you *only* get the `student_id` with the highest average. – Nick Jun 07 '20 at 02:03
  • I think you'd be better served with straight aggregation rather than window functions: https://www.db-fiddle.com/f/bo5srCB8ZkyM6Qx5VKDgq1/3 – Nick Jun 07 '20 at 02:06
  • I was trying to see the result if limit not applied. I was not going to put the final code without limit – Sachin Attri Jun 07 '20 at 02:28
0

You can unpivot the data using union all. Then to get the maximum mark you can use order by and limit:

select m.*
from ((select student_id, substring_index(marks, ',', 1) + 0 as mark, 'maths' as subject
       from marks
      ) union all
      (select student_id, substring_index(substring_index(marks, ',', 2), ',', -1) + 0 as mark, 'english'
       from marks
      ) union all
      (select student_id, substring_index(marks, ',', -1) + 0 as mark, 'science'
       from marks
      )
     ) m
order by mark desc
limit 1;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786