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