Forums: » Register « |  Free Tools |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support |
 User Name: Password: Remember me

New Free Tools on Dev Shed!
We're Excited to announce that Dev Shed now has 70 free tools on the site. To learn more, click here!

 Add This Thread To: Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb
 « Previous Thread | Next Thread » Thread Tools Search this Thread Rate Thread Display Modes
 Dev Shed Forums Sponsor:
#1
July 4th, 2013, 08:31 PM
 daprezjer
hiding my <b> from ur <strong>

Join Date: Feb 2004
Posts: 954
Time spent in forums: 6 Days 6 h 16 m 19 sec
Reputation Power: 180
Optimizing Query that calculates, groups, and groups again

Seeing if there's any bright optimization ideas out there on a particular query need.

To simplify, imagine that we want to know how many kids in a class are present (P) between 0 and 50 percent of the time, and how many are present 50 to 100 percent of the time. Call the kids with under 50% attendance "bad" and the other ones "good".

The data in the attendance table may look like:

studentid | mark
-------------------
JACK | P
JACK | A
JACK | A
JACK | A
JACK | A
JACK | P
JILL | P
JILL | P
JILL | P
JILL | A

Now, Jack is a "bad" kid because he is absent over half the time, while Jill is a "good kid".

The resulting output should simply look like

array(
'bad' => 1,
'good' => 1,
)

1 bad kid, and one good kid.

While a simple concept, the steps are expensive. The SQL first needs to calculate a percentage for each and every kid (not cheap in itself) and then aggregate the counts of those.

For just the first part - calculating the percentages - I've found that:

SELECT *, ((SELECT COUNT(*) FROM attendance WHERE mark = 'P' AND studentid=a.studentid)/(SELECT COUNT(*) FROM attendance WHERE studentid=a.studentid)) AS percentage FROM attendance AS a ... (join filters) ... GROUP BY studentid

works, but is very expensive (and I have hundreds of thousands of marks). And of course, this would still require either PHP manipulation of the results or more CASE syntax for the second step of aggregation.

Any thoughts on a different strategy? Of course, PHP manipulation from the start is also a possibility, but I'm again finding that solution is taking too long.

Thanks for any input,
Jeremy
__________________
****
Enjoy my post? Drop some props by hitting the scales button up top. JBL

Website Design in Los Angeles and Washington, DC by PoweredPages.com

#2
July 5th, 2013, 12:58 AM
 r937
SQL Consultant

Join Date: Feb 2003
Location: Toronto Canada
Posts: 26,806
Time spent in forums: 3 Months 1 Week 4 Days 7 h 20 m 48 sec
Reputation Power: 4208
Code:
```SELECT CASE WHEN percentage >= 50
THEN 'good'
ELSE 'bad' END AS category
, COUNT(*) AS total
FROM ( SELECT studentid
, 100.0 * COUNT(CASE WHEN mark = 'P'
THEN mark END) /
COUNT(*) AS percentage
FROM attendance
GROUP
BY studentid ) AS t
GROUP
BY category```
Comments on this post
daprezjer agrees!
__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book

#3
July 5th, 2013, 05:58 PM
 daprezjer
hiding my <b> from ur <strong>

Join Date: Feb 2004
Posts: 954
Time spent in forums: 6 Days 6 h 16 m 19 sec
Reputation Power: 180
Good stuff. Really simple follow up.

There's a portion of the actual script that requires some calculation of db values to do multiple comparison values with the CASE statement. For simplicity sake, let's just say:

CASE
WHEN num/total > 5 OR num/total <=10 THEN 'top'
WHEN num/total > 0 OR num/total <=5 THEN 'bottom'
END

Is there a way to write this script without using num/total twice for each WHEN statement? I've seen something like:
WHEN num/total >5 <= 10 around as a shorthand, though I don't believe I ever got it to work. My (perhaps misplaced) worry is that it has to calculate num/total twice for just the one WHEN statement.

#4
July 5th, 2013, 06:49 PM
 r937
SQL Consultant

Join Date: Feb 2003
Location: Toronto Canada
Posts: 26,806
Time spent in forums: 3 Months 1 Week 4 Days 7 h 20 m 48 sec
Reputation Power: 4208
Quote:
 Originally Posted by daprezjer My (perhaps misplaced) worry is that it has to calculate num/total twice for just the one WHEN statement.
i would be very surprised if the optimizer failed to recognize the opportunity to do one calculation instead of two

 Viewing: Dev Shed Forums > Databases > MySQL Help > Optimizing Query that calculates, groups, and groups again

Developer Shed Advertisers and Affiliates

 Thread Tools Search this Thread Search this Thread: Advanced Search Display Modes Rate This Thread Linear Mode Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off
 View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox Forum Jump Please select one User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home -------------------- Programming Languages    PHP Development        PHP FAQs and Stickies    Perl Programming        Perl FAQs and Stickies    C Programming        C Programming FAQs and Stickies    Java Help        Java FAQs    Python Programming        Python Programming FAQs    Ruby Programming        Ruby Programming FAQs    Game Development        Game Development FAQs Programming Languages - More    ASP Programming        ASP Programming FAQs    .Net Development        .Net Development FAQs    Visual Basic Programming        Visual Basic Programming FAQs    Software Design        Software Design FAQs    ColdFusion Development        ColdFusion Development FAQs    Delphi Programming        Delphi Programming FAQs    Regex Programming        Regex Programming FAQs    XML Programming        XML Programming FAQs    Other Programming Languages        Other Programming Languages FAQs Web Design    HTML Programming        HTML Programming FAQs    JavaScript Development        JavaScript Development FAQs    CSS Help        CSS Help FAQs    Flash Help        Flash Help FAQs    Photoshop Help        Photoshop Help FAQs    Web Design Help        Web Design Help FAQs    Website Critiques        Website Critiques FAQs    Search Engine Optimization        Search Engine Optimization FAQs Mobile Programming    Mobile Programming        Mobile Programming FAQs    iPhone SDK Development        iPhone SDK Development FAQs    Android Development        Android Development FAQs    BlackBerry Development        BlackBerry Development FAQs Web Site Management    Business Help        Business Help FAQs    Development Software        Development Software FAQs    Scripts        Scripts FAQs Databases    Database Management        Database Management FAQs    DB2 Development        DB2 Development FAQs    MySQL Help        MySQL Help FAQs    PostgreSQL Help        PostgreSQL Help FAQs    Firebird SQL Development        Firebird SQL Development FAQs    MS SQL Development        MS SQL Development FAQs    Oracle Development        Oracle Development FAQs    LDAP Programming        LDAP Programming FAQs System Administration    Mail Server Help        Mail Server Help FAQs    Apache Development        Apache Development FAQs    Security and Cryptography        Security and Cryptography FAQs    Antivirus Protection        Antivirus Protection FAQs    DNS        DNS FAQs    IIS        IIS FAQs    Networking Help        Networking Help FAQs    FTP Help        FTP Help FAQs Operating Systems    BSD Help        BSD Help FAQs    Linux Help        Linux Help FAQs    UNIX Help        UNIX Help FAQs    Windows Help        Windows Help FAQs    Mac Help        Mac Help FAQs Web Hosting    Web Hosting        Web Hosting FAQs    Free Web Hosting        Free Web Hosting FAQs    Web Hosting Requests        Web Hosting Requests FAQs    Web Hosting Offers        Web Hosting Offers FAQs Computer Hardware    Computer Hardware    CPUs        CPUs FAQs    Cooling        Cooling FAQs    Embedded Programming        Embedded Programming FAQs    Motherboards        Motherboards FAQs    Multimedia Hardware        Multimedia Hardware FAQs Other    Dev Shed Lounge        Dev Shed Lounge FAQs    Development Articles        Development Articles FAQs    Beginner Programming        Beginner Programming FAQs    Hire A Programmer        Hire A Programmer FAQs    Project Help Wanted        Project Help Wanted FAQs Latest News Updated Hourly    Technology News    Business News    Science News Forum Information    Forum Rules/Guidelines        Forum Rules/Guidelines FAQs    Forum Announcements        Forum Announcements FAQs    Dev Shed Gaming Center        Go to the Dev Shed Battle Arena        Go to the Dev Shed Arcade Games        Go to the Legend of the Green Dragon    Suggestions & Feedback        Suggestions & Feedback FAQs

 Forums: » Register « |  Free Tools |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support |

Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.

© 2003-2013 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap