mysql - BigQuery 或 SQL 中的增量方法？

Question

因此，我试图构建一条 delta 曲线，以了解 MLB 球员的表现如何随着年龄的增长而变化。

The “delta method” looks at all players who have played in back-to-back years. Many players have several back-to-back year “couplets,” obviously. For every player, it takes the difference between their rate of performance in Year I and Year II and puts that difference into a “bucket,” which is defined by the age of the player in those two years.

假设一名球员在 25 岁时的平均安打率为 0.300，然后在他 26 岁时的平均安打率为 0.310。因此我们取 +.10 的差异并将 25/26 年龄“桶” ”。

所以我有一个这样构造的表:

ID   Name      Age  Average
123  Joe Smith  25  .300
123  Joe Smith  26  .310
123  Joe Smith  27  .312
123  Joe Smith  28  .315

所以我正在寻找的输出基本上是一个我正在创建一个新行的输出，该行标识了我们看到球员背靠背赛季的平均差异的所有实例(所以如果有人有年龄25 岁的赛季但不是 26 岁的赛季，他们不会被包括在内)。我确信这涉及某种 CASE 语句，但我对这一切有点生疏。如果您不了解 BigQuery，请随意回复，就像这是 SQL 一样。

Answer 1

尝试以下 BigQuery 标准 SQL

#standardSQL
SELECT ID, Name, Age, Average, bucket, ROUND(diff, 3) AS diff
FROM (
  SELECT *,
    CONCAT(CAST(Age AS STRING), '/', CAST(Age + 1 AS STRING)) AS bucket, 
    MAX(Average) OVER(nextYear) - Average AS diff
  FROM yourTable
  WINDOW nextYear AS (PARTITION BY ID ORDER BY Age RANGE BETWEEN 1 FOLLOWING AND 1 FOLLOWING)
)
WHERE IFNULL(diff, 0) <> 0 
-- ORDER BY ID, Age  

您可以使用问题中的虚拟数据来测试/玩它

#standardSQL
WITH yourTable AS (
  SELECT 123 AS ID, 'Joe Smith' AS Name, 25 AS Age, .300 AS Average UNION ALL
  SELECT 123, 'Joe Smith', 26, .310 UNION ALL
  SELECT 123, 'Joe Smith', 27, .312 UNION ALL
  SELECT 123, 'Joe Smith', 28, .315 
)
SELECT ID, Name, Age, Average, bucket, ROUND(diff, 3) AS diff
FROM (
  SELECT *,
    CONCAT(CAST(Age AS STRING), '/', CAST(Age + 1 AS STRING)) AS bucket, 
    MAX(Average) OVER(nextYear) - Average AS diff
  FROM yourTable
  WINDOW nextYear AS (PARTITION BY ID ORDER BY Age RANGE BETWEEN 1 FOLLOWING AND 1 FOLLOWING)
)
WHERE IFNULL(diff, 0) <> 0 
ORDER BY ID, Age