Início / dba / Perguntas / 266242
Accepted
mustaccio
Asked: 2020-05-01 18:21:07 +0800 CST

Função de janela condicional com uma torção -- inclua no máximo X linhas anteriores, mas exclua algumas

  • 772

Pedindo para um amigo que usa SQL Server Parallel Warehouse.

They™ tem uma tabela de valores de vendas semanais, assim (perdoe a justificativa imprópria da coluna):

+-------+------------+
| week  |  amount    |
+-------+------------+
|    1  | 100.00     |
|    2  | 100.00     |
|    3  | 100.00     |
|    4  | 100.00     |
|    5  | 100000.00  |
|    6  | 100.00     |
|    7  | 50000.00   |
|    8  | 50000.00   |
|    9  | 50000.00   |
|   10  | 100.00     |
+-------+------------+

E também uma lista de semanas "ruins", por exemplo

+------+
| week |
+------+
|    5 |
|    7 |
|    8 |
|    9 |
+------+

E They™ precisa selecionar para cada semana, incluindo semanas "ruins", a soma das vendas das quatro semanas anteriores não "ruins" , ou seja, indo o mais longe possível, pulando registros de semanas "ruins", para somar no máximo quatro valores de vendas. Assim, o resultado esperado seria:

+-------+------------+
| week  | sum_not_bad|
+-------+------------+
|    1  | null       |
|    2  | 100.00     |
|    3  | 200.00     |
|    4  | 300.00     |
|    5  | 400.00     |
|    6  | 400.00     |
|    7  | 400.00     |
|    8  | 400.00     |
|    9  | 400.00     |
|   10  | 400.00     |
+-------+------------+

Eu tenho um violino que acho que um passo na direção certa, mas não consigo descobrir o(s) próximo(s) passo(s).

Alguém tem insights?

sql-server window-functions

2 respostas

  1. SELECT * 
FROM data as d
  OUTER APPLY
  (
      SELECT SUM(prev_amount) as sum_not_bad
      FROM
          (SELECT TOP(4) (p.amount)as prev_amount
           FROM data as p
               LEFT JOIN bad_weeks as b
                    ON p.week = b.week
           WHERE p.week < d.week
                AND b.week is null
           ORDER BY p.week DESC
          )a
  )oa

    resultado

    week        amount                                  sum_not_bad
1           100.00                                  NULL
2           100.00                                  100.00
3           100.00                                  200.00
4           100.00                                  300.00
5           100000.00                               400.00
6           100.00                                  400.00
7           50000.00                                400.00
8           50000.00                                400.00
9           50000.00                                400.00
10          100.00                                  400.00

    dbfiddle

    • 4
  2. Best Answer

    Aqui está uma maneira, ele usa PARTITIONpara agrupar todas as boas semanas e obter a contagem cumulativa de boas para as 4 boas semanas anteriores. Em seguida, uma abordagem ao longo das linhas da Solução 2 Usando Concatenação aqui para contornar a falta de suporte para LAST_VALUEignorar NULLs e cascata para baixo o valor "bom" anterior.

    Ele mantém o controle de duas somas cumulativas. Uma incluindo a linha atual (usada pela próxima linha se a linha "próxima" estiver ruim) e outra sem a linha atual.

    WITH T
     AS (SELECT d.week,
                d.amount,
                CASE WHEN b.week IS NULL THEN 0 ELSE 1 END AS is_bad_week,
                SUM(CASE WHEN b.week IS NULL THEN d.amount END)
                  OVER ( PARTITION BY CASE WHEN b.week IS NULL THEN 0 ELSE 1 END ORDER BY d.week rows BETWEEN 4 PRECEDING AND 1 PRECEDING) cume_sum_prev4toprev1,
                SUM(CASE WHEN b.week IS NULL THEN d.amount END)
                  OVER ( PARTITION BY CASE WHEN b.week IS NULL THEN 0 ELSE 1 END ORDER BY d.week rows BETWEEN 3 PRECEDING AND CURRENT ROW) cume_sum_prev3tocurrent
         FROM   data d
                LEFT JOIN bad_weeks b
                       ON d.week = b.week)
SELECT week,
       CASE WHEN is_bad_week = 1 THEN 
       CAST(SUBSTRING(MAX(RIGHT(CONCAT('0000000000', week), 10) + CAST(cume_sum_prev3tocurrent AS VARCHAR(20))) OVER (ORDER BY week), 11, 20) AS DECIMAL(20, 2)) 
       ELSE
       CAST(SUBSTRING(MAX(RIGHT(CONCAT('0000000000', week), 10) + CAST(cume_sum_prev4toprev1 AS VARCHAR(20))) OVER (ORDER BY week), 11, 20) AS DECIMAL(20, 2))
       END AS sum_not_bad
FROM   T
ORDER  BY week
    • 3

relate perguntas