让决策更智能
新一代智能数据分析平台

去重计数函数如何实现开窗

观小远发表于:2022年05月30日 17:21:11更新于:2022年05月30日 18:06:36

问题描述:

       常规的去重计数函数为 count(distinct ()),在SQL语句中必须配合 group by 一起使用才能计算出结果。在观远BI里,如果去重计算结果要参与二次计算或者使用时,对 count(distinct ()) 进行开窗计算会报错。

常见报错:

       未开窗—— Grouping expressions sequence is empty;

       开窗—— Distinct window functions are not supported.

产生原因:在Spark语法中不支持 count(distinct()) 的开窗。

解决方案1:

ETL里通过「分组聚合+自关联方式」进行去重计数,具体步骤如下。

步骤1:添加「分组聚合」节点,维度栏拖入分组字段,把要计算的字段拖入数值栏,聚合方式选“去重计数”。以下图为例,统计每个省每个区域的商品SKU数量。

0016294893d5d6d7177fd7d4ba33972

步骤2:添加「关联数据」节点,把原表和上一步分组聚合得到的临时表用维度字段左关联起来。本文案例里关联字段使用“省”和“区域”,把上一步去重计数结果字段改为合适的名字,即得到指定维度的去重计数结果。

0016294894a7fb08ba25c9bac6460a2

注意:「分组聚合+自关联方式」在ETL可以使用SQL节点代替(一般不推荐);SQL语句同时也适用于视图数据集,视图数据集里需要把必要的字段或者筛选条件替换为全局参数使用。SQL写法大致参考以下语句。

SELECT input2.*, t.`区域SKU数量` 
FROM input2 
LEFT JOIN
(SELECT input1.`省`, input1.`区域`, COUNT(DISTINCT input1.`SKU`) AS `区域SKU数量` 
FROM input1 
GROUP BY input1.`省`,input1.`区域`) t 
ON input2.`省`=t.`省` AND input2.`区域`=t.`区域`


解决方案2:

新建计算字段,使用替代窗口函数。

去重计数: size(collect_set([计数字段])over(partition by [分组字段]))

不去重计数:count([分组字段])over(partition by [分组字段])

00162948f1f1f884f27826705ed53db

注意:collect_set()over(partition by ) 函数是用来生成数组 (array) 的窗口函数,要对多行数据按照分组去重然后合并为一行(适用场景请参考 如何按照某列把不同行合并为同一行),处理完成后再用 size() 函数统计数组内元素的数量。在数据量大的时候,这个处理过程比较耗资源,耗时也较长。

两种方案使用对比如下,请根据自己使用场景和数据量选择合适的方案。

001629497247b94468596803b39ec68


    您需要登录后才可以回复