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

ztmd8pv5  于 2021-08-13  发布在  Java
关注(0)|答案(1)|浏览(415)

假设游戏有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 尝试,但以一种特殊的方式:
尝试一:简单的2+3+3+1+3=12
尝试2:现在2级和3级没有尝试2,但我还是想用他们最近的分数加起来:3+3+3+3+2=14
第三次尝试:我想把所有等级的分数加起来(如果没有第三次尝试),得到3+3+3+3+3=15
如何使用sql来实现它?请注意,没有对尝试次数的限制,所以一个玩家可能会尝试100次,我必须提供100个总数。

i1icjdpr

i1icjdpr1#

然后,可能是这样的:

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

相关问题