CODEWITHSUNDEEP
mysqlsql
initall
initall

Reputation: 2385

SQL optimization: Four statements into one

I have a table with some columns, one holds a timestamp that I use to run currently four single selects to compute a SELECT count() and return information like "n-rows older than 1 week", "n-rows older than 2 weeks", ...

How to transform the four queries into one SQL-Statement that is hopefully running faster?

The statements look like this:

SELECT count(foo_pk) AS oneweek FROM foo WHERE foo_timstamp < DATE_SUB(now(), INTERVAL 1 week)

Upvotes: 3

Views: 352

Answers (5)

VGE
VGE

Reputation: 4191

select count(*), 
       round(datediff(curdate(), foo_pk) / 7)
         as week_diff
from wolffinder_experiment
group by week_diff
order by week_diff
having week_diff <= 4
limit 4;

Upvotes: 0

rsbarro
rsbarro

Reputation: 27369

I'm not a MySql guy, but in SQL Server I would use the CASE statement like so:

SELECT
    SUM(CASE WHEN foo_timstamp < DATE_SUB(now(), INTERVAL 1 week) THEN 1 ELSE 0 END) as oneweek,
    SUM(CASE WHEN foo_timstamp < DATE_SUB(now(), INTERVAL 2 week) THEN 1 ELSE 0 END) as twoweek,
    SUM(CASE WHEN foo_timstamp < DATE_SUB(now(), INTERVAL 3 week) THEN 1 ELSE 0 END) as threeweek,
    SUM(CASE WHEN foo_timstamp < DATE_SUB(now(), INTERVAL 4 week) THEN 1 ELSE 0 END) as fourweek
FROM foo
WHERE 
    foo_timstamp < DATE_SUB(now(), INTERVAL 1 week)

It seems that this statement is available in MySql as well, so while my syntax might be a bit off I think something like the SQL above should work.

Upvotes: 5

Martin Smith
Martin Smith

Reputation: 453910

SELECT 
      COUNT(1) AS olderthanoneweek, 
      COUNT(CASE WHEN foo_timstamp < DATE_SUB(now(), INTERVAL 2 week) THEN 1 END) AS olderthantwoweek,
      COUNT(CASE WHEN foo_timstamp < DATE_SUB(now(), INTERVAL 3 week) THEN 1 END) AS olderthanthreeweek, 
      COUNT(CASE WHEN foo_timstamp < DATE_SUB(now(), INTERVAL 4 week) THEN 1 END) AS olderthanfourweek 
FROM foo 
WHERE foo_timstamp < DATE_SUB(now(), INTERVAL 1 week)

Upvotes: 5

Steven Ryssaert
Steven Ryssaert

Reputation: 1967

You can have it in Rows like this, if you like:

SELECT DATE_SUB(now(), INTERVAL) AS WEEK_DIFF,
  count(foo_pk) AS COUNT_PER_WEEK
FROM foo
GROUP BY DATE_SUB(now(), INTERVAL);

Upvotes: 0

BugFinder
BugFinder

Reputation: 17868

Untried, but..

select count(foo_pk), 
if(foo_timestamp<date_sub(now(),interval 1 week,"in the last week",
if(foo_timestamp<date_sub(now(),interval 2 week,"in the last 2 weeks",
if(foo_timestamp<date_sub(now(),interval 3 week,"in the last 3 weeks",
if(foo_timestamp<date_sub(now(),interval 4 week,"in the last 4 weeks",
                                                "4 weeks or more   ")))) as foo_label
order by foo_label
group by foo_label;

Upvotes: 0

Related Questions