4
\$\begingroup\$

I have created a MySQL query that works, but I feel there must be a better way. The query will be used by a PHP script whose purpose is to assign conditions & subconditions to new participants in an online experiment based on how many times each combination of condition & subcondition was already assigned to previous participants. (There are 2556 conditions and 8 subconditions, all combinations are allowed.) The purpose of the query is to calculate the number of "filtered completes" for each condition, defined as follows:

  1. for a given condition C, the number of completes in each subcondition S is the number of unique data rows with condition==C and subcondition==S
  2. for a given condition C, the "target" is 1 + the smallest number of completes for any subcondition S in C
  3. for a given condition C and subcondition S, the "filtered completes" for S in C is the minimum of the "target" for C and the actual number of completes for S in C
  4. the total "filtered completes" for the condition C is the sum of filtered completes for all subconditions S in C

Intuitively, at a given moment, I want to act as though my goal is to assign the "target" number of participants to each subcondition in a given condition. The "target" can increase only once ALL subconditions in the condition have met the target. At a given moment, I want to ignore completes in any subcondition that exceed the target. OK, here now is the query which works:

select `condition`, (
    LEAST( `Target`, `subcond_0` ) +
    LEAST( `Target`, `subcond_1` ) +
    LEAST( `Target`, `subcond_2` ) +
    LEAST( `Target`, `subcond_3` ) +
    LEAST( `Target`, `subcond_4` ) +
    LEAST( `Target`, `subcond_5` ) +
    LEAST( `Target`, `subcond_6` ) +
    LEAST( `Target`, `subcond_7` ) ) as `filtered_completes`
from (
    select `condition`,
        (1+LEAST( `subcond_0`, `subcond_1`, `subcond_2`, `subcond_3`, `subcond_4`, `subcond_5`, `subcond_6`, `subcond_7` )) as `Target`,
        `subcond_0`, `subcond_1`, `subcond_2`, `subcond_3`, `subcond_4`, `subcond_5`, `subcond_6`, `subcond_7`
    from (
        select `condition`,
            SUM( CASE `subcondition` WHEN 0 THEN `count( distinct id )` ELSE 0 END ) AS `subcond_0`,
            SUM( CASE `subcondition` WHEN 1 THEN `count( distinct id )` ELSE 0 END ) AS `subcond_1`,
            SUM( CASE `subcondition` WHEN 2 THEN `count( distinct id )` ELSE 0 END ) AS `subcond_2`,
            SUM( CASE `subcondition` WHEN 3 THEN `count( distinct id )` ELSE 0 END ) AS `subcond_3`,
            SUM( CASE `subcondition` WHEN 4 THEN `count( distinct id )` ELSE 0 END ) AS `subcond_4`,
            SUM( CASE `subcondition` WHEN 5 THEN `count( distinct id )` ELSE 0 END ) AS `subcond_5`,
            SUM( CASE `subcondition` WHEN 6 THEN `count( distinct id )` ELSE 0 END ) AS `subcond_6`,
            SUM( CASE `subcondition` WHEN 7 THEN `count( distinct id )` ELSE 0 END ) AS `subcond_7`
        from
            (
                select `condition`, `subcondition`, count( distinct id ) from test_table_3
                where approve=1 group by `condition`, `subcondition`
            ) as T1 group by `condition` 
    ) as T2
) as T3;

Reasons I don't think it's good are

  1. it requires me to hand-code the subconditions, so it would not be easy to change to a different number of subconditions
  2. the nested selects look ugly to me
  3. it seems to run a bit slow. Any suggestions for how to improve it?

Should I be doing this in PHP instead?

Here is some sample data as requested, eliminating columns that I don't think are relevant to the question:

id  subjid          condition   subcondition     approve
51  A3GM1SF5FUOS8L  1175        3                1
52  A3GM1SF5FUOS8L  456         0                1
53  A3GM1SF5FUOS8L  1301        0                1
54  A3GM1SF5FUOS8L  499         5                1
55  A3GM1SF5FUOS8L  886         5                1
56  A3GM1SF5FUOS8L  2257        7                1
57  A3GM1SF5FUOS8L  1955        4                1
58  A3GM1SF5FUOS8L  890         2                1
59  A3GM1SF5FUOS8L  1667        0                1
60  A3GM1SF5FUOS8L  1546        2                1
61  A3GM1SF5FUOS8L  1315        5                1
62  A3GM1SF5FUOS8L  139         1                1
63  A3GM1SF5FUOS8L  374         3                1
64  A3GM1SF5FUOS8L  1249        4                1
65  A3GM1SF5FUOS8L  1658        7                1
66  A3GM1SF5FUOS8L  2241        3                1
67  A3GM1SF5FUOS8L  167         6                1
68  A3GM1SF5FUOS8L  1370        0                1
69  A3GM1SF5FUOS8L  26          3                1
70  A3GM1SF5FUOS8L  2499        6                1
\$\endgroup\$
2
  • 1
    \$\begingroup\$ I'd suggest pasting an ERD, or sample data for this. Odds are the SQL statement is just the symptom of a bigger problem: badly designed tables and relationships. \$\endgroup\$ Dec 17 '13 at 23:13
  • \$\begingroup\$ Added sample data as requested. \$\endgroup\$
    – baixiwei
    Dec 18 '13 at 3:31
2
\$\begingroup\$

The generic solution that won't require changing when adding new subconditions involves several subqueries and will probably be very slow. There isn't too much data here, but it can be sped up (see below).

1. Completes

You have this one already in your query.

select
    condition,
    subcondition,
    count(distinct id) as completes
from
    test_table_3
where
    approve = 1
group by
    condition,
    subcondition

2. Minimum Completes

select
    condition,
    min(completes) as min_completes
from
    <query-1>
group by
    condition

3. Filtered Completes

Join 1 and 2

select
    condition,
    subcondition,
    min(min_completes, count(distinct id)) as filtered_completes
from
    test_table_3
join
    <query-2> using condition
where
    approve = 1
group by
    condition,
    subcondition

4. Total Filtered Completes

select
    condition,
    sum(filtered_completes) as total_filtered_completes
from
    <query-3>
group by
    condition

Going Faster

First, creating a temporary table out of <query-1> would be huge. Technically, MySQL should be smart enough to do this on its own, and being only three short columns should allow it to fit easily in RAM. However, I've found that the simplest of derived tables can often cause it to go off the rails. I will confess that I don't have a lot of MySQL tuning experience.

Second, this would be pretty easy in PHP. Load the results of <query-1> into an array and use two passes to calculate the minimum, filtered, and total completes. The latter two values can be calculated together during the second pass. Since you probably need these values in PHP in the end, the only real cost is slurping in all that data which again isn't very much here.

If you got tricky, you could even probably do it with a single pass while streaming the results from MySQL so you wouldn't have to store the entire derived table in RAM at once. For each condition, you only need to know a) the minimum subcondition completes, b) how many subconditions have that minimum value, and c) how many subconditions are over that minimum. The total filtered completes for that condition equals

a * b + (a + 1) * c
\$\endgroup\$
2
  • \$\begingroup\$ I don't think step 2 does what I need. I need to set the target for each condition equal to the minimum number of completes for subconditions within that condition, not overall. \$\endgroup\$
    – baixiwei
    Dec 18 '13 at 20:20
  • \$\begingroup\$ @baixiwei Grouping by condition on the outer query while grouping on both fields on the inner one gives you the minimum subcondition completes per condition. \$\endgroup\$ Dec 18 '13 at 21:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.