如何根据BigQuery中的另一个现有行为每个用户添加记录？

小编典典

如何根据BigQuery中的另一个现有行为每个用户添加记录？

sql

如果有人的知识比其他人多，可以在此处发布帮助。

我有一个这样的表：

| Row |   date   |user id | score |
-----------------------------------
|  1  | 20201120 |  1     |   26  |
-----------------------------------
|  2  | 20201121 |  1     |   14  |
-----------------------------------
|  3  | 20201125 |  1     |   0   |
-----------------------------------
|  4  | 20201114 |  2     |   32  |
-----------------------------------
|  5  | 20201116 |  2     |   0   |
-----------------------------------
|  6  | 20201120 |  2     |   23  |
-----------------------------------

但是，由此，我需要为每一位用户提供每天的记录，如果用户缺了一天，那么应该保留最后记录的分数，那么我将得到以下内容：

| Row |   date   |user id | score |
-----------------------------------
|  1  | 20201120 |  1     |   26  |
-----------------------------------
|  2  | 20201121 |  1     |   14  |
-----------------------------------
|  3  | 20201122 |  1     |   14  |
-----------------------------------
|  4  | 20201123 |  1     |   14  |
-----------------------------------
|  5  | 20201124 |  1     |   14  |
-----------------------------------
|  6  | 20201125 |  1     |   0   |
-----------------------------------
|  7  | 20201114 |  2     |   32  |
-----------------------------------
|  8  | 20201115 |  2     |   32  |
-----------------------------------
|  9  | 20201116 |  2     |   0   |
-----------------------------------
|  10 | 20201117 |  2     |   0   |
-----------------------------------
|  11 | 20201118 |  2     |   0   |
-----------------------------------
|  12 | 20201119 |  2     |   0   |
-----------------------------------
|  13 | 20201120 |  2     |   23  |
-----------------------------------

我正在尝试使用StandardSQL在BigQuery中做到这一点。我对如何在接下来的空日期中保持相同的分数有一个想法，但是我真的不知道如何为每个用户添加缺少日期的新行。另外，请记住，此示例只有2个用户，但是在我的数据中，我有1500多个。

我的最终目标是显示每天平均分数。对于背景而言，由于我们的逻辑，如果未在特定日期记录分数，则意味着用户仍处于记录的最后分数中，这就是为什么我每天需要为每个用户评分。

我真的很感激我能得到的任何帮助！我一直在尝试不同的选择，但没有成功

阅读 160

2021-04-15

共1个答案

小编典典

以下是BigQuery标准SQL

#standardSQL
select date, user_id, 
  last_value(score ignore nulls) over(partition by user_id order by date) as score
from (
  select user_id, format_date('%Y%m%d', day) date,  
  from (
    select user_id, min(parse_date('%Y%m%d', date)) min_date, max(parse_date('%Y%m%d', date)) max_date
    from `project.dataset.table` 
    group by user_id
  ) a, unnest(generate_date_array(min_date, max_date)) day
)
left join `project.dataset.table` b
using(date, user_id)
-- order by user_id, date

2021-04-15