CODEWITHSUNDEEP
mysqlsql
Xaver
Xaver

Reputation: 11702

Get Total From a certain timestamp

I have a column of timestamps and I like to have a result where I can see

  1. the amount of added entries for a certain date (added_on_this_date)
  2. and the total amount since the beginning (total_since_beginning)

My table:

added
==========
1392040040
1392050040
1392060040
1392070040
1392080040
1392090040
1392100040
1392110040
1392120040
1392130040
1392140040
1392150040
1392160040
1392170040
1392180040
1392190040
1392200040

The result should look like:

date       | added_on_this_date | total_since_beginning
=========================================================
2014-02-10 |                  4 |                     4
2014-02-11 |                  9 |                    13
2014-02-12 |                  4 |                    17

I'm using this query which gives me the wrong result

SELECT FROM_UNIXTIME(added, '%Y-%m-%d') AS date,
  count(*) AS added_on_this_date,

  (SELECT COUNT(*) FROM mytable t2 WHERE t2.added <= t.added) AS total_since_beginning 

FROM mytable t WHERE 1=1 GROUP BY date

I've created a fiddle for better understanding: http://sqlfiddle.com/#!2/a72a9/1

Upvotes: 1

Views: 48

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271031

This is probably more efficient to do with variables than with a subquery:

select date, added_on_this_date,
       @cumsum := @cumsum + added_on_this_date as total_since_beginning
from (SELECT FROM_UNIXTIME(added, '%Y-%m-%d') AS date,
             count(*) AS added_on_this_date
      FROM mytable t
      WHERE 1=1
      GROUP BY date
     ) d cross join
     (select @cumsum := 0) const
order by date;

EDIT (in response to comment):

The above query has a significant performance advantage because it aggregates the data once and that is basically all the effort the query needs to do. Your original formulation with a correlated subquery can be optimized using an appropriate index. Unfortunately, once the condition in the correlated subquery uses a function on both tables, then MySQL will not be able to take advantage of an index (in general).

Because the query is aggregating by date anyway, this should perform much better.

Upvotes: 1

Rapha&#235;l Althaus
Rapha&#235;l Althaus

Reputation: 60503

your mixing timestamps and yyyy-mm-dd dates...

As you group by a yyyy-mm-dd, you're not sure to know which timestamp will be taken.

You could do

SELECT FROM_UNIXTIME(added, '%Y-%m-%d') AS date,
  count(*) AS added_on_this_date,

  (SELECT COUNT(*) FROM mytable t2 WHERE FROM_UNIXTIME(t2.added, '%Y-%m-%d') <= FROM_UNIXTIME(t.added, '%Y-%m-%d')) AS total_since_beginning 

FROM mytable t  GROUP BY date

Upvotes: 2

Related Questions