窗口内计算与外部列值的距离实现求助

问题根源 #

直接使用group_concat(x-x, ', ')得到全0，是因为这里的两个x都指向当前行的字段值，而非窗口范围内其他邻居的x。要计算邻居与当前值的距离，需要先将窗口内的每个邻居x与当前行x配对，计算距离后再聚合。

具体实现（SQLite为例） #

通过CTE（公共表表达式）先展开所有符合条件的邻居配对，计算距离后再聚合：

WITH neighbor_pairs AS (
    SELECT 
        main.x AS current_x,
        neighbor.x AS neighbor_x,
        neighbor.x - main.x AS distance
    FROM demo main
    JOIN demo neighbor 
    ON floor(neighbor.x) BETWEEN floor(main.x) - 1 AND floor(main.x) + 1
)
SELECT 
    current_x AS x,
    group_concat(distance, ', ') AS neighbor_distances
FROM neighbor_pairs
GROUP BY current_x;

结果说明 #

执行后会得到每个x对应的邻居距离列表：

• 0.6对应的距离：0, 0.5, 0.9
• 1.1对应的距离：-0.5, 0, 0.4, 0.9
• 1.5对应的距离：-0.9, -0.4, 0, 0.5
• 2.0对应的距离：-1.4, -0.9, -0.5, 0

Apache Spark适配 #

如果使用Apache Spark，将group_concat替换为collect_list，逻辑一致：

import org.apache.spark.sql.functions._

val demoDF = spark.table("demo")

val neighborPairsDF = demoDF.alias("main")
  .join(demoDF.alias("neighbor"), 
        floor(col("neighbor.x")).between(floor(col("main.x")) - 1, floor(col("main.x")) + 1))
  .select(
    col("main.x").alias("current_x"),
    col("neighbor.x").alias("neighbor_x"),
    (col("neighbor.x") - col("main.x")).alias("distance")
  )

val resultDF = neighborPairsDF
  .groupBy("current_x")
  .agg(collect_list("distance").alias("neighbor_distances"))
  .withColumnRenamed("current_x", "x")

resultDF.show()

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