获取 Hive 中每个 NTILE 存储桶的最小值

发布于 2025-01-12 12:39:13 字数 620 浏览 0 评论 0原文

我正在尝试使用 NTILE 窗口函数将数据划分为百分位数（100 个相等的桶），对于按分数列排序的每个merchant_id。查询的输出将包含源表中每条记录的merchant_id、分数和百分位数。（示例代码如下）

CREATE TABLE merchant_score_ntiles
AS
SELECT merchant_id, score, NTILE(100) OVER (PARTITION BY merchant_id ORDER BY score DESC) as percentile
FROM merch_table

这将返回示例输出，如下所示：

merchant_id,分数,百分位数
1001,900,1
1001,800,1
1001,760,1
1002,900,2
1002,800,2
1002,750,2

有没有办法我们可以根据百分位数列仅返回每个merchant_id 的最低分数，如下所示？

merchant_id,分数,百分位数
1001,760,1
1002,750,2

原文

I am trying to partition the data into percentiles (100 equal buckets) using NTILE window function for each merchant_id ordered by score column. The output of the query will contain merchant_id, score, and percentile for every record in the source table. (Sample code below)

CREATE TABLE merchant_score_ntiles
AS
SELECT merchant_id, score, NTILE(100) OVER (PARTITION BY merchant_id ORDER BY score DESC) as percentile
FROM merch_table

This will return sample output as follows:

merchant_id,score,percentile
1001,900,1
1001,800,1
1001,760,1
1002,900,2
1002,800,2
1002,750,2

Is there a way we can return only the minimum score for each merchant_id based on percentile column such as below?

merchant_id,score,percentile
1001,760,1
1002,750,2

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

一梦等七年七年为一梦 2025-01-19 12:39:13

在使用NTILE窗口函数之前，您可以尝试在子查询中使用ROW_NUMBER窗口函数

SELECT merchant_id, 
       score, 
       NTILE(100) OVER (PARTITION BY merchant_id ORDER BY score DESC) as percentile
FROM (
    SELECT *,ROW_NUMBER() OVER(PARTITION BY merchant_id ORDER BY score) rn
    FROM merch_table
) t1
WHERE rn = 1

You can try to use ROW_NUMBER window function in subquery before using NTILE window function

SELECT merchant_id, 
       score, 
       NTILE(100) OVER (PARTITION BY merchant_id ORDER BY score DESC) as percentile
FROM (
    SELECT *,ROW_NUMBER() OVER(PARTITION BY merchant_id ORDER BY score) rn
    FROM merch_table
) t1
WHERE rn = 1

回复收藏 0 原文

~没有更多了~