主页 / dba / 问题 / 266242
Accepted
mustaccio
Asked: 2020-05-01 18:21:07 +0800 CST

带有扭曲的条件窗口函数 - 最多包含 X 行但排除一些

  • 772

求一个使用SQL Server Parallel Warehouse的朋友。

他们™ 有一个每周销售额表，如下所示（请原谅不正确的列对齐）：

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

还有一个“糟糕”周的列表，例如

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

并且他们™需要为每一周选择，包括“糟糕”周，前四个非“糟糕”周的销售额总和，即尽可能追溯，跳过“糟糕”周记录，最多加起来四个销售额。所以预期的结果是：

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

我有一个小提琴，我认为朝着正确的方向迈出了一步，但我无法弄清楚下一步。

有没有人有见解？

sql-server window-functions

2 个回答

  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

    输出

    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

    小提琴手

    • 4
  2. Best Answer

    这是一种方法，它用于PARTITION将所有良好的周分组在一起，并获得前 4 个良好周的累积良好计数。然后是沿着解决方案 2 使用串联的方法来解决缺乏对LAST_VALUE忽略 NULL 的支持和级联之前的“好”值的问题。

    它跟踪两个累积和。一个包括当前行（如果“下一个”行不好，则由下一行使用）和一个不包括当前行。

    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

相关问题