0

Now I am using MySQL 5.7, I have some data like this:

user_id

63763
63763
NULL
16459,16535,16613,16615,42896,42911,62463,62464,63594,63763,63764,63765,63766,63769,63930,63934,63935
NULL
NULL

I want to concat the user_id and distinct , finnally count the result of distinct result(finally I find the result still have duplicate value), is it possible to complete it in one sql? I known distinct like this:

select group_concat(DISTINCT user_id SEPARATOR ',')
from report_summary
where statistic_time >= 1615824000000

how to count the result?

enter image description here

and now I found the result still have duplicate value using this sql:

select group_concat(DISTINCT bet_user_ids SEPARATOR ',')
from report_summary
where statistic_time >= 1616083200000
and statistic_time <= 1616169599999 

the result is:

16459,16535,16613,16615,18627,42890,42896,46521,62463,62464,63591,63595,63763,63764,63765,63769,63934,63939,63940,42890,42896,42890,42896,62463,63763

the user 42890 have three times in the result.

1
  • Split CSV to separate values, then concat distinct values back. – Akina Mar 16 at 9:24
1

There's no direct way to do this with one operator, but you can use the following trick to accomplish it:

select group_concat(DISTINCT user_id SEPARATOR ','),
    LENGTH(group_concat(DISTINCT user_id SEPARATOR ',')) - LENGTH(REPLACE(group_concat(DISTINCT user_id SEPARATOR ','), ',', '')) + 1 AS IdCount
from report_summary
where statistic_time >= 1615824000000

Basically it counts the number of commas (+ 1) in the list by subtracting the length of the string after removing the commas from the original string's length. I thought it was pretty nifty and found it in this StackOverflow answer.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.