SQL进阶技巧：如何计算块熵？
目录
想要进一步了解SQL这门艺术语言的，可以订阅我的专栏数字化建设通关指南，将在该专栏进行详细解析。专栏 原价99，现在活动价39.9，按照阶梯式增长，还差3个名额将上升至59.9，直到恢复原价。
0 信息量定义
信息量是指从N 个相等可能事件中选出一个事件所需要的信息度量或含量，也就是在辩识N 个事件中特定的一个事件的过程中所需要提问是或否的最少次数。
在一个系统中，等可能事件的数量越多，事件的发生概率也就越低，传递究竟是哪个事件发生所需的比特信号也就越多。
举个例子，假如有两个相互隔离的房间A、B，二者只能通过01信号传递信息。当A房间投掷了一个硬币时，我们至少需要使用
个比特信号告诉B房间是正面朝上还是反面朝上。类似地，当A房间投掷了一个有8个面的骰子时，我们至少需要
个比特的信号来传递该信息。
假如系统中有的N个等可能事件，那么我们将称为信息量。由于等可能事件的发生概率为1/N，则信息量的公式可以写作
推而广之，假设事件x 的发生概率为p (x) ，则传递该事件发生需要的最少比特信号位（即信息量）为
信息熵
信息熵用于衡量整个事件空间包含的平均信息量，即信息量的平均期望。将事件空间中所有事件发生的概率，乘以该事件的信息量，即可得到信息熵：
1 块熵定义
“块熵”(Block Entropy)通常用于描述信息理论中的一种概念，特别是在压缩和编码方面。它是指将数据流划分为连续的块，并对每个块计算熵的过程。
在块熵中，数据流被分割成固定大小（k kk）的块（或窗口），然后对每个块应用熵的计算方法。这有助于分析数据在不同块之间的变化或随时间的演变。一般的，当k kk取1 11时，块熵将退化为信息熵
2 问题描述
假设有以下数字序列作为数据流：
data_stream=[0,1,0,0,1,1,0,1,0,1]计算这个时间序列的块熵，其中k = 2 。
将时间序列分成长度为2 22的历史块：
第一个历史块： [0, 1]
第二个历史块： [1, 0]
第三个历史块： [0, 0]
第四个历史块： [0, 1]
第五个历史块： [1, 1]
第六个历史块： [1, 0]
第七个历史块： [0, 1]
第八个历史块： [1, 0]
第九个历史块： [0, 1]
这些历史块的分布律为：
因此该序列的块熵为：
3 数据准备
create table block_entropy as
(select stack(
10,
0,
1,
0,
0,
1,
1,
0,
1,
0,
1
) as(block)
);4 问题分析
第一步：计算k2 block
select id
, collect_list(block) over (order by id rows between current row and 1 following) k2
from block_entropy
第二步：计算k2块个数及块的总个数
select k2,
max(ttl_cnt) ttl_cnt,
count(1) k2_cnt
from (select id,
k2,
count(1) over () ttl_cnt
from (select id
, collect_list(block) over (order by id rows between current row and 1 following) k2
from block_entropy) t
where size(k2) = 2) t
group by k2 
第三步：按照信息量公式计算
select k2,
-(k2_cnt / ttl_cnt) * log2(k2_cnt / ttl_cnt) log2
from (select k2,
max(ttl_cnt) ttl_cnt,
count(1) k2_cnt
from (select id,
k2,
count(1) over () ttl_cnt
from (select id
, collect_list(block) over (order by id rows between current row and 1 following) k2
from block_entropy) t
where size(k2) = 2) t
group by k2) t
第四步：对所有的信息量进行求和，计算最终的结果值即熵值。
快熵完整的计算SQL如下：
select cast(sum(log2) as decimal(18, 5)) block_entropy
from (select k2,
-(k2_cnt / ttl_cnt) * log2(k2_cnt / ttl_cnt) log2
from (select k2,
max(ttl_cnt) ttl_cnt,
count(1) k2_cnt
from (select id,
k2,
count(1) over () ttl_cnt
from (select id
, collect_list(block) over (order by id rows between current row and 1 following) k2
from block_entropy) t
where size(k2) = 2) t
group by k2) t) t
;
5 小结
在实际应用中，块熵可用于识别数据中的模式、周期性或其他结构，并有助于设计有效的压缩算法或数据处理方法。例如，在音频处理中，可以将音频信号划分为连续的时间窗口，并对每个窗口的信号进行熵计算，以了解音频信号的频谱特性或声音事件的分布。在网络数据传输中，块熵可以用于检测数据流中的重复模式或压缩数据以减少带宽消耗。
想要进一步了解SQL这门艺术语言的，可以订阅我的专栏数字化建设通关指南，将在该专栏进行详细解析。
专栏 原价99，现在活动价39.9，按照阶梯式增长，还差3个名额将上升至59.9，直到恢复原价。
数字化建设通关指南
主要内容：
（1）SQL进阶实战技巧
可以参考如下教程，具体链接如下
上面链接中的文章及技巧会不定期更新。
（2）数仓建模实战技巧和个人心得
1）新人入职新公司后应如何快速了解业务？
2）以业务视角看宽表化建设？
3) 维度建模 or 关系型建模？
4）业务模型与数据模型有什么区别？业务阶段的模型该如何建设？
5）业务指标体系该如何建设？指标体系该如何维护？指标平台应如何建设？指标体系 该由谁来搭建？
6）如何优雅设计DWS层？DWS层模型好坏该如何评价？
7）指标发生异常，该如何排查？应从哪些方面入手寻找问题点？
8） 数据架构的选择，mpp or hadoop?
9）数仓团队应如何体现自己的业务价值，讲好数据故事？
10）BI与大数据有什么关系？BI与信息化、数字化之间有什么关系？BI与报表之间的关 系？
11）数据部门如何与业务部门沟通，并规划指引业务需求？
文章不限于以上内容，有新的想法也会及时更新到该专栏。
具体专栏链接如下：数字化建设通关指南_莫叫石榴姐的博客-CSDN博客
原文地址：https://blog.csdn.net/godlovedaniel/article/details/142587182
免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！