mysql - PostgreSQL 上的公式查询

Question

我的输入数据是这样的

wavelength   reflectance
341.6        1.15  
343.1        1.14
344.7        1.13
346.3        1.14
347.9        1.14
349.5        1.12
351.1        1.12
352.6        1.13
354.2        1.13

我正在使用这个公式查询

WITH CTE AS(
    SELECT 
      ROW_NUMBER() OVER(    
        PARTITION BY CAST(wavelength AS INT)
                     -CAST(wavelength AS INT)%5
        ORDER BY wavelength) AS ROW_ID,
      wavelength,
      avg( reflectance ) OVER(
        PARTITION BY CAST(wavelength AS INT)
                     -CAST(wavelength AS INT)%5
        ORDER BY wavelength
        rows BETWEEN 1 FOLLOWING
             and UNBOUNDED FOLLOWING) As reflectance 
      FROM 
      test
    )
    select trunc(wavelength/5)*5 AS wavelengthwavelength, reflectance
    from CTE
    where row_id = 1

在此查询中，它提供如下输出

wavelength  reflectance
340         2.6400000000000000
340         2.5200000000000000
345         2.5200000000000000
355         2.5500000000000000
360         2.4250000000000000
365         2.4650000000000000
365         2.5450000000000000
370         2.4733333333333333
380         2.6600000000000000
385         2.7400000000000000
390         2.7700000000000000
390         2.8833333333333333

在此输出中，340,365,390 被放置为两次，而不是放置为两次，而应该仅根据最小值放置为一次，应该如何执行此操作...

Answer 1

WITH cte AS(
   SELECT row_number() OVER(PARTITION BY wavelength::int - wavelength::int%5
                            ORDER BY wavelength) AS row_id,
          wavelength,
          avg(reflectance) OVER(PARTITION BY wavelength::int - wavelength::int%5
                           ORDER BY wavelength
                           ROWS BETWEEN 1 FOLLOWING
                           AND UNBOUNDED FOLLOWING) AS reflectance 
   FROM   test
   )
SELECT DISTINCT ON (1)
       trunc(wavelength/5)*5 AS wavelength, reflectance
FROM   cte
WHERE  row_id = 1
ORDER  BY 1, 2;

DISTINCT ON 是标准 SQL DISITNCT 的 Postgres 扩展，如果您想要添加更多列并仍然选择具有最小反射率<的行，则特别有用。否则，GROUP BY 也可以完成这项工作。

此相关答案中的更多详细信息:
Select first row in each GROUP BY group?

使用DISTINCT ON，您需要在最终的SELECT中ORDER BY。但是，无论哪种方式，您都需要添加它。 Per documentation:

Currently, window functions always require presorted data, and so the query output will be ordered according to one or another of the window functions' PARTITION BY/ORDER BY clauses. It is not recommended to rely on this, however. Use an explicit top-level ORDER BY clause if you want to be sure the results are sorted in a particular way.