mysql - 将类似 sql 查询的结果按百分位数分组 : In Redshift/postgresql

Question

我有一个 group_name 及其计数的集合。假设这来自以下声明 -:

--sample input set --
select group_name, count(*) as group_count 
     from mytable group by group_name 
     order by group_count desc ;

    group_name  group_count 
    A 205
    B 200
    C 67
    D 55
    E 50 
    F 12
    and so on..

我想要的是将 groups_counts 的结果及其组名称组织为 3 组，例如 Head、core 和 tail - 其中每个组占总 group_count 的 33%。因此 10、5 等将替换为各自的百分位数。所有这些我都需要在 redshift(postgres 8.0.2 ) 中完成

作为第一级，它应该是这样的，

-- 这不是有效的语法--

select case when group_count  >10 then group_name end ) as Head_group,
case when group_count  >5  and group_count <10 then query end ) as core_group, 
case when group_count  <5   then group_name end ) as tail_group, 
 from 
 ( select group_name, count(*) as group_count 
 from mytable group by group_name 
 order by group_count desc ) ;

在所需的语法中，选择将基于 sum(group_count) - 这将是所有组计数的总和。我如何在 postgressql 中得到相同的结果，更具体地说是在 Redshift 中。请注意，Redshift 不支持创建函数。 Redshift 中也可以使用prepare & set，但不能使用prepare 语句。

   --sample output set---
    Head_group core_group tail_group 
    A           D          F
    B           E
    C
    --Alternative sample output set---
    Head_group 
    A
    B
    C
    core_group 
    D
    E
    tail_group 
    F

请注意，每个组可以返回不同数量的行。在 mysql 中，我可以做类似的事情:

set @total_group_count =(select count(*) from mytable ) ;
set @percentile_group_count = ( select @total_group_count*(30/100))  ; 

引用我的相关问题: Storing the results of a prepared statement as a table in mysql?

Answer 1

ntile 窗口函数是您最可能想要在此处使用的函数。

它可以与您的查询一起使用，如下所示:

select group_name, count(*) as group_count,
       ntile(3) over(order by group_count desc) AS group_ntile
     from mytable group by group_name 
     order by group_count desc;

这应该将 group_count 列的(降序)值分为三个相等的组。然后，您可以在 CASE 语句中使用 group_ntile 值，根据其所在的组执行您想要的操作。

基于Redshift doc，看来 ntile 确实可用。

编辑以回应OP的评论:

ntile 的参数是排名组的数量。

即ntile 会将结果(使用指定的窗口函数参数)存储到函数参数中指定的组数中。因此，如果您想要真正的百分位数，那么您应该使用ntile(100)。