如何充分发挥SQL能力？
阿里妹导读
如何充分发挥 sql 能力，是本篇文章的主题。本文尝试独辟蹊径，强调通过灵活的、发散性的数据处理思维，就可以用最基础的语法，解决复杂的数据场景。
一、前言
1.1 初衷
如何高效地使用 maxcompute（odps）sql ，将基础 sql 语法运用到极致。
在大数据如此流行的今天，不只是专业的数据人员，需要经常地跟 sql 打交道，即使是产品、运营等非技术同学，也会或多或少地使用到 sql ，如何高效地发挥 sql 的能力，继而发挥数据的能力，变得尤为重要。 maxcompute（odps）sql 发展到今天已经颇为成熟，作为一种 sql 方言，其 sql 语法支持完备，具有非常丰富的内置函数，支持开窗函数、用户自定义函数、用户自定义类型等诸多高级特性，可以高效地应用在各种数据处理场景。
如何充分发挥 sql 能力，是本篇文章的主题。本文尝试独辟蹊径，强调通过灵活的、发散性的数据处理思维，就可以用最基础的语法，解决复杂的数据场景。
1.2 适合人群
不论是初学者还是资深人员，本篇文章或许都能有所帮助，不过更适合中级、高级读者阅读。
本篇文章重点介绍数据处理思维，并没有涉及到过多高阶的语法，同时为了避免主题发散，文中涉及的函数、语法特性等，不会花费篇幅进行专门的介绍，读者可以按自身情况自行了解。
1.3 内容结构
本篇文章将围绕数列生成、区间变换、排列组合、连续判别等主题进行介绍，并附以案例进行实际运用讲解。每个主题之间有轻微的前后依赖关系，依次阅读更佳。
1.4 提示信息
本篇文章涉及的 sql 语句只使用到了 maxcompute（odps）sql 基础语法特性，理论上所有 sql 均可以在当前最新版本中运行，同时特意注明，运行环境、兼容性等问题不在本篇文章关注范围内。
二、数列
数列是最常见的数据形式之一，实际数据开发场景中遇到的基本都是有限数列。本节将从最简单的递增数列开始，找出一般方法并推广到更泛化的场景。
2.1 常见数列
2.1.1 一个简单的递增数列
首先引出一个简单的递增整数数列场景：
从数值 0 开始；
之后的每个数值递增 1 ；
至数值 3 结束；
如何生成满足以上三个条件的数列？即 [0,1,2,3] 。
实际上，生成该数列的方式有多种，此处介绍其中一种简单且通用的方案。
-- sql - 1select t.pos as a_nfrom ( select posexplode(split(space(3), space(1), false))) t;
通过上述 sql 片段可得知，生成一个递增序列只需要三个步骤：
1）生成一个长度合适的数组，数组中的元素不需要具有实际含义； 2）通过 udtf 函数 posexplode 对数组中的每个元素生成索引下标；
3）取出每个元素的索引下标。以上三个步骤可以推广至更一般的数列场景：等差数列、等比数列。下文将以此为基础，直接给出最终实现模板。
2.1.2 等差数列
若设首项，公差为 ，则等差数列的通项公式为 。
sql 实现：
-- sql - 2select a + t.pos * d as a_nfrom ( select posexplode(split(space(n - 1), space(1), false))) t;
2.1.3 等比数列
若设首项 ，公比为 ，则等比数列的通项公式为。
sql 实现：
-- sql - 3select a * pow(q, t.pos) as a_nfrom ( select posexplode(split(space(n - 1), space(1), false))) t;提示：亦可直接使用 maxcompute（odps）系统函数 sequence 快速生成数列。 -- sql - 4select sequence(1, 3, 1);-- result[1, 2, 3]
2.2 应用场景举例
2.2.1 还原任意维度组合下的维度列簇名称
在多维分析场景下，可能会用到高阶聚合函数，如 cube 、 rollup 、 grouping sets 等，可以针对不同维度组合下的数据进行聚合统计。
场景描述
现有用户访问日志表 visit_log ，每一行数据表示一条用户访问日志。
-- sql - 5with visit_log as ( select stack ( 6, '2024-01-01', '101', '湖北', '武汉', 'android', '2024-01-01', '102', '湖南', '长沙', 'ios', '2024-01-01', '103', '四川', '成都', 'windows', '2024-01-02', '101', '湖北', '孝感', 'mac', '2024-01-02', '102', '湖南', '邵阳', 'android', '2024-01-03', '101', '湖北', '武汉', 'ios' ) -- 字段：日期，用户，省份，城市，设备类型 as (dt, user_id, province, city, device_type))select * from visit_log;现针对省份 province , 城市 city, 设备类型 device_type 三个维度列，通过 grouping sets 聚合统计得到了不同维度组合下的用户访问量。问： 1）如何知道一条统计结果是根据哪些维度列聚合出来的？
2）想要输出 聚合的维度列的名称，用于下游的报表展示等场景，又该如何处理？
解决思路
可以借助 maxcompute（odps）提供的 grouping__id 来解决，核心方法是对 grouping__id 进行逆向实现。
详细步骤如下：
一、准备好所有的 grouping__id 。
生成一个包含个数值的递增数列，将每个数值转为 2 进制字符串，并展开该 2 进制字符串的每个比特位。
grouping__id bits
0 { ..., 0, 0, 0 }
1 { ..., 0, 0, 1 }
2 { ..., 0, 1, 0 }
3 { ..., 0, 1, 1 }
... ...
2n2n ...
其中 为所有维度列的数量， 即为所有维度组合的数量，每个数值表示一种 grouping__id。
二、准备好所有维度名称。
生成一个字符串序列，依次保存个维度列的名称，即
{ dim_name_1, dim_name_2, ..., dim_name_n }三、将 grouping__id 映射到维度列名称。
对于 grouping__id 递增数列中的每个数值，将该数值的 2 进制每个比特位与维度名称序列的下标进行映射，输出所有对应比特位 0 的维度名称。例如：
grouping__id：3 => { 0, 1, 1 }维度名称序列：{ 省份, 城市, 设备类型 }映射：{ 0:省份, 1:城市, 1:设备类型 }grouping__id 为 3 的数据行聚合维度即为：省份
sql 实现
-- sql - 6with group_dimension as ( select -- 每种分组对应的维度字段 gb.group_id, concat_ws(,, collect_list(case when gb.placeholder_bit = 0 then dim_col.val else null end)) as dimension_name from ( select groups.pos as group_id, pe.* from ( select posexplode(split(space(cast(pow(2, 3) as int) - 1), space(1), false)) ) groups -- 所有分组 lateral view posexplode(regexp_extract_all(lpad(conv(groups.pos,10,2), 3, 0), '(0|1)')) pe as placeholder_idx, placeholder_bit -- 每个分组的bit信息 ) gb left join ( -- 所有维度字段 select posexplode(split(省份,城市,设备类型, ',')) ) dim_col on gb.placeholder_idx = dim_col.pos group by gb.group_id)select group_dimension.dimension_name, province, city, device_type, visit_countfrom ( select grouping_id(province, city, device_type) as group_id, province, city, device_type, count(1) as visit_count from visit_log b group by province, city, device_type grouping sets( (province), (province, city), (province, city, device_type) )) tjoin group_dimension on t.group_id = group_dimension.group_idorder by group_dimension.dimension_name;
dimension_name province city device_type visit_count
省份 湖北 null null 3
省份 湖南 null null 2
省份 四川 null null 1
省份,城市 湖北 武汉 null 2
省份,城市 湖南 长沙 null 1
省份,城市 湖南 邵阳 null 1
省份,城市 湖北 孝感 null 1
省份,城市 四川 成都 null 1
省份,城市,设备类型 湖北 孝感 mac 1
省份,城市,设备类型 湖南 长沙 ios 1
省份,城市,设备类型 湖南 邵阳 android 1
省份,城市,设备类型 四川 成都 windows 1
省份,城市,设备类型 湖北 武汉 android 1
省份,城市,设备类型 湖北 武汉 ios 1
三、区间
区间相较数列具有不同的数据特征，不过在实际应用中，数列与区间的处理具有较多相通性。本节将介绍一些常见的区间场景，并抽象出通用的解决方案。
3.1 常见区间操作
3.1.1 区间分割
已知一个数值区间，如何将该区间均分成 段子区间？
该问题可以简化为数列问题，数列公式为 ，其中，具体步骤如下：
1）生成一个长度为 的数组； 2）通过 udtf 函数 posexplode 对数组中的每个元素生成索引下标；
3）取出每个元素的索引下标，并进行数列公式计算，得出每个子区间的起始值与结束值。
sql 实现：
-- sql - 7select a + t.pos * d as sub_interval_start, -- 子区间起始值 a + (t.pos + 1) * d as sub_interval_end -- 子区间结束值from ( select posexplode(split(space(n - 1), space(1), false))) t;
3.1.2 区间交叉
已知两个日期区间存在交叉 ['2024-01-01', '2024-01-03'] 、 ['2024-01-02', '2024-01-04']。问：
1）如何合并两个日期区间，并返回合并后的新区间？
2）如何知道哪些日期是交叉日期，并返回该日期交叉次数？
解决上述问题的方法有多种，此处介绍其中一种简单且通用的方案。核心思路是结合数列生成、区间分割方法，先将日期区间分解为最小处理单元，即多个日期组成的数列，然后再基于日期粒度做统计。具体步骤如下：
1）获取每个日期区间包含的天数； 2）按日期区间包含的天数，将日期区间拆分为相应数量的递增日期序列；
3）通过日期序列统计合并后的区间，交叉次数。
sql 实现：
-- sql - 8with dummy_table as ( select stack( 2, '2024-01-01', '2024-01-03', '2024-01-02', '2024-01-04' ) as (date_start, date_end))select min(date_item) as date_start_merged, max(date_item) as date_end_merged, collect_set( -- 交叉日期计数 case when date_item_cnt > 1 then concat(date_item, ':', date_item_cnt) else null end ) as overlap_datefrom ( select -- 拆解后的单个日期 date_add(date_start, pos) as date_item, -- 拆解后的单个日期出现的次数 count(1) over (partition by date_add(date_start, pos)) as date_item_cnt from dummy_table lateral view posexplode(split(space(datediff(date_end, date_start)), space(1), false)) t as pos, val) t;
date_start_merged date_end_merged overlap_date
2024-01-01 2024-01-04 [2024-01-02:2,2024-01-03:2]
增加点儿难度！
如果有多个日期区间，且区间之间交叉状态未知，上述问题又该如何求解。即：
1）如何合并多个日期区间，并返回合并后的多个新区间？
2）如何知道哪些日期是交叉日期，并返回该日期交叉次数？
sql 实现：
-- sql - 9with dummy_table as ( select stack( 5, '2024-01-01', '2024-01-03', '2024-01-02', '2024-01-04', '2024-01-06', '2024-01-08', '2024-01-08', '2024-01-08', '2024-01-07', '2024-01-10' ) as (date_start, date_end))select min(date_item) as date_start_merged, max(date_item) as date_end_merged, collect_set( -- 交叉日期计数 case when date_item_cnt > 1 then concat(date_item, ':', date_item_cnt) else null end ) as overlap_datefrom ( select -- 拆解后的单个日期 date_add(date_start, pos) as date_item, -- 拆解后的单个日期出现的次数 count(1) over (partition by date_add(date_start, pos)) as date_item_cnt, -- 对于拆解后的单个日期，重组为新区间的标记 date_add(date_add(date_start, pos), 1 - dense_rank() over (order by date_add(date_start, pos))) as cont from dummy_table lateral view posexplode(split(space(datediff(date_end, date_start)), space(1), false)) t as pos, val) tgroup by cont;
date_start_merged date_end_merged overlap_date
2024-01-01 2024-01-04 [2024-01-02:2,2024-01-03:2]
2024-01-06 2024-01-10 [2024-01-07:2,2024-01-08:3]
3.2 应用场景举例
3.2.1 按任意时段统计数据
场景描述
现有用户还款计划表 user_repayment ，该表内的一条数据，表示用户在指定日期区间内 [date_start, date_end] ，每天还款 repayment 元。
-- sql - 10with user_repayment as ( select stack( 3, '101', '2024-01-01', '2024-01-15', 10, '102', '2024-01-05', '2024-01-20', 20, '103', '2024-01-10', '2024-01-25', 30 ) -- 字段：用户，开始日期，结束日期，每日还款金额 as (user_id, date_start, date_end, repayment))select * from user_repayment;如何统计任意时段内（如：2024-01-15至2024-01-16）每天所有用户的应还款总额？
解决思路
核心思路是将日期区间转换为日期序列，再按日期序列进行汇总统计。
sql 实现
-- sql - 11select date_item as day, sum(repayment) as total_repaymentfrom ( select date_add(date_start, pos) as date_item, repayment from user_repayment lateral view posexplode(split(space(datediff(date_end, date_start)), space(1), false)) t as pos, val) twhere date_item >= '2024-01-15' and date_item = 2group by user_id;
user_id
101
102
基于连续活跃区间实现
可以视作 基于相邻日期差实现（排序版） 的衍生版本，该实现能获取到更多信息，如连续活跃区间。
-- sql - 21select user_idfrom ( select user_id, cont, -- 连续活跃区间 min(dt) as cont_date_start, max(dt) as cont_date_end from ( select *, date_add(dt, 1 - dense_rank() over (partition by user_id order by dt)) as cont from visit_log ) t1 group by user_id, cont) t2where datediff(cont_date_end, cont_date_start) + 1 >= 2group by user_id;
user_id
101
102
5.2 动态连续活跃统计
场景描述
现有用户访问日志表 visit_log ，每一行数据表示一条用户访问日志。
-- sql - 22with visit_log as ( select stack ( 6, '2024-01-01', '101', '湖北', '武汉', 'android', '2024-01-01', '102', '湖南', '长沙', 'ios', '2024-01-01', '103', '四川', '成都', 'windows', '2024-01-02', '101', '湖北', '孝感', 'mac', '2024-01-02', '102', '湖南', '邵阳', 'android', '2024-01-03', '101', '湖北', '武汉', 'ios' ) -- 字段：日期，用户，省份，城市，设备类型 as (dt, user_id, province, city, device_type))select * from visit_log;如何获取最长的 2 个连续活跃用户，输出用户、最长连续活跃天数、最长连续活跃日期区间？
上述问题在分析连续性时，获取连续性的结果不是且无法与固定的阈值作比较，而是各自以最长连续活跃作为动态阈值，此处归类为 动态连续活跃场景统计。
sql 实现
基于 普通连续活跃场景统计 的思路进行扩展即可，此处直接给出最终 sql ：
-- sql - 23select user_id, -- 最长连续活跃天数 datediff(max(dt), min(dt)) + 1 as cont_days, -- 最长连续活跃日期区间 min(dt) as cont_date_start, max(dt) as cont_date_endfrom ( select *, date_add(dt, 1 - dense_rank() over (partition by user_id order by dt)) as cont from visit_log) t1group by user_id, contorder by cont_days desclimit 2;
user_id cont_days cont_date_start cont_date_end
101 3 2024-01-01 2024-01-03
102 2 2024-01-01 2024-01-02
六、扩展
引申出更复杂的场景，是本篇文章前面章节内容的结合与变种。
6.1 区间连续（最长子区间切分）
场景描述
现有用户扫描或连接 wifi 记录表 user_wifi_log ，每一行数据表示某时刻用户扫描或连接 wifi 的日志。
-- sql - 24with user_wifi_log as ( select stack ( 9, '2024-01-01 1000', '101', 'cmcc-starbucks', 'scan', -- 扫描 '2024-01-01 1000', '101', 'cmcc-starbucks', 'scan', '2024-01-01 1000', '101', 'cmcc-starbucks', 'scan', '2024-01-01 1000', '101', 'cmcc-starbucks', 'conn', -- 连接 '2024-01-01 1000', '101', 'cmcc-starbucks', 'conn', '2024-01-01 1000', '101', 'cmcc-starbucks', 'conn', '2024-01-01 1100', '101', 'cmcc-starbucks', 'conn', '2024-01-01 1100', '101', 'cmcc-starbucks', 'conn', '2024-01-01 1100', '101', 'cmcc-starbucks', 'conn' ) -- 字段：时间，用户，wifi，状态（扫描、连接） as (time, user_id, wifi, status))select * from user_wifi_log;现需要进行用户行为分析，如何划分用户不同 wifi 行为区间？满足： 1）行为类型分为两种：连接（scan）、扫描（conn）； 2）行为区间的定义为：相同行为类型，且相邻两次行为的时间差不超过 30 分钟；
3）不同行为区间在满足定义的情况下应取到最长；
user_id wifi status time_start time_end 备注
101 cmcc-starbucks scan 2024-01-01 1000 2024-01-01 1000 用户扫描了 wifi
101 cmcc-starbucks conn 2024-01-01 1000 2024-01-01 1000 用户连接了 wifi
101 cmcc-starbucks conn 2024-01-01 1100 2024-01-01 1100 距离上次连接已经超过 30 分钟，认为是一次新的连接行为
上述问题稍显复杂，可视作 动态连续活跃统计 中介绍的 最长连续活跃 的变种。可以描述为 结合连续性阈值与行为序列中的上下文信息，进行最长子区间的划分 的问题。
sql 实现
核心逻辑：以用户、wifi 分组，结合连续性阈值与行为序列上下文信息，划分行为区间。
详细步骤：
1）以用户、wifi 分组，在分组窗口内对数据按时间正序排序； 2）依次遍历分组窗口内相邻两条记录，若两条记录之间的时间差超过 30 分钟，或者两条记录的行为状态（扫描态、连接态）发生变更，则以该临界点划分行为区间。直到遍历所有记录；
3）最终输出结果：用户、wifi、行为状态（扫描态、连接态）、行为开始时间、行为结束时间；
-- sql - 25select user_id, wifi, max(status) as status, min(time) as start_time, max(time) as end_timefrom ( select *, max(if(lag_status is null or lag_time is null or status lag_status or datediff(time, lag_time, 'ss') > 60 * 30, rn, null)) over (partition by user_id, wifi order by time) as group_idx from ( select *, row_number() over (partition by user_id, wifi order by time) as rn, lag(time, 1) over (partition by user_id, wifi order by time) as lag_time, lag(status, 1) over (partition by user_id, wifi order by time) as lag_status from user_wifi_log ) t1) t2group by user_id, wifi, group_idx;
user_id wifi status start_time end_time
101 cmcc-starbucks scan 2024-01-01 1000 2024-01-01 1000
101 cmcc-starbucks conn 2024-01-01 1000 2024-01-01 1000
101 cmcc-starbucks conn 2024-01-01 1100 2024-01-01 1100
该案例中的连续性判别条件可以推广到更多场景，例如基于日期差值、时间差值、枚举类型、距离差值等作为连续性判别条件的数据场景。
结语
通过灵活的、散发性的数据处理思维，就可以用基础的语法，解决复杂的数据场景 是本篇文章贯穿全文的思想。文中针对数列生成、区间变换、排列组合、连续判别等常见的场景，给出了相对通用的解决方案，并结合实例进行了实际运用的讲解。
本篇文章尝试独辟蹊径，强调灵活的数据处理思维，希望能让读者觉得眼前一亮，更希望真的能给读者产生帮助。同时毕竟个人能力有限，思路不一定是最优的，甚至可能出现错误，欢迎提出意见或建议。
芯片反向设计流程
基于65nm工艺的五分频器设计方案
人工智能正在帮助克服较旧的拍摄技术带来的困难，
如何理解二分查找算法
LoRaWAN网关与常见网络服务器的协议
如何充分发挥SQL能力？
三星二季度出货量大幅放缓，华为年内赶超三星或难以实现
四维图新旗下杰发科技获ISO 26262汽车功能安全最高等级ASIL D流程认证证书
不可回收标志，可回收标志图
英特尔已打算将整个智能手机调制解调器业务打包出售
意法半导体推出全新开发框架，更快速简单地实现物联网传感器
华为方舟编译器获多硬件平台支持，未来RISC-V芯片或也支持
LG成功生产关键OLED材料 以减少对进口依赖
腾讯智能下棋机器人机械手装置专利
苹果支付1.13亿美元和解iPhone“降速门”指控
可调稳压器LM317的全面介绍
C语言入门教程-指针常见错误
法国拆除华为无线设备
智能烟感产品市场需求崛起；电动汽车或迎来随充时代
AMS高精度温度传感器在智能洗衣机中的应用
如何充分发挥 sql 能力，是本篇文章的主题。本文尝试独辟蹊径，强调通过灵活的、发散性的数据处理思维，就可以用最基础的语法，解决复杂的数据场景。
一、前言
1.1 初衷
如何高效地使用 maxcompute（odps）sql ，将基础 sql 语法运用到极致。
在大数据如此流行的今天，不只是专业的数据人员，需要经常地跟 sql 打交道，即使是产品、运营等非技术同学，也会或多或少地使用到 sql ，如何高效地发挥 sql 的能力，继而发挥数据的能力，变得尤为重要。 maxcompute（odps）sql 发展到今天已经颇为成熟，作为一种 sql 方言，其 sql 语法支持完备，具有非常丰富的内置函数，支持开窗函数、用户自定义函数、用户自定义类型等诸多高级特性，可以高效地应用在各种数据处理场景。
如何充分发挥 sql 能力，是本篇文章的主题。本文尝试独辟蹊径，强调通过灵活的、发散性的数据处理思维，就可以用最基础的语法，解决复杂的数据场景。
1.2 适合人群
不论是初学者还是资深人员，本篇文章或许都能有所帮助，不过更适合中级、高级读者阅读。
本篇文章重点介绍数据处理思维，并没有涉及到过多高阶的语法，同时为了避免主题发散，文中涉及的函数、语法特性等，不会花费篇幅进行专门的介绍，读者可以按自身情况自行了解。
1.3 内容结构
本篇文章将围绕数列生成、区间变换、排列组合、连续判别等主题进行介绍，并附以案例进行实际运用讲解。每个主题之间有轻微的前后依赖关系，依次阅读更佳。
1.4 提示信息
本篇文章涉及的 sql 语句只使用到了 maxcompute（odps）sql 基础语法特性，理论上所有 sql 均可以在当前最新版本中运行，同时特意注明，运行环境、兼容性等问题不在本篇文章关注范围内。
二、数列
数列是最常见的数据形式之一，实际数据开发场景中遇到的基本都是有限数列。本节将从最简单的递增数列开始，找出一般方法并推广到更泛化的场景。
2.1 常见数列
2.1.1 一个简单的递增数列
首先引出一个简单的递增整数数列场景：
从数值 0 开始；
之后的每个数值递增 1 ；
至数值 3 结束；
如何生成满足以上三个条件的数列？即 [0,1,2,3] 。
实际上，生成该数列的方式有多种，此处介绍其中一种简单且通用的方案。
-- sql - 1select t.pos as a_nfrom ( select posexplode(split(space(3), space(1), false))) t;
通过上述 sql 片段可得知，生成一个递增序列只需要三个步骤：
1）生成一个长度合适的数组，数组中的元素不需要具有实际含义； 2）通过 udtf 函数 posexplode 对数组中的每个元素生成索引下标；
3）取出每个元素的索引下标。以上三个步骤可以推广至更一般的数列场景：等差数列、等比数列。下文将以此为基础，直接给出最终实现模板。
2.1.2 等差数列
若设首项，公差为 ，则等差数列的通项公式为 。
sql 实现：
-- sql - 2select a + t.pos * d as a_nfrom ( select posexplode(split(space(n - 1), space(1), false))) t;
2.1.3 等比数列
若设首项 ，公比为 ，则等比数列的通项公式为。
sql 实现：
-- sql - 3select a * pow(q, t.pos) as a_nfrom ( select posexplode(split(space(n - 1), space(1), false))) t;提示：亦可直接使用 maxcompute（odps）系统函数 sequence 快速生成数列。 -- sql - 4select sequence(1, 3, 1);-- result[1, 2, 3]
2.2 应用场景举例
2.2.1 还原任意维度组合下的维度列簇名称
在多维分析场景下，可能会用到高阶聚合函数，如 cube 、 rollup 、 grouping sets 等，可以针对不同维度组合下的数据进行聚合统计。
场景描述
现有用户访问日志表 visit_log ，每一行数据表示一条用户访问日志。
-- sql - 5with visit_log as ( select stack ( 6, '2024-01-01', '101', '湖北', '武汉', 'android', '2024-01-01', '102', '湖南', '长沙', 'ios', '2024-01-01', '103', '四川', '成都', 'windows', '2024-01-02', '101', '湖北', '孝感', 'mac', '2024-01-02', '102', '湖南', '邵阳', 'android', '2024-01-03', '101', '湖北', '武汉', 'ios' ) -- 字段：日期，用户，省份，城市，设备类型 as (dt, user_id, province, city, device_type))select * from visit_log;现针对省份 province , 城市 city, 设备类型 device_type 三个维度列，通过 grouping sets 聚合统计得到了不同维度组合下的用户访问量。问： 1）如何知道一条统计结果是根据哪些维度列聚合出来的？
2）想要输出 聚合的维度列的名称，用于下游的报表展示等场景，又该如何处理？
解决思路
可以借助 maxcompute（odps）提供的 grouping__id 来解决，核心方法是对 grouping__id 进行逆向实现。
详细步骤如下：
一、准备好所有的 grouping__id 。
生成一个包含个数值的递增数列，将每个数值转为 2 进制字符串，并展开该 2 进制字符串的每个比特位。
grouping__id bits
0 { ..., 0, 0, 0 }
1 { ..., 0, 0, 1 }
2 { ..., 0, 1, 0 }
3 { ..., 0, 1, 1 }
... ...
2n2n ...
其中 为所有维度列的数量， 即为所有维度组合的数量，每个数值表示一种 grouping__id。
二、准备好所有维度名称。
生成一个字符串序列，依次保存个维度列的名称，即
{ dim_name_1, dim_name_2, ..., dim_name_n }三、将 grouping__id 映射到维度列名称。
对于 grouping__id 递增数列中的每个数值，将该数值的 2 进制每个比特位与维度名称序列的下标进行映射，输出所有对应比特位 0 的维度名称。例如：
grouping__id：3 => { 0, 1, 1 }维度名称序列：{ 省份, 城市, 设备类型 }映射：{ 0:省份, 1:城市, 1:设备类型 }grouping__id 为 3 的数据行聚合维度即为：省份
sql 实现
-- sql - 6with group_dimension as ( select -- 每种分组对应的维度字段 gb.group_id, concat_ws(,, collect_list(case when gb.placeholder_bit = 0 then dim_col.val else null end)) as dimension_name from ( select groups.pos as group_id, pe.* from ( select posexplode(split(space(cast(pow(2, 3) as int) - 1), space(1), false)) ) groups -- 所有分组 lateral view posexplode(regexp_extract_all(lpad(conv(groups.pos,10,2), 3, 0), '(0|1)')) pe as placeholder_idx, placeholder_bit -- 每个分组的bit信息 ) gb left join ( -- 所有维度字段 select posexplode(split(省份,城市,设备类型, ',')) ) dim_col on gb.placeholder_idx = dim_col.pos group by gb.group_id)select group_dimension.dimension_name, province, city, device_type, visit_countfrom ( select grouping_id(province, city, device_type) as group_id, province, city, device_type, count(1) as visit_count from visit_log b group by province, city, device_type grouping sets( (province), (province, city), (province, city, device_type) )) tjoin group_dimension on t.group_id = group_dimension.group_idorder by group_dimension.dimension_name;
dimension_name province city device_type visit_count
省份 湖北 null null 3
省份 湖南 null null 2
省份 四川 null null 1
省份,城市 湖北 武汉 null 2
省份,城市 湖南 长沙 null 1
省份,城市 湖南 邵阳 null 1
省份,城市 湖北 孝感 null 1
省份,城市 四川 成都 null 1
省份,城市,设备类型 湖北 孝感 mac 1
省份,城市,设备类型 湖南 长沙 ios 1
省份,城市,设备类型 湖南 邵阳 android 1
省份,城市,设备类型 四川 成都 windows 1
省份,城市,设备类型 湖北 武汉 android 1
省份,城市,设备类型 湖北 武汉 ios 1
三、区间
区间相较数列具有不同的数据特征，不过在实际应用中，数列与区间的处理具有较多相通性。本节将介绍一些常见的区间场景，并抽象出通用的解决方案。
3.1 常见区间操作
3.1.1 区间分割
已知一个数值区间，如何将该区间均分成 段子区间？
该问题可以简化为数列问题，数列公式为 ，其中，具体步骤如下：
1）生成一个长度为 的数组； 2）通过 udtf 函数 posexplode 对数组中的每个元素生成索引下标；
3）取出每个元素的索引下标，并进行数列公式计算，得出每个子区间的起始值与结束值。
sql 实现：
-- sql - 7select a + t.pos * d as sub_interval_start, -- 子区间起始值 a + (t.pos + 1) * d as sub_interval_end -- 子区间结束值from ( select posexplode(split(space(n - 1), space(1), false))) t;
3.1.2 区间交叉
已知两个日期区间存在交叉 ['2024-01-01', '2024-01-03'] 、 ['2024-01-02', '2024-01-04']。问：
1）如何合并两个日期区间，并返回合并后的新区间？
2）如何知道哪些日期是交叉日期，并返回该日期交叉次数？
解决上述问题的方法有多种，此处介绍其中一种简单且通用的方案。核心思路是结合数列生成、区间分割方法，先将日期区间分解为最小处理单元，即多个日期组成的数列，然后再基于日期粒度做统计。具体步骤如下：
1）获取每个日期区间包含的天数； 2）按日期区间包含的天数，将日期区间拆分为相应数量的递增日期序列；
3）通过日期序列统计合并后的区间，交叉次数。
sql 实现：
-- sql - 8with dummy_table as ( select stack( 2, '2024-01-01', '2024-01-03', '2024-01-02', '2024-01-04' ) as (date_start, date_end))select min(date_item) as date_start_merged, max(date_item) as date_end_merged, collect_set( -- 交叉日期计数 case when date_item_cnt > 1 then concat(date_item, ':', date_item_cnt) else null end ) as overlap_datefrom ( select -- 拆解后的单个日期 date_add(date_start, pos) as date_item, -- 拆解后的单个日期出现的次数 count(1) over (partition by date_add(date_start, pos)) as date_item_cnt from dummy_table lateral view posexplode(split(space(datediff(date_end, date_start)), space(1), false)) t as pos, val) t;
date_start_merged date_end_merged overlap_date
2024-01-01 2024-01-04 [2024-01-02:2,2024-01-03:2]
增加点儿难度！
如果有多个日期区间，且区间之间交叉状态未知，上述问题又该如何求解。即：
1）如何合并多个日期区间，并返回合并后的多个新区间？
2）如何知道哪些日期是交叉日期，并返回该日期交叉次数？
sql 实现：
-- sql - 9with dummy_table as ( select stack( 5, '2024-01-01', '2024-01-03', '2024-01-02', '2024-01-04', '2024-01-06', '2024-01-08', '2024-01-08', '2024-01-08', '2024-01-07', '2024-01-10' ) as (date_start, date_end))select min(date_item) as date_start_merged, max(date_item) as date_end_merged, collect_set( -- 交叉日期计数 case when date_item_cnt > 1 then concat(date_item, ':', date_item_cnt) else null end ) as overlap_datefrom ( select -- 拆解后的单个日期 date_add(date_start, pos) as date_item, -- 拆解后的单个日期出现的次数 count(1) over (partition by date_add(date_start, pos)) as date_item_cnt, -- 对于拆解后的单个日期，重组为新区间的标记 date_add(date_add(date_start, pos), 1 - dense_rank() over (order by date_add(date_start, pos))) as cont from dummy_table lateral view posexplode(split(space(datediff(date_end, date_start)), space(1), false)) t as pos, val) tgroup by cont;
date_start_merged date_end_merged overlap_date
2024-01-01 2024-01-04 [2024-01-02:2,2024-01-03:2]
2024-01-06 2024-01-10 [2024-01-07:2,2024-01-08:3]
3.2 应用场景举例
3.2.1 按任意时段统计数据
场景描述
现有用户还款计划表 user_repayment ，该表内的一条数据，表示用户在指定日期区间内 [date_start, date_end] ，每天还款 repayment 元。
-- sql - 10with user_repayment as ( select stack( 3, '101', '2024-01-01', '2024-01-15', 10, '102', '2024-01-05', '2024-01-20', 20, '103', '2024-01-10', '2024-01-25', 30 ) -- 字段：用户，开始日期，结束日期，每日还款金额 as (user_id, date_start, date_end, repayment))select * from user_repayment;如何统计任意时段内（如：2024-01-15至2024-01-16）每天所有用户的应还款总额？
解决思路
核心思路是将日期区间转换为日期序列，再按日期序列进行汇总统计。
sql 实现
-- sql - 11select date_item as day, sum(repayment) as total_repaymentfrom ( select date_add(date_start, pos) as date_item, repayment from user_repayment lateral view posexplode(split(space(datediff(date_end, date_start)), space(1), false)) t as pos, val) twhere date_item >= '2024-01-15' and date_item = 2group by user_id;
user_id
101
102
基于连续活跃区间实现
可以视作 基于相邻日期差实现（排序版） 的衍生版本，该实现能获取到更多信息，如连续活跃区间。
-- sql - 21select user_idfrom ( select user_id, cont, -- 连续活跃区间 min(dt) as cont_date_start, max(dt) as cont_date_end from ( select *, date_add(dt, 1 - dense_rank() over (partition by user_id order by dt)) as cont from visit_log ) t1 group by user_id, cont) t2where datediff(cont_date_end, cont_date_start) + 1 >= 2group by user_id;
user_id
101
102
5.2 动态连续活跃统计
场景描述
现有用户访问日志表 visit_log ，每一行数据表示一条用户访问日志。
-- sql - 22with visit_log as ( select stack ( 6, '2024-01-01', '101', '湖北', '武汉', 'android', '2024-01-01', '102', '湖南', '长沙', 'ios', '2024-01-01', '103', '四川', '成都', 'windows', '2024-01-02', '101', '湖北', '孝感', 'mac', '2024-01-02', '102', '湖南', '邵阳', 'android', '2024-01-03', '101', '湖北', '武汉', 'ios' ) -- 字段：日期，用户，省份，城市，设备类型 as (dt, user_id, province, city, device_type))select * from visit_log;如何获取最长的 2 个连续活跃用户，输出用户、最长连续活跃天数、最长连续活跃日期区间？
上述问题在分析连续性时，获取连续性的结果不是且无法与固定的阈值作比较，而是各自以最长连续活跃作为动态阈值，此处归类为 动态连续活跃场景统计。
sql 实现
基于 普通连续活跃场景统计 的思路进行扩展即可，此处直接给出最终 sql ：
-- sql - 23select user_id, -- 最长连续活跃天数 datediff(max(dt), min(dt)) + 1 as cont_days, -- 最长连续活跃日期区间 min(dt) as cont_date_start, max(dt) as cont_date_endfrom ( select *, date_add(dt, 1 - dense_rank() over (partition by user_id order by dt)) as cont from visit_log) t1group by user_id, contorder by cont_days desclimit 2;
user_id cont_days cont_date_start cont_date_end
101 3 2024-01-01 2024-01-03
102 2 2024-01-01 2024-01-02
六、扩展
引申出更复杂的场景，是本篇文章前面章节内容的结合与变种。
6.1 区间连续（最长子区间切分）
场景描述
现有用户扫描或连接 wifi 记录表 user_wifi_log ，每一行数据表示某时刻用户扫描或连接 wifi 的日志。
-- sql - 24with user_wifi_log as ( select stack ( 9, '2024-01-01 1000', '101', 'cmcc-starbucks', 'scan', -- 扫描 '2024-01-01 1000', '101', 'cmcc-starbucks', 'scan', '2024-01-01 1000', '101', 'cmcc-starbucks', 'scan', '2024-01-01 1000', '101', 'cmcc-starbucks', 'conn', -- 连接 '2024-01-01 1000', '101', 'cmcc-starbucks', 'conn', '2024-01-01 1000', '101', 'cmcc-starbucks', 'conn', '2024-01-01 1100', '101', 'cmcc-starbucks', 'conn', '2024-01-01 1100', '101', 'cmcc-starbucks', 'conn', '2024-01-01 1100', '101', 'cmcc-starbucks', 'conn' ) -- 字段：时间，用户，wifi，状态（扫描、连接） as (time, user_id, wifi, status))select * from user_wifi_log;现需要进行用户行为分析，如何划分用户不同 wifi 行为区间？满足： 1）行为类型分为两种：连接（scan）、扫描（conn）； 2）行为区间的定义为：相同行为类型，且相邻两次行为的时间差不超过 30 分钟；
3）不同行为区间在满足定义的情况下应取到最长；
user_id wifi status time_start time_end 备注
101 cmcc-starbucks scan 2024-01-01 1000 2024-01-01 1000 用户扫描了 wifi
101 cmcc-starbucks conn 2024-01-01 1000 2024-01-01 1000 用户连接了 wifi
101 cmcc-starbucks conn 2024-01-01 1100 2024-01-01 1100 距离上次连接已经超过 30 分钟，认为是一次新的连接行为
上述问题稍显复杂，可视作 动态连续活跃统计 中介绍的 最长连续活跃 的变种。可以描述为 结合连续性阈值与行为序列中的上下文信息，进行最长子区间的划分 的问题。
sql 实现
核心逻辑：以用户、wifi 分组，结合连续性阈值与行为序列上下文信息，划分行为区间。
详细步骤：
1）以用户、wifi 分组，在分组窗口内对数据按时间正序排序； 2）依次遍历分组窗口内相邻两条记录，若两条记录之间的时间差超过 30 分钟，或者两条记录的行为状态（扫描态、连接态）发生变更，则以该临界点划分行为区间。直到遍历所有记录；
3）最终输出结果：用户、wifi、行为状态（扫描态、连接态）、行为开始时间、行为结束时间；
-- sql - 25select user_id, wifi, max(status) as status, min(time) as start_time, max(time) as end_timefrom ( select *, max(if(lag_status is null or lag_time is null or status lag_status or datediff(time, lag_time, 'ss') > 60 * 30, rn, null)) over (partition by user_id, wifi order by time) as group_idx from ( select *, row_number() over (partition by user_id, wifi order by time) as rn, lag(time, 1) over (partition by user_id, wifi order by time) as lag_time, lag(status, 1) over (partition by user_id, wifi order by time) as lag_status from user_wifi_log ) t1) t2group by user_id, wifi, group_idx;
user_id wifi status start_time end_time
101 cmcc-starbucks scan 2024-01-01 1000 2024-01-01 1000
101 cmcc-starbucks conn 2024-01-01 1000 2024-01-01 1000
101 cmcc-starbucks conn 2024-01-01 1100 2024-01-01 1100
该案例中的连续性判别条件可以推广到更多场景，例如基于日期差值、时间差值、枚举类型、距离差值等作为连续性判别条件的数据场景。
结语
通过灵活的、散发性的数据处理思维，就可以用基础的语法，解决复杂的数据场景 是本篇文章贯穿全文的思想。文中针对数列生成、区间变换、排列组合、连续判别等常见的场景，给出了相对通用的解决方案，并结合实例进行了实际运用的讲解。
本篇文章尝试独辟蹊径，强调灵活的数据处理思维，希望能让读者觉得眼前一亮，更希望真的能给读者产生帮助。同时毕竟个人能力有限，思路不一定是最优的，甚至可能出现错误，欢迎提出意见或建议。
芯片反向设计流程
基于65nm工艺的五分频器设计方案
人工智能正在帮助克服较旧的拍摄技术带来的困难，
如何理解二分查找算法
LoRaWAN网关与常见网络服务器的协议
如何充分发挥SQL能力？
三星二季度出货量大幅放缓，华为年内赶超三星或难以实现
四维图新旗下杰发科技获ISO 26262汽车功能安全最高等级ASIL D流程认证证书
不可回收标志，可回收标志图
英特尔已打算将整个智能手机调制解调器业务打包出售
意法半导体推出全新开发框架，更快速简单地实现物联网传感器
华为方舟编译器获多硬件平台支持，未来RISC-V芯片或也支持
LG成功生产关键OLED材料 以减少对进口依赖
腾讯智能下棋机器人机械手装置专利
苹果支付1.13亿美元和解iPhone“降速门”指控
可调稳压器LM317的全面介绍
C语言入门教程-指针常见错误
法国拆除华为无线设备
智能烟感产品市场需求崛起；电动汽车或迎来随充时代
AMS高精度温度传感器在智能洗衣机中的应用