T N's questions

在准备另一个问题的答案时，我编写了一个查询，其中包含具有相同子句的多个窗口函数OVER(...)。结果符合预期。

select ...
    sum(sum(s.price)) over (partition by p.productid
                            order by c.date
                            rows between 6 preceding and current row)
        / nullif(
             sum(count(s.price)) over(partition by p.productid
                                      order by c.date
                                      rows between 6 preceding and current row),
             0)
        as avg7DayPrice
...

当我尝试OVER(...)用共享WINDOW子句引用OVER Last7Days（后来定义为 ）替换重​​复子句时WINDOW Last7Days AS (...)，结果却出乎意料地不同。（请参阅WINDOW子句- 2022 年新增。）

select ...
    sum(sum(s.price)) over last7days
        / nullif(sum(count(s.price)) over last7days, 0)
        as avg7DayPrice
...
window last7days as (partition by p.productid
                     order by c.date
                     rows between 6 preceding and current row)

完整原始查询：

-- Inline window over() clauses
-- Results are as expected
with calendar as (
    select min(date) as date, max(date) as endDate
    from sales
    union all
    select dateadd(day, 1, date), endDate
    from calendar
    where date < enddate
),
products as (
    select distinct productid
    from sales
)
select
    p.productid,
    c.date as salesDate, 
    avg(price) as avg1DayPrice,
    sum(sum(s.price)) over (partition by p.productid
                            order by c.date
                            rows between 6 preceding and current row)
        / nullif(
             sum(count(s.price)) over(partition by p.productid
                                      order by c.date
                                      rows between 6 preceding and current row),
             0)
        as avg7DayPrice
from calendar c
cross join products p
left join sales s
    on s.date = c.date
    and s.productid = p.productid
group by p.productid, c.date

完整修改的查询：

-- Reference to common defined window clause
-- Expect same results, but that is not what I get.
with calendar as (
    select min(date) as date, max(date) as endDate
    from sales
    union all
    select dateadd(day, 1, date), endDate
    from calendar
    where date < enddate
),
products as (
    select distinct productid
    from sales
)
select
    p.productid,
    c.date as salesDate, 
    avg(price) as avg1DayPrice,
    sum(sum(s.price)) over last7days
        / nullif(
             sum(count(s.price)) over last7days,
             0)
        as avg7DayPrice
from calendar c
cross join products p
left join sales s
    on s.date = c.date
    and s.productid = p.productid
group by p.productid, c.date
window last7days as (partition by p.productid
                     order by c.date
                     rows between 6 preceding and current row)

销售表：

原始结果：

修改结果：

参见这个 db<>fiddle。

我不明白为什么结果不同。看起来sum(count(s.price)) over(...)计算部分是1, 3, 4针对第一个查询的，但4, 4, 4第二个查询却不同。执行计划也有很大不同。有人能解释一下吗？

我正在尝试生成然后复制多个UNIQUEIDENTIFIER值用作NEWID()测试数据。以下是我的尝试，首先生成 N1 个值，然后使用 aCROSS APPLY将这些值每个复制 N2 次：

SELECT *
FROM (
    SELECT
        S1.value,
        NEWID() AS Id
    FROM GENERATE_SERIES(1, 3) S1
) S1
CROSS APPLY (
    SELECT
        S2.value,
        S1.Id -- Trying to repeat the same ID value multiple times
    FROM GENERATE_SERIES(1, 2) S2
) S2
ORDER BY S1.Value, S2.Value

期望的结果：

实际结果：

我知道这NEWID()将为使用它的每一行生成唯一的值，但我认为在子查询中使用时，这些值将在该子查询中生成，而不是在最外层生成。如果这种行为在NEWID 文档或其他地方我找不到明确的解释。

CAST()我尝试使用、TOUPPER()和插入中间计算REPLACE()，我认为这可以捕获并巩固这些NEWID()值。我还尝试将第一部分包裹在 CTE 中。我仍然无法重复生成的值。

我可以使用 #temp 表或 @table 变量分多个步骤执行此操作，但我只是想知道是否有一种方法可以在单个语句中完成此操作。

数据库<>小提琴

这是另一个类似的案例：

-- Another similar case
WITH CTE AS (
    SELECT NEWID() AS Id -- Is this one value or many?
)
SELECT *
FROM ( VALUES (1), (2), (3) ) ID(Id)
CROSS JOIN CTE

跟进：

我在dba stackexchange上发现了一个类似的问题，它向我指出了一份15 年前的 Microsoft Connect 错误报告和讨论，最终该错误报告因无法修复、功能按设计而被关闭。其基本原理是，“优化器不保证标量函数的执行时间或次数”，并且“很难准确确定……每行一次的行为意味着什么。”

同一篇文章有​​一个较晚的评论，找到了一种使用GROUP BY和的组合FULL OUTER JOIN来诱导所需行为的解决方法，但后来的评论表明这仍然是未定义的行为。

我还发现了一些2005 年的旧书在线文档，其中包含最佳实践：“将非确定性函数的使用限制在最外层的选择列表中。” 我在当前文档中找不到相同的内容，但可能没有在正确的位置查找。

因此，尽管 ValNik 下面的答案提出了一种涉及 的解决方法GROUP BY，但我认为这也是建立在不稳定基础上的。（ValNik 的回答实际上是从“没有意义”的评论开始的。）

我还发现添加IS NOT NULL检查也可以达到所需的结果（小提琴）。同样，这可能是未定义的行为，但似乎有效 - 最好避免。

最重要的是，最好遵循旧的“将非确定性函数的使用限制到最外层选择列表”实践，这意味着选择临时表或表变量，如 Thom A 推荐和 nbk 记录为答案。