时间戳

首页 > 解决方案 > 如何用最近的记录替换缺失的数字求和

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

问题描述

假设游戏有 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

Attempt 2：现在Level 2和3没有Attempt 2，但我还是想用他们最近的分数加起来：3 + 3 + 3 + 3 + 2 = 14

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

我怎样才能用 SQL 做到这一点？请注意，尝试次数没有限制，因此玩家实际上可以尝试关卡 100 次，而我必须提供 100 次总和。

标签： sqlvertica

解决方案

然后，它可能是这样的：

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

推荐阅读