主页 / dba / 问题 / 89031
Accepted
Omri
Asked: 2015-01-12 01:21:19 +0800 CST

在带有 OVER 的窗口函数中使用 DISTINCT

  • 772

我正在尝试将查询从 Oracle 迁移到 SQL Server 2014。

这是我在 Oracle 中运行良好的查询：

select
count(distinct A) over (partition by B) / count(*) over() as A_B
from MyTable

这是我尝试在 SQL Server 2014 中运行此查询后遇到的错误。

Use of DISTINCT is not allowed with the OVER clause

有谁知道是什么问题？SQL Server 中是否可以进行此类查询？请指教。

sql-server sql-server-2014

5 个回答

  1. 这给出了由 B 分区的 A 的不同计数（*）：

    dense_rank() over (partition by B order by A) 
+ dense_rank() over (partition by B order by A desc) 
- 1
    • 21
  2. Best Answer

    有谁知道是什么问题？SQL Server 中是否可以进行此类查询？

    不，目前尚未实施。请参阅以下连接项请求。

    OVER 子句增强请求 - 聚合函数的 DISTINCT 子句

    另一种可能的变体是

    SELECT M.A,
       M.B,
       T.A_B
FROM   MyTable M
       JOIN (SELECT CAST(COUNT(DISTINCT A) AS NUMERIC(18,8)) / SUM(COUNT(*)) OVER() AS A_B,
                    B
             FROM   MyTable
             GROUP  BY B) T
         ON EXISTS (SELECT M.B INTERSECT SELECT T.B)

    强制转换NUMERIC是为了避免整数除法。此处解释了加入子句的原因。

    ON M.B = T.B OR (M.B IS NULL AND T.B IS NULL)如果首选（或者ON M.B = T.B如果B列不可为空），可以将其替换为。

    • 13

  3. 您可以取 的最大值dense_rank()来获得由 B 分区的 A 的不同计数。

    要处理 A 可以具有空值的情况，您可以使用first_value来确定分区中是否存在空值，然后减去 1（如果按照 Martin Smith 在评论中的建议）。

    select (max(T.DenseRankA) over(partition by T.B) - 
          cast(iif(T.FirstA is null, 1, 0) as numeric(18, 8))) / T.TotalCount as A_B
from (
     select dense_rank() over(partition by T.B order by T.A) DenseRankA,
            first_value(T.A) over(partition by T.B order by T.A) as FirstA,
            count(*) over() as TotalCount,
            T.A,
            T.B
     from MyTable as T
     ) as T
    • 8

  4. 尝试进行子查询，按 A、B 分组并包括计数。然后在您的外部查询中，您的 count(distinct) 变为常规计数，并且您的 count(*) 变为 sum(cnt)。

    select
count(A) over (partition by B) * 1.0 / 
    sum(cnt) over() as A_B
from
(select A, B, count(*) as cnt
 from MyTable
 group by A, B) as partial;
    • 5

  5. SQL Server 目前不允许Distinct与窗口函数一起使用。

    但是一旦你记住了窗口函数是如何工作的（即：它们被应用于查询的结果集），你就可以解决这个问题：

    select B,
min(count(distinct A)) over (partition by B) / max(count(*)) over() as A_B
from MyTable
group by B
    • 0

相关问题