Google bigquery 在BigQuery中查找匹配行的前后行?

Google bigquery 在BigQuery中查找匹配行的前后行?,google-bigquery,Google Bigquery,是否可以在BigQuery查询中查找匹配行之前和之后的行?例如,如果我这样做: select textPayload from logs.logs_20160709 where textPayload like "%something%" 然后说我得到了这些结果: something A something B 如何显示匹配行前后的3行?大概是这样的: some text 1 some text 2 some text 3 something A some text 4 some text

是否可以在BigQuery查询中查找匹配行之前和之后的行?例如,如果我这样做:

select textPayload from logs.logs_20160709 where textPayload like "%something%"
然后说我得到了这些结果:

something A
something B
如何显示匹配行前后的3行?大概是这样的:

some text 1
some text 2
some text 3
something A
some text 4
some text 5
some text 6
some text 90
some text 91
some text 92
something B
some text 93
some text 94
some text 95

这可能吗?如果可能的话,怎么可能?

我想,您的示例中缺少了一个部分-定义顺序的额外字段,因此我在回答中为此添加了ts字段。这意味着我假设您的表包含两个字段:textPayload和ts

试试下面。应该给你你所需要的

SELECT 
  all.textPayload
FROM (
  SELECT start, finish
  FROM (
    SELECT textPayload,
      LAG(ts, 3) OVER(ORDER BY ts ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS start, 
      LEAD(ts, 3) OVER(ORDER BY ts ROWS BETWEEN CURRENT ROW AND 3 FOLLOWING) AS finish
    FROM YourTable
  )
  WHERE textPayload CONTAINS 'something'
) AS matches
CROSS JOIN YourTable AS all
WHERE all.ts BETWEEN matches.start AND matches.finish

请注意:这取决于ts字段的类型-您可能需要在该字段的查询中进行一些数据转换。希望不要

我想,您的示例中缺少了一个部分—定义顺序的额外字段,因此我在回答中为此添加了ts字段。这意味着我假设您的表包含两个字段:textPayload和ts

试试下面。应该给你你所需要的

SELECT 
  all.textPayload
FROM (
  SELECT start, finish
  FROM (
    SELECT textPayload,
      LAG(ts, 3) OVER(ORDER BY ts ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS start, 
      LEAD(ts, 3) OVER(ORDER BY ts ROWS BETWEEN CURRENT ROW AND 3 FOLLOWING) AS finish
    FROM YourTable
  )
  WHERE textPayload CONTAINS 'something'
) AS matches
CROSS JOIN YourTable AS all
WHERE all.ts BETWEEN matches.start AND matches.finish

请注意:这取决于ts字段的类型-您可能需要在该字段的查询中进行一些数据转换。希望在祖马海滩时不要这样-我在最初的回答中考虑避免交叉连接。
检查以下内容-应
便宜得多
,尤其是对于大型电视机

SELECT textPayload
FROM (
  SELECT textPayload, 
    SUM(match) OVER(ORDER BY ts ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING) AS flag
  FROM (
    SELECT textPayload, ts,  IF(textPayload CONTAINS 'something', 1, 0) AS match 
    FROM YourTable
  )
)
WHERE flag > 0

当然,避免交叉连接的另一种方法是使用BigQuery标准SQL。但是,上面的解决方案根本没有连接,比我在祖马海滩时的原始答案要好,我在原始答案中考虑避免交叉连接。
检查以下内容-应
便宜得多
,尤其是对于大型电视机

SELECT textPayload
FROM (
  SELECT textPayload, 
    SUM(match) OVER(ORDER BY ts ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING) AS flag
  FROM (
    SELECT textPayload, ts,  IF(textPayload CONTAINS 'something', 1, 0) AS match 
    FROM YourTable
  )
)
WHERE flag > 0
当然,避免交叉连接的另一种方法是使用BigQuery标准SQL。但是,上面没有连接的解决方案比我原来的答案要好