最大函数未过滤结果。

huangapple go评论55阅读模式
英文:

Max function is not filtering results

问题

我试图根据最后更新日期字段获取最近的开始日期。当我尝试使用max函数时,它没有筛选结果,仅显示最近的数据。

select max(a.LAST_UPDATED_DATE),a.term_start  
from table AS a
where a.ID='123456' and location='Colorado' 
group by 2

这是我得到的输出:

MAX(a.LAST_UPDATED_DATE) TERM_START
2023-02-27T18:27:50Z 2023-04-12T07:00:00Z
2023-01-24T18:26:44Z 2023-04-05T07:00:00Z

我只想得到:

MAX(a.LAST_UPDATED_DATE) TERM_START
2023-02-27T18:27:50Z 2023-04-12T07:00:00Z
英文:

I am trying to only get the most recent start date based on the last updated date field. When I try to use max, it is not filtering the results to only the most recent data.

select max(a.LAST_UPDATED_DATE),a.term_start  
from table AS a
where a.ID='123456' and location='Colorado' 
group by 2

This is the output I am getting

MAX(a.LAST_UPDATED_DATE) TERM_START
2023-02-27T18:27:50Z 2023-04-12T07:00:00Z
2023-01-24T18:26:44Z 2023-04-05T07:00:00Z

I only want to get

MAX(a.LAST_UPDATED_DATE) TERM_START
2023-02-27T18:27:50Z 2023-04-12T07:00:00Z

答案1

得分: 1

如果您可以访问窗口函数,您可以使用它

选择 max(a.LAST_UPDATED_DATE) 分区按 (a.ID, location) ,a.term_start  
从表格 作为 a
其中 a.ID='123456' 并且 location='科罗拉多'
英文:

If you have access to window function, you can use it

select max(a.LAST_UPDATED_DATE) partition by (a.ID, location) ,a.term_start  
from table AS a
where a.ID='123456' and location='Colorado' 

答案2

得分: 0

我已经在Oracle、MS Server和HANA上尝试过这个操作:

Select
*
from
(
select 
 ROW_NUMBER() OVER( PARTITION BY a.ID, a.location
     ORDER BY term_start DESC, a.LAST_UPDATED_DATE DESC
 ) row_num,
 a.LAST_UPDATED_DATE,
 a.term_start  
from  a
where a.ID='123456' and 
location='Colorado'
) as test
where row_num = 1

如果你移除 where a.ID='123456' and location='Colorado',它仍然可以工作,并且你会在结果中看到所有的位置和ID。

在分区中使用每个 a.ID 和 location 的组合,其中 row_num = 1 返回了最近的值,因为在分区内,这些值是按 term_start DESC、a.LAST_UPDATED_DATE DESC 排序的。

在你的代码中,你正在以 term_start 进行分组,这会导致 TERM_START 的唯一值,因此有两行。也许你想要以 a.ID 和 location 进行分组。这样你就会得到每个位置的 MAX(a.LAST_UPDATED)。在查询的意图中,你还提到你想要最近的值。MAX 和最近不一定是相同的。

英文:

I've tried this on Oracle, MS Server, and HANA:

    Select
    *
    from
    (
    select 
     ROW_NUMBER() OVER( PARTITION BY a.ID, a.location
         ORDER BY term_start DESC, a.LAST_UPDATED_DATE DESC
     ) row_num,
     a.LAST_UPDATED_DATE,
     a.term_start  
    from  a
    where a.ID='123456' and 
    location='Colorado'
    ) as test
    where row_num = 1

It will still work if you remove `where a.ID='123456' and location='Colorado' and you will see all of the locations and ids in your result

Using each a.ID and location combination in the partition where row_num = 1 returns the most recent value since within the partition the values are ordered term_start DESC, a.LAST_UPDATED_DATED_DATE DESC.

In your code, you are grouping on the term_start which results in unique values for TERM_START, therefore, two rows. You might want to group on the a.ID and location instead. You will get the MAX (a.LAST_UPDATED) per location. In the intent of the query you also mentioned you wanted most recent. The MAX and Most recent are not necessarily the same.

huangapple
  • 本文由 发表于 2023年6月29日 01:27:52
  • 转载请务必保留本文链接:https://go.coder-hub.com/76575459.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定