Hello. I apologize for an indiscreet question, but by evening I can’t think of how I can select from the database or just an operation on the resulting array.

Suppose there is a table with 3 columns.

user_id | date | clicks

user_id - just int date - has a format 01/01/2011 clicks - also int

We need to weed out those user_id whose number of clicks was zero or 2 days at a time, or 3 in total for the month.

Tell me at least an approximate condition, I will be very grateful!

  • one
    How to understand this? How can I sample from the database or just an operation on the resulting array - AseN
  • What does it mean "2 days at a time". And what does "3 in total for the month" mean (i.e., January, February, etc.)? If for a month, then for what - for any? for the current? Field type 'date'? - alexlz

2 answers 2

The first sample is a sample of pairs of consecutive dates in which 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

The second sample for the number with zeros, of which 3 and more:

 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 united through union.

This is January '12. If it is necessary at intervals, the meaning remains the same, just in the samples to display the month of the date, and group by it.

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