mysql每日一题0727--窗口函数-mysql除法,取余数
hope you can enjoy! 😄
mysql除法与取余数
解题思路
- 利用窗口函数,求出每行的排序,每个type下的行数,每个type下的汇总求和数
- 判断如果是type的最后一行,则将余数加到该行
SELECT
a.id,
a.type,
CASE
WHEN rk = num THEN
mod_num + divide_num ELSE divide_num
END 平均
FROM
(
SELECT
a.*,
row_number() over ( PARTITION BY type ORDER BY id ASC ) rk, --- 每个type内的排序
count(1) over ( PARTITION BY type ) num, --- 每个type的行数
MOD (
sum( quantity ) over ( PARTITION BY type ),
count(1)) over ( PARTITION BY type )) mod_num, --- 取余数
floor(
sum( quantity ) over ( PARTITION BY type ) / count(1) over ( PARTITION BY type )) divide_num --- 地板除法
FROM
t0727a a
LEFT JOIN t0727b b ON a.id = b.id
) a