代码之家 › 专栏 › 技术社区 › Vinko Vrsalovic

简化(别名)T-SQL case语句。有什么改进吗?

case tsql sql-server

Vinko Vrsalovic · 技术社区 · 15 年前

正如你所看到的,这段时间很糟糕。还有其他选择吗?我试图在group by子句中使用列别名,但没有任何效果。

select count(callid) ,
case
        when callDuration > 0 and callDuration < 30 then 1
        when callDuration >= 30 and callDuration < 60 then 2
        when callDuration >= 60 and callDuration < 120 then 3
        when callDuration >= 120 and callDuration < 180 then 4
        when callDuration >= 180 and callDuration < 240 then 5
        when callDuration >= 240 and callDuration < 300 then 6
        when callDuration >= 300 and callDuration < 360 then 7
        when callDuration >= 360 and callDuration < 420 then 8
        when callDuration >= 420 and callDuration < 480 then 9
        when callDuration >= 480 and callDuration < 540 then 10
        when callDuration >= 540 and callDuration < 600 then 11
        when callDuration >= 600 then 12
end as duration
from callmetatbl
where programid = 1001 and callDuration > 0
group by case
        when callDuration > 0 and callDuration < 30 then 1
        when callDuration >= 30 and callDuration < 60 then 2
        when callDuration >= 60 and callDuration < 120 then 3
        when callDuration >= 120 and callDuration < 180 then 4
        when callDuration >= 180 and callDuration < 240 then 5
        when callDuration >= 240 and callDuration < 300 then 6
        when callDuration >= 300 and callDuration < 360 then 7
        when callDuration >= 360 and callDuration < 420 then 8
        when callDuration >= 420 and callDuration < 480 then 9
        when callDuration >= 480 and callDuration < 540 then 10
        when callDuration >= 540 and callDuration < 600 then 11
        when callDuration >= 600 then 12
end

编辑: 我真的想问一下如何使用一个案例源,但是无论如何,案例修改都是受欢迎的(尽管因为间隔可能会被修改,甚至可能会自动生成),所以不太有用。

正如一些人所考虑的,callDuration实际上是一个浮点值,因此一些列出的解决方案在我的用例中是无效的,方法是将值从间隔中去掉。

教训:

在case表达式中查找模式,以在可能和值得时减少它

 case
    when callDuration > 0 AND callDuration < 30 then 1
    when callDuration > 600 then 12
    else floor(callDuration/60) + 2  end
 end as duration

使用内联视图具有案例的单一来源

select count(d.callid), d.duration
from (   
   select callid
        , case
           when callDuration > 0 AND callDuration < 30 then 1
           when callDuration > 600 then 12
           else floor(callDuration/60) + 2  end
          end as duration
    from callmetatbl
    where programid = 1001
          and callDuration > 0
) d
group by d.duration

或使用公共表表达式

   with duration_case as (
      select callid ,
      case
        when callDuration > 0 AND callDuration < 30 then 1
        when callDuration > 600 then 12
        else floor(callDuration/60) + 2  end
      end as duration
   from callmetatbl
   where programid = 1001 and callDuration > 0 )
    select count(callid), duration
    from duration_case
    group by duration

或者使用用户定义的函数(到目前为止没有示例:-)

或者使用查阅表格和联接

DECLARE @t TABLE(durationFrom float, durationTo float, result INT)
--populate table with values so the query works
select count(callid) , COALESCE(t.result, 12)
from callmetatbl JOIN @t AS t ON callDuration >= t.durationFrom 
AND callDuration < t.durationTo 
where programid = 1001 and callDuration > 0

多亏了所有人,我很难选择一个被接受的答案,因为许多人涵盖了问题的不同部分(我当时认为这是一个简单的问题,有一个直截了当的答案:—),很抱歉弄混了。

11 回复 | 直到 15 年前

jdt141 15 年前

问: 如何在group by子句中使用别名

一种方法是使用内联视图。[编辑]雷姆斯·鲁萨诺的回答(+1!)给出了一个实现相同操作的公共表表达式的示例。[/编辑]

内联视图为复杂表达式提供一个简单的“别名”,然后可以在外部查询的group by子句中引用该表达式:

select count(d.callid)
     , d.duration
  from (select callid
             , case
               when callDuration >= 600 then 12
               when callDuration >= 540 then 11
               when callDuration >= 480 then 10
               when callDuration >= 420 then 9
               when callDuration >= 360 then 8
               when callDuration >= 300 then 7
               when callDuration >= 240 then 6
               when callDuration >= 180 then 5
               when callDuration >= 120 then 4
               when callDuration >=  60 then 3
               when callDuration >=  30 then 2
               when callDuration >    0 then 1
               --else null
               end as duration
             from callmetatbl
            where programid = 1001
              and callDuration > 0
       ) d
group by d.duration

我们把它打开。

调用内部(缩进)查询并 内联视图 (我们给它起了一个别名 d )
在外部查询中,我们可以引用别名 duration 从 D

这足以回答你的问题。如果要查找等效替换表达式,则 泰克布鲁斯 ( + 1! )是正确的答案(它适用于边界和非整数。)

从tekblues(+1!):

select count(d.callid)
     , d.duration
  from (select callid
             , case 
               when callduration >=30 and callduration<600
                    then floor(callduration/60)+2
               when callduration>0 and callduration< 30
                    then 1 
               when callduration>=600
                    then 12
               end as duration
          from callmetatbl
         where programid = 1001
           and callDuration > 0
       ) d
 group by d.duration

(这应该足以回答您的问题。)

[更新:]sample 用户定义函数 (替换内联case表达式)

CREATE FUNCTION [dev].[udf_duration](@cd FLOAT)
RETURNS SMALLINT
AS
BEGIN
  DECLARE @bucket SMALLINT
  SET @bucket = 
  CASE
  WHEN @cd >= 600 THEN 12
  WHEN @cd >= 540 THEN 11
  WHEN @cd >= 480 THEN 10
  WHEN @cd >= 420 THEN 9
  WHEN @cd >= 360 THEN 8
  WHEN @cd >= 300 THEN 7
  WHEN @cd >= 240 THEN 6
  WHEN @cd >= 180 THEN 5
  WHEN @cd >= 120 THEN 4
  WHEN @cd >=  60 THEN 3
  WHEN @cd >=  30 THEN 2
  WHEN @cd >    0 THEN 1
  --ELSE NULL
  END
  RETURN @bucket
END

select count(callid)
     , [dev].[udf_duration](callDuration)
  from callmetatbl
 where programid = 1001
   and callDuration > 0
 group by [dev].[udf_duration](callDuration)

笔记: 请注意,用户定义的函数将增加开销,并且(当然)添加对另一个数据库对象的依赖关系。

这个示例函数等价于原始表达式。op case表达式没有任何间隙,但它引用了每个“断点”两次,我更喜欢只测试下限。(条件满足时返回case。反向执行测试可以让未处理的情况(<=0或NULL)在没有测试的情况下通过,并且 ELSE NULL 不是必需的,但为了完整性可以添加。

其他详细信息

(一定要检查性能和优化器计划,以确保它与原始计划相同(或不明显比原始计划差)。在过去,我在将谓词推送到内联视图中时遇到了一些问题,在您的情况下,这看起来并不是一个问题。)

存储视图

请注意 内联的 视图也可以存储为数据库中的视图定义。但是除了从语句中“隐藏”复杂的表达式之外,没有理由这样做。

简化复杂表达式

使复杂表达式“简单”的另一种方法是使用用户定义的函数。但是,用户定义的函数本身就有一组问题(包括性能下降)。

添加数据库“查找”表

有些答案建议向数据库中添加“查阅”表。我不认为这真的是必要的。当然可以这样做,如果你想得到不同的值 期间 从 callDuration 在空中, 没有必须修改查询并没有必须运行任何DDL语句(例如更改视图定义或修改用户定义函数)。

通过联接到“lookup”表,一个好处是只需对“lookup”表执行DML操作,查询就可以返回不同的结果集。

但同样的优势实际上也可能是缺点。

如果收益大于弊,请仔细考虑。考虑新表对单元测试的影响,如何验证查找表的内容是否有效且未更改(是否有重叠)?有什么差距吗?)对代码持续维护的影响(由于额外的复杂性)。

一些重大假设

这里给出的许多答案似乎都假定 调用持续时间 是整数数据类型。他们似乎忽视了它不是整数的可能性,但也许我错过了问题中的金块。

演示以下内容非常简单:

callDuration BETWEEN 0 AND 30

是不是相当于

callDuration > 0 AND callDuration < 30

Spencer Ruport 15 年前

你有什么理由不使用 between ?案例陈述本身并不太糟糕。如果你真的讨厌它,你可以把这些扔到一张桌子上,然后把它标出来。

Durations
------------------
low   high   value
0     30     1
31    60     2

等。。。

(SELECT value FROM Durations WHERE callDuration BETWEEN low AND high) as Duration

编辑:或者,在使用浮动的情况下, 之间 变得笨重。

(SELECT value FROM Durations WHERE callDuration >= low AND callDuration <= high) as Duration

tekBlues 15 年前

案件可以这样写:

case 
when callduration >=30 and callduration<600 then floor(callduration/60)+2
when callduration>0 and callduration< 30 then 1 
when callduration>=600 then 12
end

不需要HAVING,将其替换为“where callDuration>0”

我喜欢前面给出的翻译表答案!那是最好的解决办法

Vinko Vrsalovic 15 年前

您需要将案例进一步推下查询树,以便GroupBy可以看到其投影。这可以通过两种方式实现:

使用派生表(Spencer、Adam和Jeremy已经演示了如何使用)

使用公用表表达式

with duration_case as (
select callid ,
case
    when callDuration > 0 and callDuration < 30 then 1
    when callDuration >= 30 and callDuration < 60 then 2
    when callDuration >= 60 and callDuration < 120 then 3
    when callDuration >= 120 and callDuration < 180 then 4
    when callDuration >= 180 and callDuration < 240 then 5
    when callDuration >= 240 and callDuration < 300 then 6
    when callDuration >= 300 and callDuration < 360 then 7
    when callDuration >= 360 and callDuration < 420 then 8
    when callDuration >= 420 and callDuration < 480 then 9
    when callDuration >= 480 and callDuration < 540 then 10
    when callDuration >= 540 and callDuration < 600 then 11
    when callDuration >= 600 then 12
end as duration
from callmetatbl
where programid = 1001 and callDuration > 0 )
   select count(callid), duration
   from duration_case
   group by duration

这两种解决方案在各个方面都是等效的。我发现CTE更具可读性,有些人更喜欢派生表,因为它更易于移植。

Joel Coehoorn 15 年前

分 callDuration 60:

case
        when callDuration between 1 AND 29 then 1
        when callDuration > 600 then 12
        else (callDuration /60) + 2  end
end as duration

注意 between 包含界限,我假设CallDuration将被视为整数。

更新:
将这个问题与其他一些答案结合起来,就可以将整个查询归结为:

select count(d.callid), d.duration
from (   
       select callid
            , case
                when callDuration between 1 AND 29 then 1
                when callDuration > 600 then 12
                else (callDuration /60) + 2  end
              end as duration
        from callmetatbl
        where programid = 1001
              and callDuration > 0
    ) d
group by d.duration

Adam Robinson 15 年前

select count(callid), duration from
(
    select callid ,
    case
            when callDuration > 0 and callDuration < 30 then 1
            when callDuration >= 30 and callDuration < 60 then 2
            when callDuration >= 60 and callDuration < 120 then 3
            when callDuration >= 120 and callDuration < 180 then 4
            when callDuration >= 180 and callDuration < 240 then 5
            when callDuration >= 240 and callDuration < 300 then 6
            when callDuration >= 300 and callDuration < 360 then 7
            when callDuration >= 360 and callDuration < 420 then 8
            when callDuration >= 420 and callDuration < 480 then 9
            when callDuration >= 480 and callDuration < 540 then 10
            when callDuration >= 540 and callDuration < 600 then 11
            when callDuration >= 600 then 12
    end as duration
    from callmetatbl
    where programid = 1001 and callDuration > 0
) source
group by duration

JeremyDWill 15 年前

未经测试的:

select  count(callid) , duracion
from
    (select 
        callid,
        case        
            when callDuration > 0 and callDuration < 30 then 1        
            when callDuration >= 30 and callDuration < 60 then 2        
            when callDuration >= 60 and callDuration < 120 then 3        
            when callDuration >= 120 and callDuration < 180 then 4        
            when callDuration >= 180 and callDuration < 240 then 5        
            when callDuration >= 240 and callDuration < 300 then 6        
            when callDuration >= 300 and callDuration < 360 then 7        
            when callDuration >= 360 and callDuration < 420 then 8        
            when callDuration >= 420 and callDuration < 480 then 9        
            when callDuration >= 480 and callDuration < 540 then 10        
            when callDuration >= 540 and callDuration < 600 then 11        
            when callDuration >= 600 then 12        
            else 0
        end as duracion
    from callmetatbl
    where programid = 1001) GRP
where duracion > 0
group by duracion

Joel Coehoorn 15 年前

将所有事例添加到表变量中并执行外部联接

DECLARE @t TABLE(durationFrom INT, durationTo INT, result INT)
--        when callDuration > 0 and callDuration < 30 then 1
INSERT INTO @t VALUES(1, 30, 1);
--        when callDuration >= 30 and callDuration < 60 then 2
INSERT INTO @t VALUES(30, 60, 2);

select count(callid) , COALESCE(t.result, 12)
from callmetatbl JOIN @t AS t ON callDuration >= t.durationFrom AND callDuration  < t.durationTo 
where programid = 1001 and callDuration > 0

tom 15 年前

这是我的照片。您需要的所有组件都可以用直接的SQL来完成。

select
  count(1) as total
 ,(fixedDuration / divisor) + adder as duration
from
(
    select
      case/*(30s_increments_else_60s)*/when(callDuration<60)then(120)else(60)end as divisor
     ,case/*(increment_by_1_else_2)*/when(callDuration<30)then(1)else(2)end as adder
     ,(/*duration_capped@600*/callDuration+600-ABS(callDuration-600))/2 as fixedDuration
     ,callDuration
    from 
      callmetatbl
    where
      programid = 1001
    and 
      callDuration > 0
) as foo
group by
  (fixedDuration / divisor) + adder

这是我用来测试的SQL。 (我没有自己的callmetbl;)

select
  count(1) as total
 ,(fixedDuration / divisor) + adder as duration
from
(
    select
      case/*(30s_increments_else_60s)*/when(callDuration<60)then(120)else(60)end as divisor
     ,case/*(increment_by_1_else_2)*/when(callDuration<30)then(1)else(2)end as adder
     ,(/*duration_capped@600*/callDuration+600-ABS(callDuration-600))/2 as fixedDuration
     ,callDuration
    from -- callmetatbl -- using test view below
      (  
       select 1001 as programid,   0 as callDuration union
       select 1001 as programid,   1 as callDuration union
       select 1001 as programid,  29 as callDuration union
       select 1001 as programid,  30 as callDuration union
       select 1001 as programid,  59 as callDuration union
       select 1001 as programid,  60 as callDuration union
       select 1001 as programid, 119 as callDuration union
       select 1001 as programid, 120 as callDuration union
       select 1001 as programid, 179 as callDuration union
       select 1001 as programid, 180 as callDuration union
       select 1001 as programid, 239 as callDuration union
       select 1001 as programid, 240 as callDuration union
       select 1001 as programid, 299 as callDuration union
       select 1001 as programid, 300 as callDuration union
       select 1001 as programid, 359 as callDuration union
       select 1001 as programid, 360 as callDuration union
       select 1001 as programid, 419 as callDuration union
       select 1001 as programid, 420 as callDuration union
       select 1001 as programid, 479 as callDuration union
       select 1001 as programid, 480 as callDuration union
       select 1001 as programid, 539 as callDuration union
       select 1001 as programid, 540 as callDuration union
       select 1001 as programid, 599 as callDuration union
       select 1001 as programid, 600 as callDuration union
       select 1001 as programid,1000 as callDuration
      ) as callmetatbl
    where
      programid = 1001
    and 
      callDuration > 0
) as foo
group by
  (fixedDuration / divisor) + adder

SQL输出如下所示,每个持续时间(bucket)1到12统计2条记录。

total  duration
2             1
2             2
2             3
2             4
2             5
2             6
2             7
2             8
2             9
2            10
2            11
2            12

下面是“foo”子查询的结果:

divisor adder   fixedDuration  callDuration
120         1               1             1
120         1              29            29
120         2              30            30
120         2              59            59
60          2              60            60
60          2             119           119
60          2             120           120
60          2             179           179
60          2             180           180
60          2             239           239
60          2             240           240
60          2             299           299
60          2             300           300
60          2             359           359
60          2             360           360
60          2             419           419
60          2             420           420
60          2             479           479
60          2             480           480
60          2             539           539
60          2             540           540
60          2             599           599
60          2             600           600
60          2             600          1000

干杯。

Wyatt Barnett 15 年前

这里的用户定义函数有什么问题?您可以从视觉上清理代码并以这种方式集中功能。从性能上看,我看不出打击太可怕,除非你做了一些真正的弱智在说自卫队。

dance2die 15 年前

为创建查阅表格 duration
使用查找表将加快 SELECT 声明也一样。

下面是查找表的最终结果。

select  count(a.callid), b.ID as duration
from    callmetatbl a
        inner join DurationMap b 
         on a.callDuration >= b.Minimum
        and a.callDuration < IsNUll(b.Maximum, a.CallDuration + 1)
group by  b.ID

这是查找台。

create table DurationMap (
    ID          int identity(1,1) primary key,
    Minimum     int not null,
    Maximum     int 
)

insert  DurationMap(Minimum, Maximum) select 0,30
insert  DurationMap(Minimum, Maximum) select 30,60
insert  DurationMap(Minimum, Maximum) select 60,120
insert  DurationMap(Minimum, Maximum) select 120,180
insert  DurationMap(Minimum, Maximum) select 180,240
insert  DurationMap(Minimum, Maximum) select 240,300
insert  DurationMap(Minimum, Maximum) select 300,360
insert  DurationMap(Minimum, Maximum) select 360,420
insert  DurationMap(Minimum, Maximum) select 420,480
insert  DurationMap(Minimum, Maximum) select 480,540
insert  DurationMap(Minimum, Maximum) select 540,600
insert  DurationMap(Minimum) select 600