mysql - postgresql 数据的间隔平均

Question

我的数据是这样的

wavelength    reflectance
341.6         2.48
343.6         2.58
347.6         4.51
351.1         8.51
359.2         9.56
362.2         11.2
364.2         25.3
365.3         58.2
366.6         58.2
368.9         24.2
373.6         28.2

我希望取 10 个波长间隔，并对它们之间的反射率间隔取平均值，然后输出将是我想要这样的输出

wavelength    reflectnce
341.6         2.48
351.1         5.20
362.2         10.38
373.6         32.35

如果我使用此代码，它会很好地显示波长间隔。现在我想平均它们之间的反射率间隔，我将把它放在下一行反射率上

    ;WITH CTE AS(
SELECT ROW_NUMBER() OVER(PARTITION BY CAST(wavelength AS INT)-CAST(wavelength AS INT)%10 ORDER BY wavelength) AS ROW_ID,wavelength,reflectance FROM Your_Table
)

SELECT wavelength,reflectance FROM CTE WHERE ROW_ID=1

如果有人知道请帮帮我...

Answer 1

我猜你看起来像这样 --> demo (click me please)

WITH CTE AS(
SELECT 
  ROW_NUMBER() OVER(
    PARTITION BY CAST(wavelength AS INT)
                 -CAST(wavelength AS INT)%10 
    ORDER BY wavelength) AS ROW_ID,
  wavelength,
  avg( reflectance ) OVER(
    PARTITION BY CAST(wavelength AS INT)
                 -CAST(wavelength AS INT)%10 
    ORDER BY wavelength
    rows BETWEEN 1 FOLLOWING
         and UNBOUNDED FOLLOWING) As reflectance 
  FROM 
  Your_Table
)
select wavelength, reflectance
from cte
where row_id = 1

这是示例数据查询的结果:

| WAVELENGTH | REFLECTANCE |
|------------|-------------|
|      341.6 |       3.545 |
|      351.1 |        9.56 |
|      362.2 |      41.475 |
|      373.6 |      (null) |

计算的平均值不包括第一个值，
例如，对于输入数据中的以下行:

wavelength    reflectance
341.6         2.48
343.6         2.58
347.6         4.51

avg = ( 2.58 + 4.51 )/2 --> 第一行被跳过