如何在 SQL Server 中按复杂规则统计行数？

结论

可以实现，不过题目给出的期望结果与“固定 15 天计费周期”的规则并不完全一致。

计算前需要先确定每位客户的计费周期起点。之后，在每个“客户 + 周期 + channel”组合中只保留第一次查看，并按照这次计费发生的月份汇总。

如果把每位客户最早的 dateViewed 作为周期起点，客户 B 的结果符合预期：

• 1 月：1 次
• 2 月：4 次

客户 A 的情况不同。1 月 28 日已经进入新的 15 天周期，当天又查看了两个 channel，因此不能只计费 1 次。根据现有数据和规则，客户 A 在 1 月应计费 3 次。如果期望结果必须是 1，还需要提供客户 A 的周期起点、订阅状态或其他过滤条件。

查询思路

可以按以下顺序处理：

1. 确定每位客户的计费周期起点。
2. 使用 DATEDIFF 算出每条查看记录所属的 15 天周期。
3. 按客户、周期和 channelid 去重。
4. 取每个组合的首次查看日期作为计费日期，再按月份汇总。

周期编号的计算公式是：

cycle_no = DATEDIFF(day, cycle_start, dateViewed) / 15

SQL Server 在这里执行整数除法，所以：

• 相差 0～14 天的记录属于周期 0；
• 相差 15～29 天的记录属于周期 1；
• 相差 30～44 天的记录属于周期 2。

以客户 B 为例，其周期起点是 2016-01-19：

• 2016-01-19 至 2016-02-02 属于周期 0；
• 2016-02-03 至 2016-02-17 属于周期 1；
• 2016-02-18 至 2016-03-03 属于周期 2。

这样，2016-02-02 对 channel 2 的查看仍在周期 0 内，不会重复计费。

SQL Server 查询

假设表名为 dbo.ChannelViews：

WITH CustomerCycleStart AS
(
    SELECT
        custid,
        MIN(dateViewed) AS cycle_start
    FROM dbo.ChannelViews
    GROUP BY custid
),
ViewsWithCycle AS
(
    SELECT
        v.custid,
        v.custname,
        v.channelid,
        v.dateViewed,
        DATEDIFF(DAY, s.cycle_start, v.dateViewed) / 15 AS cycle_no
    FROM dbo.ChannelViews AS v
    INNER JOIN CustomerCycleStart AS s
        ON s.custid = v.custid
),
BillableViews AS
(
    SELECT
        custid,
        custname,
        channelid,
        cycle_no,
        MIN(dateViewed) AS billed_date
    FROM ViewsWithCycle
    GROUP BY
        custid,
        custname,
        channelid,
        cycle_no
)
SELECT
    custid,
    custname,
    MONTH(billed_date) AS [month],
    COUNT(*) AS [count]
FROM BillableViews
GROUP BY
    custid,
    custname,
    YEAR(billed_date),
    MONTH(billed_date)
ORDER BY
    custid,
    YEAR(billed_date),
    MONTH(billed_date);

分组时必须保留 YEAR(billed_date)，否则数据跨年后，2025 年 1 月和 2026 年 1 月会被合并到一起。

在实际业务中，直接返回年月通常比只返回月份数字更合适：

WITH CustomerCycleStart AS
(
    SELECT
        custid,
        MIN(dateViewed) AS cycle_start
    FROM dbo.ChannelViews
    GROUP BY custid
),
ViewsWithCycle AS
(
    SELECT
        v.custid,
        v.custname,
        v.channelid,
        v.dateViewed,
        DATEDIFF(DAY, s.cycle_start, v.dateViewed) / 15 AS cycle_no
    FROM dbo.ChannelViews AS v
    INNER JOIN CustomerCycleStart AS s
        ON s.custid = v.custid
),
BillableViews AS
(
    SELECT
        custid,
        custname,
        channelid,
        cycle_no,
        MIN(dateViewed) AS billed_date
    FROM ViewsWithCycle
    GROUP BY
        custid,
        custname,
        channelid,
        cycle_no
)
SELECT
    custid,
    custname,
    DATEFROMPARTS(
        YEAR(billed_date),
        MONTH(billed_date),
        1
    ) AS invoice_month,
    COUNT(*) AS billable_count
FROM BillableViews
GROUP BY
    custid,
    custname,
    DATEFROMPARTS(
        YEAR(billed_date),
        MONTH(billed_date),
        1
    )
ORDER BY
    custid,
    invoice_month;

客户 B 的计算结果

客户 B 的周期起点是 2016-01-19。

汇总结果如下：

custid  custname  month  count
2       B         1      1
2       B         2      4

为什么客户 A 与期望值不一致

客户 A 最早的查看日期是 2016-01-09。按照固定 15 天周期计算：

• 周期 0：2016-01-09 至 2016-01-23
• 周期 1：2016-01-24 至 2016-02-07

对应的计费记录是：

2016-01-09  channel 1  周期 0
2016-01-28  channel 3  周期 1
2016-01-28  channel 1  周期 1

只有同一周期内的同一个 channel 才会去重。channel 1 分别出现在两个周期中，所以两次都应计费。客户 A 在 1 月的结果应为：

custid  custname  month  count
1       A         1      3

如果要得到题目中的 1，还需要存在某条尚未说明的规则，例如：

• 2016-01-28 的两条记录不属于有效订阅；
• 客户 A 的计费周期不是从 2016-01-09 开始；
• 计费周期并非固定 15 天；
• 部分 channel 不参与计费；
• 示例数据或期望结果有遗漏。

仅凭当前字段，无法可靠地推导出这些条件。

使用真实订阅起始日期

生产系统不适合用最早查看日期代替订阅起点。更可靠的做法是在客户订阅表中保存 billing_start_date：

WITH ViewsWithCycle AS
(
    SELECT
        v.custid,
        v.custname,
        v.channelid,
        v.dateViewed,
        DATEDIFF(
            DAY,
            s.billing_start_date,
            v.dateViewed
        ) / 15 AS cycle_no
    FROM dbo.ChannelViews AS v
    INNER JOIN dbo.CustomerSubscription AS s
        ON s.custid = v.custid
    WHERE v.dateViewed >= s.billing_start_date
),
BillableViews AS
(
    SELECT
        custid,
        custname,
        channelid,
        cycle_no,
        MIN(dateViewed) AS billed_date
    FROM ViewsWithCycle
    GROUP BY
        custid,
        custname,
        channelid,
        cycle_no
)
SELECT
    custid,
    custname,
    YEAR(billed_date) AS invoice_year,
    MONTH(billed_date) AS invoice_month,
    COUNT(*) AS billable_count
FROM BillableViews
GROUP BY
    custid,
    custname,
    YEAR(billed_date),
    MONTH(billed_date)
ORDER BY
    custid,
    invoice_year,
    invoice_month;

如果同一客户可以多次订阅、暂停或重新激活，周期划分时还应包含订阅实例 ID，不能只按 custid 计算。

注意事项

• 需要明确“15 天”指连续 15 个自然日，不是每月 1～15 日和 16 日至月末。
• 计费月份通常取去重后的首次查看日期，也就是 billed_date 所在的月份。
• 如果 dateViewed 是 datetime，上述按天计算的方式会忽略一天内的具体时间。周期若要精确到小时或秒，需要改用粒度更细的时间差。
• custname 可能发生变化。长期账单可以先按 custid 汇总，再从客户主表中取得开票时使用的名称。
• 可以为查看表建立以下索引，改善周期计算和分组性能：

CREATE INDEX IX_ChannelViews_CustomerDateChannel
ON dbo.ChannelViews (custid, dateViewed, channelid)
INCLUDE (custname);

备注：内容仅供参考。