Question 22242 in Mysql Question posted in
The official MySQL documentation can be found here.

How to simplify a sql with a lot of “case when then else …” – Mysql

Run into sql code with a lot of when:

case
    when callDuration >    0 and callDuration <  30 then  1.4
    when callDuration >=  30 and callDuration <  60 then  2.3
    when callDuration >=  60 and callDuration < 120 then  3.7
    when callDuration >= 120 and callDuration < 180 then  4.5
    when callDuration >= 180 and callDuration < 240 then  5.2
    when callDuration >= 240 and callDuration < 300 then  6.1
    when callDuration >= 300 and callDuration < 360 then  7.3
    when callDuration >= 360 and callDuration < 420 then  8.4
    when callDuration >= 420 and callDuration < 480 then  9.2
    when callDuration >= 480 and callDuration < 540 then 10.1
    when callDuration >= 540 and callDuration < 600 then 11.9
    when callDuration >= 600                        then 12.3
end as duration

If there are 100 lines of this kinds of when and then, how to simplify it and more elegant, I can think use Jinjia Template or with a lookup table. Any better approach, not restrict by specific variant?

Tags:

3

Answers


  1. 0

    Approach

    I think the most elegant solution would be a lookup table (you mentioned it above).

    Below is an example, but for simplicity I didn’t enter all the ranges listed in your example.

    Create Data

    create table lookupTable(
startCallDuration int,
endCallDuration int,
returnValue float);

insert into lookupTable values (0,30,1.4);
insert into lookupTable values (30,60,2.3);
insert into lookupTable values (60,120,3.7);
insert into lookupTable values (120,999999999,4.5);

create table callDuration(
callDuration int );

insert into callDuration values (30);
insert into callDuration values (60);

    Sql Statement

    select returnValue from lookupTable l, callDuration c
where c.callDuration >= startCallDuration and
c.callDuration < endCallDuration;

    Sql Statement (Inner Join)

    select returnValue from lookupTable l inner join callDuration c
on c.callDuration >= startCallDuration and
c.callDuration < endCallDuration;

    SqlFiddle: http://www.sqlfiddle.com/#!9/11009e6/3

    Login or Signup to reply.
  2. 0

    Heartily agree! This is precisely the job for a "lookup table," which ought to have always been a fundamental part of the database for precisely this reason. These "100 lines of logic" ought to be "a 100-line lookup table." @Menelaos showed you two examples.

    Now, before proceeding further, you should carefully search through the application code for all(!) such "messy examples of the old way," to be certain that all of them exactly match with your lookup-table replacement strategy. This is a so-called highly-pervasive change which should be approached very carefully.

    Login or Signup to reply.
  3. 0

    Consider below for BigQuery

    with lookup_table as (
  select 
    [0, 30, 60, 120, 180, 240, 300, 360, 420, 480, 540, 600] ranges,
    [1.4, 2.3, 3.7, 4.5, 5.2, 6.1, 7.3, 8.4, 9.2, 10.1, 11.9, 12.3] choice
)
select callDuration, choice[safe_offset(range_bucket(callDuration, ranges) - 1)] as duration
from your_table, lookup_table

    if applied to sample data (your_table) like below

    enter image description here

    the output is

    enter image description here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search