如何通过在新表中包含UID列进行分组

Question

我正试图用以下查询对一些数据进行分组，但我还需要在c_schema.wort_case表中添加a_import.veg表中的uid列，该表包含唯一值。据我所知，我不能像往常一样添加它，因为groupby无法正常工作，因为它占用了新表中的所有唯一id。。。有办法做到这一点吗？

CREATE TABLE IF NOT EXISTS c_schema.worst_case
as SELECT back_str, ahead_str, substr(weather_case, 1, 4), min(min_dist) as min_dist
FROM a_import.veg
GROUP BY back_str, ahead_str, substr(weather_case, 1, 4)
ORDER BY back_str, ahead_str;

Answer 1

我猜您想要distinct on:

SELECT DISTINCT ON (back_str, ahead_str, substr(weather_case, 1, 4)) v.*
FROM a_import.veg v
ORDER BY back_str, ahead_str, substr(weather_case, 1, 4), min_dist;

(选择您实际需要的列。(

实际上，Postgres现在支持LEFT()，所以您可以将其缩短为：

SELECT DISTINCT ON (back_str, ahead_str, LEFT(weather_case, 4)) v.*
FROM a_import.veg v
ORDER BY back_str, ahead_str, LEFT(weather_case, 4), min_dist;