I apologize for the indiscreet question, but by the evening I can’t figure out how to make a selection from the database or just an operation on the resulting array.
Let’s say there is a table with 3 columns
user_id | date | clicks
user_id – just an int
date – has the format 01/31/2011
clicks – also int
It is necessary to weed out those user_ids whose number of clicks was at zero, either 2 days at a time, or 3 in total per month.
Tell me at least an approximate condition, I will be very grateful!
Answer 1, authority 100%
The first selection is a selection of pairs of consecutive dates where the clicks are zero:
SELECT t1.user_id FROM tab t1, tab t2 WHERE t1.dat=date_add(t2.dat,INTERVAL 1 DAY) AND t1.dat BETWEEN STR_TO_DATE('01.01.2012','%d.%m.%Y') and STR_TO_DATE('30.01.2012','%d.%m.%Y') AND t1.user_id=t2.user_id AND t1.clicks = 0 AND t2.clicks = 0 GROUP BY t1.user_id
Second sample with 3 or more zeros:
SELECT t3.user_id FROM tab t3 WHERE t3.clicks=0 AND t3.dat BETWEEN STR_TO_DATE('01.01.2012','%d.%m.%Y') and STR_TO_DATE('31.01.2012','%d.%m.%Y') GROUP BY t3.user_id HAVING count(t3.user_id)>=3
Further, these requests can be combined through union.
This is as of January ’12. If you need intervals, the meaning remains the same, just display the month of the date in the selections, and group by it.
SELECT * FROM table t WHERE t.clicks = 0 AND t.clicks < 3 AND t.date BETWEEN TO_DATE('01.01.2012','DD/MM/YYYY') AND TO_DATE('01.02.2012','DD/MM/YYYY')