CODEWITHSUNDEEP
mysql
Denis Dell
Denis Dell

Reputation: 49

If a value occurs for the first time mark 1 else 0, and count those in group

My data looks like this:

CreateTime | mobile
-----------+--------
2017/01/01 | 111
2017/01/01 | 222
2017/01/05 | 111
2017/01/08 | 333
2017/03/09 | 111

What I am trying to do is to add a variable if it is the first time that this mobile number occured:

CreateTime | mobile | FirstTime
-----------+--------+----------
2017/01/01 |   111  |  1
2017/01/01 |   222  |  1
2017/01/05 |   111  |  0
2017/01/08 |   333  |  1
2017/03/09 |   111  |  0
2017/03/15 |   222  |  0
2017/03/18 |   444  |  1

Basically we need to add a "true/false" column if it is the first time (based on createtime (and some other fields) which may or may not be sorted) that this specific mobile number occurred.

Ideally, this adjusted table will then be able to give me the following results when queried:

Select Month(createtime) as month,
       count(mobile) as received,
       sum(Firsttime) as Firsttimers
  from ABC
  Group by month(createtime)

Result:

Month   | Received | FirstTimers
--------+----------+------------
2017/01 |   4      |  3
2017/03 |   3      |  1

If I can get to the RESULTS without needing to create the additional step, then that will be even better.

I do however need the query to run fast hence my thinking of creating the middle table perhaps but I stand corrected.

This is my current code and it works but it is not as fast as I'd like nor is it elegant.

SELECT  Month(InF1.createtime) as 'Month',
                    Count(InF1.GUID) AS Received,
                    Sum(coalesce(Unique_lead,1)) As FirstTimers
FROM MYDATA_TABLE as InF1
Left Join
    ( SELECT createtime, mobile, GUID, 0 as Unique_lead
        FROM  MYDATA_TABLE as InF2
            WHERE createtime = (SELECT min(createtime)
                                            FROM MYDATA_TABLE as InF3
                                            WHERE InF2.mobile=InF3.mobile
                                        )
    ) as InF_unique
On Inf1.GUID  = InF_unique.GUID
group by month(createtime)

(appologies if the question is incorrectly posted, it is my first post)

Upvotes: 2

Views: 165

Answers (1)

trincot
trincot

Reputation: 350770

You could use sub query to get the first date per mobile, outer join it on the actual mobile date, and count matches. Make sure to count distinct mobile numbers to not double count the same number when it occurs with the same date twice:

select      substr(createtime, 1, 7) month,
            count(*) received,
            count(distinct grp.mobile) firsttimers
from        abc
left join  (
            select   mobile,
                     min(createtime) firsttime
            from     abc
            group by mobile
            ) grp
        on  abc.mobile = grp.mobile
        and abc.createtime = grp.firsttime
group by    month

Here is an alternative using variables, which can give you a row number:

select      substr(createtime, 1, 7) month,
            count(*) received,
            sum(rn = 1) firsttimers
from        (
            select createtime,
                   @rn := if(@mob = mobile, @rn + 1, 1) rn,
                   @mob := mobile mobile
            from   (select * from abc order by mobile, createtime) ordered,
                   (select @rn := 1, @mob := null) init
            order by mobile, createtime
            ) numbered
group by    month;

NB: If you have MySql 8+, then use window functions.

Upvotes: 1

Related Questions