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

Question

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)

See https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column?rq=1 for better ways to write the subquery that gets the minimum row for each `mobile` number. — Barmar, Aug 31 '17 at 23:51
Appologies, I've just fixed and added (unique_lead) to the select statement. My bad.(Ps, thanks for fixing the formating) — Denis Dell, Sep 01 '17 at 00:03

trincot · Answer 1 · 2017-09-01T00:46:19.377

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.

Thanks for the time to answer, appreciated. However, I've tried this approach before and the reason I ended up not doing it this way was that I had a number of instances where the createtime for the same mobile number was the exact same (to the second). Using the above (or very similar code) I've then counted that specific record twice. — Denis Dell, Sep 01 '17 at 00:09
You can use the word `distinct` in `count(distinct mobile)` for that. I also added an alternative using variables. — trincot, Sep 01 '17 at 00:47
@denisdell in which case, you need to amend your question to include the PRIMARY KEY — Strawberry, Sep 01 '17 at 06:25

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

1 Answers1