在使用分析功能作为视图的一部分时，在DATETIME字段上使用分析函数时进行修剪，并带有一天的粒度

Question

我正在尝试使用分析功能（例如first_value），同时仍然从分区修剪中受益。这是在用日粒度分区的桌子分区的。

示例数据

让我们假设一个具有以下列的表：

名称	类型
dt	dateTime
value	字符串字符串

该表在dt用Day划分为DT 粒度。

可以使用以下SQL创建示例表：

CREATE TABLE `project.dataset.example_partioned_table`
PARTITION BY DATE(dt)
AS
SELECT dt, CONCAT('some value: ', STRING(dt)) AS value
FROM (
    SELECT
        DATETIME_ADD(
            DATETIME(_date),
            INTERVAL ((_hour * 60 + _minute) * 60 + _second) SECOND
        ) AS dt
    FROM UNNEST(GENERATE_DATE_ARRAY(DATE('2020-01-01'), DATE('2020-12-31'))) AS _date
    CROSS JOIN UNNEST(GENERATE_ARRAY(0, 23)) AS _hour
    CROSS JOIN UNNEST(GENERATE_ARRAY(0, 59)) AS _minute
    CROSS JOIN UNNEST(GENERATE_ARRAY(0, 59)) AS _second
)

生成的表将超过1 GB（每天约3.4 MB）。

现在的问题

我想在每个分区中获得第一个值。 （后来我实际上想进一步分解），

因为我想使用视图，视图本身不知道最终日期范围。在示例查询中，我使用临时表t_view代替视图。

WITH t_view AS (
    SELECT
        dt,
        value,
        FIRST_VALUE(value) OVER(
            PARTITION BY DATE(dt)
            ORDER BY dt
        ) AS first_val
    FROM `project.dataset.example_partioned_table`
)

SELECT *,
FROM t_view
WHERE DATE(dt) = DATE('2020-01-01')

该查询将包含一些值之类的内容：2020-01-01 00:00:00 for first_val（即日期的第一个值）。

但是，就目前而言，当它应该扫描分区时，它正在扫描整个桌子（超过1 GB）。

其他观察结果

如果我不包括first_val（分析功能），则分区修剪按预期工作。 包括first_val导致其扫描所有内容。

如果我不将dt与date 包装，则分区修剪也有效，但当然不会提供正确的值。

我还尝试了datetime_trunc（request.timestamp，day），与date（request.timestamp）相同的缺乏分区修剪结果。

还添加临时表中子句工作的日期，但我不知道视图内的日期范围。

如何将分析函数限制为行分区？

失败的解决方法使用组

相关的组，我还尝试了使用（日期）组使用组的解决方法，结果与

WITH t_view_1 AS (
    SELECT
        dt,
        DATE(dt) AS _date,
        value,
    FROM `project.dataset.example_partioned_table`
),

t_view_2 AS (
    SELECT
        _date,
        MIN(value) AS first_val
    FROM t_view_1
    GROUP BY _date
),

t_view AS (
    SELECT
        t_view_1._date,
        t_view_1.dt,
        t_view_2.first_val
    FROM t_view_1
    JOIN t_view_2
        ON t_view_2._date = t_view_1._date
)

SELECT *,
FROM t_view
WHERE _date = '2020-01-01'

以前相同，它正在扫描整个表，而不仅仅是使用选定的日期处理分区。

然后

如果表在日期字段（_DATE）分区（例如：

CREATE TABLE `project.dataset.example_date_field_partioned_table`
PARTITION BY _date
AS
SELECT dt, DATE(dt) AS _date, CONCAT('some value: ', STRING(dt)) AS value
FROM (
    SELECT
        DATETIME_ADD(
            DATETIME(_date),
            INTERVAL ((_hour * 60 + _minute) * 60 + _second) SECOND
        ) AS dt
    FROM UNNEST(GENERATE_DATE_ARRAY(DATE('2020-01-01'), DATE('2020-12-31'))) AS _date
    CROSS JOIN UNNEST(GENERATE_ARRAY(0, 23)) AS _hour
    CROSS JOIN UNNEST(GENERATE_ARRAY(0, 59)) AS _minute
    CROSS JOIN UNNEST(GENERATE_ARRAY(0, 59)) AS _second
)

，分区修剪可以与以下调整后的示例查询一起使用：

WITH t_view AS (
    SELECT
        dt,
        _date,
        value,
        FIRST_VALUE(value) OVER(
            PARTITION BY _date
            ORDER BY dt
        ) AS first_val
    FROM `elife-data-pipeline.de_proto.example_date_field_partioned_table`
)

SELECT *,
FROM t_view
WHERE _date = DATE('2020-01-01')

IE查询扫描大约4 MB） 而不是1 GB

但是，现在我需要添加和填充该附加_DATE字段， 。 （具有外部数据源的不方便）

具有两个带有冗余信息的字段也可能令人困惑。

此外，现在根本没有分区修剪dt（查询需要确保使用_date而不是）。

Answer 1

BQ功能有时会导致查询优化器做出一些效率低下的选择，但是我们一直在尝试改进查询优化器。

因此，在您的方案中，最好的解决方法是添加额外的列日期列并使用它来分区表。

IE。

CREATE TABLE `project.dataset.example_date_field_partioned_table`
PARTITION BY _date
AS
SELECT dt, DATE(dt) AS _date, CONCAT('some value: ', STRING(dt)) AS value
FROM (
    SELECT
        DATETIME_ADD(
            DATETIME(_date),
            INTERVAL ((_hour * 60 + _minute) * 60 + _second) SECOND
        ) AS dt
    FROM UNNEST(GENERATE_DATE_ARRAY(DATE('2020-01-01'), DATE('2020-12-31'))) AS _date
    CROSS JOIN UNNEST(GENERATE_ARRAY(0, 23)) AS _hour
    CROSS JOIN UNNEST(GENERATE_ARRAY(0, 59)) AS _minute
    CROSS JOIN UNNEST(GENERATE_ARRAY(0, 59)) AS _second
)

WITH t_view AS (
   SELECT
       dt,
       _date,
       value,
       FIRST_VALUE(value) OVER(
           PARTITION BY _date
           ORDER BY dt
       ) AS first_val
   FROM `mock.example_date_field_partioned_table`
)
 
SELECT *,
FROM t_view
WHERE _date = DATE('2020-01-01')