如何用最近的记录替换缺失的数字进行求和

Question

>假设游戏有5个关卡，每个关卡玩家可以获得1、2和3的分数。玩家 X 具有以下数据：

Level | Attempt | Score
1     | 1       | 2  
1     | 2       | 3
2     | 1       | 3
3     | 1       | 3
4     | 1       | 1
4     | 2       | 3
5     | 1       | 2
5     | 2       | 2
5     | 3       | 3

现在我想SUM分数GROUP BY尝试，但以一种特殊的方式：

尝试 1：简单 2 + 3 + 3 + 1 + 3 = 12

尝试 2：现在 2 级和 3 级没有尝试 2，但我仍然想用他们最近的分数加起来：3 + 3 + 3 + 3 + 2 = 14

尝试 3：再次，我想将所有级别与它们的最新分数相加(如果没有尝试 3(，所以我得到 3 + 3 + 3 + 3 + 3 = 15

如何使用 SQL 执行此操作？请注意，尝试次数没有限制，因此玩家实际上可以尝试 100 次关卡，我必须提供 100 次总和。

Answer 1

然后，它可能是这样的：

WITH
-- your input ...
input(level,attempt,score) AS (
SELECT 1,1,2
UNION ALL SELECT 1,2,3
UNION ALL SELECT 2,1,3
UNION ALL SELECT 3,1,3
UNION ALL SELECT 4,1,1
UNION ALL SELECT 4,2,3
UNION ALL SELECT 5,1,2
UNION ALL SELECT 5,2,2
UNION ALL SELECT 5,3,3
)
-- your input ends here
, -- replace comma with WITH in real query ..
-- creating a table with 5 rows per each of the 3 attempts
frame (level,attempt) AS (
SELECT
i.level
, a.attempt
FROM input i
CROSS JOIN (
SELECT DISTINCT
attempt
FROM input
) a
WHERE i.attempt=1
)
-- SELECT * FROM frame; -- un-comment this line to test the frame table
,
gapfilled AS (
-- Query before GROUPing: left join the frame table with the input table
-- and fill the resulting NULLs using the LAST_VALUE ( ... IGNORE NULLS) 
-- OLAP function. If you can take a previous one, pick it, if not , pick
-- a following one. 
-- Vertica has named OLAP windows, which we use here - one forward, one backward
SELECT
frame.level
, NVL(
LAST_VALUE(input.attempt IGNORE NULLS) OVER(fwd)
, LAST_VALUE(input.attempt IGNORE NULLS) OVER(bwd)
)  AS attempt
, NVL(
LAST_VALUE(input.score   IGNORE NULLS) OVER(fwd) 
, LAST_VALUE(input.score   IGNORE NULLS) OVER(bwd)
)  AS score
FROM frame LEFT JOIN input USING(level,attempt)
WINDOW fwd AS (PARTITION BY frame.attempt ORDER BY frame.level)
,      bwd AS (PARTITION BY frame.attempt ORDER BY frame.level DESC)
)
-- SELECT * FROM gapfilled ORDER BY 2,1; -- UN-comment to test gapfilled table
SELECT
attempt
, SUM(score) AS score_sum
FROM gapfilled
GROUP BY
attempt;
-- out  attempt | score_sum 
-- out ---------+-----------
-- out        1 |        11
-- out        2 |        14
-- out        3 |        15