前端之家

  1. 首页
  2. 问答

根据两个字段获取最新记录

• 问答

我有一个包含以下字段的表格：

user_id         year      month     amount    type
--------------------------------------------------
5               2018      1         100       Foo
6               2017      12        100       Bar
6               2018      11        100       Foo
6               2018      12        100       Bar
7               2018      12        100       Foo
7               2019      12        100       Bar
8               2019      12        100       Foo

我想做三件事：

  1. 获取特定用户的每个用户 ID 的最新记录（1 条记录，最近的年份和月份字段），

类似：

select *
from myTable
where user_id in (6,7) and <is latest year / month>

哪个应该返回

user_id         year      month     amount     type
---------------------------------------------------
6               2018      12        100        Bar
7               2019      12        100        Foo
  1. 计算上述查询的总数，例如：
    select SUM(amount) as myTotal,avg(amount) as myAverage,(count # of foos in result) as numberOfFoos,(count # of bars in result) as numberOfBars
    from myTable
    where user_id in (6,7) and <is latest year / month>

这将返回带有自定义字段的一行：

myTotal     myAverage     numberOfFoos      numberOfBars
--------------------------------------------------------
300         100           2                 1
  1. 与上面的查询相同，但对于一段时间内的每个月，例如。在过去的 3 到 5 年里，最好是尽可能少地调用，而不是手动循环 36 个月以上并单独拨打电话。
    year     month    myTotal     myAverage     numberOfFoos      numberOfBars
    --------------------------------------------------------------------------
    2018     1        300         100           2                 1
    2018     2        300         100           2                 1
    2018     3        300         100           2                 1
    ...
    2020     12       300         100           2                 1
jzym123 回答：根据两个字段获取最新记录

第一个问题可以用row_number函数解决：

with d as (
  select 
    users.*,row_number() over (partition by user_id  order by year desc,month desc ) rn 
  from  users 
  where  user_id in (5,6)
) 
select * from d
where  rn = 1;

PostgreSQL fiddle here

第二个问题可以用同样的方法解决：

with d as (
  select 
    users.*,month desc) rn 
  from  users 
  where  user_id in (6,7)
) 
select 
    sum(amount),avg(amount),count(*) filter (where type = 'Foo') count_foo,count(*) filter (where type = 'Bar') count_bar
from d
where  rn = 1;

Test solution here

, 
select year,month,sum(amount),sum(case when type = 'Foo' then 1 else 0 end) as num_foos,sum(case when type = 'Bar' then 1 else 0 end) as num_bars
from (select u.*,month desc ) as seqnum 
      from users 
      where user_id in (5,6)
     ) u
where seqnum = 1
group by year,month;
greatest-n-per-grouppostgresqlsql
本文链接：https://www.f2er.com/32943.html

大家都在问