BigQuery标准SQL中按条件生成parent_ListViewID列的实现

原始生成表格 #

现有查询代码 #

SELECT
    CONCAT(fullVisitorID, CAST(visitID AS string), date) AS sessionID,
    hits.hitNumber AS hitNumber,
    CONCAT( fullvisitorID, CAST(visitId AS STRING), date, hits.hitNumber ) AS ListViewID,
    product.productListPosition AS Position,
    customDimensions.value AS Sponsored,
    (CASE
         WHEN product.isClick = TRUE THEN TRUE
         ELSE FALSE
     END) AS isClick,
    SUM(
  IF
    (product.isImpression, 1,0)) AS impressions,
  SUM(
  IF
    (product.isClick,1,0)) AS clicks
FROM
    `Table`,
UNNEST (hits) AS hits,
UNNEST (hits.product) AS product,
UNNEST (product.customDimensions) AS customDimensions
WHERE
    product.productListName IN ("searchpage", "categorypage")
    AND customDimensions.index = 68
GROUP BY
    SessionID, hitNumber, ListViewID, Position,
    Sponsored, isClick

新增parent_ListViewID列规则 #

• 当isClick = false时，parent_ListViewID取值为当前行的ListViewID；
• 当isClick = true时，parent_ListViewID取值为同一sessionID下最近一行isClick = false的ListViewID。

期望结果表格 #

解决方案 #

通过在原查询外层嵌套窗口函数实现需求，核心利用LAST_VALUE函数追踪同一sessionID内最近的isClick=false对应的ListViewID：

WITH original_data AS (
    SELECT
        CONCAT(fullVisitorID, CAST(visitID AS string), date) AS sessionID,
        hits.hitNumber AS hitNumber,
        CONCAT( fullvisitorID, CAST(visitId AS STRING), date, hits.hitNumber ) AS ListViewID,
        product.productListPosition AS Position,
        customDimensions.value AS Sponsored,
        (CASE
             WHEN product.isClick = TRUE THEN TRUE
             ELSE FALSE
         END) AS isClick,
        SUM(IF(product.isImpression, 1,0)) AS impressions,
        SUM(IF(product.isClick,1,0)) AS clicks
    FROM
        `Table`,
    UNNEST (hits) AS hits,
    UNNEST (hits.product) AS product,
    UNNEST (product.customDimensions) AS customDimensions
    WHERE
        product.productListName IN ("searchpage", "categorypage")
        AND customDimensions.index = 68
    GROUP BY
        SessionID, hitNumber, ListViewID, Position,
        Sponsored, isClick
)
SELECT
    *,
    CASE
        WHEN isClick = FALSE THEN ListViewID
        ELSE LAST_VALUE(CASE WHEN isClick = FALSE THEN ListViewID END IGNORE NULLS) 
             OVER (PARTITION BY sessionID ORDER BY hitNumber ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
    END AS parent_ListViewID
FROM original_data
ORDER BY sessionID, hitNumber, Position;

代码说明 #

1. 用original_data公共表达式保存原查询结果，简化后续逻辑；
2. 对isClick=false的行，直接返回当前行的ListViewID；
3. 对isClick=true的行，通过LAST_VALUE函数在同一sessionID分组内，按hitNumber排序，筛选出当前行之前最近的isClick=false对应的ListViewID，IGNORE NULLS参数确保跳过isClick=true行的空值；
4. 最终按sessionID、hitNumber和Position排序，保证结果顺序符合预期。

内容的提问来源于stack exchange，提问作者Yaro