Select with gap in dates-CodePudding

It is possible to make a selection with a pre-defined interval of days between records, ex:

 ------ ---------------- -------- 
| user | date           |  Data  |
 -----  ---------------- -------- 
|    1 | 2022-01-01     |   1    |
|    1 | 2022-01-02     |   5    |
|    1 | 2022-01-03     |   2    |
|    1 | 2022-01-06     |   3    |
|    1 | 2022-01-07     |   7    |
|    1 | 2022-01-08     |   1    |
|    1 | 2022-01-09     |   2    |
 ------ ---------------- --------

GIVE ME ALL RECORDS WITH 2 DAYS INTERVAL FOR EACH DATE

(There may be gaps in the records at the bank as happened between 2022-01-03 and 2022-01-06)

Result:

 ------ ---------------- -------- 
| user | date           |  Data  |
 -----  ---------------- -------- 
|    1 | 2022-01-01     |   1    |
|    1 | 2022-01-06     |   3    |
|    1 | 2022-01-09     |   2    |
 ------ ---------------- --------

But this value of 2 days can be changed to 7 days, 30 etc.

Currently I'm doing this manually by code, I get the start date, add x days, do the select, add x days, do the other select until the interval ends, but this is extremely expensive and the process takes minutes.

Is there any way to do this in a single query?

CodePudding user response：

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY `date`) rn
    FROM tablename
)
SELECT * 
FROM cte
WHERE rn MOD 3 = 1