填充玩家未登录天的等级记录

现在有这样一个需求，需要一段时间每一个玩家的每一天的最大等级。
这个需求看似很简单，但其实重点是玩家不一定每天都会登录，
对于没有登陆的天，你就拿不到当天的最大等级，所以要做填充，扩展行。

上面这个图里，3-7号玩家没有登陆，所以获取不到他当天的等级，需要做填充。
用sql查询扩展行只有连接，但那天都不在，你和谁连接？
这里可以自己思考下怎么实现这个需求，最好找数据自己试试。
我也是想了好久，才找到解决方法。
核心是生成日期序列数组，和上上篇讲的将数组平展为多行。
Bigquery提供了生成GENERATE_DATE_ARRAY这个函数。

SELECT GENERATE_DATE_ARRAY('2016-10-05', '2016-10-08') AS example;

+--------------------------------------------------+
| example                                          |
+--------------------------------------------------+
| [2016-10-05, 2016-10-06, 2016-10-07, 2016-10-08] |
+--------------------------------------------------+

再把这个数组用cross join和原数据做连接，得到玩家登陆天数*数组长度的中间表。
这个时候，玩家登陆的每一天都会有数组长度这么多行，没有登陆的天就连不到这个数组，
但是反过来，每一个日期都会有小于等于它的最大的那个天存在，
只要取这个往前最接近的这个值就好了。

WITH
-- 按天分组，找每个玩家每天的最大等级
  t1 AS (
  SELECT
    DATE(ts, '+08') AS date,
    uid,
    MAX(level) AS level
  FROM
    `登陆表`
  WHERE
    DATE(ts) >= "2021-02-9"
    AND uid IN( '230***209',
      '109***969',
      '134***185',
      '529***049')
  GROUP BY
    1,
    2,
  t2 AS (
  SELECT
    t1.*,
-- 生成日期序列数组，直接接在原数据的每一行上
    GENERATE_DATE_ARRAY('2021-2-9', '2021-3-12', INTERVAL 1 day) AS date_array
  FROM
    t1 )
SELECT
    date2,
    t2.uid,
    t2.level,
    date
FROM
    t2,
-- cross join unnest的简写，一个逗号平展数组为行
    t2.date_array AS date2

可以看到每一天的日期序列都原表的日期连上了。
接下来只需要用where限制登陆时间小于序列时间再对等级取最大值就好了。

WITH
  t1 AS (
  SELECT
    DATE(ts, '+08') AS date,
    uid,
    MAX(level) AS level
  FROM
    `nova-empire-168011.pgame_stellaris.cmd_login`
  WHERE
    DATE(ts) >= "2021-02-9"
    AND uid IN( '230238209',
      '109651969',
      '134621185',
      '529410049')
  GROUP BY
    1,
    2
  ORDER BY
    2,
    1),
  t2 AS (
  SELECT
    t1.*,
    GENERATE_DATE_ARRAY('2021-2-9', '2021-3-12', INTERVAL 1 day) AS date_array
  FROM
    t1 )
  ,t3 AS (
  SELECT
    date2,
    t2.uid,
    t2.level,
    date
  FROM
    t2,
    t2.date_array AS date2
-- 找序列日期往前最接近的一天，包括当天
  WHERE
    date<=date2 )
SELECT
  date2,
  uid,
  MAX(level) as level
-- 找按序列日期和uid分组的最大等级
FROM
  t3
GROUP BY
  1,
  2
  order by 2,1

可以看到刚才缺失的3-7已经正确的填充了。
这里等级是只增不减的，所以取最大值也没有问题，
如果是有增有减的，用前面讲过的分析函数，last_value并按ts升序，限制窗口为从头到当前位置就可以了。
至于pandas怎么实现，可以去看我在成长曲线里讲的方法，pivot_table再向左填充空值。