Lag函数求环比缺陷与改进

不会Hive的啊扬

已于 2023-11-14 23:45:34 修改

阅读量224

点赞数

文章标签： java 数据库 sql

于 2023-10-25 19:22:22 首次发布

本文链接：https://blog.csdn.net/m0_70949976/article/details/134041365

版权

一、环比概念

首先了解一个概念环比是什么？

"环比"是一个用于比较两个连续时间段内数据变化的术语，通常用于经济、金融和业务分析中。环比是指将当前时间段的数据与上一个时间段（通常是上一个周期，如上个月或上一季度）的数据进行比较，以分析数据的增长或减少趋势。环比通常以百分比的形式表示，可以用以下公式计算：

环比增长率=当前时间段数据−上一个时间段数据上一个时间段数据×100%环比增长率=上一个时间段数据当前时间段数据−上一个时间段数据×100%

环比分析通常用于以下领域：

经济分析： 用于分析国民生产总值（GDP）、就业率、失业率等宏观经济数据的月度或季度变化。
股票市场： 用于比较股票价格、交易量等指标在两个相邻交易日或交易周之间的变化。
业务分析： 用于比较销售额、利润、用户数量等业务指标在两个相邻时间段内的变化，以帮助企业了解业务趋势。
市场营销： 用于分析广告效果、销售渠道、市场份额等营销数据的变化。

环比分析有助于识别短期内的趋势和波动，帮助决策者更好地了解数据的变化情况。与"同比"（与去年同一时间段相比）和"季节性调整"（考虑季节性变化）等分析方法结合使用，可以提供更全面的数据洞察。

二、Lag求环比方式一

SELECT brand ,experience_value , mention_numbers, emotions_positive, emotions_negative, emotions_neutral,
        lag(experience_value,#{req.datePeriod}, 0) over ( partition by brand order by dt) experience_value_prev,
        lag(mention_numbers,#{req.datePeriod}, 0) over ( partition by brand order by dt) mention_numbers_prev,
        lag(emotions_positive,#{req.datePeriod}, 0) over ( partition by brand order by dt) emotions_positive_prev,
        lag(emotions_negative,#{req.datePeriod}, 0) over ( partition by brand order by dt) emotions_negative_prev,
        lag(emotions_neutral,#{req.datePeriod}, 0) over ( partition by brand order by dt) emotions_neutral_prev

req.datePeriod为程序封装参数，计算两个日期之间的间隔天数，我们会在前端传入开始日期和结束日期

    public long getDatePeriod( ) {
        if(dt==null || dt.length<2 || dt[0]==null || dt[1] ==null){
            return 1;
        }

        LocalDate start=dt[0];
        LocalDate end=dt[1];
        long between = ChronoUnit.DAYS.between( start,end) +1;//4
        return between;
    }

而此时我们通过跨间隔天数为步长，取上份数据，当做本行数据的上个周期对应的那一天，比如当天为2月1号，我们取1月1号数据，外层吧所有Lag值做sum，好像确实能拿到上个周期所有数据，但这里存在两个问题：

1、倘若2月1号没有数据，1月1号数据就无法取到上周期sum中

2、如果上周期日期不连续，则间隔30天，将无法取到我们想要的那天的数据，因为我们计算二月份的环比，一定取的数据范围是1月1号到2月28号的

三、Lag求环比方式二

SELECT brand ,experience_value , mention_numbers, emotions_positive, emotions_negative, emotions_neutral,
        lag(experience_value, 1, 0) OVER (PARTITION BY brand,case when dt &lt; #{req.startDate} then DATE_ADD(dt,INTERVAL #{req.datePeriod} DAY) else dt end ORDER BY dt asc)
        experience_value_prev,
        lag(mention_numbers, 1, 0) OVER (PARTITION BY brand,case when dt &lt; #{req.startDate} then DATE_ADD(dt,INTERVAL #{req.datePeriod} DAY) else dt end ORDER BY dt asc)
        mention_numbers_prev,
        lag(emotions_positive, 1, 0) OVER (PARTITION BY brand,case when dt &lt; #{req.startDate} then DATE_ADD(dt,INTERVAL #{req.datePeriod} DAY) else dt end ORDER BY dt asc)
        emotions_positive_prev,
        lag(emotions_negative, 1, 0) OVER (PARTITION BY brand,case when dt &lt; #{req.startDate} then DATE_ADD(dt,INTERVAL #{req.datePeriod} DAY) else dt end ORDER BY dt asc)
        emotions_negative_prev,
        lag(emotions_neutral, 1, 0) OVER (PARTITION BY brand,case when dt &lt; #{req.startDate} then DATE_ADD(dt,INTERVAL #{req.datePeriod} DAY) else dt end ORDER BY dt asc)
        emotions_neutral_prev

此时我们采用步长为1，如果该行日期小于开始日期，即2月1日，则强行将该日期加上周期天数，比如将1月1号和2月1号数据分入一组，1月2号和2月2号分入一组，这样似乎是解决了上个周期日期不连续问题，但倘若本周期哪天没有数据的问题还是无法解决

四：转变思路

解决方式：

以上两种方法都有缺陷，一个要求上周期本周期都要日期连续，另一个要求本周期日期连续，这时候我们就会想到一个方法，那就是我用原表和一个日期表做join,如果没有那天日期，就给其他列补0

优化思路：

但上述方法仍然无法满足一种情况，那就是如果我一天内有多条数据，是不是lag就会把本该属于本周期的数据取到上个周期呢？我们还应该首先做一步预处理，将一天中的数据先进行一次sum合并，保证一天内只有一条数据，且日期连续

五：最终方式

但上述方式是十分麻烦的，其实应该将本周期的数据单独做计算，上周期也单独做计算，无论其是否连续或是否一天内多条数据，都是不受影响的，这时，可以采用两种方式：

1、通过外部给sql传值，根据传的值确定是计算本周期还是上周期，然后在程序里进行环比计算

        List<UserTagMentionVO> userTagRatioList = dorisLabelBitmapMapper.userTagRatio2(reqVO, tenantId, true);//当前周期
        List<UserTagMentionVO> userTagRatioList2 = dorisLabelBitmapMapper.userTagRatio2(reqVO, tenantId, false);//上个周期

        int mentionNumbersTotal = 0;//当前标签页总提及量
        for (UserTagMentionVO item : userTagRatioList) {
            mentionNumbersTotal += item.getMentionNumbers();
        }

        Map<String, UserTagMentionVO> userTagRatioMap2 = userTagRatioList2.stream().collect(Collectors.toMap(item -> item.getLabelValueCode(), item -> item));


        for (UserTagMentionVO item : userTagRatioList) {
            UserTagMentionVO itemPrev = userTagRatioMap2.get(item.getLabelValueCode());
            if (itemPrev != null) {
                if (item.getExperienceValue() != null && itemPrev.getExperienceValue() != null && itemPrev.getExperienceValue() != 0) {
                    item.setExperienceRate(Math.round((item.getExperienceValue() - itemPrev.getExperienceValue()) * 100.0 / itemPrev.getExperienceValue() * 100.0) / 100.0);
                } else {
                    item.setExperienceRate(null);
                }
                if (item.getMentionNumbers() != null && itemPrev.getMentionNumbers() != null && itemPrev.getMentionNumbers() != 0) {
                    item.setMentionNumbersRate(Math.round((item.getMentionNumbers() - itemPrev.getMentionNumbers()) * 100.0 / itemPrev.getMentionNumbers() * 100.0) / 100.0);
                } else {
                    item.setMentionNumbersRate(null);
                }
            }
            if (item.getMentionNumbers() != null && mentionNumbersTotal != 0) {
                item.setMentionNumbersPercentCur(Math.round(item.getMentionNumbers() * 100.0 / mentionNumbersTotal * 100.0) / 100.0);
            } else {
                item.setMentionNumbersPercentCur(null);
            }

解释：将本周期作为一个list，上周期结果作为一个map，通过遍历本周不同维度，去上周期取对应维度的值，和本周期做计算，如果上周期不存在该维度，则不处理，如果上周期存在，判断分母是否为空或0，分子是否为空，在内部做两位小数处理时，首先要知道Math.round方法只能对double类型四舍五入到整型，因此先将结果乘以100.0，保证传入参数是double且先往前进2位，再乘以100保证结果是百分比形式，最后再除以一开始多乘的100.0，即可四舍五入两位小数

缺陷：虽然在程序中使用map结构可代替程序中的Join操作提升性能，但在程序中需要对分子分母不断的进行防止空指针和保留两位小数的处理逻辑，而这些在sql中处理显然更方便，而且如果有占比运算，甚至需要再多写sql对本周期甚至上周期总量进行求值

2、单独对某一部分数据做计算，就是分组干的事情，我们可以在一段sql中对本周期和上周期数据打上分组标记，分别计算其聚合结果，最后再进行环比计算处理

    <select id="getExpSum" resultType="com.guyuai.module.dashboard.dal.dataobject.dashboard.DashBoardExpDO"
            parameterType="com.guyuai.module.dashboard.controller.admin.experience.vo.ExperienceReqVO"
    >
        select
        metrics_id,
        experience_value,
        mention_numbers,
        emotions_positive,
        emotions_negative,
        emotions_neutral,
        total_mention_numbers_cur,
        experience_value_prev,
        mention_numbers_prev,
        emotions_positive_prev,
        emotions_negative_prev,
        emotions_neutral_prev,
        total_mention_numbers_prev,
        round((experience_value - experience_value_prev) / experience_value_prev * 100, 2) as experience_rate,
        round((mention_numbers - mention_numbers_prev) / mention_numbers_prev * 100, 2) as mention_rate,
        round((emotions_positive - emotions_positive_prev) / emotions_positive_prev * 100, 2) as positive_rate,
        round((emotions_negative - emotions_negative_prev) / emotions_negative_prev * 100, 2) as negative_rate,
        round((emotions_neutral - emotions_neutral_prev) / emotions_neutral_prev * 100, 2) as neutral_rate,
        round(mention_numbers / total_mention_numbers_cur * 100, 2) as mention_numbers_percent_cur,
        round(mention_numbers_prev / total_mention_numbers_prev * 100, 2) as mention_numbers_percent_prev,
        round((mention_numbers / total_mention_numbers_cur - mention_numbers_prev / total_mention_numbers_prev)
        / (mention_numbers_prev / total_mention_numbers_prev) * 100, 2) as mention_numbers_percent_rate
        from(
        select
        metrics_id,
        TRUNCATE((sum(if(flag = 'cur', emotions_positive, null))-sum(if(flag = 'cur', emotions_negative, null)))
        * 100 / sum(if(flag = 'cur', mention_numbers, null)) ,2) as experience_value,
        sum(if(flag = 'cur', mention_numbers, null)) as mention_numbers,
        sum(if(flag = 'cur', emotions_positive, null)) as emotions_positive,
        sum(if(flag = 'cur', emotions_negative, null)) as emotions_negative,
        sum(if(flag = 'cur', emotions_neutral, null)) as emotions_neutral,
        max(if(flag = 'cur', total_mention_numbers, null)) as total_mention_numbers_cur,
        TRUNCATE((sum(if(flag = 'prev', emotions_positive, null))-sum(if(flag = 'prev', emotions_negative, null)))
        * 100 / sum(if(flag = 'prev', mention_numbers, null)) ,2) as experience_value_prev,
        sum(if(flag = 'prev', mention_numbers, null)) as mention_numbers_prev,
        sum(if(flag = 'prev', emotions_positive, null)) as emotions_positive_prev,
        sum(if(flag = 'prev', emotions_negative, null)) as emotions_negative_prev,
        sum(if(flag = 'prev', emotions_neutral, null)) as emotions_neutral_prev,
        max(if(flag = 'prev', total_mention_numbers, null)) as total_mention_numbers_prev
        from(
        select
        metrics_id, flag,
        mention_numbers * cnt as mention_numbers,
        emotions_positive * cnt as emotions_positive,
        emotions_negative * cnt as emotions_negative,
        emotions_neutral * cnt as emotions_neutral,
        sum(mention_numbers * cnt) over(partition by flag) as total_mention_numbers
        from(
        select metrics_id, mention_numbers, emotions_positive, emotions_negative, emotions_neutral,
        case when dt &lt; #{req.startDate} then 'prev' else 'cur' end as flag,
        <choose>
            <when test="req.labelValues != null and req.labelValues.size() > 0">
                bitmap_count(bitmap_and(m.one_id_bitmap,l.label_bitmap)) as cnt
                from dimension_metrics_bitmap m
                cross join
                (
                select bitmap_intersect(label_bitmap) as label_bitmap from label_bitmap
                <where>
                    `value` in
                    <foreach collection="req.labelValues" item="b" index="index" separator="," open="(" close=")">#{b}</foreach>
                </where>
                ) l
            </when>
            <otherwise>
                1 as cnt
                from dimension_metrics m
            </otherwise>
        </choose>
        <where>
            <include refid="sqlWhereDateFull"></include>
            <include refid="sqlWhereMetricsLibrary"></include>
            <include refid="sqlWhereRegion"></include>
            <include refid="sqlWhereDatasource"></include>
            <choose>
                <when test="req.brand !=null and req.brand.length != 0">
                    and brand in
                    <foreach collection="req.brand" item="b" index="index" separator="," open="(" close=")">#{b}</foreach>
                </when>
                <otherwise>
                    and brand = '0'
                </otherwise>
            </choose>
            <include refid="sqlWhereNDealerZero"></include>
        </where>
        ) pcv
        where cnt > 0
        ) sv
        <where>
            <include refid="sqlWhereMetrics"></include>
        </where>
        group by metrics_id
        ) iv
    </select>

解释：根据程序内的choose流程控制，如果传入标签值，则和人群有关，用内部表，否则用外部表，用内部表时，需要和标签维度表做一次Join，来拿出所选标签对应的人群位图，而标签维度表由于是不同行之间的筛选，因此用bitmap_intersect做交集处理，而join后是列之间的筛选，因此用bitmap_and交集处理，最后用bitmap_count计算人数，内部表外层需要拿体验值乘上这个人数，而外部表直接就是聚合结果，不需要乘以人数，但为了代码通用性，因此在内层给cnt赋值为1，外层通过sumover开窗，提前算出本周期和上周期的提及量总量，再后面通过指标分组，只计算筛选的相应指标的结果，通过sumif进行本周期和上周期对应值的行转列，对于开窗值，因为每行都有一样的结果，因此可以直接当做分组维度，或者对其求max，最后进行相应环比和占比计算