php array processing

Hello.
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.


Answer 2

Maybe so…

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')