SQL进阶技巧：如何计算块熵？

0 信息量定义

信息熵

1 块熵定义

2 问题描述

3 数据准备

4 问题分析

5 小结

想要进一步了解SQL这门艺术语言的，可以订阅我的专栏数字化建设通关指南，将在该专栏进行详细解析。专栏原价99，现在活动价39.9，按照阶梯式增长，还差3个名额将上升至59.9，直到恢复原价。

数字化建设通关指南

主要内容：（1）SQL进阶实战技巧

（2）数仓建模实战技巧和个人心得

0 信息量定义

信息量是指从N 个相等可能事件中选出一个事件所需要的信息度量或含量，也就是在辩识N 个事件中特定的一个事件的过程中所需要提问是或否的最少次数。

在一个系统中，等可能事件的数量越多，事件的发生概率也就越低，传递究竟是哪个事件发生所需的比特信号也就越多。

举个例子，假如有两个相互隔离的房间A、B，二者只能通过01信号传递信息。当A房间投掷了一个硬币时，我们至少需要使用

个比特信号告诉B房间是正面朝上还是反面朝上。类似地，当A房间投掷了一个有8个面的骰子时，我们至少需要

个比特的信号来传递该信息。

假如系统中有的N个等可能事件，那么我们将 $log2^{_{}^{N}}$ 称为信息量。由于等可能事件的发生概率为1/N，则信息量的公式可以写作

推而广之，假设事件x 的发生概率为p (x) ，则传递该事件发生需要的最少比特信号位（即信息量）为

信息熵

信息熵用于衡量整个事件空间包含的平均信息量，即信息量的平均期望。将事件空间中所有事件发生的概率，乘以该事件的信息量，即可得到信息熵：

1 块熵定义

“块熵”(Block Entropy)通常用于描述信息理论中的一种概念，特别是在压缩和编码方面。它是指将数据流划分为连续的块，并对每个块计算熵的过程。

在块熵中，数据流被分割成固定大小（k kk）的块（或窗口），然后对每个块应用熵的计算方法。这有助于分析数据在不同块之间的变化或随时间的演变。一般的，当k kk取1 11时，块熵将退化为信息熵

2 问题描述

假设有以下数字序列作为数据流：

sql">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 数据准备

sql">create table block_entropy as(select stack(10,0,1,0,0,1,1,0,1,0,1) as(block));

4 问题分析

第一步：计算k2 block

sql">select id, collect_list(block) over (order by id rows between current row and 1 following) k2from block_entropy

第二步：计算k2块个数及块的总个数

sql">select k2,max(ttl_cnt) ttl_cnt,count(1)     k2_cntfrom (select id,k2,count(1) over () ttl_cntfrom (select id, collect_list(block) over (order by id rows between current row and 1 following) k2from block_entropy) twhere size(k2) = 2) tgroup by k2

第三步：按照信息量公式计算

sql">select k2,-(k2_cnt / ttl_cnt) * log2(k2_cnt / ttl_cnt) log2from (select k2,max(ttl_cnt) ttl_cnt,count(1)     k2_cntfrom (select id,k2,count(1) over () ttl_cntfrom (select id, collect_list(block) over (order by id rows between current row and 1 following) k2from block_entropy) twhere size(k2) = 2) tgroup by k2) t

第四步：对所有的信息量进行求和，计算最终的结果值即熵值。

快熵完整的计算SQL如下：

sql">select cast(sum(log2) as decimal(18, 5)) block_entropy
from (select k2,-(k2_cnt / ttl_cnt) * log2(k2_cnt / ttl_cnt) log2from (select k2,max(ttl_cnt) ttl_cnt,count(1)     k2_cntfrom (select id,k2,count(1) over () ttl_cntfrom (select id, collect_list(block) over (order by id rows between current row and 1 following) k2from block_entropy) twhere size(k2) = 2) tgroup by k2) t) t
;

5 小结

在实际应用中，块熵可用于识别数据中的模式、周期性或其他结构，并有助于设计有效的压缩算法或数据处理方法。例如，在音频处理中，可以将音频信号划分为连续的时间窗口，并对每个窗口的信号进行熵计算，以了解音频信号的频谱特性或声音事件的分布。在网络数据传输中，块熵可以用于检测数据流中的重复模式或压缩数据以减少带宽消耗。

想要进一步了解SQL这门艺术语言的，可以订阅我的专栏数字化建设通关指南，将在该专栏进行详细解析。
专栏原价99，现在活动价39.9，按照阶梯式增长，还差3个名额将上升至59.9，直到恢复原价。

数字化建设通关指南

主要内容：
（1）SQL进阶实战技巧

可以参考如下教程，具体链接如下

SQL很简单，可你却写不好？也许这才是SQL最好的教程

上面链接中的文章及技巧会不定期更新。

（2）数仓建模实战技巧和个人心得

1）新人入职新公司后应如何快速了解业务？

2）以业务视角看宽表化建设？

3) 维度建模 or 关系型建模？

4）业务模型与数据模型有什么区别？业务阶段的模型该如何建设？

5）业务指标体系该如何建设？指标体系该如何维护？指标平台应如何建设？指标体系该由谁来搭建？

6）如何优雅设计DWS层？DWS层模型好坏该如何评价？

7）指标发生异常，该如何排查？应从哪些方面入手寻找问题点？

8）数据架构的选择，mpp or hadoop?

9）数仓团队应如何体现自己的业务价值，讲好数据故事？

10）BI与大数据有什么关系？BI与信息化、数字化之间有什么关系？BI与报表之间的关系？

11）数据部门如何与业务部门沟通，并规划指引业务需求？

文章不限于以上内容，有新的想法也会及时更新到该专栏。

具体专栏链接如下：数字化建设通关指南_莫叫石榴姐的博客-CSDN博客