T N's questions

Ao preparar uma resposta para outra pergunta aqui , codifiquei uma consulta que continha múltiplas funções de janela com a mesma OVER(...)cláusula. Os resultados foram os esperados.

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
...

Quando tentei substituir as OVER(...)cláusulas duplicadas por uma WINDOWreferência de cláusula compartilhada OVER Last7Days, definida posteriormente como WINDOW Last7Days AS (...), os resultados foram inesperadamente diferentes. (Consulte WINDOWcláusula - nova em 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)

Consulta original completa:

-- 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

Consulta modificada completa:

-- 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)

Tabela de vendas:

Resultados originais:

Resultados modificados:

Veja este db<>fiddle .

Não entendo por que os resultados são diferentes. Parece que a sum(count(s.price)) over(...)parte do cálculo é 1, 3, 4para a primeira consulta, mas 4, 4, 4na segunda. O plano de execução também é significativamente diferente. Alguém pode explicar isso?

Estou tentando gerar e duplicar vários UNIQUEIDENTIFIERvalores usando NEWID()como dados de teste. A seguir, minha tentativa é primeiro gerar valores N1 e, em seguida, usar a CROSS APPLYpara duplicar esses valores N2 vezes cada:

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

Resultados desejados:

Resultados reais:

Entendo que NEWID()isso gerará valores exclusivos para cada linha onde for usado, mas teria pensado que, quando usados ​​em uma subconsulta, os valores seriam gerados nessa subconsulta em vez de serem gerados no nível mais externo. Não consigo encontrar uma explicação clara desse comportamento na documentação do NEWID ou em outro lugar.

Tentei inserir cálculos intermediários usando CAST(), TOUPPER()e REPLACE(), que achei que poderiam capturar e solidificar os NEWID()valores. Também tento encerrar a primeira parte em um CTE. Ainda não consigo repetir os valores gerados.

Posso fazer isso em várias etapas usando uma tabela #temp ou uma variável @table, mas queria saber se existe uma maneira de fazer isso em uma única instrução.

banco de dados<> violino

Aqui está outro caso semelhante:

-- 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

Seguir:

Eu descobri uma pergunta semelhante no dba stackexchange que me apontou para um relatório e discussão de bug do Microsoft Connect de 15 anos , que terminou com o relatório de bug sendo fechado como não será corrigido, funções conforme projetado. A justificativa é que "o otimizador não garante o tempo ou o número de execuções de funções escalares" e "é muito difícil definir com precisão ... o que significa o comportamento uma vez por linha ".

Essa mesma postagem teve um comentário tardio que encontrou uma solução alternativa usando uma combinação de GROUP BYe FULL OUTER JOINpara persuadir o comportamento desejado, mas comentários posteriores sugerem que esse ainda era um comportamento indefinido.

Também encontrei alguma documentação on-line de livros antigos de 2005 que inclui uma prática recomendada: "Limitar o uso de funções não determinísticas às listas de seleção mais externas". Não consegui encontrar o mesmo na documentação atual, mas talvez não estivesse procurando no lugar certo.

Portanto, embora a resposta de ValNik abaixo sugira uma solução alternativa envolvendo GROUP BY, acho que isso também é construído em terreno instável. (A resposta de ValNik, na verdade, começa com um comentário “não faz sentido”.)

Também descobri que adicionar uma IS NOT NULLverificação também estimula o resultado desejado ( violino ). Novamente, este é provavelmente um comportamento indefinido que parece funcionar - é melhor evitar.

Resumindo, provavelmente é melhor seguir a antiga prática "Limitar o uso de funções não determinísticas às listas de seleção mais externas", o que significa selecionar uma tabela temporária ou variável de tabela, conforme recomendado por Thom A e documentado como resposta.