题解 | #根据指定记录是否存在输出不同情况#

题目：请你筛选表中的数据，当有任意一个0级用户未完成试卷数大于2时，输出每个0级用户的试卷未完成数和未完成率（保留3位小数）；若不存在这样的用户，则输出所有有作答记录的用户的这两个指标。结果按未完成率升序排序。

条件分解：

• 任意一个0级用户未完成试卷数大于2时，输出每个0级用户的试卷未完成数和未完成率（保留3位小数）
• 若不存在这样的用户，则输出所有有作答记录的用户的这两个指标
• 按未完成率升序排序

思路：

第一步：算出所有用户的试卷未完成数和未完成率

        select
          uid,
          sum(if (submit_time is null, 1, 0)) incomplete_cnt,
          sum(if (submit_time is null, 1, 0)) / count(*) incomplete_rate
        from
          exam_record
        group by
          uid         

第二步：已用户表为主表，关联上面算出来的完成率，并开窗算出每个级别下最大的未完成数

第三步：拿出level=0的用户uid

	  select
        uid
      from
        user_info
      where
        level = 0

第四步：拿出有作答记录的用户uid

      select
        uid
      from
        exam_record
      group by
        uid

第五步：对任意一个0级用户未完成试卷数大于2进行判断，因为我们前面已经算出来每个等级对应未完成数的最大值，所以只要判断最大值是否大于2

if (
    (
      select
        max(max_incomplete_cnt)
      from
        complete_info
      where
        level = 0
    ) > 2

完整代码：

with
  complete_info as (
    select
      t1.uid,
      t1.level,
      coalesce(incomplete_cnt, 0) incomplete_cnt,
      round(coalesce(incomplete_rate, 0), 3) incomplete_rate,
      max(incomplete_cnt) over (
        partition by
          t1.level
      ) max_incomplete_cnt
    from
      user_info t1
      left join (
        select
          uid,
          sum(if (submit_time is null, 1, 0)) incomplete_cnt,
          sum(if (submit_time is null, 1, 0)) / count(*) incomplete_rate
        from
          exam_record
        group by
          uid
      ) t2 on t1.uid = t2.uid
  )
select
  uid,
  incomplete_cnt,
  incomplete_rate
from
  complete_info
where
  if (
    (
      select
        max(max_incomplete_cnt)
      from
        complete_info
      where
        level = 0
    ) > 2,
    uid in (
      select
        uid
      from
        user_info
      where
        level = 0
    ),
    uid in (
      select
        uid
      from
        exam_record
      group by
        uid
    )
  )
order by incomplete_rate asc