QuickBI如何根据排名大小给数据分组

产品名称

QuickBI

产品模块

数据集

概述

本文通过介绍SQL的举例,可实现按数据大小顺序分组,帮助您实现对排序后分组的结果进行分析、或者分批导出超过1万条数据的需求。

问题描述

有如下数据,如何实现按照订单量大小排名后,对排名进行分组。比如:订单量排名前10的为一组,11-20的为一组,21-30的为一组……

解决方案

QuickBI新交叉表组件可以对数据进行排序,但是无法对排序后的结果分组

  • 如果需要先排名再对排名结果分组,需要通过SQL创建数据集,把排名结果先在SQL里取出来,然后再对结果进行分组。如果您用的数据库支持开窗函数,以MaxCompute数据库为例,可以参考如下SQL创建数据集:
select city,
       order_num,
       case when rank_num<=10 then 'top1-10'
            when rank_num>10 and rank_num<=20 then 'top11-20'
            when rank_num>20 and rank_num<=30 then 'top21-30'
            else '30以上' end as rank_group
from(
     select city,
            count(distinct order_id) as order_num,
            row_number() over(order by count(distinct order_id) desc) as rank_num
     from company_sales_record
     group by city
)a
  • 如果您的数据库不支持开窗函数,以mysql5.7为例,可以参考如下SQL:
(
 select city,count(distinct order_id) order_num,'top1-10' as rank_num

 from   company_sales_record
 group by city
 order by count(distinct order_id) desc
 limit 10
)

union all
(
 select b1.city,
        b1.order_num,
        'top11-20' as rank_num

 from(
      select city,
             count(distinct order_id) order_num

      from   company_sales_record
      group by city
      order by count(distinct order_id) desc
      limit 20
     )b1
 left join
     (

      select city,
             count(distinct order_id) order_num

      from   company_sales_record
      group by city
      order by count(distinct order_id) desc
      limit 10
     )b2 on b1.city=b2.city
 where b2.city is null
)

union all
(
 select c1.city,
        c1.order_num,
        'top21-30' as rank_num

 from(
      select city,
             count(distinct order_id) order_num

      from   company_sales_record
      group by city
      order by count(distinct order_id) desc
      limit 30
      )c1
left join
     (

      select city,
             count(distinct order_id) order_num

      from   company_sales_record
      group by city
      order by count(distinct order_id) desc
      limit 20
      )c2 on c1.city=c2.city
 where c2.city is null
)
注:
1、如果您的排名是有附加条件的,比如需要任意筛选最近6个月、12个月的销量排名,那么可以在每个子查询中加参数字段,将“日期”字段作为参数。
2、上述方案不只适用于对排名后分组结果的分析,同样适用于高级版、标准版用户分批导出数据。由于高级版最多只能导出1万条记录,当您需要导出超过1万条的数据时,就可以通过上述方案,将数据每1万条分为一组,然后就可以分几次不重复导出数据。

更多信息

相关文档

参数SQL如何写:https://help.aliyun.com/knowledge_detail/273069.html