How to select group by with Regex

Question

I've a table with the columns: itime, service, count.

I can write such a SQL to query some result:

SELECT
    toUnixTimestamp(toStartOfMinute(itime)) * 1000 as t,
    service,
    sum(count)
FROM myTable
WHERE
    -- aaa and bbb are two integers
    itime BETWEEN toDateTime(aaa) AND toDateTime(bbb)
    and service like 'mySvr_%'
GROUP BY
    service,
    t
ORDER BY t

It works as expected.

For the column service, the contents always start with mySvr_, such as mySvr_101, mySvr_102, mySvr_201, mySvr_202.

Now, I want to group by the service with some regular expression so that the result will be group by like this: mySvr_1xx, mySvr_2xx, mySvr_3xx etc.

But I don't know how. Could someone help me?

Answer 1

We can use the case statements to specify the pattern to get desired output using GROUP BY and LIKE:

For the below table,

    >>SELECT * FROM temp_table;
    +-------+------------+-------+
    | itime | service    | count |
    +-------+------------+-------+
    |     1 | mySvr_1234 |     2 |
    |     2 | mySvr_2123 |     3 |
    |     1 | mySvr_1233 |     4 |
    |     4 | mySvr_3212 |     3 |
    |     5 | mySvr_2317 |     2 |
    +-------+------------+-------+
    5 rows in set (0.00 sec)

the code is as follows:

    SELECT service,count(*),
     CASE
       WHEN service LIKE 'mySvr_1%' THEN '1st'
       WHEN service LIKE 'mySvr_2%' THEN '2nd'
       ELSE '3rd'
     END AS group_by_result
    FROM stackOverflow
    GROUP BY
     CASE
       WHEN service LIKE 'mySvr_1%' THEN '1st'
       WHEN service LIKE 'mySvr_2%' THEN '2nd'
       ELSE '3rd'
     END;