SQL Server中如何用TSQL统计门店销售额>0的累计活跃天数

你可以使用SQL Server的窗口聚合函数实现该需求，核心逻辑是按门店分组、按日期排序，累加销售额大于0的日期计数即可，参考代码如下：

如果你的Sales Amount字段存储带$符号的字符串、Date字段为字符串类型，使用以下兼容写法：

SELECT 
    [Date],
    [Store ID],
    [Sales Amount],
    SUM(
        CASE WHEN CAST(REPLACE([Sales Amount], '$', '') AS DECIMAL(18,2)) > 0 
        THEN 1 
        ELSE 0 
        END
    ) OVER (
        PARTITION BY [Store ID] 
        ORDER BY CONVERT(DATE, [Date], 101) 
        ROWS UNBOUNDED PRECEDING
    ) AS [Active Days]
FROM StoreSales
ORDER BY CONVERT(DATE, [Date], 101), [Store ID]

如果你的字段已经做了类型优化：Date为DATE类型、Sales Amount为数值类型（无$符号），可以简化为：

SELECT 
    [Date],
    [Store ID],
    [Sales Amount],
    SUM(CASE WHEN [Sales Amount] > 0 THEN 1 ELSE 0 END) OVER (
        PARTITION BY [Store ID] 
        ORDER BY [Date] 
        ROWS UNBOUNDED PRECEDING
    ) AS [Active Days]
FROM StoreSales
ORDER BY [Date], [Store ID]

逻辑说明 #

• PARTITION BY [Store ID] 按门店拆分计算维度，每个门店的活跃天数独立统计
• ORDER BY [Date] 保证按日期从早到晚的顺序累加
• CASE 表达式将销售额大于0的日期标记为1，其余为0
• 开窗求和的范围为当前门店最早记录到当前行，刚好匹配需求的统计区间

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