实测：Vertica 与 WutongDB 函数对比测试之_分析函数篇

头像
千钧
     甘肃
    阅读 8 分钟
    2
    头图

    测试环境：

    • Vertica：Vertica Analytic Database v11.1.1-22
    • WutongDB：V5.4.10.0

    1. argmax

    • 功能说明：

      argmax(expression1, expression2) OVER (PARTITION BY ...) 函数用于在一个分区（PARTITION）中，根据expression2的排序，返回expression1的值，该值与expression2的最大值对应。这个函数常用于找到最大值所对应的其他列的值。

    • 测试语句：

      select
  user_id,
  cell_type,
  age,
  argmax(user_id, age) over(
      partition by cell_type
      order by
      age
  )
from
  bdcis.tb_cis_user_induser_day
order by
  cell_type,
  user_id;

    • Vertica 输出结果：

      输出结果如下图所示：

      argmax_vertica_out.png

    • WutongDB 输出结果：

      在梧桐数据库中无此函数，如下图所示：

      argmax_wutongdb_out.png

    • WutongDB 5.4 替代方案：

      • 在 WutongDB 5.4 中，无法直接使用argmax和argmin函数。可以通过窗口函数结合子查询的方式实现类似效果。

        SELECT user_id, cell_type, age
FROM (
  SELECT user_id, cell_type, age,
         ROW_NUMBER() OVER (PARTITION BY cell_type ORDER BY age DESC) as rn
  FROM bdcis.tb_cis_user_induser_day
) as subquery
WHERE rn = 1;

      • 函数说明：

        • ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)：为分区中的每一行按指定顺序分配一个唯一的行号，从1开始。
        • 使用子查询获取排序后的第一行（最大或最小值）。

    • WutongDB 6 替代方案：

      • 在 WutongDB 6 中，可以使用类似的解决方案，但也有直接支持FILTER子句的FIRST_VALUE和LAST_VALUE函数来简化操作：

        SELECT DISTINCT ON (cell_type) user_id, cell_type, age
FROM bdcis.tb_cis_user_induser_day
ORDER BY cell_type, age DESC, user_id;
    - **函数说明：**

  - `DISTINCT ON (expression)`：返回每个分区中按指定顺序排列的第一条记录。
  - `ORDER BY ... DESC`：确保返回最大或最小值对应的记录。

    2. argmin

    • 功能说明：

      argmin(expression1, expression2) OVER (PARTITION BY ...) 函数与argmax类似，但它返回的是expression1中与expression2的最小值对应的值。用于找到最小值所对应的其他列的值。

    • 测试语句：

      select
    user_id,
    cell_type,
    age,
    argmin(user_id, age) over(
        partition by cell_type
        order by
            age
    )
from
    bdcis.tb_cis_user_induser_day
order by
    cell_type,
    user_id;

    • Vertica 输出结果：

      输出结果如下图所示：

      argmin_vertica_out.png

    • WutongDB 输出结果：

      argmin()函数不存在，如下图所示：

      argmin_wutongdb_out.png

    • WutongDB 替代方案：

      • 参考 argmin 的解决替代方案。

    3. conditional_change_event

    • 功能说明：

      conditional_change_event(expression) 函数用于检测指定条件下的值变化事件。例如，监控一组数据中，当某个条件首次变为真时触发的事件。这个函数在时间序列或事件驱动的分析中很有用。

    • 测试语句：

      select
    18,
    '全国亲情网',
    to_date(create_date, 'yyyymmdd') create_date,
    count(1) as 全国亲情网
from
    gs_test.ceshi_to_date
where
    to_date(create_date, 'yyyymmdd') >= '2020-09-23'
group by
    to_date(create_date, 'yyyymmdd')
ORDER BY
    create_date;

    • Vertica 输出结果：

      在Vertica和梧桐中的测试表中数据一样，测试语句一样，就测试结果不一致。

      conditional_change_event_vertica_out.png

    • WutongDB 输出结果：

      conditional_change_event() 函数不存在，如下图所示：

      conditional_change_event_wutongdb_out.png

    • WutongDB 5.4 替代方案：

      • 在 WutongDB 5.4 中，这种功能可以通过窗口函数和条件表达式的结合来实现。

        SELECT user_id, cell_type, age,
       CASE WHEN LAG(expression) OVER (PARTITION BY cell_type ORDER BY ...) <> expression THEN 1 ELSE 0 END as change_event
FROM bdcis.tb_cis_user_induser_day;

      • 函数说明：

        • LAG(expression) OVER (PARTITION BY ... ORDER BY ...)：获取当前行之前的某一行的值，用于比较前后行是否变化。
        • CASE WHEN ... THEN ... ELSE ... END：条件表达式，根据逻辑判断返回不同的结果。

    • WutongDB 6 替代方案：

      • 在 WutongDB 6中，可以继续使用上述方法，还可以结合 FIRST_VALUE或LAST_VALUE 函数提高灵活性。

        SELECT user_id, cell_type, age,
       CASE WHEN expression <> FIRST_VALUE(expression) OVER (PARTITION BY cell_type ORDER BY ...) THEN 1 ELSE 0 END as change_event
FROM bdcis.tb_cis_user_induser_day;

      • 函数说明：

        • FIRST_VALUE(expression) OVER (PARTITION BY ... ORDER BY ...)：返回分区内第一个值，用于比较变化。

    4. conditional_true_event

    • 功能说明：

      conditional_true_event(condition) OVER (PARTITION BY ...) 函数用于监控在一个分区内，当某个条件第一次变为真时触发的事件。这通常用于标记第一次满足某个条件的记录。

    • 测试语句：

      select
    user_id,
    up_area_code,
    age,
    conditional_true_enent(age > 30) over(
        partition by up_area_code
        order by
            join_date
    )
from
    bdcis.tb_cis_user_induser_day;

    • Vertica 输出结果：

      conditional_true_event() 函数不存在，如下图所示：

      conditional_true_event_vertica_out.png

    • WutongDB 输出结果：

      conditional_true_event() 函数不存在，如下图所示：

      conditional_true_event_wutongdb_out.png

    • Wutong DB 5.4 替代方案：

      • 在 Wutong DB 5.4 中，可以使用窗口函数结合CASE语句模拟类似功能。

        SELECT user_id, up_area_code, age,
       CASE WHEN condition AND ROW_NUMBER() OVER (PARTITION BY up_area_code ORDER BY ...) = 1 THEN 1 ELSE 0 END as true_event
FROM bdcis.tb_cis_user_induser_day;

      • 函数说明：

        • ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)：生成分区中按指定顺序排列的行号，用于标记第一次满足条件的行。

    • Wutong DB 6 替代方案：

      • 在Wutong DB 6中，可以更简洁地通过FILTER子句来实现：

        SELECT user_id, up_area_code, age,
       BOOL_OR(condition) OVER (PARTITION BY up_area_code ORDER BY join_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as true_event
FROM bdcis.tb_cis_user_induser_day;

      • 函数说明：

        • BOOL_OR(expression)：返回分区内布尔表达式的逻辑或结果，用于标记首次满足条件的行。

    5. median

    • 功能说明：

      median(expression) OVER (PARTITION BY ...) 函数用于计算分区内排序后的中位数。中位数是将所有值按顺序排列后，位于中间的值。

    • 测试语句：

      select
    DISTINCT group_id,
    median(credit_score) over(partition by group_id) as median_credit_score
from
    bdcis.tb_cis_user_induser_day;

    • Vertica 输出结果：

      在 vertica 中可以执行（使用该函数必须和开窗函数一起使用）

      median_vertica_out.png

    • WutongDB 输出结果：

      在梧桐数据库中不与开窗函数一起使用，显示函数不存在，和开窗函数一起使用报错：over() 必须和聚合函数一起使用。，如下图所示：

      median_wutongdb_out.png

    • Wutong DB 5.4 替代方案：

      • 在Wutong DB 5.4中，没有直接的median函数。可以通过PERCENTILE_CONT函数来模拟计算中位数。

        SELECT cell_type,
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY age) as median_age
FROM bdcis.tb_cis_user_induser_day
GROUP BY cell_type;

      • 函数说明：

        • PERCENTILE_CONT(percent) WITHIN GROUP (ORDER BY ...)：计算指定百分位数的值，0.5表示中位数。

    • Wutong DB 6 替代方案：

      • Wutong DB 6 继续支持使用PERCENTILE_CONT，且可以在窗口函数中更方便地使用：

        SELECT DISTINCT cell_type,
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY age) OVER (PARTITION BY cell_type) as median_age
FROM bdcis.tb_cis_user_induser_day;

      • 函数说明：

        • 此方式允许在窗口函数上下文中使用PERCENTILE_CONT直接计算中位数。

    6. nth_value

    • 功能说明：

      nth_value(expression, n) OVER (PARTITION BY ...) 函数用于返回分区内按顺序排列的第n个值。该函数对于需要获取组内特定位置的值的情况非常有用。

    • 测试语句：

      select
    user_id,
    cell_type,
    age,
    nth_value(age, 5) over(
        partition by cell_type
        order by
            join_date
    ) as "分组内的第三个age值"
from
    bdcis.tb_cis_user_induser_day;

    • Vertica 输出结果：

      在 vertica 中可以执行，如下图所示：

      nth_value_vertica_out.png

    • WutongDB 输出结果：

      nth_value() 函数不存在，如下图所示：

      nth_value_wutongdb_out.png

    • Wutong DB 5.4 替代方案：

      • 在Wutong DB 5.4中，nth_value函数不可用，但可以使用窗口函数和子查询来获取类似效果。

        SELECT user_id, cell_type, age
FROM (
  SELECT user_id, cell_type, age,
         ROW_NUMBER() OVER (PARTITION BY cell_type ORDER BY join_date) as rn
  FROM bdcis.tb_cis_user_induser_day
) as subquery
WHERE rn = 5;

      • 函数说明：

        • ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)：为分区中的每一行按指定顺序分配一个唯一的行号。
        • 通过子查询获取排序后的第n行。

    • Wutong DB 6 替代方案：

      • 在Wutong DB 6中，可以直接使用nth_value函数：

        SELECT user_id, cell_type, nth_value(age, 5) OVER (PARTITION BY cell_type ORDER BY join_date) as nth_age
FROM bdcis.tb_cis_user_induser_day;

      • 函数说明：

        • nth_value(expression, n) OVER (PARTITION BY ... ORDER BY ...)：直接返回分区内排序后第n个值。

    7. percentile_cont

    • 功能说明：

      percentile_cont(percent) WITHIN GROUP (ORDER BY expression) 函数用于计算一个分组内的百分位数值，percent指定要计算的百分位，例如0.5表示中位数。此函数通常用于统计学分析。

    • 测试语句：

      select
    cell_type,
    percentile_cont(0.5) within group(
        order by
            age
    ) as "分组呢内所占0.5比例的age值"
from
    bdcis.tb_cis_user_induser_day
group by
    cell_type;

    • Vertica 输出结果：

      在 vertica 中和开窗函数一起使用。，如下图所示：

      percentile_cont_vertica_out.png

    • WutongDB 输出结果：

      和开窗函数一起使用报错：over() 必须和聚合函数一起使用。不用开窗函数，使用 group by 可以执行，如下图所示：

      percentile_cont_wutongdb_out.png

    • Wutong DB 5.4 替代方案：

      • 在Wutong DB 5.4中，无法直接使用这些函数，可以通过自定义排序和行数计算来模拟百分位数计算。例如：

        SELECT age
FROM (
  SELECT age,
         NTILE(100) OVER (ORDER BY age) as percentile_rank
  FROM bdcis.tb_cis_user_induser_day
) as subquery
WHERE percentile_rank = 50;
    - **函数说明：**
  - `NTILE(n) OVER (ORDER BY ...)`：将结果集分成n个桶，每个桶内的行数尽量相等，可以用来近似计算百分位数。

    • Wutong DB 6 替代方案：

      • 在Wutong DB 6中，可以直接使用PERCENTILE_CONT和PERCENTILE_DISC函数：

        SELECT cell_type,
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY age) as median_age
FROM bdcis.tb_cis_user_induser_day
GROUP BY cell_type;
        SELECT cell_type,
       PERCENTILE_DISC(0.8) WITHIN GROUP (ORDER BY age) as disc_percentile_age
FROM bdcis.tb_cis_user_induser_day
GROUP BY cell_type;

    • 函数说明：

      • PERCENTILE_CONT(percent) WITHIN GROUP (ORDER BY ...)：计算指定百分位数的插值结果。
      • PERCENTILE_DISC(percent) WITHIN GROUP (ORDER BY ...)：返回指定百分位数的实际数据点，而不是插值。

    8. percentile_disc

    • 功能说明：

      percentile_disc(percent) WITHIN GROUP (ORDER BY expression) 函数与percentile_cont类似，但它返回的是在排序后最接近指定百分位数的实际数据点，而不是插值后的值。

    • 测试语名：

      select
    cell_type,
    percentile_disc(0.8) within group(
        order by
            age
    ) as "分组呢内所占0.8比例的age值"
from
    bdcis.tb_cis_user_induser_day
group by
    cell_type;

    • Vertica 输出结果：

      在 vertica 中和开窗函数一起使用。，如下图所示：

      percentile_disc_vertica_out.png

    • WutongDB 输出结果：

      和开窗函数一起使用报错（pq: window OVER clause can only be used with an aggregate），使用 group by 可以执行，对比结果一致，如下图所示：

      percentile_disc_wutongdb_out.png

    • WutongDB 替代方案

      • 请参考 percentile_cont 的替代方案。
    数据库
    千钧
    7 声望3 粉丝

    不爱美食的古玩爱好者不是一个真正的程序猿！

    « 上一篇
    实测： Vertica 与 WutongDB 函数对比测试之 _ 格式化函数篇
    下一篇 »
    实测： Vertica 与 Wutong DB 函数对比测试之 _ 字符串函数篇

    引用和评论

    推荐阅读
    头像
    利用 WuTongDB 高效解析嵌套 JSON 数据

    千钧

    头像
    数据集汇总｜18个电影/音乐数据集汇总，覆盖影片/歌曲推荐、电影评价、歌词识别、音乐流派······

    超神经HyperAI赞 3阅读 1.2k

    头像
    使用 MongoDB 构建 AI：Gradient Accelerator Block 如何在几秒钟内让您从零开发 AI

    MongoDB数据平台赞 1阅读 12.9k

    头像
    MongoDB Atlas与YoMio.AI近乎完美适配:推理更快速、查询更灵活、场景更丰富

    MongoDB数据平台赞 1阅读 12.4k

    头像
    MongoDB 8.0已全面可用

    MongoDB数据平台阅读 12k

    头像
    简道云搭载阿里云MongoDB数据库，帮助数以万计企业重构业务系统

    MongoDB数据平台阅读 11.3k

    头像
    MySQL 回收表碎片实践教程

    MySQL技术赞 2阅读 321

    0 条评论
    得票最新