多维分析——grouping set

本文介绍了一种在 SQL 中处理多维度数据汇总的方法——使用 Grouping Sets。通过实例演示了如何灵活运用此功能实现不同维度的数据聚合,并利用 Grouping__ID 来区分不同的汇总级别。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

场景

在编写报表的 sql 脚本的时候,可能会遇到多维度组合的情况,例如下面的情况。

- 省 城市 区 人口数量
	- 省 城市 区 街道 人口数量

常规做法

上面据的例子中,既要汇总出省、城市、区的人口,也要汇总出省、城市、区、街道的人口。常规的做法是编写两种维度组合的 sql ,然后再使用 union all 进行全集。
类似下面的写法:

 select  province
       , city
       , region
       , '-' as street
       , sum(population_quantity)
    from population
group by province
       , city
       , region
union all 
 select  province
       , city
       , region
       , street
       , sum(population_quantity)
    from population
group by province
       , city
       , region
       , street

grouping set 的解决方法

 select  province
       , city
       , region
       , street
       , sum(population_quantity)
    from population
group by province
       , city
       , region
       , street
grouping sets((province,city,region),(province,city,region,street))       

呵呵,我坦白,我写的不一定对,你要在自己的机子上试试,否则错,就浪费时间了。

那么问题了来,我怎么知道哪些行是统计到省、城市、区,哪些行是统计到省、城市、区、街道的。这时候就要请我们的 Grouping__ID 闪亮登场了。那么我们的 sql 也要改成如下的样子:

 select  province
       , city
       , region
       , street
       , Grouping__ID as group_id
       , sum(population_quantity)
    from population
group by province
       , city
       , region
       , street
grouping sets((province,city,region),(province,city,region,street))       

Grouping__ID 是 hive 帮我们生成的,标识维度组合的十进制数字。那么它的生成规则是怎样的呢?

以上面的例子说明 Grouping__ID 的生成规则:
group by 后面有四个字段,在 grouping sets 的组合中存在的字段使用 1 表示,否则使用 0 表示。
grouping sets 中有两组字段组合,分别是:
province,city,region:1 1 1 0 换算成十进制为 14
province,city,region,street:1 1 1 1 换算成十进制为 15
Grouping__ID 就是最后生成的十进制数字。

发现了这个规律后,我们应该更进一步,利用算法,让 sql 帮我计算出十进制代表了那些维度组合。

我们知道二进制的并运算,例如,1 0 0 0 & x ,其中 x 是任意一个四位二进制数字,经过并运算,我们得到一个 y ,如何 y = 8 ,那说明 x 的第四数是 1 ,如果 y 是 0 ,那说明 x 的第四位数是 0。根据这一原理,我们就能判断出 Grouping__ID 代表的维度组合中有那些字段了。

例如,我们得到一个结果表,population_sum。

select concat(   if(group_id & (1<<0) = (1<<0) ) , 'province', '')
               ,'-', if(group_id & (1<<1) = (1<<1) ) , 'city', '')
               ,'-', if(group_id & (1<<2) = (1<<2) ) , 'region', '')
               ,'-', if(group_id & (1<<3) = (1<<3) ) , 'street', '')               
             )  as dim_group -- 维度组合 
  from population_sum

上面的 sql 语句是 mysql 上运行的,如果你所在的平台(例如,presto)没有&和为运算,那改怎么办呢?这里介绍一下,我想出来的一个办法,在每一个符合 sql 标准的数据平台中,应该都会支持指数运算和取余运算,例如,8 是的二进制是 1000, 8/2=4,二
进制为 100,8/8=1,二进制是1。通过上面的例子,可以看出,除 2 相当于向右进行位运算,如果一个二进制数对应的十进制数除以 2 的 n - 1 次方,则可以将这个二进制的数的第 n 位放到对应 10 进制数的各位,然后进行取余运算,如果结果是 0 ,则第 N 位是 0 ,否则为 1 。

select concat(if(mod(from_base('1001',2)/cast(pow(2,0) as bigint),2) = 1 ,'biz_date' ,'') 
      ,',',if(mod(from_base('1000',2)/cast(pow(2,2) as bigint),2) =1 ,'month_code' ,'')) 

也可以使用 from_base(‘1100’,2) -> 12 自己算一下 groupping 的值等于几

大概就是这样吧。我也不知道我写的到底对不对,各位看官,看懂了,还要自己测试一下。

### 配置设置或部署指南 在准备将 Apache OFBiz 设置用于生产环境时,特别是针对电子商务部署的情况,需要关注一些基本的配置选项[^1]。这些配置不仅涉及软件本身的参数调整,还可能涉及到外部服务集成以及性能优化。 #### 基本配置项概述 以下是几个重要的配置领域: 1. **数据库连接配置**: 数据库是任何企业级应用的核心组件之一,在 OFBiz 中可以通过 `ofbiz-component.xml` 文件中的 `<delegator>` 和 `<datasource>` 节点来定义数据源的相关属性。例如: ```xml <delegator name="default" entity-model-reader="main" entity-group-reader="main" entity-eca-reader="main"> <group-map group-name="org.ofbiz" datasource-name="localmysql"/> </delegator> <jdbc-uri_datasource name="localmysql" jdbc-uri="jdbc:mysql://localhost/ofbiz?useUnicode=true&characterEncoding=UTF-8" username="root" password="password" pool-minsize="5" pool-maxsize="20"/> ``` 2. **日志管理**: 日志记录对于监控和调试至关重要。如果遇到类似于 `"Not eligible for data streams"` 的错误消息,则可能是由于某些索引配置不兼容所致[^2]。因此建议检查 Elasticsearch 或其他日志存储系统的具体需求,并确保其版本与当前使用的工具相匹配。 3. **分布式文件系统 (DFS)**: 如果计划运行 Hadoop DFS 来处理大规模数据分析任务,则可以在主节点上执行如下命令启动集群服务[^3]: ```bash ./bin/start-dfs.sh ``` 此外还需要确认 NameNode 和 DataNodes 是否已正确初始化并保持通信状态良好。 4. **安全性增强措施**: 生产环境中应启用 HTTPS 协议保护敏感信息传输;同时通过角色权限控制机制限制不同用户的访问范围。 #### 性能调优提示 为了提高系统响应速度及稳定性,可以考虑以下几个方面: - 缓存策略定制化; - 减少不必要的网络请求次数; - 定期清理过期会话数据以释放内存资源。 ---
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值