Vega-Lite：如何计算数据中不同行的数值差值？

我有一段Vega-Lite的最小示例代码：

{
  "$schema": "https://vega.github.io/schema/vega-lite/v5.json",
  "data": {"values": [
    {"year":2022, "week":1, "val":5},
    {"year":2022, "week":2, "val":4},
    {"year":2022, "week":3, "val":6},
    {"year":2021, "week":1, "val":3},
    {"year":2021, "week":2, "val":7}
  ]},
  "mark":"line",
  "encoding": {
    "x":{"field":"week"},
    "y":{"field":"val"},
    "color":{"field":"year"}
  }
}

我想要计算当年与前一年同周的数值差值，如果其中某一年的同周数据缺失，就不显示该周的差值。比如示例里周1的差值是2（5-3），周2是-3（4-7）。

之前用LAG转换实现过，但这个方法只有两年周数完全一致时才有效，实际数据里周数经常不匹配。想知道有没有办法把当年和前一年同周的数值放到同一列，或者更通用的实现方式？

可以通过数据转换关联前一年同周数据的方式实现，具体步骤如下：

• 用lookup转换，根据week字段匹配前一年（year-1）的val值，生成新字段prev_year_val；
• 用calculate转换计算差值diff = val - prev_year_val；
• 过滤掉prev_year_val为空的行（也就是某周数据缺失的情况）。

完整代码（引用自身数据源） #

{
  "$schema": "https://vega.github.io/schema/vega-lite/v5.json",
  "data": {"name": "raw_data", "values": [
    {"year":2022, "week":1, "val":5},
    {"year":2022, "week":2, "val":4},
    {"year":2022, "week":3, "val":6},
    {"year":2021, "week":1, "val":3},
    {"year":2021, "week":2, "val":7}
  ]},
  "transform": [
    {
      "lookup": "week",
      "from": {
        "data": {"source": "raw_data"},
        "key": "week",
        "fields": ["val"],
        "filter": "datum.year == parent.year - 1"
      },
      "as": ["prev_year_val"]
    },
    {"calculate": "datum.val - datum.prev_year_val", "as": "diff"},
    {"filter": "datum.prev_year_val != null"}
  ],
  "mark": "bar",
  "encoding": {
    "x": {"field": "week", "type": "ordinal"},
    "y": {"field": "diff", "type": "quantitative"},
    "tooltip": [
      {"field": "week", "title": "周数"},
      {"field": "val", "title": "当年数值"},
      {"field": "prev_year_val", "title": "上年数值"},
      {"field": "diff", "title": "差值"}
    ]
  }
}

方案优势 #

• 不依赖两年周数完全一致，只要同周存在对应数据就会计算差值；
• 自动过滤仅单年有数据的周，只保留两年都有数据的周的差值；
• 通过lookup的过滤条件精准匹配前一年数据，逻辑清晰易维护。

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