SQL中case when子句的使用(连续变量的区间分段统计场景)

某院系2018年计划举办为期一周的学生电竞通关能力预测比赛。现已进入决赛，有6位选手入围。要求根据给定的两张表如下所示:
UserUser表(
user_id int,—用户ID
user_name varchar(50),—用户姓名
user_type int—用户类型
)
Game_detail表(
day_id date,—玩游戏的日期
user_id int,—用户ID
game_id int,—游戏ID
duration int—玩游戏的时长(单位:秒)
)
要求：使用一条SQL语句更新该用户表(UserUser表)的user_type字段。在游戏表中(Game_detail表)累计玩过3-6(包含3和6)个不同游戏的用户更新为1，玩过6个以上不同游戏的更新为2，玩过小于3个游戏或者没有玩过游戏的用户更新为0.

1. sqlite3实现

1.1.code

import sqlite3 as lite
with lite.connect('E:/C盘jupyter代码备份/Kaggle_in/SQLite/rain.forecast.sqlite') as conn:
    curs=conn.cursor()
    #出现database is locked异常,先关闭spyder,重启Spyder后分次序执行一下三个curs.execute(),每次只能执行一个
    curs.execute("""drop table if exists UserUser;""")
    curs.execute("create table UserUser(user_id int,user_name varchar(25),user_type int)")
    #curs.execute("""drop TABLE Game_detail""")
    curs.execute("""drop table if exists Game_detail;""")
    curs.execute("""CREATE TABLE Game_detail(day_id date,user_id int,game_id int,duration int)""")
    curs.execute("""insert into  UserUser values(100001,'彭彭',0)""")
    curs.execute("""insert into  UserUser values(100002,'玉玉',0)""")
    curs.execute("""insert into  UserUser values(100003,'小青',0)""")
    curs.execute("""insert into  UserUser values(100004,'健健',0)""")
    curs.execute("""insert into  UserUser values(100005,'静静',0)""")
    curs.execute("""insert into  UserUser values(100006,'龙龙',0)""")
    #插入游戏玩耍记录表
    curs.execute("""insert into Game_detail values('2018-10-20 9:20:15',100001,1012,100)""")
    curs.execute("""insert into Game_detail values('2018-10-20 9:20:15',100002,101,100)""")
    curs.execute("""insert into Game_detail values('2018-10-20 9:20:15',100003,101,90)""")
    curs.execute("""insert into Game_detail values('2018-1-20 9:20:15',100004,1012,60)""")
    curs.execute("""insert into Game_detail values('2018-3-20 9:20:15',100005,1012,170)""")
    curs.execute("""insert into Game_detail values('2018-7-20 9:20:15',100006,101,80)""")
    curs.execute("""insert into Game_detail values('2018-10-20 9:20:15',100006,1013,150)""")
    curs.execute("""insert into Game_detail values('2018-9-20 9:20:15',100006,1013,100)""")
    curs.execute("""insert into Game_detail values('2018-2-20 9:20:15',100004,1013,100)""")
    curs.execute("""insert into Game_detail values('2018-3-20 9:20:15',100004,1013,100)""")
    curs.execute("""insert into Game_detail values('2018-2-20 9:20:15',100004,1013,40)""")
    curs.execute("""insert in