如何在SQL中实现类似Excel COUNTIFS的行级条件计数？

需要在SQL中实现类似Excel COUNTIFS() 的功能，对每行数据按指定条件进行计数。

示例数据 #

UserID     Row_Num   Date_    
1          1         0621     
1          2         0622      
1          3         0623
1          4         0625 
1          5         0626 
1          6         0628  
1          7         0630 
2          1         0621 
2          2         0622 
2          3         0623
2          4         0625 
2          5         0626 
2          6         0628  
2          7         0630 

计数逻辑 #

• 同一UserID
• Date_处于当前行Date_的过去5天范围内

Excel对应函数 #

COUNTIFS(A:A, UserID, C:C, "<="&Date, C:C, ">="&Date-5)

期望结果 #

UserID     Row_Num   Date_    Count_   
1          1         0630    3
1          2         0628    3
1          3         0626    4
1          4         0625    4
1          5         0623    3
1          6         0622    2
1          7         0621    1
2          1         0630    3
2          2         0628    3
2          3         0626    4
2          4         0625    4
2          5         0623    3
2          6         0622    2
2          7         0621    1

SQL实现方案 #

方案1：关联子查询 #

逻辑直观，与Excel的COUNTIFS逻辑直接对应：

SELECT 
    t1.UserID,
    t1.Row_Num,
    t1.Date_,
    (
        SELECT COUNT(*)
        FROM your_table t2
        WHERE t2.UserID = t1.UserID
          -- 若Date_为MMDD格式字符串，转数值计算；若为日期类型，替换为日期函数判断
          AND CAST(t2.Date_ AS INT) BETWEEN CAST(t1.Date_ AS INT) - 5 AND CAST(t1.Date_ AS INT)
    ) AS Count_
FROM your_table t1
ORDER BY t1.UserID, t1.Row_Num;

方案2：窗口函数（性能更优） #

适用于支持窗口函数的数据库（如MySQL 8.0+、PostgreSQL、SQL Server等）：

SELECT 
    UserID,
    Row_Num,
    Date_,
    COUNT(*) OVER (
        PARTITION BY UserID
        ORDER BY CAST(Date_ AS INT)
        RANGE BETWEEN 5 PRECEDING AND CURRENT ROW
    ) AS Count_
FROM your_table
ORDER BY UserID, Row_Num;

注意：如果Date_是标准日期类型，需调整范围判断逻辑，例如用DATE_SUB(t1.Date_, INTERVAL 5 DAY)替代数值减法，保证日期范围计算准确。

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