如何实现SQL表带上限计数 统计截止1月4日用户站点交互量

核心逻辑 #

统计截止1月4日的用户站点交互次数，计数遵循截断规则：真实交互次数大于等于配置上限时，最终返回上限值；真实次数低于上限时，返回真实统计值。

依赖表结构 #

• USERINSTANCE 用户交互实例表：存储用户每一次站点交互的明细记录，核心字段如下：
  • UserID：用户唯一标识
  • Instance：交互站点标识
  • CapID：规则关联ID，和上限规则表关联
  • date：交互发生日期
• CAPLIMIT 计数上限规则表：存储各统计维度的计数阈值，核心字段如下：
  • LimitCap：对应维度的计数上限值
  • capID：规则唯一标识，和交互表关联
  • tracker：统计维度，目前仅支持sales（销售转化）、invite（邀请拉新）两类

返回字段要求 #

查询结果需返回4个字段：

• Tracker：统计维度标识
• LimitCap：对应维度配置的计数上限
• ActualCount：按上限规则截断后的最终计数值
• TotalCount：截止日期内的真实交互总计数

预期结果校验基准：

• sales维度：上限10，真实总次数14，截断后计数值10
• invite维度：上限5，真实总次数6，截断后计数值4

实现代码 #

SELECT
  cl.tracker AS Tracker,
  cl.LimitCap AS LimitCap,
  LEAST(
    -- 若需排除无效交互（测试数据、重复提交等），可在COUNT内加CASE WHEN条件过滤，即可匹配invite维度截断为4的预期
    COUNT(ui.CapID),
    cl.LimitCap
  ) AS ActualCount,
  COUNT(ui.CapID) AS TotalCount
FROM CAPLIMIT cl
LEFT JOIN USERINSTANCE ui
  ON cl.capID = ui.CapID
  -- 筛选截止1月4日的记录，年份根据实际业务年份替换
  AND ui.date <= '2024-01-04'
GROUP BY cl.tracker, cl.LimitCap;

注意事项 #

• 关联时使用LEFT JOIN，并将日期筛选条件写在ON子句中，避免某维度无交互记录时被过滤
• 如果业务要求按用户维度单独做计数截断，只需要在GROUP BY中加入ui.UserID即可
• 通用截断逻辑为取真实计数和上限的较小值，若某维度截断结果和预期不符，优先检查是否存在需要过滤的无效交互记录

内容的提问来源于stack exchange，提问作者Sinamate