代码之家 › 专栏 › 技术社区 › Alex T

如何在BigQuery中执行pandas列移位?

google-bigquery

Alex T · 技术社区 · 7 年前

假设我们有一个包含以下数据的数据集:

timestamp,col1

1533286270,1
1533286271,2
1533286272,3
1533286273,4
1533286274,5

我想先发制人 col1 价值 col1_prev 能够比较它们。结果应该与 pandas.shift(-1) .

如何用简单的SQL查询实现这个功能?

查询结果应如下所示:

timestamp,col1,col1_prev

1533286270,1,NULL
1533286271,2,1
1533286272,3,2
1533286273,4,3
1533286274,5,4

1 回复 | 直到 7 年前

Graham Polley 7 年前

使用 lag() 功能是一种方式:

WITH
  input AS (
  SELECT
    1533286270 AS timestamp,
    1 AS col1
  UNION ALL
  SELECT
    1533286271 AS timestamp,
    2 AS col1
  UNION ALL
  SELECT
    1533286272 AS timestamp,
    3 AS col1
  UNION ALL
  SELECT
    1533286273 AS timestamp,
    4 AS col1
  UNION ALL
  SELECT
    1533286274 AS timestamp,
    5 AS col1 )
SELECT
  timestamp,
  col1,
  LAG(col1) OVER(ORDER BY col1) AS col1_prev
FROM
  input