从每组的第一行和最后一行获取值 [英] Get values from first and last row per group

问题描述

我是Postgres的新手，来自MySQL，希望你们中的一个能够帮助我。

I'm new to Postgres, coming from MySQL and hoping that one of y'all would be able to help me out.

我有一个有三列的表格： name ， week 和值。该表格记录了名称，记录高度的一周以及它们的高度值。
就像这样：

I have a table with three columns: name, week, and value. This table has a record of the names, the week at which they recorded the height, and the value of their height. Something like this:

Name  |  Week  | Value
------+--------+-------
John  |  1     | 9
Cassie|  2     | 5
Luke  |  6     | 3
John  |  8     | 14
Cassie|  5     | 7
Luke  |  9     | 5
John  |  2     | 10
Cassie|  4     | 4
Luke  |  7     | 4

我想要的是每个用户在最低周和最高周的价值列表。像这样：

What I want is a list per user of the value at the minimum week and the max week. Something like this:

Name  |minWeek | Value |maxWeek | value
------+--------+-------+--------+-------
John  |  1     | 9     | 8      | 14
Cassie|  2     | 5     | 5      | 7
Luke  |  6     | 3     | 9      | 5

在Postgres中，我使用这个查询：

In Postgres, I use this query:

select name, week, value
from table t
inner join(
select name, min(week) as minweek
from table
group by name)
ss on t.name = ss.name and t.week = ss.minweek
group by t.name
;

但是，我收到一个错误：

However, I receive an error:

列w.week必须出现在GROUP BY子句中或用于汇总函数

Position：20

column "w.week" must appear in the GROUP BY clause or be used in an aggregate function
Position: 20

这对我在MySQL中工作得很好，所以我想知道我在这里做错了什么？

This worked fine for me in MySQL so I'm wondering what I'm doing wrong here?

推荐答案

这有点痛苦，因为Postgres有很好的窗口函数 first_value（）和 last_value（），但这些不是聚合函数。所以，这里有一个方法：

This is a bit of a pain, because Postgres has the nice window functions first_value() and last_value(), but these are not aggregation functions. So, here is one way:

select t.name, min(t.week) as minWeek, max(firstvalue) as firstvalue,
       max(t.week) as maxWeek, max(lastvalue) as lastValue
from (select t.*, first_value(value) over (partition by name order by week) as firstvalue,
             last_value(value) over (partition by name order by week) as lastvalue
      from table t
     ) t
group by t.name;

这篇关于从每组的第一行和最后一行获取值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！