需要筛选3列的行号()

2skhul33 于 2021-08-09 发布在 Java

关注(0)|答案(2)|浏览(240)

我的数据库里有这样的数据

---------------------------------------------------------------
shopid | shopname | keyword | date     | filename
---------------------------------------------------------------
1      | shop1    | A_key       |2020-04-24| A     
---------------------------------------------------------------
1      | shop1    | A_key       |2020-04-24| B
---------------------------------------------------------------
1      | shop1    | B_key       |2020-04-25| B     
---------------------------------------------------------------
1      | shop1    | B_key       |2020-04-25| C     
---------------------------------------------------------------
2      | shop2    | C_key       |2020-04-24| A     
---------------------------------------------------------------
2      | shop2    | C_key       |2020-04-24| B
---------------------------------------------------------------
3      | shop3    | D_key       |2020-04-24| A    
---------------------------------------------------------------
3      | shop3    | D_key       |2020-04-24| B    
---------------------------------------------------------------
3      | shop3    | E_key       |2020-04-24| A 
---------------------------------------------------------------
3      | shop3    | E_key       |2020-04-25| B
---------------------------------------------------------------

我想要的结果是来自最新文件名的最新数据

---------------------------------------------------------------
shopid | shopname | keyword | date     | filename
---------------------------------------------------------------
1      | shop1    | A_key       |2020-04-24| B
---------------------------------------------------------------
1      | shop1    | B_key       |2020-04-25| C     
---------------------------------------------------------------
2      | shop2    | C_key       |2020-04-24| B
---------------------------------------------------------------
3      | shop3    | D_key       |2020-04-24| B    
---------------------------------------------------------------
3      | shop3    | E_key       |2020-04-24| A 
---------------------------------------------------------------
3      | shop3    | E_key       |2020-04-25| B
---------------------------------------------------------------

我想知道如何用sql实现这一点我的sql

select *,rank() over (partition by date,keyword order by filename desc) rank
from table
 order by date asc;

sql Database amazon-redshift

来源：https://stackoverflow.com/questions/62280602/need-to-filter-3-columns-with-row-number

2条答案

按热度按时间

pnwntuvh1#

我想要的结果是来自最新文件名的最新数据
我不完全明白这一点。下面返回每个商店的最新日期行：

select t.*
from (select t.*,
             rank() over (partition by shopid order by date desc) as seqnum
      from table t
     ) t
where seqnum = 1;

赞(0）回复(0）举报 2021-08-09

yqlxgs2m2#

看起来您希望每个商店/关键字/日期有一行，如果有多行符合此条件，请使用文件名来决定哪个是最新的（c晚于b等）
您可以使用分析工具：

SELECT 
  x.*
FROM 
  (
    SELECT
      *,
      row_number() over (partition by shopid,keyword,date order by filename desc) as rn
    FROM 
      table 
  ) x
WHERE 
  x.rn = 1

但我敢说，你也可以用标准分组：

SELECT
  shopid, shopname, keyword, date, MAX(filename) as filename
FROM 
  table 
GROUP BY 
  shopid, shopname, keyword, date

如果您想在中开始添加其他详细列，则此方法将失败，但据我从您的q中看到的，没有任何详细列

赞(0）回复(0）举报 2021-08-09

我来回答

需要筛选3列的行号()

2条答案

相关问题

热门标签

最新问答