python - python中每组与sqlite3最接近的匹配？

Question

我很想知道如何找到最接近的匹配，但后来我意识到sql-server ROW_NUMBER 和 PARTITION BY 并没有在 python 脚本中的 sqlite3 中实现。

这是我正在尝试做的事情:

我有一个与此类似的表格:

Motor MotorType CalibrationValueX CalibrationValueY
A     Car       1.2343            2.33343
B     Boat      1.2455            2.55434
B1    Boat      1.4554            2.11211
C     Car       1.4323            4.56555
D     Car       1.533             4.6666
..... 500 entries

在我的 SQL 查询中，我试图找到 CalibrationValueY 的平均值，其中 CalibrationValueX 是某个值:

SELECT avg(CalibrationValueY), MotorType, Motor FROM MotorTable
WHERE CalibrationValueX = 1.23333
GROUP BY MotorType

这不会返回任何内容，因为没有正好等于 1.23333 的 CalibrationValueX 值。

我可以通过以下方式为每个 MotorTable 单独找到最接近的匹配:

SELECT CalibrationValueY, FileSize, MotorType, Motor FROM MotorTable
where FileType = 'text' order by abs(FileSize - 1.23333) LIMIT 1

但是，我无法让它与 group by 语句一起使用。我该如何做到这一点，以便如果我按 MotorType 分组并且正在搜索 CalibrationValueX = 1.23333，我会得到以下结果:

A     Car       1.2343            2.33343
B     Boat      1.2455            2.55434

据我所知，使用 row_count 和分区依据的解决方案在 sqlite3 中不起作用。

with cte as (
    SELECT MotorType, CalibrationValueX, CalibrationValueY,
        ROW_NUMBER() over (partition by MotorType order by abs(CalibrationValueX - 1.23333)) rn
    from historyCR
)
SELECT *
from cte
where rn = 1

或者...我可以只为每个 MotorType 执行工作代码 X 类型...类似 SELECT DISTINCT MotorType FROM MotorTable 并在循环中运行其他查询。但是，我担心速度。

Answer 1

1. 获取所有电机类型:

   SELECT DISTINCT MotorType
   FROM MotorTable;
   

2. 获取每种电机类型具有最接近校准值的电机:

   SELECT MotorType,
          (SELECT Motor
           FROM MotorTable AS T2
           WHERE T2.MotorType = Types.MotorType
           ORDER BY abs(CalibrationValueX - 1.2345)
           LIMIT 1
          ) AS Motor
   FROM (SELECT DISTINCT MotorType
         FROM MotorTable) AS Types;
   

3. 使用以下值过滤原始表:

   SELECT Motor,
          MotorType,
          CalibrationValueX,
          CalibrationValueY
   FROM MotorTable
   JOIN (SELECT MotorType,
                (SELECT Motor
                 FROM MotorTable AS T2
                 WHERE T2.MotorType = Types.MotorType
                 ORDER BY abs(CalibrationValueX - 1.2345)
                 LIMIT 1
                ) AS Motor
         FROM (SELECT DISTINCT MotorType
               FROM MotorTable) AS Types)
   USING (MotorType, Motor);