How to simplify a sql with a lot of “case when then else …”-CodePudding

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?

CodePudding user response：

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:

the output is