[DONE] Reduce redundant SQLite nested query?

Just starting out? Need help? Post your questions and find answers here.
User avatar
skywalk
Addict
Addict
Posts: 3972
Joined: Wed Dec 23, 2009 10:14 pm
Location: Boston, MA

[DONE] Reduce redundant SQLite nested query?

Post by skywalk »

To the SQL gods, how can I reduce this nested query so that X,Y,Z are filtered prior to checking AA?
This works but is expensive since it calculates X,Y,Z for each subquery.
Only AA needs to be checked in each.

Code: Select all

SELECT 3*b3.bin3 + 2*b2.bin2 + b1.bin1 FROM 
  (SELECT count(*) AS bin1 FROM `TD` WHERE
  `X` = 1 AND
  `Y` >= 2 AND
  `Z` >= 2 AND
  `AA` >= 1 AND `AA` <= 2) b1
  JOIN
  (SELECT count(*) AS bin2 FROM `TD` WHERE
  `X` = 1 AND
  `Y` >= 2 AND
  `Z` >= 2 AND
  `AA` >= 2.01 AND `AA` <= 3) b2
  JOIN
  (SELECT count(*) AS bin3 FROM `TD` WHERE
  `X` = 1 AND
  `Y` >= 2 AND
  `Z` >= 2 AND
  `AA` >= 3.01 AND `AA` <= 4) b3;
Reduced Form:

Code: Select all

WITH `v` AS
(SELECT `AA` FROM `TD` WHERE
  `X` = 1 AND
  `Y` >= 2 AND
  `Z` >= 2)
SELECT 3*bin3 + 2*bin2 + bin1 FROM
  (SELECT count(*) AS bin1 FROM `v` WHERE
  `AA` >= 1 AND `AA` <= 2)
  JOIN
  (SELECT count(*) AS bin2 FROM `v` WHERE
  `AA` >= 2.01 AND `AA` <= 3)
  JOIN
  (SELECT count(*) AS bin3 FROM `v` WHERE
  `AA` >= 3.01 AND `AA` <= 4);
The nice thing about standards is there are so many to choose from. ~ Andrew Tanenbaum