如何使用SQL计算指定用户特定年份的月均下载次数
目标年份用户月均下载次数统计实现
实现逻辑
严格遵循给定统计规则:月均下载次数 = 目标自然年内指定用户的累计下载总次数 / 固定分母12,不根据实际存在下载记录的月份数调整分母,和示例计算逻辑完全对齐。
可直接落地的Oracle SQL实现
SELECT NVL(COUNT(dl.DOWNLOAD_TIMESTAMP), 0) / 12 AS monthly_avg_download FROM USER_PROFILE up LEFT JOIN DOWNLOAD dl ON up.ID = TO_CHAR(dl.PROFILE_ID) -- 圈定2020年全年的下载范围,避免时分秒导致的边界漏算 AND dl.DOWNLOAD_TIMESTAMP >= DATE '2020-01-01' AND dl.DOWNLOAD_TIMESTAMP < DATE '2021-01-01' WHERE -- 匹配指定用户C1234 up.ID = 'C1234';
实现细节说明
- 时间范围筛选:没有用
<= DATE '2020-12-31'作为结束边界,是因为Oracle的DATE类型携带时分秒属性,12月31日当天0点之后产生的下载会被漏掉;用下一年1月1日作为左开右闭的边界可以覆盖全年所有时间点的下载记录。 - 空值处理:用
NVL将无下载记录的场景结果转为0,避免全年无下载时返回NULL值、甚至结果集为空的问题。 - 类型适配:原表中
DOWNLOAD.PROFILE_ID是INT类型,USER_PROFILE.ID是VARCHAR2类型,关联时通过TO_CHAR做类型对齐,避免关联报错。
原始建表语句的待修正问题
给出的建表语句存在两处无法直接执行的语法问题:
USER_PROFILE表的ID列定义末尾多了冗余逗号,会触发语法报错- 自增列(
GENERATED ALWAYS AS IDENTITY)本质是整数序列生成器,定义为VARCHAR2(20)类型和列的特性不匹配,实际使用时建议调整为数值类型(如NUMBER)。
结果验证:如果用户2020年1月产生12次下载、其余月份无下载,该SQL返回的结果为
12/12 = 1,和需求示例的计算逻辑完全一致。
内容的提问来源于stack exchange,提问作者Jiakai Dong
相关产品推荐
相关产品推荐

