如何获得2列组合的最后记录？ [英] How to get the last records for a combination of 2 columns?

问题描述

我认为可以与CamelCamelCamel，Keepa等服务进行比较。
可以说，我跟踪了两个国家/地区每天的商品价格。所以我的桌子叫趋势，看起来像这样

I have a situation which I think can be compared to services like CamelCamelCamel, Keepa and so on. Lets say I track the price of an article on each day for a couple of countries. So my table, lets call it Trend, would look something like this

Id     Created      ArticleId    Country    Price
-------------------------------------------------
01     19/11/05     452          US         45.90
02     19/11/05     452          CA         52.99
03     19/11/05     452          MX         99.99
04     19/11/06     452          US         20.00
05     19/11/06     452          CA         25.00
06     19/11/06     452          MX         50.00
...                
97     19/11/05     738          US         12.99
98     19/11/05     738          CA         17.50
99     19/11/05     738          MX         45.50

所以是第二天，我想更新 Trend 表。如果某个国家/地区的价格仍然相同，则跳过商品/国家/地区组合。如果有新价格，我将添加一条新记录。

So it's the next day and I want to update the Trend table. If the price in a country is still the same, I skip the article/country combination. If there is a new price I'll add a new record.

现在，我想查询表格以获取每个 ArticleId / 国家组合。但是只有它的最后一条记录（按时间戳排序）。因此，以上述示例为例，我希望获得记录 04 ， 05 和 06 表示 ArticleId 452 。不是 01 ， 02 和 03

Now I want to query the table to get each ArticleId / Country combination. But only the last record of it (orderd by timestamp). So taken the example above I'd expect to get the records 04, 05 and 06 for ArticleId 452. Not 01, 02 and 03

所以我从这个基本查询开始。但是，如何更改它以得到预期的结果？

So I start out with this basic query. But how do I get to change it to get my expected results?

SELECT
    *
FROM
    Trend
ORDER BY 
    Created DESC

推荐答案

一种方法使用相关子查询进行过滤：

One method uses a correlated subquery for filtering:

select t.*
from trend t
where t.created = (
    select max(t1.created) 
    from trend t1
    where t1.articleId = t.articleId and t1.country = t.country
)

为了提高性能，您需要在上建立索引（articleId，国家/地区，已创建）。

For performance, you want an index on (articleId, country, created).

您可能还需要考虑采用反左联接方法：

You might also want to consider the anti-left join approach:

select t.*
from trend t
left join trend t1 
    on  t1.articleId = t.articleId 
    and t1.country = t.country
    and t1.created > t.created
where t1.articleId is null

最后，另一个典型的解决方案是加入带有汇总查询的表：

Finally, another typical solution is to join the table with an aggregate query:

select t.*
from trend t
inner join (
    select articleId, country, max(created) created
    from trend
    group by articleId, country
) t1 
    on  t1.articleId = t.articleId 
    and t1.country = t.country
    and t1.created = t.created

解决方案的效果取决于数据的大小和分布。

Which solution performs better depends on the size and distribution of your data.

这篇关于如何获得2列组合的最后记录？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！