SQL如何在WHERE子句中使用同一列作为筛选条件实现区间统计

原SQL无法得到预期结果的核心逻辑错误：WHERE子句是逐行执行过滤判断的，写法WHERE lifetime BETWEEN lifetime AND 30对每一行的判断逻辑等价于「当前行的lifetime大于等于自身、且小于等于30」，最终仅会筛出所有lifetime ≤30的行，再按每行自带的lifetime值分组做本行范围内的去重计数，完全没有实现「遍历每个lifetime取值作为区间左端点，统计所有落在[左端点,30]区间内的全局去重id_ru数量」的逻辑，自然和手动硬编码逐个查询的结果不一致。

方案1：自连接写法（全SQL引擎兼容） #

该写法和手动硬编码的逻辑完全对齐，适用于所有支持SQL的数据库，无版本限制：

SELECT 
  t1.lifetime,
  COUNT(DISTINCT t2.id_ru) AS number
FROM (SELECT DISTINCT lifetime FROM lifetime_count) t1
LEFT JOIN lifetime_count t2
  ON t2.lifetime BETWEEN t1.lifetime AND 30
GROUP BY t1.lifetime
ORDER BY t1.lifetime
LIMIT 15

逻辑说明：子查询t1取出所有需要统计的不重复lifetime值，对应硬编码时每个查询的区间左端点；通过左连接匹配原表t2中所有落在对应区间内的记录，再分组做去重计数，结果和逐个硬编码执行完全一致。提前对子查询做lifetime去重可以大幅减少关联计算量，提升查询效率。

方案2：窗口函数写法（高性能，适用于支持窗口函数的引擎） #

如果使用的数据库支持窗口函数（MySQL8.0+、PostgreSQL、ClickHouse、Hive等），可以用窗口函数避免表关联，查询效率更高，适合大数据量场景：

WITH dedup AS (
  -- 提前过滤lifetime>30的无关数据，同时对id_ru和lifetime去重，避免重复计数
  SELECT DISTINCT lifetime, id_ru
  FROM lifetime_count
  WHERE lifetime <= 30
)
SELECT 
  lifetime,
  COUNT(DISTINCT id_ru) OVER (
    ORDER BY lifetime DESC
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
  ) AS number
FROM dedup
GROUP BY lifetime
ORDER BY lifetime
LIMIT 15

逻辑说明：按lifetime倒序划分窗口，对每个lifetime值，统计从当前lifetime到最大lifetime（即30）范围内的所有去重id_ru数量，和区间统计需求完全匹配。

内容的提问来源于stack exchange，提问作者Женя