问题：

BigQuery/SQL：在年份之间以重复行的形式填补空白

颛孙信厚

2023-03-14

我有一个非常类似的场景，如这个线程所示：复制记录组来填补谷歌BigQuery中的多个日期空白

我使用的查询如下：

WITH history AS (
  SELECT 2012 AS d, 'a' AS product, 'x' AS partner, 10 AS value UNION ALL
  SELECT 2010 AS d, 'b' AS product, 'x' AS partner, 15 AS value UNION ALL
  SELECT 2014 AS d, 'a' AS product, 'y' AS partner, 11 AS value UNION ALL
  SELECT 2012 AS d, 'b' AS product, 'y' AS partner, 16 AS value UNION ALL
  SELECT 2015 AS d, 'b' AS product, 'x' AS partner, 13 AS value UNION ALL
  SELECT 2017 AS d, 'a' AS product, 'y' AS partner, 15 AS value UNION ALL
  SELECT 2017 AS d, 'a' AS product, 'x' AS partner, 15 AS value 
),
daterange AS (
  SELECT EXTRACT(YEAR FROM fiscalYear) as date_in_range
  FROM UNNEST(
    GENERATE_DATE_ARRAY(DATE('2010-01-01'), CURRENT_DATE(), INTERVAL 1 YEAR)
    ) AS fiscalYear),
temp AS (
  SELECT d, product, partner, value, LEAD(d) OVER(PARTITION BY product, partner ORDER BY d) AS next_d
  FROM history
  ORDER BY product, partner, d
)
SELECT date_in_range, product, partner, value
FROM daterange
JOIN temp
ON daterange.date_in_range >= temp.d 
AND (daterange.date_in_range < temp.next_d OR temp.next_d IS NULL)
ORDER BY product, partner, date_in_range

我注意到一件事，对我的场景来说并不理想，那就是每个产品的日期，合作伙伴组合并不总是从2010年开始，这就是我想要的。

因此，每个产品合作伙伴的输出日期应在[2010、2011、2012、2013、2014、2015、2016、2017、2018、2019]范围内

此查询返回的输出为：

+---------------+---------+---------+-------+
| date_in_range | product | partner | value |
+---------------+---------+---------+-------+
| 2012          | a       | x       | 10    |
+---------------+---------+---------+-------+
| 2013          | a       | x       | 10    |
+---------------+---------+---------+-------+
| 2014          | a       | x       | 10    |
+---------------+---------+---------+-------+
| 2015          | a       | x       | 10    |
+---------------+---------+---------+-------+
| 2016          | a       | x       | 10    |
+---------------+---------+---------+-------+
| 2017          | a       | x       | 15    |
+---------------+---------+---------+-------+
| 2018          | a       | x       | 15    |
+---------------+---------+---------+-------+
| 2019          | a       | x       | 15    |
+---------------+---------+---------+-------+
| 2014          | a       | y       | 11    |
+---------------+---------+---------+-------+
| 2015          | a       | y       | 11    |
+---------------+---------+---------+-------+
| 2016          | a       | y       | 11    |
+---------------+---------+---------+-------+
| 2017          | a       | y       | 15    |
+---------------+---------+---------+-------+
| 2018          | a       | y       | 15    |
+---------------+---------+---------+-------+
| 2019          | a       | y       | 15    |
+---------------+---------+---------+-------+
| 2010          | b       | x       | 15    |
+---------------+---------+---------+-------+
| 2011          | b       | x       | 15    |
+---------------+---------+---------+-------+
| 2012          | b       | x       | 15    |
+---------------+---------+---------+-------+
| 2013          | b       | x       | 15    |
+---------------+---------+---------+-------+
| 2014          | b       | x       | 15    |
+---------------+---------+---------+-------+
| 2015          | b       | x       | 13    |
+---------------+---------+---------+-------+
| 2016          | b       | x       | 13    |
+---------------+---------+---------+-------+
| 2017          | b       | x       | 13    |
+---------------+---------+---------+-------+
| 2018          | b       | x       | 13    |
+---------------+---------+---------+-------+
| 2019          | b       | x       | 13    |
+---------------+---------+---------+-------+
| 2012          | b       | y       | 16    |
+---------------+---------+---------+-------+
| 2013          | b       | y       | 16    |
+---------------+---------+---------+-------+
| 2014          | b       | y       | 16    |
+---------------+---------+---------+-------+
| 2015          | b       | y       | 16    |
+---------------+---------+---------+-------+
| 2016          | b       | y       | 16    |
+---------------+---------+---------+-------+
| 2017          | b       | y       | 16    |
+---------------+---------+---------+-------+
| 2018          | b       | y       | 16    |
+---------------+---------+---------+-------+
| 2019          | b       | y       | 16    |
+---------------+---------+---------+-------+

所需输出为：

+---------------+---------+---------+-------+
| date_in_range | product | partner | value |
+---------------+---------+---------+-------+
| 2010          | a       | x       | 10    |
+---------------+---------+---------+-------+
| 2011          | a       | x       | 10    |
+---------------+---------+---------+-------+
| 2012          | a       | x       | 10    |
+---------------+---------+---------+-------+
| 2013          | a       | x       | 10    |
+---------------+---------+---------+-------+
| 2014          | a       | x       | 10    |
+---------------+---------+---------+-------+
| 2015          | a       | x       | 10    |
+---------------+---------+---------+-------+
| 2016          | a       | x       | 10    |
+---------------+---------+---------+-------+
| 2017          | a       | x       | 15    |
+---------------+---------+---------+-------+
| 2018          | a       | x       | 15    |
+---------------+---------+---------+-------+
| 2019          | a       | x       | 15    |
+---------------+---------+---------+-------+
| 2010          | a       | y       | 11    |
+---------------+---------+---------+-------+
| 2011          | a       | y       | 11    |
+---------------+---------+---------+-------+
| 2012          | a       | y       | 11    |
+---------------+---------+---------+-------+
| 2013          | a       | y       | 11    |
+---------------+---------+---------+-------+
| 2014          | a       | y       | 11    |
+---------------+---------+---------+-------+
| 2015          | a       | y       | 11    |
+---------------+---------+---------+-------+
| 2016          | a       | y       | 11    |
+---------------+---------+---------+-------+
| 2017          | a       | y       | 15    |
+---------------+---------+---------+-------+
| 2018          | a       | y       | 15    |
+---------------+---------+---------+-------+
| 2019          | a       | y       | 15    |
+---------------+---------+---------+-------+
| 2010          | b       | x       | 15    |
+---------------+---------+---------+-------+
| 2011          | b       | x       | 15    |
+---------------+---------+---------+-------+
| 2012          | b       | x       | 15    |
+---------------+---------+---------+-------+
| 2013          | b       | x       | 15    |
+---------------+---------+---------+-------+
| 2014          | b       | x       | 15    |
+---------------+---------+---------+-------+
| 2015          | b       | x       | 13    |
+---------------+---------+---------+-------+
| 2016          | b       | x       | 13    |
+---------------+---------+---------+-------+
| 2017          | b       | x       | 13    |
+---------------+---------+---------+-------+
| 2018          | b       | x       | 13    |
+---------------+---------+---------+-------+
| 2019          | b       | x       | 13    |
+---------------+---------+---------+-------+
| 2010          | b       | y       | 16    |
+---------------+---------+---------+-------+
| 2011          | b       | y       | 16    |
+---------------+---------+---------+-------+
| 2012          | b       | y       | 16    |
+---------------+---------+---------+-------+
| 2013          | b       | y       | 16    |
+---------------+---------+---------+-------+
| 2014          | b       | y       | 16    |
+---------------+---------+---------+-------+
| 2015          | b       | y       | 16    |
+---------------+---------+---------+-------+
| 2016          | b       | y       | 16    |
+---------------+---------+---------+-------+
| 2017          | b       | y       | 16    |
+---------------+---------+---------+-------+
| 2018          | b       | y       | 16    |
+---------------+---------+---------+-------+
| 2019          | b       | y       | 16    |
+---------------+---------+---------+-------+

共有1个答案

子车鸿运

2023-03-14

下面是BigQuery标准SQL

#standardSQL
WITH history AS (
  SELECT 2012 AS d, 'a' AS product, 'x' AS partner, 10 AS value UNION ALL
  SELECT 2010 AS d, 'b' AS product, 'x' AS partner, 15 AS value UNION ALL
  SELECT 2014 AS d, 'a' AS product, 'y' AS partner, 11 AS value UNION ALL
  SELECT 2012 AS d, 'b' AS product, 'y' AS partner, 16 AS value UNION ALL
  SELECT 2015 AS d, 'b' AS product, 'x' AS partner, 13 AS value UNION ALL
  SELECT 2017 AS d, 'a' AS product, 'y' AS partner, 15 AS value UNION ALL
  SELECT 2017 AS d, 'a' AS product, 'x' AS partner, 15 AS value 
),
daterange AS (
  SELECT EXTRACT(YEAR FROM fiscalYear) AS date_in_range
  FROM UNNEST(
    GENERATE_DATE_ARRAY(DATE('2010-01-01'), CURRENT_DATE(), INTERVAL 1 YEAR)
    ) AS fiscalYear),
history_ext AS (
  SELECT date_in_range, x.product, x.partner, value
  FROM daterange dr
  CROSS JOIN (SELECT DISTINCT product, partner FROM history) x
  LEFT JOIN history h
  ON dr.date_in_range = h.d
  AND STRUCT(h.product, h.partner) = STRUCT(x.product, x.partner)
)
SELECT date_in_range, product, partner, 
COALESCE(
  value, 
  LAST_VALUE(value IGNORE NULLS) OVER(PARTITION BY product, partner ORDER BY date_in_range ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),
  FIRST_VALUE(value IGNORE NULLS) OVER(PARTITION BY product, partner ORDER BY date_in_range ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) 
) AS value
FROM history_ext
ORDER BY product, partner, date_in_range

然后返回

Row date_in_range   product partner value    
1   2010    a   x   10   
2   2011    a   x   10   
3   2012    a   x   10   
4   2013    a   x   10   
5   2014    a   x   10   
6   2015    a   x   10   
7   2016    a   x   10   
8   2017    a   x   15   
9   2018    a   x   15   
10  2019    a   x   15   
11  2010    a   y   11   
12  2011    a   y   11   
13  2012    a   y   11   
14  2013    a   y   11   
15  2014    a   y   11   
16  2015    a   y   11   
17  2016    a   y   11   
18  2017    a   y   15   
19  2018    a   y   15   
20  2019    a   y   15   
21  2010    b   x   15   
22  2011    b   x   15   
23  2012    b   x   15   
24  2013    b   x   15   
25  2014    b   x   15   
26  2015    b   x   13   
27  2016    b   x   13   
28  2017    b   x   13   
29  2018    b   x   13   
30  2019    b   x   13   
31  2010    b   y   16   
32  2011    b   y   16   
33  2012    b   y   16   
34  2013    b   y   16   
35  2014    b   y   16   
36  2015    b   y   16   
37  2016    b   y   16   
38  2017    b   y   16   
39  2018    b   y   16   
40  2019    b   y   16

类似资料：

重复的记录组可填补Google BigQuery中的多个日期空白

问题内容：我发现了一个类似的问题（重复记录以填补Google BigQuery中日期之间的空白），但是存在不同的情况，答案不适用。我的数据结构如下（基本上是多个产品和合作伙伴的价格变动历史记录）：我需要的是一个查询（特别是用BigQuery Standard SQL编写），该查询在给定日期范围（在这种情况下为）下输出以下结果：对于产品和合作伙伴的每种组合，基本上都是价格历史记录，其中包含
以整数形式获取MySQL中两个日期之间的年份差异

问题内容：我正在尝试计算数据库中一个人的年龄。让我们假设有一个简单的表： id是主键（实际上，表更复杂，但我已经对其进行了简化）。我想要一个人多大年龄：但是它返回的结果是几天而不是几年，我可以将其除以365：但是它返回一个浮点值，我想要一个整数。这样我就可以计算出年份：但是有一个问题：例如现在是5月，如果一个战争发生在1970年6月，他仍然31岁而不是32岁，但表达式返回32。我不
年份与周基年份之间的差异？

问题内容： Java 8的类有一个方法，，它使您可以从字符串A-z，a-z字母定义格式。这些例子并没有明确的区别y，今年的时代和Y，以星期为一年。它是什么？问题答案：与2006-W52一样，这是“年－周”样式日期的年值。如果有问题的一周跨越年份边界，则可能偏离年份值+1或-1。
Flexbox-填充剩余空间[重复]

我有一个基本的flexbox布局，如下所示。。我试图使顶部的div填充剩余的空间，底部的div是动态的，因此高度根据文本的不同而变化。我的结果是这样的。。在这种情况下，flexbox是最好的选择吗？
如何填补空白？

问题内容：假设我有两条记录，都有日期和计数：您将如何选择此项以填补时间空白，并始终保持最近的记录？因此输出将是：我还没有找到一个整齐的解决方案。我想可以使用DATEDIFF和for循环来完成此操作，但我希望可以更轻松地完成此操作。问题答案：您有2个要解决的问题。第一个问题是如何填补空白。第二个问题是为那些缺少的记录填充“计数”字段。问题1：这可以通过使用或创建一个来解决。如果可以的话
填补IDENTITY列中的空白

问题内容：我有一个带有IDENTITY列的表在添加/删除了beeing的某些行之后，我以Id值的间隔结尾：有没有一种简单的方法可以将值压缩为？问题答案：我认为用相同的模式创建第二张表，从第一张表导入所有数据（当然，除了identity列；让第二张表开始重新编号），然后删除第一张表并将第二张表重命名为原始名称。如果您有大量的FK关系要与其他表等一起重建，是否容易成为问题。

BigQuery/SQL：在年份之间以重复行的形式填补空白

共有1个答案

相关问答

相关文章

相关阅读

相关工具

相关文档