SQL每日一题(20220412)--运用向下取整数和窗口函数

题目

有如下两张表F0412A

F0412B
 

希望得到以下结果：

要求结果不是小数，且type的平均相加等于总数的和就行。

例如1-SERIES包含ID为1,2,3这3行，他们的和为1,2的QUANTITY的和（因为ID为3没有值），所以我们需要将1,2的和平均分给1,2，3。这样结果的平均值就为100/3=33.33，但是要求结果不为小数，那么我们需要将他们都取为整数。最接近的平均数且整数为100的就为33,33,34。

测试数据

CREATE TABLE F0412A (
ID INT,
TYPE NVARCHAR(28)
)
INSERT INTO F0412A VALUES (1,'1-SERIES')
INSERT INTO F0412A VALUES (2,'1-SERIES')
INSERT INTO F0412A VALUES (3,'1-SERIES')
INSERT INTO F0412A VALUES (4,'2-SERIES')
INSERT INTO F0412A VALUES (5,'2-SERIES')
INSERT INTO F0412A VALUES (6,'2-SERIES')
INSERT INTO F0412A VALUES (7,'2-SERIES')
INSERT INTO F0412A VALUES (8,'2-SERIES')

CREATE TABLE F0412B (
ID INT,
QUANTITY INT
)
INSERT INTO F0412B VALUES (1,50)
INSERT INTO F0412B VALUES (2,50)
INSERT INTO F0412B VALUES (4,33)
INSERT INTO F0412B VALUES (5,33)
INSERT INTO F0412B VALUES (6,33)

我的答案

with TOTALTEMP as(
select t1.*,count(1) over(partition by t1.TYPE) typeCount,t2.total,ROW_NUMBER() over(partition by t1.TYPE ORDER BY T1.ID) NUM
from F0412A t1
inner join (
select t1.TYPE,sum(t2.QUANTITY) total
from F0412A t1
inner join F0412B t2 on t2.ID = t1.ID
group by t1.TYPE) t2 on t2.TYPE = t1.TYPE
)

select g1.ID,g1.TYPE,g1.avgType 平均
from (SELECT *,floor(total /typeCount) avgType  FROM TOTALTEMP where typeCount != NUM
union all
SELECT *, (total -  floor(total /typeCount) * (typeCount - 1)) avgType  FROM TOTALTEMP where typeCount = NUM) g1
order by g1.ID

 想法

        我是这样想的，向通过临时表取出同一个类型的数量和关联表的总和，按分组排好序。然后分两段来计算，一部分是 不等于最后一行的直接平均后向下取整数，如果是最后一行的，那么用总数 - （平均数*（除当前行的行数））得到的就是余下的

结果