sql-server - SQL Server 用户定义的函数来计算年龄段

Question

我创建了一个 UDF 来计算数据库中的年龄段。我使用了以下代码

CREATE FUNCTION Agebracket(@Ages INT)
RETURNS VARCHAR
AS
BEGIN
DECLARE @Age_Group varchar 

SET @Age_Group = CASE WHEN @Ages BETWEEN 0 AND 9 THEN  '[0-9]'
                 WHEN @Ages BETWEEN 10 AND 19 THEN  '[10-19]'
                 WHEN @Ages BETWEEN 20 AND 29 THEN  '[20-29]'
                 WHEN @Ages BETWEEN 30 AND 39 THEN  '[30-39]'
                 WHEN @Ages BETWEEN 40 AND 49 THEN  '[40-49]'
                 WHEN @Ages BETWEEN 50 AND 59 THEN  '[50-59]'
                 WHEN @Ages BETWEEN 60 AND 69 THEN  '[60-69]'
                 WHEN @Ages BETWEEN 70 AND 79 THEN  '[70-79]'
                 WHEN @Ages BETWEEN 80 AND 89 THEN  '[80-89]'
                 WHEN @Ages BETWEEN 90 AND 99 THEN  '[90-99]'
                 WHEN @Ages>=100 THEN  '[100+]'  end  
RETURN @Age_Group
END

当我用下面的例子测试时:

SELECT  [dbo].[Agebracket](10) 

输出结果为 [。

关于我能做什么的任何想法，因为我希望输出为 [10-19]

Answer 1

如果性能很重要，那么标量函数不适合您。 内联表值函数 (itvf) 几乎总是表现更好。 将 Alexei 发布的内容转换为 itvf 可使该功能在我的电脑上快 6 倍。让我演示一下。首先，这是一个使用 CHOOSE 的解决方案.我喜欢这里的 CHOOSE，因为它更简洁(但并不比老式的 CASE 语句快)。

CREATE FUNCTION dbo.agebracket(@Ages tinyint) 
RETURNS VARCHAR(10) AS
BEGIN RETURN '['+(isnull(choose(@ages/10+1,'0-9','10-19','20-29','30-39',
             '40-49','50-59','60-69','70-79','80-89','90-99'),'100+'))+']' END

请注意，我使用 tinyint 是因为我们不需要负数，而 256 足以处理年龄(除非您谈论的是国家、恐龙骨骼等)...

现在让我们将其重写为一个内联表值函数。

CREATE FUNCTION dbo.agebracket_itvf(@Ages tinyint) 
RETURNS TABLE AS RETURN 
SELECT ages = 
'['+(isnull(choose(@ages/10+1,'0-9','10-19','20-29','30-39',
             '40-49','50-59','60-69','70-79','80-89','90-99'),'100+'))+']';

接下来是一些用于性能测试的示例数据。

if object_id('tempdb..#ageList') is not null drop table #ageList;
GO
create table #ageList (age tinyint);
insert #ageList
select top (1000000) abs(checksum(newid())%100)+1
from sys.all_columns a, sys.all_columns b;

在我们测试之前，以下是您如何使用每个功能:

-- scalar version
select top(10) t.age, ages = dbo.agebracket(t.age)
from #ageList t;

-- itvf version
select top(10) t.age, fn.ages
from #ageList t
cross apply dbo.agebracket_itvf(t.age) fn;

结果:

age  ages
---- ----------
76   [70-79]
19   [10-19]
32   [30-39]
58   [50-59]
40   [40-49]
22   [20-29]
41   [40-49]
66   [60-69]
74   [70-79]
31   [30-39]    

age  ages
---- -------
76   [70-79]
19   [10-19]
32   [30-39]
58   [50-59]
40   [40-49]
22   [20-29]
41   [40-49]
66   [60-69]
74   [70-79]
31   [30-39]

现在是性能测试。

print 'scalar version'+char(13)+char(10)+replicate('-',50);
go
declare @st datetime = getdate(), @x varchar(10);
select @x = dbo.agebracket(t.age)
from #ageList t
print datediff(ms,@st,getdate());
GO 3

print 'itvf version'+char(13)+char(10)+replicate('-',50);
go
declare @st datetime = getdate(), @x varchar(10);
select @x = fn.ages
from #ageList t
cross apply dbo.agebracket_itvf(t.age) fn
print datediff(ms,@st,getdate());
GO 3

这是结果。 再次强调，itvf 版本快了 6 倍!

scalar version
--------------------------------------------------
Beginning execution loop
2140
2167
2267
Batch execution completed 3 times.

itvf version 
--------------------------------------------------
Beginning execution loop
380
383
370
Batch execution completed 3 times.