CODEWITHSUNDEEP
mysql
bio
bio

Reputation: 33

MySQL get count of periods where date in row

I have an MySQL table, similar to this example:

c_id    date    value
66  2015-07-01  1
66  2015-07-02  777
66  2015-08-01  33
66  2015-08-20  200
66  2015-08-21  11
66  2015-09-14  202
66  2015-09-15  204
66  2015-09-16  23
66  2015-09-17  0
66  2015-09-18  231

What I need to get is count of periods where dates are in row. I don't have fixed start or end date, there can be any.

For example: 2015-07-01 - 2015-07-02 is one priod, 2015-08-01 is second period, 2015-08-20 - 2015-08-21 is third period and 2015-09-14 - 2015-09-18 as fourth period. So in this example there is four periods.

SELECT 
SUM(value) as value_sum,
... as period_count
FROM my_table 
WHERE cid = 66

Cant figure this out all day long.. Thx.

Upvotes: 3

Views: 889

Answers (4)

bio
bio

Reputation: 33

Tnx. @cars10 solution worked in MySQL, but could not manage to get period count to echo in PHP. It returned 0. Got it working tnx to @jarkinstall. So my final select looks something like this:

SELECT sum(coalesce(count_tmp,coalesce(count_reserved,0))) as sum ,(SELECT COUNT(*) FROM my_table WHERE cid='.$cid.' AND DATE_ADD(date, INTERVAL - 1 DAY) NOT IN (SELECT date from my_table WHERE cid='.$cid.' AND coalesce(count_tmp,coalesce(count_reserved,0))>0)) as periods ,count(*) as count ,(min(date)) as min_date ,(max(date)) as max_date FROM my_table WHERE cid=66 AND coalesce(count_tmp,coalesce(count_reserved,0))>0 ORDER BY date;

Upvotes: 0

Carsten Massmann
Carsten Massmann

Reputation: 28196

there is a simpler way of doing this, see here SQLfiddle:

SELECT min(date) start,max(date) end,sum(value) total FROM 
 (SELECT @i:=@i+1 i,
         ROUND(Unix_timestamp(date)/(24*60*60))-@i diff,
         date,value 
  FROM tbl, (SELECT @i:=0)n WHERE c_id=66 ORDER BY date) t 
GROUP BY diff

This select groups over the same difference between sequential number and date value.

Edit

As Strawberry remarked quite rightly, there was a flaw in my apporach, when a period spans a month change or indeed a change into the next year. The unix_timestamp() function can cure this though: It returns the seconds since 1970-1-1, so by dividing this number by 24*60*60 you get the days since that particular date. The rest is simple ...

If you only need the count, as your last comment stated, you can do it even simpler:

SELECT count(distinct diff) period_count FROM 
 (SELECT @i:=@i+1 i,
         ROUND(Unix_timestamp(date)/(24*60*60))-@i diff,
         date,value 
  FROM tbl,(SELECT @i:=0)n WHERE c_id=66 ORDER BY date) t

Upvotes: 2

Jake
Jake

Reputation: 822

I don't have enough reputation to comment to the above answer.

If all you need is the NUMBER of splits, then you can simply reword your question: "How many entries have a date D, such that the date D - 1 DAY does not have an entry?"

In which case, this is all you need:

SELECT 
    COUNT(*) as PeriodCount
FROM
    `periods`
WHERE
    DATE_ADD(`date`, INTERVAL - 1 DAY) NOT IN (SELECT `date` from `periods`);

In your PHP, just select the "PeriodCount" column from the first row.

You had me working on some crazy stored procedure approach until that clarification :P

Upvotes: 3

Strawberry
Strawberry

Reputation: 33935

I should get deservedly flamed for this, but anyway, consider the following...

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(date DATE NOT NULL PRIMARY KEY
,value INT NOT NULL
);

INSERT INTO my_table VALUES
('2015-07-01',1),
('2015-07-02',777),
('2015-08-01',33),
('2015-08-20',200),
('2015-08-21',11),
('2015-09-14',202),
('2015-09-15',204),
('2015-09-16',23),
('2015-09-17',0),
('2015-09-18',231);

SELECT x.*
     , SUM(y.value) total
  FROM 
     ( SELECT a.date start
            , MIN(c.date) end 
         FROM my_table a
         LEFT 
         JOIN my_table b 
           ON b.date = a.date - INTERVAL 1 DAY 
         LEFT 
         JOIN my_table c   
           ON c.date >= a.date
         LEFT 
         JOIN my_table d 
           ON d.date = c.date + INTERVAL 1 DAY
        WHERE b.date IS NULL 
          AND c.date IS NOT NULL
          AND d.date IS NULL
        GROUP 
           BY a.date
     ) x
  JOIN my_table y
    ON y.date BETWEEN x.start AND x.end
 GROUP 
    BY x.start;

+------------+------------+-------+
| start      | end        | total |
+------------+------------+-------+
| 2015-07-01 | 2015-07-02 |   778 |
| 2015-08-01 | 2015-08-01 |    33 |
| 2015-08-20 | 2015-08-21 |   211 |
| 2015-09-14 | 2015-09-18 |   660 |
+------------+------------+-------+
4 rows in set (0.00 sec) -- <-- This is the number of periods

Upvotes: 2

Related Questions