mysql每日一题0727--窗口函数-mysql除法,取余数

hope you can enjoy! 😄

mysql除法与取余数

image

image

解题思路

  • 利用窗口函数,求出每行的排序,每个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

posted on 2021-07-27 12:36  多一点  阅读(511)  评论(0编辑  收藏  举报

导航