Mysql consecutive days user wise

gaps-and-islandsMySQL

Table 'present'

+--------+------------+
| emp_id | pre_date   |
+-----------+---------+
| 002    | 2019-04-01 |
| 001    | 2019-04-21 |
| 001    | 2019-04-15 |
| 001    | 2019-04-19 |
| 002    | 2019-04-15 |
| 002    | 2019-04-16 |
| 002    | 2019-04-10 |
| 001    | 2019-04-10 |
| 001    | 2019-04-20 |
| 002    | 2019-04-19 |
| 001    | 2019-04-01 |
| 001    | 2019-04-30 |
| 002    | 2019-04-21 |
| 002    | 2019-04-24 |
| 001    | 2019-04-16 |
| 002    | 2019-04-20 |
| 001    | 2019-04-24 |
+-----------+---------+

I want result

+--------+------------+
| emp_id | count      |
+-----------+---------+
| 001    |3           |
+-----------+---------+

Because Emp 001 present 2019-04-19, 2019-04-20, 2019-04-21 that 3 consecutive days

Best Answer

Simplest way to use sub query.

SELECT emp_id,count(*) as count FROM ( SELECT *, count(p.id) FROM present p GROUP BY pre_date , emp_id ) d group by d.emp_id

Related Solutions

T-sql – Using Row_Number to find consecutive row count

The general name for this type of query is "gaps and islands". One approach below. If you can have duplicates in the source data you might need dense_rank rather than row_number

WITH DATA(C) AS
(
SELECT 724 UNION ALL
SELECT 727 UNION ALL
SELECT 728 UNION ALL
SELECT 729 UNION ALL
SELECT 735 UNION ALL
SELECT 737 UNION ALL
SELECT 743 UNION ALL
SELECT 744 UNION ALL
SELECT 747 UNION ALL
SELECT 749
), T1 AS
(
SELECT C,
       C - ROW_NUMBER() OVER (ORDER BY C) AS Grp
FROM DATA)
SELECT C,
       ROW_NUMBER() OVER (PARTITION BY Grp ORDER BY C) AS Consecutive
FROM T1

Returns

C           Consecutive
----------- --------------------
724         1
727         1
728         2
729         3
735         1
737         1
743         1
744         2
747         1
749         1

PostgreSQL – Counting 3 Consecutive Unsynced Dates

This counts ranges of at least 3 days with minutes_asleep = 0.
A continuous range of 5 days still counts as 1.
A continuous range of 6 days counts as 2. Etc.
Ignoring all other entries where minutes_asleep is different.

SELECT sum(ct) AS total_count
FROM  (
   SELECT (count(*)/3)::int AS ct  -- integer division truncates as desired + see below
   FROM (
      SELECT user_id
           , the_date - (row_number() OVER (PARTITION BY user_id
                                            ORDER BY the_date))::int AS grp
      FROM   fitness_data
      WHERE  minutes_asleep = 0
      ) sub1
   GROUP  BY user_id, grp
   HAVING count(*) >= 3
   ) sub2
UNION ALL  -- see below
SELECT 0
LIMIT 1;

UNION ALL ... is just to return 0 instead of "no row" where no qualifying ranges are found. You could also use COALESCE in another subselect, but I find this more elegant.

Why (count(*)/3)::int? count() returns bigint. sum(bigint) returns numeric. I am pretty positive we don't need to go that far. Casting back to int, so we get a bigint result. Should be faster, too.

Best Answer

Related Solutions

T-sql – Using Row_Number to find consecutive row count

PostgreSQL – Counting 3 Consecutive Unsynced Dates

Related Question