2

I have the following selection string:

SELECT * , AVG( `q1` ) AS betavg, COUNT( bet_id ) AS nr, MAX( q1 ) AS qmax, 
(MAX( q1 ) - AVG( `q1` )) AS diff FROM jos_bet_1x3_best GROUP BY `bet_id`
HAVING ( COUNT( `bet_id` ) >2 AND betavg <= q1
AND (qmax - MIN( q1 )) < 0.5 AND q1 <3)
ORDER BY `diff` DESC LIMIT 0 , 30

Can I optimize this somehow ? I want to select that data where the q1 is bigger than the average.

This is the sample data:

+-------+---------------+------+------+------+---------------------+-----------+
| id    | bet_id        | q1   | qx   | q2   | timestamp           | bookie_id |
+-------+---------------+------+------+------+---------------------+-----------+
| 32665 | 4e371a6e23735 | 1.90 | 3.50 | 3.80 | 2011-08-01 21:28:14 |         4 |
| 33410 | 4e371a6e23735 | 2.02 | 3.25 | 3.70 | 2011-08-01 21:28:31 |         8 |
| 34423 | 4e371a6e23735 | 2.05 | 3.25 | 3.25 | 2011-08-01 21:30:08 |         3 |
| 32666 | 4e371a6e23fe8 | 2.20 | 3.25 | 3.20 | 2011-08-01 21:28:14 |         4 |
| 33407 | 4e371a6e23fe8 | 2.17 | 3.20 | 3.35 | 2011-08-01 21:28:31 |         8 |
| 34252 | 4e371a6e23fe8 | 2.20 | 3.25 | 3.25 | 2011-08-01 21:28:52 |         2 |
| 34418 | 4e371a6e23fe8 | 2.05 | 3.25 | 3.30 | 2011-08-01 21:30:08 |         3 |
| 32667 | 4e371a6e24750 | 1.40 | 4.25 | 8.00 | 2011-08-01 21:28:14 |         4 |
| 33406 | 4e371a6e24750 | 1.45 | 4.10 | 7.10 | 2011-08-01 21:28:31 |         8 |
| 34253 | 4e371a6e24750 | 1.40 | 4.20 | 8.50 | 2011-08-01 21:28:52 |         2 |
| 34425 | 4e371a6e24750 | 1.45 | 3.75 | 7.00 | 2011-08-01 21:30:08 |         3 |
| 32668 | 4e371a6e24ee4 | 1.80 | 3.50 | 4.25 | 2011-08-01 21:28:14 |         4 |
| 33462 | 4e371a6e24ee4 | 1.87 | 3.35 | 4.15 | 2011-08-01 21:28:31 |         8 |
| 34251 | 4e371a6e24ee4 | 1.75 | 3.55 | 4.65 | 2011-08-01 21:28:52 |         2 |
| 34420 | 4e371a6e24ee4 | 1.85 | 3.25 | 4.00 | 2011-08-01 21:30:08 |         3 |
+-------+---------------+------+------+------+---------------------+-----------+

Finally, I made an other solution based on drex solution:

SELECT A. * , ( A.q1 - tb1.betavg) AS diff, tb1.betavg, tb1.nr FROM jos_bet_1x3_best A 
    INNER JOIN (
        SELECT bet_id, AVG( `q1` ) AS betavg, COUNT( bet_id ) AS nr
        FROM jos_bet_1x3_best GROUP BY `bet_id`
    ) AS tb1
USING ( bet_id ) WHERE tb1.nr >2 AND (A.q1 - tb1.betavg) >0
ORDER BY diff DESC LIMIT 0 , 30
5
  • I had 'BB.*,AA.q1' which weas wrong. I flipped it to 'AA.*,BB.q1'. Please try again !!! Aug 2, 2011 at 10:34
  • Updated it again. Try again, please !!! Aug 2, 2011 at 10:53
  • Question : Does your original work ??? Aug 2, 2011 at 11:02
  • Yes, it worked.
    – Mokus
    Aug 2, 2011 at 11:11
  • Question : How many rows should come back ??? I am only getting one row. Aug 2, 2011 at 11:29

2 Answers 2

3
SELECT A.*
FROM    jos_bet_1x3_best A
   INNER JOIN
      (SELECT bet_id,
              q1,
              AVG(`q1`) AS betavg,
              COUNT(bet_id) AS nr,
              MAX(q1) AS qmax,
              (MAX(q1) - AVG(`q1`)) AS diff
         FROM jos_bet_1x3_best
       GROUP BY `bet_id`
       HAVING (q1 >= betavg AND nr > 2 AND diff < .5 AND q1 < 3)
        LIMIT 0, 30) AS tb1
   USING (bet_id)
GROUP BY `bet_id`;

Please try this.

7
  • I have to admit but THIS IS THE CORRECT ANSWER !!! Aug 2, 2011 at 11:42
  • You get a +1 from me !!! Aug 2, 2011 at 11:42
  • I did change your answer a little so that q1 does not appear in the display twice. Aug 2, 2011 at 11:43
  • Otherwise it is totally correct. iUngi, please try drex's query. Aug 2, 2011 at 11:44
  • Thank you!! I'm just not sure if yours is faster than mine.. i haven't tested it yet thoroughly.
    – drex
    Aug 2, 2011 at 11:45
1
ALTER TABLE jos_bet_1x3_best ADD INDEX (bet_id,id);
ALTER TABLE jos_bet_1x3_best ADD INDEX (bet_id,q1);
SELECT A.*
FROM
(
    SELECT AA.*,BB.q1 FROM
    (
        SELECT
            bet_id,
            COUNT(1) nr, 
            MAX(q1) qmax,
            AVG(q1) betavg,
            MAX(q1)-AVG(q1) diff
        FROM
            (SELECT bet_id,q1 FROM jos_bet_1x3_best) AAA
        GROUP BY
            bet_id
        HAVING
            (COUNT(1) > 2 AND betavg <= q1 AND
            (qmax-MIN(q1)) < 0.5 AND q1 < 3)
        ORDER BY
            diff DESC LIMIT 0,30
    ) AA INNER JOIN
    (
        SELECT bet_id,q1
        FROM jos_bet_1x3_best
    ) BB USING(bet_id)
) A INNER JOIN jos_bet_1x3_best USING (bet_id,q1);

Not sure if this will speed things up, but give it a try anyway !!!

5
  • I tried your version, but I'm getting the following error: ERROR 1054 (42S22): Unknown column 'q1' in 'having clause'
    – Mokus
    Aug 2, 2011 at 7:45
  • I'm still getting the same error.
    – Mokus
    Aug 2, 2011 at 10:43
  • Still the same, not working.
    – Mokus
    Aug 2, 2011 at 10:58
  • If it's help, this is the database, with data: sharetext.org/AZWV
    – Mokus
    Aug 2, 2011 at 11:02
  • Looks like drex's query is correct !!! Aug 2, 2011 at 11:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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