apache impala group by和order by在一起

6pp0gazn 于 2021-06-26 发布在 Impala

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

我是新来的Apache Impala 。正在构建查询。
这是示例数据。

------------------------------------------------------------------
| id_ingredient | ingredient_name | price |  timestamp | deleted |
------------------------------------------------------------------
|        1      | potatoes        | 10.00 | 1445899655 |    0    |
|        2      | tomatoes        | 20.00 | 1445836421 |    0    |
|        1      | sweet potatoes  | 10.00 | 1445899132 |    0    |
|        1      | sweet potatoes  | 15.00 | 1445959231 |    0    |
|        2      | tomatoes        | 30.00 | 1445894337 |    1    |
|        3      | chicken         | 50.00 | 1445899655 |    0    |

我想建立一个表格，只显示尚未删除的每个成分的最新状态。
非常感谢

sql impala

来源：https://stackoverflow.com/questions/48607314/apache-impala-group-by-and-order-by-together

2条答案

按热度按时间

nukf8bse1#

你可以用一个小组

select ingredient_name , max(timestamp) 
from my_table
where deleted = 0
group by ingredient_name

of for retrieve the entire row=可以

select * from my_table 
inner join  (
select ingredient_name , max(timestamp) 
from my_table a
where deleted = 0
group by ingredient_name ) t on t.ingredient_name  =a.ingredient_name  
       and t.timestamp = a.timestamp

赞(0）回复(0）举报 2021-06-26

hkmswyz62#

如果假设是 deleted 将仅处于最新状态，然后您可以执行以下操作：

select t.*
from (select t.*,
             row_number() over (partition by ingredient_name order by timestamp desc) as seqnum
      from t
     ) t
where seqnum = 1 and deleted = 0;

赞(0）回复(0）举报 2021-06-26

我来回答

apache impala group by和order by在一起

2条答案

相关问题

热门标签

最新问答