Luxury Numbers Table in BigQuery

Question

数字表在各种情况下都是非常宝贵和有用的。
有很多方法可以在sql中创建和填充数字表。
在BigQuery中处理这个问题的最好、最优的方法是什么?

Answer 1

我发现有几个合理的选项适用于GBQ中的大多数情况。
到目前为止，我最喜欢的是不使用任何现有的表作为基础，而是动态生成它，并使用内联的主sql逻辑

SELECT pos 
FROM (
     SELECT ROW_NUMBER() OVER() AS pos, h 
     FROM (FLATTEN((
        SELECT SPLIT(RPAD('', :VAR_END, '.'),'') AS h FROM (SELECT NULL)),h
))) 
WHERE pos BETWEEN :VAR_START AND :VAR_END
AND (pos - :VAR_START) % :VAR_STEP = 0

用您需要的值替换:VAR_START，:VAR_STEP和:VAR_END，您可以获得相应的数字表

这种方法对我来说在大多数情况下都有效，并且很容易转换为其他情况。例如，日期表:

SELECT DATE(DATE_ADD(TIMESTAMP(:VAR_START), pos - 1, "DAY")) as day
FROM (
     SELECT ROW_NUMBER() OVER() AS pos, h
     FROM (FLATTEN((
         SELECT SPLIT(RPAD('', 1 + DATEDIFF(TIMESTAMP(:VAR_END), TIMESTAMP(:VAR_START)), '.'),'') AS h
         FROM (SELECT NULL)),h
)))

将:VAR_START和:VAR_END分别替换为'2015-08-25'和'2015-09-15'，则该表中包含所有日期

我有时使用的另一个选项是JS UDF来生成更高级的序列