带有领带的Hive SQL排名

Question

如何在中重写下面的查询我已经在Oracle中尝试了下面的内容，但是需要用Hive编写同样的查询。

select * from sales order by unit_price fetch with ties

Answer 1

甲骨文的命令。使用轮胎获取NEXT N行，将顶部N行(使用ORDER BY )限制为NEXT指定的行数(第一行)。使用轮胎意味着，如果某些行的值顺序相同，那么除了指定的行数之外，还会返回它们。

Hive没有获取功能。这是有限度的，它不支持轮胎。

您可以使用analytics dense_rank()函数加上WHERE过滤器实现类似的功能。例如，我们需要以最低的价格获得5个销售，如果有相同价格的销售，也可以返回它们。dense_rank将以相同的价格给行分配相同的等级。

select * 
from
(
    select s.*, 
           dense_rank() over(order by unit_price) rnk --lowest unit_price first
                                                      --to get highest price, use order by unit_price DESC
     from sales s 
) 
where rnk<=5  --If there are rows with the same rank, they all will be returned
order by rnk

Answer 2

使用窗口函数进行复制是非常棘手的。fetch with ties返回到第n行，然后返回值相同的所有行。因此，一种方法是：

select s.*
from sales *
where s.unit_price <= (select s2.unit_price
                       from sales s2
                       order by unit_price 
                       limit 4, 1
                      );

这并不准确，因为如果少于5行，它就不能工作。另一种使用窗口函数的方法可以更容易地修复：

select s.*
from (select s.*,
             max(case when seqnum = 5 then unit_price end) over () as unit_price_5
      from (select s.*,
                   row_number() over (order by unit_price) as seqnum
            from s
           ) s
     ) s
where unit_price <= unit_price_5 or
      unit_price_5 is null;

请注意，内置的窗口函数没有一个处理这种情况。例如，如果价格是：

1
1
1
1
1
1
2

然后row_number()只返回前5个1s，dense_rank()和rank()将返回所有行。