id、状态和日期上的 CTE 导致结果平方

Question

我有一个表定义为

CREATE TABLE ItemDetail (
    ItemNumber        bigint not null,
    SiteId            int not null,
    Status            int not null,
    ScanDate          datetime not null,
)

我想要做的是按站点、按状态、按天获取计数。我有一个已定义的 CTE

;WITH statCTE AS (
    SELECT
        Count(ItemNumber) over(partition by SiteId, Status, DATEADD(dd, 0, DATEDIFF(dd, 0, ScanDate))) as ItemCount,
        SiteId,
        Status,
        DATEADD(dd, 0, DATEDIFF(dd, 0, ScanDate)) AS ScanDate
    FROM
        ItemDetail
)

问题是，当我运行时

select * from statCTE where siteid = 119 and scandate = '3/3/2011'

，我得到

ItemCount SiteId Status ScanDate
2   119 0   2011-03-03 00:00:00.000
2   119 0   2011-03-03 00:00:00.000
6   119 5   2011-03-03 00:00:00.000
6   119 5   2011-03-03 00:00:00.000
6   119 5   2011-03-03 00:00:00.000
6   119 5   2011-03-03 00:00:00.000
6   119 5   2011-03-03 00:00:00.000
6   119 5   2011-03-03 00:00:00.000

结果集应该是 2 行，其中 2 行表示状态 0，一行 6 表示状态 6。因此，我的分区无法转换日期只是一个日期对象并选择结果。我可以在最终查询（数据透视）中对 ItemCount 总数求平方根，但这更像是一种黑客行为，而不是解决实际问题。

Answer 1

您误解了窗口函数（或排名函数）的使用。在您的情况下，计数不是聚合。此用途将函数的结果添加到查询返回的每一行。通常，聚合是在 select 子句之前处理的，但在本例中，它是在 ALONG select 子句之后处理的。因此，TSQL 会计算有多少行满足您的分区，并将其沿着查询返回的每一行输出。

您有 2 个分区，计数分别为 2 和 6（由窗口分区函数计算），然后整个结果集会附加附加列。

如果您想要 2 和 6 结果，您需要使用 group by 子句编写查询

WITH statCTE AS (
    SELECT
        Count(ItemNumber)as ItemCount,
        SiteId,
        Status,
        DATEADD(dd, 0, DATEDIFF(dd, 0, ScanDate)) AS ScanDate
    FROM
        ItemDetail
    group by  SiteId, Status, DATEADD(dd, 0, DATEDIFF(dd, 0, ScanDate))
)