在查询中丢弃旧数据时分组

I have a table (Oracle):

我有一张桌子（Oracle）：

price
--------
integer id (pk)
integer part_id (fk)
number amount
timestamp change_timestamp

And whenever a part price is changed the software (not mine) adds a new row to this table. Instead of altering the data and there being one entry per part, it adds a new row to the table. The software then looks for the latest entry. But the "old" data lingers in the table.

每当零件价格发生变化时，软件（不是我的）就会在该表中添加一个新行。它不是改变数据而是每个部分有一个条目，而是向表中添加一个新行。然后该软件查找最新条目。但是“旧”数据在表格中徘徊不前。

I am trying to write a query that gives me a) the current (latest) price and b) the date that price was entered.

我正在尝试编写一个查询，它给出了a）当前（最新）价格和b）输入价格的日期。

I wrote this query:

我写了这个查询：

select part_id, amount, max(change_timestamp)
from price
group by part_id, amount

But this returns every entry for that part, even the old ones.

但这会返回该部分的每个条目，即使是旧条目。

How do I return the latest date and the amount for that date while discarding the older, irrelevant data?

如何在丢弃较旧的无关数据的同时返回该日期的最新日期和金额？

No, it's not my database to change or my software to change.

不，改变我的数据库或改变我的软件不是我的数据库。

2 个解决方案

#1

But this returns every entry for that part, even the old ones.

但这会返回该部分的每个条目，即使是旧条目。

That's because you are grouping by part_id, amount both, which would consider multiple groups for each part_id, since amount is different for each record. To get just part_id and MAX(timestamp), simply removing amount in group by and select will work. If you want amount as well, then

那是因为你按part_id进行分组，两者都是合计的，因为每个part_id的数量都是不同的，因为每个记录的数量都不同。要获得part_id和MAX（时间戳），只需删除group by和select中的金额即可。如果你想要金额，那么

You could use the MAX analytic function and find the record with that change_timestamp

您可以使用MAX分析函数并使用该change_timestamp查找记录

 SELECT part_id
    ,amount
    ,change_timestamp
FROM (
    SELECT p.*
        ,max(change_timestamp) OVER (PARTITION BY part_id) max_change_timestamp
    FROM price p
    )
WHERE change_timestamp = max_change_timestamp;

Or using row_number which you find already in another answer.

或者使用你在另一个答案中找到的row_number。

Or using a correlated query:

或使用相关查询：

SELECT part_id
    ,amount
    ,change_timestamp
FROM price  p1
WHERE change_timestamp = (
        SELECT MAX(change_timestamp)
         FROM price p2
        WHERE p2.part_id = p1.part_id
        );

Or LAST ( but not least) aggregate function:

或者最后（但并非最不重要）聚合函数：

SELECT part_id
    ,MAX(amount) KEEP DENSE_RANK(LAST ORDER BY change_timestamp)
    ,MAX(change_timestamp)
GROUP BY part_id;

#2

Use ROW_NUMBER() to find the latest entry of a part_id.

使用ROW_NUMBER（）查找part_id的最新条目。

SELECT part_id
    ,amount
    ,change_timestamp
FROM (
    SELECT price.*
        ,ROW_NUMBER() OVER (PARTITION BY part_id ORDER BY change_timestamp DESC) as rn
    FROM price  
    )   
WHERE rn = 1

#1