获取具有列最大值的行+同一行中同一列的最小值 [英] Fetch row which has the maximum value for a column + Minimum value of the same column in the same row

问题描述

我检查了以下问题：

GROUP BY与MAX（日期）

获取具有列的最大值的行

我想每个 account_number 加上最旧的 time_event_published 同样的的app_guid > account_number ，用于每个 app_guid 在最后一行的另一列。

  SELECT id，account_number，app_guid，time_event_published，< oldest_time_event_published_for_2152331553409959696> FROM（
 SELECT ID，account_number，app_guid，time_event_published，
 RANK（）OVER（PARTITION BY app_guid ORDER BY time_event_published DESC）dest_rank 
 FROM event where account_number = 2152331553409959696 
）where dest_rank = 1; 
  

我只能用 ASC来想象另一个数据库命中相同的查询。有没有其他方法以及如何处理这个要求？

 数据库条目：
 2152331553409959696，TEST-ONE-APP_GUID，25-JAN 
 2152331553409959696，TEST- ONE-APP_GUID，1-JAN 
 
 2152331553409959696，TEST-TWO-APP_GUID，25-FEB 
 2152331553409959696，TEST-TWO-APP_GUID，1-FEB 
 
必填结果：
 2152331553409959696，TEST-ONE-APP_GUID，25-JAN，1-JAN 
 2152331553409959696，TEST-TWO-APP_GUID，25-FEB，1-FEB 
  

解决方案

如果我正确地理解了您的问题，那么我认为下面的SQL将执行您所需的操作：

SELECT id，account_number，app_guid，time_event_published，oldest_time_event_published
FROM（
SELECT id，account_number，app_guid，time_event_published，
RANK（）OVER（PARTITION BY app_guid ORDER BY time_event_published DESC）dest_rank，
MIN（time_event_published）OVER（PARTITION BY app_guid）oldest_time_event_published
FROM event where account_number = 2152331553409959696
）where dest_rank = 1;

让我知道它如何处理您的数据并让我们知道。

我还没有用样本数据进行测试，但我非常有信心它会适用于您！

Ted。

I checked the following questions:

GROUP BY with MAX(DATE)

Fetch the row which has the Max value for a column

Columns : account_number, app_guid, time_event_published(epoch time).

I want the latest row of each app_guid for a given account_number PLUS oldest time_event_published of the same account_number for each app_guid in another column of the latest row.

SELECT id, account_number, app_guid, time_event_published , <oldest_time_event_published_for_2152331553409959696> FROM ( 
  SELECT id, account_number, app_guid, time_event_published,
    RANK() OVER (PARTITION BY app_guid ORDER BY time_event_published DESC) dest_rank
    FROM event where account_number=2152331553409959696
  ) where dest_rank = 1;

I am only able to think of another DB hit with same query with ASC. Is there any other way and how to approach this requirement?

DB Entries:
2152331553409959696, TEST-ONE-APP_GUID, 25-JAN
2152331553409959696, TEST-ONE-APP_GUID, 1-JAN

2152331553409959696, TEST-TWO-APP_GUID, 25-FEB
2152331553409959696, TEST-TWO-APP_GUID, 1-FEB

Required Result:
2152331553409959696, TEST-ONE-APP_GUID, 25-JAN, 1-JAN
2152331553409959696, TEST-TWO-APP_GUID, 25-FEB, 1-FEB

解决方案

If I understood your question correctly then I think the below SQL will do what you require:

SELECT id, account_number, app_guid, time_event_published , oldest_time_event_published FROM ( SELECT id, account_number, app_guid, time_event_published, RANK() OVER (PARTITION BY app_guid ORDER BY time_event_published DESC) dest_rank, MIN (time_event_published) OVER (PARTITION BY app_guid) oldest_time_event_published FROM event where account_number=2152331553409959696 ) where dest_rank = 1;

Let me know how it works with your data and let us know.

I haven't tested with sample data but I am pretty confident it will work for you!

Ted.

这篇关于获取具有列最大值的行+同一行中同一列的最小值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！