源数据 用户视频互动表tbuservideolog id uid videoid starttime endtime iffollow iflike ifretweet commentid 1hr101hr2001hr2021100110：00：00 2021100110：00：30 0hr1hr1hrNULL 2hr102hr2001hr2021100110：00：00 2021100110：00：24 0hr0hr1hrNULL 3hr103hr2001hr2021100111：00：00 2021100111：00：34 0hr1hr0hr1732526hr4hr101hr2002hr2021090110：00：00 202190110：00：42 1hr0hr1hrNULL 5hr102hr2002hr2021100111：00：00 2021100110：00：30 1hr0hr1hrNULL （uid用户ID，videoid视频ID，starttime开始观看时间，endtime结束观看时间，iffollow是否关注，iflike是否点赞，ifretweet是否转发，commentid评论ID） 短视频信息表tbvideoinfo id videoid author tag duration releasetime 1hr2001hr901hr影视 30hr2021010107：00：00 2hr2002hr901hr美食 60hr2021010107：00：00 3hr2003hr902hr旅游 90hr2021010107：00：00 （videoid视频ID，author创作者ID，tag类别标签，duration视频时长（秒），releasetime发布时间）问题：计算2021年里有播放记录的每个视频的完播率（结果保留三位小数），并按完播率降序排序 注：视频完播率是指完成播放次数占总播放次数的比例。简单起见，结束观看时间与开始播放时间的差视频时长时，视为完成播放。 输出示例： 示例数据的结果如下： videoid avgcompplayrate 2001hr0。667 2002hr0。000 SQL代码方法1使用sum函数selecta。videoid，round（sum（if（timestampdiff（second，a。starttime，a。endtime）b。duration，1，0））count（a。videoid），3）asavgcompplayratefromtbuservideologajointbvideoinfobona。videoidb。videoidwhereyear（a。starttime）2021groupbya。videoidorderbyavgcompplayratedesc方法2使用avg函数selecta。videoid，round（avg（if（timestampdiff（second，a。starttime，a。endtime）b。duration，1，0）），3）asavgcompplayratefromtbuservideologajointbvideoinfobona。videoidb。videoidwhereyear（a。starttime）2021groupbya。videoidorderbyavgcompplayratedesc 注意区分timestampdiff（）与datediff（）用法 timestampdiff（）函数的作用是返回两个日期时间之间的整数差。而datediff（）函数的作用也是返回两个日期值之差。 它们的函数语法分别为： TIMESTAMPDIFF（unit，starttime，endtime） DATEDIFF（starttime，endtime）问题：计算各类视频的平均播放进度，将进度大于60的类别输出。 注：播放进度播放时长视频时长100，当播放时长大于视频时长时，播放进度均记为100。结果保留两位小数，并按播放进度倒序排序。 输出示例： 示例数据的输出结果如下： tag avgplayprogress 影视 90。00 美食 75。00 SQL代码selecttag，concat（avg1，）avgplayprogressfrom（selectb。tag，round（avg（if（timestampdiff（second，a。starttime，a。endtime）b。duration，1，timestampdiff（second，a。starttime，a。endtime）b。duration））100，2）asavg1fromtbuservideologajointbvideoinfobona。videoidb。videoidgroupbyb。taghavingavg160）torderbyavgplayprogressdesc问题：统计在有用户互动的最近一个月（按包含当天在内的近30天算，比如10月31日的近30天为10。210。31之间的数据）中，每类视频的转发量和转发率（保留3位小数）。 注：转发率转发量播放量。结果按转发率降序排序。 输出示例： 示例数据的输出结果如下 tag retweetcut retweetrate 影视 2hr0。667 美食 1hr0。500 SQL代码selectb。tag，sum（a。ifretweet）asretweetcut，round（avg（a。ifretweet），3）asretweetratefromtbuservideologajointbvideoinfobona。videoidb。videoidwheredate（a。starttime）datesub（（selectmax（starttime）fromtbuservideolog），INTERVAL30DAY）groupbyb。tagorderbyretweetratedesc注意：date（a。starttime）datesub（（selectmax（starttime）fromtbuservideolog），INTERVAL30DAY）不能写成date（a。starttime）datesub（max（starttime），INTERVAL30DAY）知道原因的同学跟我说一下 注意datesub函数用法 DATESUB（）函数从日期减去指定的时间间隔。 DATESUB（date，INTERVALexprtype） 实例：DATESUB（202332，INTERVAL1day）返回的是202331问题：计算2021年里每个创作者每月的涨粉率及截止当月的总粉丝量 注：涨粉率（加粉量掉粉量）播放量。结果按创作者ID、总粉丝量升序排序。iffollow是否关注为1表示用户观看视频中关注了视频创作者，为0表示此次互动前后关注状态未发生变化，为2表示本次观看过程中取消了关注。 解题思路：联结按照作者和月进行分组：groupbyauthor，dateformat（starttime，Ym）涨粉率：加粉和减粉变化（粉丝变化）：sum（if（iffollow2，1，iffollow））播放量：count（1）截止当月的总粉丝量sum（粉丝变化）over（partitionbyauthororderbydateformat（starttime，Ym））条件：2021年方法1：selecta。author，dateformat（b。starttime，Ym）asmonth，round（sum（if（b。iffollow2，1，b。iffollow））count（1），3）asfansgrowthrate，sum（sum（if（b。iffollow2，1，b。iffollow）））over（partitionbya。authororderbydateformat（b。starttime，Ym））astotalfansfromtbvideoinfoajointbuservideologbona。videoidb。videoidWHEREYEAR（b。starttime）2021groupbya。author，monthorderbya。author，totalfans方法2：withmainas（统计每个用户的播放量、加粉量、掉粉量selectauthor，mid（starttime，1，7）asmonth，count（starttime）asb，count（if（iffollow1，1，null））asfollowadd，count（if（iffollow2，1，null））asfollowsubfromtbuservideologa，tbvideoinfobwherea。videoidb。videoidandyear（starttime）2021groupbyauthor，month）计算2021年里每个创作者每月的涨粉率及截止当月的总粉丝量selectauthor，month，round（（followaddfollowsub）b，3）asfansgrowthrate，sum（followaddfollowsub）over（partitionbyauthororderbymonth）astotalfansfrommainorderbyauthor，totalfans 关于开窗函数用法可以参考学习：https：blog。csdn。netweixin43997319articledetails124964670问题：统计2021年国庆头3天每类视频每天的近一周总点赞量和一周内最大单天转发量，结果按视频类别降序、日期升序排序。假设数据库中数据足够多，至少每个类别下国庆头3天及之前一周的每天都有播放记录。 输出示例： 示例数据的输出结果如下 tag dt sumlikecnt7d maxretweetcnt7d 旅游 20211001 5hr2hr旅游 20211002 5hr3hr旅游 20211003 6hr3hr解释： 由表tbuservideolog里的数据可得只有旅游类视频的播放，2021年9月25到10月3日每天的点赞量和转发量如下： tag dt likecnt retweetcnt 旅游 20210925 1hr2hr旅游 20210926 0hr1hr旅游 20210927 1hr0hr旅游 20210928 0hr1hr旅游 20210929 0hr1hr旅游 20210930 1hr1hr旅游 20211001 2hr1hr旅游 20211002 1hr3hr旅游 20211003 1hr0hr因此国庆头3天（10。0110。03）里10。01的近7天（9。2510。01）总点赞量为5次，单天最大转发量为2次（9月25那天最大）；同理可得10。02和10。03的两个指标。 SQL代码withtas（selectb。tag，mid（a。starttime，1，10）asdt，sum（sum（a。iflike））over（partitionbyb。tagorderbymid（a。starttime，1，10）rows6preceding）assumlikecnt7d，max（sum（a。ifretweet））over（partitionbyb。tagorderbymid（a。starttime，1，10）rows6preceding）asmaxretweetcnt7dfromtbuservideologajointbvideoinfobona。videoidb。videoidwheredatediff（20211003，mid（a。starttime，1，10））9groupbyb。tag，dt）selectfromtwheredt20211001anddt20211003orderbytagdesc，dtasc 开窗函数rowsnperceding：从当前行到前n行（一共n1行）rangrowsbetween边界规则1and边界规则2：rang表示按照值的范围进行定义框架，rows表示按照行的范围进行定义框架 rowsbetween2percedingand2following当前行往前2行当前行当前行往后2行（一共5行） rowsbetween1following3following当前行的后1后3（共3行） rowsbetweenunboundedprecedingandcurrentrow从第一行到当前行问题：找出近一个月发布的视频中热度最高的top3视频。 注：热度（a视频完播率b点赞数c评论数d转发数）新鲜度；新鲜度1（最近无播放天数1）；当前配置的参数a，b，c，d分别为100、5、3、2。最近播放日期以endtime结束观看时间为准，假设为T，则最近一个月按〔T29，T〕闭区间统计。结果中热度保留为整数，并按热度降序排序。视频完播率是指完成播放次数占总播放次数的比例。简单起见，结束观看时间与开始播放时间的差视频时长时，视为完成播放。 输出示例： 示例数据的输出结果如下 videoid hotindex 2001hr122hr2002hr56hr2003hr1hr解释： 最近播放日期为20211003，记作当天日期；近一个月（20210904及之后）发布的视频有2001、2002、2003、2004，不过2004暂时还没有播放记录； 视频2001完播率1。0（被播放次数4次，完成播放4次），被点赞3次，评论1次，转发2次，最近无播放天数为0，因此热度为：（1001。0533122）（01）122 同理，视频2003完播率0，被点赞数1，评论和转发均为0，最近无播放天数为3，因此热度为：（1000513020）（31）1（1。2保留为整数）。 SQL代码witht1as（selecta。videoid，avg（if（timestampdiff（second，a。starttime，a。endtime）b。duration，1，0））asa，视频完播率sum（a。iflike）asb，点赞数count（a。commentid）asc，评论数sum（a。ifretweet）asd，转发数datediff（（selectmax（endtime）fromtbuservideolog），max（a。endtime））asf最近无播放天数fromtbuservideologajointbvideoinfobona。videoidb。videoidwheredate（b。releasetime）datesub（（selectmax（endtime）fromtbuservideolog），interval30day）groupbya。videoid）selectvideoid，round（（a100b5c3d2）（f1），0）ashotindexfromt1orderbyhotindexdesclimit3 题目本身不复杂，但是理解题目好难比如，最近无播放天数，一直理解成最近两次播放天数间的间隔天数，实际要简单很多，就是整体最近的播放日期减每个视频的最近播放日期。
诺基亚2760Flip4G翻盖手机曝光，运行KaiOSIT之家12月19日消息，NokiaPowerUser最近的一份报告称，一款型号为TA1398的诺基亚N139DL翻盖手机出现在FCC（联邦通信委员会）平台上，该设备可能会以诺……张馨予分享与宝宝的合照，婚后生活十分滋润，36岁被丈夫称为小2017年《奇兵神犬》录制的时候，正是张馨予事业最低谷的时期，因为一段十分高调的过去式恋情，遭受了许多外界的非议和打压，资源也比以前更差了。《奇兵神犬》是一档与警犬有关的……1388元华为WATCHGT2Pro手表京东新低在2020年10月末的华为Mate40系列发布会上，华为发布WATCHGT2Pro手表，售价2388元。今日京东预售直降至1388元，属近期好价：京东HUAWEIWATC……HMD诺基亚手机被迫退出德国和瑞士市场因专利纠纷，只剩2款机IT之家2月26日消息，HMDGlobal被迫将其大部分智能手机撤出德国和瑞士市场，只留下两款新发布的机型诺基亚G21和G11。HMDGlobal宣布，VoiceAgeE……脾胃不好还一直补呢？医生少花冤枉钱，根源问题可避免脾胃不好，所指范围很宽，可以划分的原因也很多，甚至连症状都可能是完全相反的。如有的人很胖，不知道的人还以为是吃太好，营养过剩了，其实真正的原因可能是胃消化不好。而有……只需三步解决卡顿！华为将为旧手机推出系统云翻新功能感谢IT之家网友努力的蜗牛的线索投递！IT之家4月9日消息，一般来说，受限于硬件老化和文件碎片等问题，手机使用到一定年限就会变得卡顿，运行不流畅。华为近日针对这个问……林允儿不愧是国民理想型！近期搭配汇总，每一套都很女神娱评大赏林允儿不愧为国家理想型！最近，每一套都是女神林允儿的搭配不同于以前甜美可爱的造型。驼色西装外套搭配同色衬衫，上身精致干练知性。牛仔裤突出了年轻人活力的一面，……夜视仪警告！vivoX80曝全系天玑9000自研影像芯片，配IT之家4月13日消息，vivoX80系列此前已得到多次曝光，搭载天玑9000处理器，消息称在4月中下旬发布。今日，数码博主数码闲聊站透露，vivoX80全系将配备天玑9……公布了！上半年我国GDP同比增长2。5，约8。68万亿美元，2022年二季度以及上半年经济数据公布了，今年上半年可以说相当不容易，国内疫情反弹，从珠三角到长三角，从东北到中西部，疫情多地散发，消费和工业生产等都受到了不同程度的影响。而国……消息称AMDVermeerS处理器将亮相CES，还有Zen2IT之家12月1日消息，据ChipHell用户热心市民描边怪消息，AMD将推出一款Zen2架构的新品处理器，代号为RenoirX，AM4接口，规格会和锐龙4000G相似，但是没……亚马逊AWSEC2M6a实例采用AMDEPYC三代处理器，性IT之家12月1日消息，根据AMD中国官方消息，亚马逊Web服务AmazonWebServices（AWS），日前宣布推出AmazonEC2M6a实例，采用第三代AMDEPYC……各位孕妈们都做过什么胎梦，准吗？大家分享一个我怀孕时候的乌龙事件，我一直想要个女儿，备孕的时候听别人说有个神婆奶奶，可以调男女，让我对她深信不疑是因为我有个同事，换多囊很多年，吃了很多药看了很多医院，都是说没……