How to create a custom dimension on even/odd numbers


(Luc) #1

Hi,

This is a typical Friday afternoon idea. I am trying to do a “random” segmentation on user id to create two groups and I would like to have all even user id’s in group 1 and the odd user id’s in group 2. Does anyone have a smart solution to do this? :slight_smile:

Have a nice weekend.

Best,

Luc


(Luc) #2

I just posted this and then realized the solution. Apparently I cannot delete this post, so please just ignore.


(Izzy) #3

@luclafleur Want to make the most of the post and share your solution with the rest of us!? :slight_smile:


(Luc) #4

Yes, of course. I need it for a test, so I am sure there is a better solution, but for now I just created a new dimension base on the user_id dimension.

dimension: user_id_segmentation {
type: number
sql: CASE WHEN ${user_id} LIKE ‘%1’ OR
${user_id} LIKE ‘%3’ OR
${user_id} LIKE ‘%5’ OR
${user_id} LIKE ‘%7’ OR
${user_id} LIKE ‘%9’ THEN ‘1’ ELSE ‘2’ END;;
}


(Menashe Hamm) #5

Depending on your SQL dialect, searching on the end of a string can be slow. If your user ID is an integer, it may be faster to use ${user_id} % 2 where % is the modulo operator (it may be mod(${user_id},2) in your dialect. Try it my way and yours and see which is faster! (But note that my way will yield 0 and 1 whereas yours yields 2 and 1.)


(Luc) #6

Interesting, I am indeed noticing small improvements in performance. The 0 and 1 vs 1 and 2 does not matter. I only used 1 and 2 as a personal reference to odd and even. Thanks!