NI
Size: a a a
NI
II
NI
NI
AT
NI
mysql> select count(distinct d) from test.t WHERE d BETWEEN '2021-06-30' and '2021-07-03';
+-------------------+
| count(distinct d) |
+-------------------+
| 4 |
+-------------------+
1 row in set (0.00 sec)
AT
NI
AT
NI
mysql> select id,d, DATE_SUB(d, interval 1 day) > IFNULL(LAG(d) OVER (ORDER BY d),'1000-01-01') is_period_start from t;
+----+------------+-----------------+
| id | d | is_period_start |
+----+------------+-----------------+
| 1 | 2021-06-25 | 1 |
| 2 | 2021-06-28 | 1 |
| 3 | 2021-06-30 | 1 |
| 4 | 2021-07-01 | 0 |
| 5 | 2021-07-02 | 0 |
| 6 | 2021-07-02 | 0 |
| 7 | 2021-07-03 | 0 |
+----+------------+-----------------+
7 rows in set (0.00 sec)
AT
NI
create table x select id,d, DATE_SUB(d, interval 1 day) > IFNULL(LAG(d) OVER (ORDER BY d),'1000-01-01') is_period_start, DATE_ADD(d, interval 1 day) < IFNULL(LAG(d) OVER (ORDER BY d DESC),'9999-12-31') is_period_end from t order by id;
mysql> SELECT MIN(d),COUNT(distinct d) FROM (select d, (SELECT d FROM x x1 WHERE x.d >= x1.d and x1.is_period_start=1 ORDER BY d desc LIMIT 1) start_date from x) a GROUP BY start_date;
+------------+-------------------+
| MIN(d) | COUNT(distinct d) |
+------------+-------------------+
| 2021-06-25 | 1 |
| 2021-06-28 | 1 |
| 2021-06-30 | 4 |
| 2021-07-05 | 1 |
+------------+-------------------+
4 rows in set (0.00 sec)
NI
NI
NI
create table x select id,d, DATE_SUB(d, interval 1 day) > IFNULL(LAG(d) OVER (ORDER BY d),'1000-01-01') is_period_start from t order by id;
NI
AT
NI
AT
NI