barracuda barracuda - 5 months ago 9
MySQL Question

MYSQL Calculate average of a specific occurance in a column

I need to calculate the average of occurrences in a dataset for a given value in a column. I made an easy example but in my current database contains around 2 inner joins to reduce it to 100k records. I need to perform the following select distinct statement for 10 columns.

My current design forces an inner join for each column. Another constraint is that I need to perform it at least 50-100 rows for each name in this example.

I need to figure out an efficient way to calculate this values without using too many resources while making the query fast.

http://sqlfiddle.com/#!9/c2378/3

My expected Result is:

Name | R Avg dir | L Avg dir 1 | L Avg dir 2 | L Avg dir 3
A 0 .5 .25 .25


Create table query:

CREATE TABLE demo
(`id` int, `name` varchar(10),`hand` varchar(1), `dir` int)
;

INSERT INTO demo
(`id`, `name`, `hand`, `dir`)
VALUES
(1, 'A', 'L', 1),
(2, 'A', 'L', 1),
(3, 'A', 'L', 2),
(4, 'A', 'L', 3),
(5, 'A', 'R', 3),
(6, 'A', 'R', 3)
;


Example Query:

SELECT distinct name,
COALESCE(( (Select count(id) as 'cd' from demo where hand = 'L' AND dir = 1) /(Select count(id) as 'fd' from demo where hand = 'L')),0) as 'L AVG dir'
FROM
demo
where hand = 'L' AND dir = 1 AND name = 'A'

Answer

One option is to use conditional aggregation:

SELECT name, 
       count(case when hand = 'L' and dir = 1 then 1 end) / 
            count(case when hand = 'L' then 1 end) L1Avg,
       count(case when hand = 'L' and dir = 2 then 1 end) /
            count(case when hand = 'L' then 1 end) L2Avg,
       count(case when hand = 'L' and dir = 3 then 1 end) /
            count(case when hand = 'L' then 1 end) L3Avg,
       count(case when hand = 'R' and dir = 3 then 1 end) /
            count(case when hand = 'R' then 1 end) RAvg
FROM demo
WHERE name = 'A'
GROUP BY name

Please note, I wasn't 100% sure why you wanted your RAvg to be 0 -- I assumed you meant 100%. If not, you can adjust the above accordingly.