【SQL】分配函数一枚[AllotToTable]

适用环境:MSSQL 2005+。其中05需修改部分语句的写法才行,如:

--变量的声明和赋值需分开写
DECLARE @scale INT = 0 --需改为如下
DECLARE @scale INT; SET @scale = 0

--05不支持+=这样的复合运算符
SET @scale += 1 --需改为如下
SET @scale = @scale + 1

功能:

将一个数字(整数或有限小数)分配成指定份,每份一行,返回一个单列表格。

使用示例:

可见,这不是除法,而是为了尽可能公平的把数字分配完,该分配法有如下

特点:

  • 不存在除不尽的情况
  • 能保证所有份数之和与原数相等
  • 结果精度与原数(被分配数)精度一致。3.470的精度视为0.01,所以它分成3份是1.16、1.16、1.15,而不是1.157、1.157、1.156
  • 结果最多只会出现两种数值,大值与小值之差为原数精度的1个单位。即在原数精度范围内,各份之间的差值保证最小,以此做到尽可能公平的分配。如上例1.16与1.15相差0.01
  • 若被分配数为NULL、或份数为NULL或0,返回0行

像这种分配法我估计在多种场景都有在用,它应该有个名堂的,只是我不知道,还请知道的猿友告知,谢谢。

函数源码:

/*--------------------
函数:分配0.01
Author:AhDung
Update:201504211157
--------------------*/
ALTER FUNCTION dbo.AllotToTable(@num DECIMAL(16,4), @div TINYINT)
RETURNS @t TABLE(Val DECIMAL(16,4))
BEGIN
IF @num IS NULL OR @div IS NULL OR @div=0 RETURN

IF @div=1 --除数为1,直接返回1行
BEGIN
  INSERT @t SELECT @num
  RETURN
END

IF @num=0 --被除数为0,直接返回@div行0
BEGIN
  INSERT @t SELECT 0 FROM FMakeRows(@div)
  RETURN
END

/*得到@num的真实精度*/
DECLARE @scale INT=0, @numToInt DECIMAL(16,4)=@num
WHILE @numToInt % 1 <> 0
BEGIN
  SET @numToInt *= 10
  SET @scale += 1
END

/*填充元素(舍弃过的)*/
DECLARE @roundDownItem DECIMAL(16,4)=ROUND(@num / @div, @scale, 1)
INSERT @t SELECT @roundDownItem FROM FMakeRows(@div)

/*将余数均分到top n行。余数之所以不采用@num % @roundDownItem,是因为后者为1时就不对*/
DECLARE @mod DECIMAL(16,4) = @num - @roundDownItem*@@ROWCOUNT
IF @mod<>0
BEGIN
  DECLARE @pow INT = POWER(10, @scale)
  UPDATE TOP (CAST(ABS(@mod) * @pow AS INT)) @t
  SET Val += SIGN(@mod)/@pow
END

RETURN
END

函数中用到了一个叫做FMakeRows的自定义表值函数,有劳移步至这篇博文取用:http://www.cnblogs.com/ahdung/p/4195509.html,当然你可以小加改造,让它不依赖外部函数。

有尝试过用排名函数NTILE实现,借助它的组分配能力,但性能不理想,因为被分配数有多大,就得构造多大行数的表格,空间和时间都不值得。另请老鸟指点改善,谢谢。

-文毕-

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏数说戏聊

09-10章 汇总分组数据第9章

如果需要汇总数据而不是检索,SQL 提供专用函数,可用于检索数据,以便分析和报表生成。这种类型的检索例子有:

9610
来自专栏数据之美

Hive 在多维统计分析中的应用 & 技巧总结

多维统计一般分两种,我们看看 Hive 中如何解决: 1、同属性的多维组合统计 (1)问题: 有如下数据,字段内容分别为:url, catePath...

52980
来自专栏数据库

常用SQL语句和语法汇总

近几年数据库发挥了越来越重要的作用,这其中和大数据、数据科学的兴起有不可分割的联系。学习数据库,可以说是每个从事IT行业的必修课。你学或不学,它就在那里;你想或...

25850
来自专栏程序员的SOD蜜

使用CTE解决复杂查询的问题

最近,同事需要从数个表中查询用户的业务和报告数据,写了一个SQL语句,查询比较慢: Select S.Name, S.AccountantCode, ( Se...

25260
来自专栏Jackson0714

【T-SQL基础】02.联接查询

39790
来自专栏乐沙弥的世界

PL/SQL --> 动态SQL调用包中函数或过程

      动态SQL主要是用于针对不同的条件或查询任务来生成不同的SQL语句。最常用的方法是直接使用EXECUTE IMMEDIATE来执行动态SQL语句字符...

11620
来自专栏编码小白

ofbiz实体引擎(八) 创建表

/** * @author 郑小康 * * 1.检验实体是否为空 * * 2.检验视图实体是否为空 ...

28630
来自专栏Jackson0714

【T-SQL基础】01.单表查询-几道sql查询题

36190
来自专栏深度学习

利用RNN和LSTM生成小说题记

一、选取素材 本文选取的小说素材来自17k小说网的一篇小说《两只橙与遠太郎》,手工复制小说中的题记。 小说网址:http://www.17k.com/list/...

55970
来自专栏Leetcode名企之路

【Leetcode】175. 组合两个表

编写一个 SQL 查询,满足条件:无论 person 是否有地址信息,都需要基于上述两表提供 person 的以下信息:

9610

扫码关注云+社区

领取腾讯云代金券