How to create fast database queries

Counting skills

Comments enabled. I *really* need your comment

From Stack Overflow:

Ok, i've been trying to solve this for about 2 hours now... Please advise:


PROFILE [id (int), name (varchar), ...]
SKILL [id (int), id_profile (int), id_app (int), lvl (int), ...]
APP [id (int), ...]

The lvl can basically go from 0 to 3.

I'm trying to get this particular stat: "What is the percentage of apps that is covered by at least two people having a skill of 2 or higher?"

Thanks a lot

We actually need to count ratio here:

(apps that have 2 or more skills with level 2+) / (total number of apps)

This is one of not so common cases when AVG aggregate apparently comes handy.

What we need here, is:

  1. Assign a 1 to apps that match the criteria above,
  2. Assign a 0 to apps that don't match these, and
  3. Take the average.

First thought, of course, is to COUNT(*) all lvl 2+ skills for each app and filter them out:

SELECT  AVG(covered)
FROM    (
        SELECT  CASE WHEN COUNT(*) >= 2 THEN 1 ELSE 0 END AS covered
        FROM    app a
        LEFT JOIN
                skill s
        ON      (s.id_app = a.id AND s.lvl >= 2)
        GROUP BY
        ) ao

, which will work all right.

But this query may be not so performant in certain cases. Let's create the tables and fill them with sample data:

Database creation scripts

Written by Quassnoi

February 28th, 2009 at 9:50 pm

Posted in MySQL

Leave a Reply