精华内容
下载资源
问答
  • 相关公式如下: 同比增长率计算公式 (当年值-上年值)/上年值x100% 环比增长率计算公式 (当月值-上月值)/上月值x100% 同比的话,如果每个月都齐全,都有数据lag(num,12)就ok.。我们的例子中只有19年20年1-4月份的...

    一、数据准备

    -- 创建表并插入数据
    CREATE TABLE `saleorder`  (
      `order_id` int ,
      `order_time` date ,
      `order_num` int
    ) 
    
    INSERT INTO `saleorder` VALUES 
    (1, '2020-04-20', 420),
    (2, '2020-04-04', 800),
    (3, '2020-03-28', 500),
    (4, '2020-03-13', 100),
    (5, '2020-02-27', 300),
    (6, '2020-01-07', 450),
    (7, '2019-04-07', 800),
    (8, '2019-03-15', 1200),
    (9, '2019-02-17', 200),
    (10, '2019-02-07', 600),
    (11, '2019-01-13', 300);
    


    查看表信息

    select * from saleorder;

    二、占比

    写法一:

    基本思路:用式内连接,外加嵌套找出分子分母,相除(最后要分组)

    SELECT
    	order_month,
    	num,
    	total,
    	round( num / total, 2 ) AS ratio 
    FROM
    (
    	-- 月统计
        SELECT 
    	    DATE_FORMAT(order_time,"yyyy-MM") AS order_month,
    	    sum( order_num ) AS num
        FROM    
    	saleorder 
        GROUP BY
    		DATE_FORMAT(order_time,"yyyy-MM")
    )t1,
    (
    	-- 年统计
        SELECT 
    	    year(order_time) AS order_year,
    	    sum( order_num ) AS total
        FROM 
    	saleorder 
        GROUP BY
    		year(order_time)
    )t2 
    where substr(t1.order_month,1,4) = t2.order_year;
    

    写法二:显示内连接

    基本思路:显示内联接,先分组、汇总–>笛卡尔积连接–>相除
    提示: 时间处理的时候除了用date_formate()也可以用substr()函数来截取年月日格式

    SELECT
    	order_month,
    	num,
    	total,
    	round(num/total,2) as ratio
    FROM
    (
        SELECT
    	    substr( order_time, 1, 7 ) AS order_month,
    	    sum( order_num ) AS num 
        FROM
    	    saleorder 
        GROUP BY
    	    substr( order_time, 1, 7 ) 
    ) t1
    INNER JOIN 
    (
        SELECT
    	    substr( order_time, 1, 4 ) AS order_year,
    	    sum( order_num ) AS total 
        FROM
    	    saleorder
        GROUP BY
    	    substr( order_time, 1, 4 ) 
    ) t2 
    ON substr( order_month, 1, 4 ) = t2.order_year ;
    

    写法三:开窗函数

    SELECT 
    	order_month,
    	num,
    	total,
    	round( num / total, 2 ) AS ratio 
    FROM 
    (
        select 
            substr(order_time, 1, 7) as order_month, 
            sum(order_num) over (partition by substr(order_time, 1, 7)) as num,
            sum( order_num ) over ( PARTITION BY substr( order_time, 1, 4 ) ) total,
            row_number() over (partition by substr(order_time, 1, 7)) as rk 
        from saleorder
    ) temp 
    where rk = 1;
    

    注意:

    (1) 时间处理的时候除了用date_formate()也可以用substr()函数来截取年月日格式
    (2)当我们求的占比分子分母没有时间维度只有数量的时候,我们可以采用on 1=1 来进行关联,构造成笛卡尔积
    

    三、环比

    与上年度数据对比称"同比",与上月数据对比称"环比"。
    相关公式如下:

    同比增长率计算公式
    (当年值-上年值)/上年值x100% 
    
    环比增长率计算公式
    (当月值-上月值)/上月值x100% 
    

    实现:

    select 
        now_month,
        now_num,
        last_num,
        round( (now_num-last_num) / last_num, 2 ) as ratio
    FROM 
    (
        select 
            now_month,
            now_num, 
            lag( t1.now_num, 1 ) over (order by t1.now_month ) as last_num 
        from 
        (
            select 
                substr(order_time, 1, 7) as now_month, 
                sum(order_num) as now_num 
            from saleorder 
            group by 
                substr(order_time, 1, 7) 
        ) t1
    ) t2;
    

    结果优化一下:

    select 
        now_month,
        now_num,
        last_num,
        -- round( (now_num-last_num) / last_num, 2 ) as ratio
        concat( nvl ( round( ( now_num - last_num ) / last_num * 100, 2 ), 0 ), "%" ) 
    FROM 
    (
        select 
            now_month,
            now_num, 
            lag( t1.now_num, 1 ) over (order by t1.now_month ) as last_num 
        from 
        (
            select 
                substr(order_time, 1, 7) as now_month, 
                sum(order_num) as now_num 
            from saleorder 
            group by 
                substr(order_time, 1, 7) 
        ) t1
    ) t2;
    

    四、同比

    与上年度数据对比称"同比",与上月数据对比称"环比"。
    相关公式如下:

    同比增长率计算公式
    (当年值-上年值)/上年值x100% 
    
    环比增长率计算公式
    (当月值-上月值)/上月值x100% 
    

    同比的话,如果每个月都齐全,都有数据lag(num,12)就ok.。我们的例子中只有19年和20年1-4月份的数据。这种特殊情况应该如何处理?

    写法一:本案例进行单独处理

    有4个月数据,我就lag(num,4)

    select 
        now_month,
        now_num,
        last_num,
        round( (now_num-last_num) / last_num, 2 ) as ratio 
    FROM 
    (
        select 
            now_month,
            now_num, 
            lag( t1.now_num, 4 ) over (order by t1.now_month ) as last_num 
        from 
        (
            select 
                substr(order_time, 1, 7) as now_month, 
                sum(order_num) as now_num 
            from saleorder 
            group by 
                substr(order_time, 1, 7) 
        ) t1
    ) t2;
    

    优化:
    对空值可以做一下优化处理,用到nvl()函数和lag()函数的第三个参数。

    select 
        now_month,
        now_num,
        last_num,
        nvl ( round( ( now_num - last_num ) / last_num, 2 ), 0 ) AS ratio 
    FROM 
    (
        select 
            now_month,
            now_num, 
            lag( t1.now_num, 4, 0 ) over (order by t1.now_month ) as last_num 
        from 
        (
            select 
                substr(order_time, 1, 7) as now_month, 
                sum(order_num) as now_num 
            from saleorder 
            group by 
                substr(order_time, 1, 7) 
        ) t1
    ) t2;
    

    写法二:通用方法

    基本思路:利用date_add()生成跨年时间

    SELECT
    	t1.now_month,
        CASE WHEN now_num IS NULL OR now_num = 0 
    	    THEN 0 ELSE now_num 
            END now_num,
        CASE WHEN last_num IS NULL OR last_num = 0 
            THEN 0 ELSE last_num 
    		END last_num,
        CASE WHEN last_num IS NULL OR last_num = 0 
            THEN 0 ELSE round( ( now_num - last_num ) / last_num, 2 ) 
    		END ratio 
    FROM
    (
    	SELECT
    		DATE_FORMAT( order_time, 'yyyy-MM' ) AS now_month,
    		sum( order_num ) AS now_num 
    	FROM
    		saleorder 
    	GROUP BY
    		DATE_FORMAT( order_time, 'yyyy-MM' ) 
    ) t1
    LEFT JOIN 
    (
    	SELECT
    		DATE_FORMAT( DATE_ADD( order_time, 365 ), 'yyyy-MM' ) AS now_month,
    		sum( order_num ) AS last_num 
    	FROM
    		saleorder 
    	GROUP BY
    	DATE_FORMAT( DATE_ADD( order_time, 365 ), 'yyyy-MM' ) 
    ) AS t2 ON t1.now_month = t2.now_month;
    

    优化:
    用nvl()代替case when

    SELECT
    	t1.now_month,
        nvl ( now_num, 0 ) AS now_num,
    	nvl ( last_num, 0 ) AS last_num,
    	nvl ( round( ( now_num - last_num ) / last_num, 2 ), 0 ) AS ratio 
    FROM
    (
    	SELECT
    		DATE_FORMAT( order_time, 'yyyy-MM' ) AS now_month,
    		sum( order_num ) AS now_num 
    	FROM
    		saleorder 
    	GROUP BY
    		DATE_FORMAT( order_time, 'yyyy-MM' ) 
    ) t1
    LEFT JOIN 
    (
    	SELECT
    		DATE_FORMAT( DATE_ADD( order_time, 365 ), 'yyyy-MM' ) AS now_month,
    		sum( order_num ) AS last_num 
    	FROM
    		saleorder 
    	GROUP BY
    	DATE_FORMAT( DATE_ADD( order_time, 365 ), 'yyyy-MM' ) 
    ) AS t2 ON t1.now_month = t2.now_month;
    

    展开全文
  • 相关公式如下: 同比增长率计算公式 (当年值-上年值)/上年值x100% 环比增长率计算公式 (当月值-上月值)/上月值x100% 同比的话,如果每个月都齐全,都有数据lag(num,12)就ok.。我们的例子中只有19年20年1-4月份的...

    一、数据准备

    -- 创建表并插入数据
    CREATE TABLE `saleorder`  (
      `order_id` int ,
      `order_time` date ,
      `order_num` int
    ) 
    
    -- ----------------------------
    -- Records of saleorder
    -- ----------------------------
    INSERT INTO `saleorder` VALUES 
    (1, '2020-04-20', 420),
    (2, '2020-04-04', 800),
    (3, '2020-03-28', 500),
    (4, '2020-03-13', 100),
    (5, '2020-02-27', 300),
    (6, '2020-01-07', 450),
    (7, '2019-04-07', 800),
    (8, '2019-03-15', 1200),
    (9, '2019-02-17', 200),
    (10, '2019-02-07', 600),
    (11, '2019-01-13', 300);
    

    查看表信息

    select * from saleorder;
    

    查看表信息

    二、占比

    写法一:

    基本思路:用隐式内连接,外加嵌套找出分子分母,相除(最后要分组)

    SELECT
    	order_month,
    	num,
    	total,
    	round( num / total, 2 ) AS ratio 
    FROM
    (
    	-- 月统计
        SELECT 
    	    DATE_FORMAT(order_time,"yyyy-MM") AS order_month,
    	    sum( order_num ) AS num
        FROM    
    	saleorder 
        GROUP BY
    		DATE_FORMAT(order_time,"yyyy-MM")
    )t1,
    (
    	-- 年统计
        SELECT 
    	    year(order_time) AS order_year,
    	    sum( order_num ) AS total
        FROM 
    	saleorder 
        GROUP BY
    		year(order_time)
    )t2 
    where substr(t1.order_month,1,4) = t2.order_year;
    

    最后结果

    写法二:显示内连接

    基本思路:显示内联接,先分组、汇总–>笛卡尔积连接–>相除
    友情提示: 时间处理的时候除了用date_formate()也可以用substr()函数来截取年月日格式

    SELECT
    	order_month,
    	num,
    	total,
    	round(num/total,2) as ratio
    FROM
    (
        SELECT
    	    substr( order_time, 1, 7 ) AS order_month,
    	    sum( order_num ) AS num 
        FROM
    	    saleorder 
        GROUP BY
    	    substr( order_time, 1, 7 ) 
    ) t1
    INNER JOIN 
    (
        SELECT
    	    substr( order_time, 1, 4 ) AS order_year,
    	    sum( order_num ) AS total 
        FROM
    	    saleorder
        GROUP BY
    	    substr( order_time, 1, 4 ) 
    ) t2 
    ON substr( order_month, 1, 4 ) = t2.order_year ;
    

    最后结果

    写法三:开窗函数

    SELECT 
    	order_month,
    	num,
    	total,
    	round( num / total, 2 ) AS ratio 
    FROM 
    (
        select 
            substr(order_time, 1, 7) as order_month, 
            sum(order_num) over (partition by substr(order_time, 1, 7)) as num,
            sum( order_num ) over ( PARTITION BY substr( order_time, 1, 4 ) ) total,
            row_number() over (partition by substr(order_time, 1, 7)) as rk 
        from saleorder
    ) temp 
    where rk = 1;
    

    最后结果

    注意:

    (1) 时间处理的时候除了用date_formate()也可以用substr()函数来截取年月日格式
    (2)当我们求的占比分子分母没有时间维度只有数量的时候,我们可以采用on 1=1 来进行关联,构造成笛卡尔积
    

    三、环比

    与上年度数据对比称"同比",与上月数据对比称"环比"。
    相关公式如下:

    同比增长率计算公式
    (当年值-上年值)/上年值x100% 
    
    环比增长率计算公式
    (当月值-上月值)/上月值x100% 
    

    实现:

    select 
        now_month,
        now_num,
        last_num,
        round( (now_num-last_num) / last_num, 2 ) as ratio
    FROM 
    (
        select 
            now_month,
            now_num, 
            lag( t1.now_num, 1 ) over (order by t1.now_month ) as last_num 
        from 
        (
            select 
                substr(order_time, 1, 7) as now_month, 
                sum(order_num) as now_num 
            from saleorder 
            group by 
                substr(order_time, 1, 7) 
        ) t1
    ) t2;
    

    环比结果
    也可以对显示结果稍微优化一下:

    select 
        now_month,
        now_num,
        last_num,
        -- round( (now_num-last_num) / last_num, 2 ) as ratio
        concat( nvl ( round( ( now_num - last_num ) / last_num * 100, 2 ), 0 ), "%" ) 
    FROM 
    (
        select 
            now_month,
            now_num, 
            lag( t1.now_num, 1 ) over (order by t1.now_month ) as last_num 
        from 
        (
            select 
                substr(order_time, 1, 7) as now_month, 
                sum(order_num) as now_num 
            from saleorder 
            group by 
                substr(order_time, 1, 7) 
        ) t1
    ) t2;
    

    最后结果

    四、同比

    与上年度数据对比称"同比",与上月数据对比称"环比"。
    相关公式如下:

    同比增长率计算公式
    (当年值-上年值)/上年值x100% 
    
    环比增长率计算公式
    (当月值-上月值)/上月值x100% 
    

    同比的话,如果每个月都齐全,都有数据lag(num,12)就ok.。我们的例子中只有19年和20年1-4月份的数据。这种特殊情况应该如何处理?

    写法一:本案例进行单独处理

    有4个月数据,我就lag(num,4)

    select 
        now_month,
        now_num,
        last_num,
        round( (now_num-last_num) / last_num, 2 ) as ratio 
    FROM 
    (
        select 
            now_month,
            now_num, 
            lag( t1.now_num, 4 ) over (order by t1.now_month ) as last_num 
        from 
        (
            select 
                substr(order_time, 1, 7) as now_month, 
                sum(order_num) as now_num 
            from saleorder 
            group by 
                substr(order_time, 1, 7) 
        ) t1
    ) t2;
    

    最终结果
    优化:
    对空值可以做一下优化处理,用到nvl()函数和lag()函数的第三个参数。

    select 
        now_month,
        now_num,
        last_num,
        nvl ( round( ( now_num - last_num ) / last_num, 2 ), 0 ) AS ratio 
    FROM 
    (
        select 
            now_month,
            now_num, 
            lag( t1.now_num, 4, 0 ) over (order by t1.now_month ) as last_num 
        from 
        (
            select 
                substr(order_time, 1, 7) as now_month, 
                sum(order_num) as now_num 
            from saleorder 
            group by 
                substr(order_time, 1, 7) 
        ) t1
    ) t2;
    

    最终结果

    写法二:通用方法

    基本思路:利用date_add()生成跨年时间

    SELECT
    	t1.now_month,
        CASE WHEN now_num IS NULL OR now_num = 0 
    	    THEN 0 ELSE now_num 
            END now_num,
        CASE WHEN last_num IS NULL OR last_num = 0 
            THEN 0 ELSE last_num 
    		END last_num,
        CASE WHEN last_num IS NULL OR last_num = 0 
            THEN 0 ELSE round( ( now_num - last_num ) / last_num, 2 ) 
    		END ratio 
    FROM
    (
    	SELECT
    		DATE_FORMAT( order_time, 'yyyy-MM' ) AS now_month,
    		sum( order_num ) AS now_num 
    	FROM
    		saleorder 
    	GROUP BY
    		DATE_FORMAT( order_time, 'yyyy-MM' ) 
    ) t1
    LEFT JOIN 
    (
    	SELECT
    		DATE_FORMAT( DATE_ADD( order_time, 365 ), 'yyyy-MM' ) AS now_month,
    		sum( order_num ) AS last_num 
    	FROM
    		saleorder 
    	GROUP BY
    	DATE_FORMAT( DATE_ADD( order_time, 365 ), 'yyyy-MM' ) 
    ) AS t2 ON t1.now_month = t2.now_month;
    

    最后结果
    优化:
    用nvl()代替case…when

    SELECT
    	t1.now_month,
        nvl ( now_num, 0 ) AS now_num,
    	nvl ( last_num, 0 ) AS last_num,
    	nvl ( round( ( now_num - last_num ) / last_num, 2 ), 0 ) AS ratio 
    FROM
    (
    	SELECT
    		DATE_FORMAT( order_time, 'yyyy-MM' ) AS now_month,
    		sum( order_num ) AS now_num 
    	FROM
    		saleorder 
    	GROUP BY
    		DATE_FORMAT( order_time, 'yyyy-MM' ) 
    ) t1
    LEFT JOIN 
    (
    	SELECT
    		DATE_FORMAT( DATE_ADD( order_time, 365 ), 'yyyy-MM' ) AS now_month,
    		sum( order_num ) AS last_num 
    	FROM
    		saleorder 
    	GROUP BY
    	DATE_FORMAT( DATE_ADD( order_time, 365 ), 'yyyy-MM' ) 
    ) AS t2 ON t1.now_month = t2.now_month;
    

    效果是一样的
    最终结果

    展开全文
  • Hive计算环比同比

    千次阅读 2019-12-17 13:58:24
    -- (本周期 - 上周期) / 上周期 select date_time ...,(val-val7)/val7 as `周同比` from ( select date_time ,val ,lag(val,1) over(order by date_time) as val1 ,lag(val,7) over(order b...

    1. 方式一

    在这里插入图片描述

    select
    num                                       -- 数量
    ,( num-num_1ago )/num_1ago                -- 日环比
    ,( num-num_7ago )/num_7ago                -- 周同比
    
    ,concat( round( X/10000,1 ),'万' )        -- 保留一位小数,拼接上万
    ,concat( round( X*100,2 ),'%' )           -- 格式化成百分数
    
    ,lpad( cast( X as string ),5,'0' )        -- 不够5位往左边补充0,支持mysql hive oracle
    ,rpad( cast( X as string ),5,'0' )        -- 不够5位往右边补充0,支持mysql hive oracle
    from A
    
    left join B
    on A.uin = B.uin
    
    left join C
    on A.uin = C.uin
    

    2. 方式二

    在这里插入图片描述

    select
    ds
    ,val
    ,( val-val_1ago )/val_1ago as `日环比`
    ,( val-val_7ago )/val_7ago as `周同比`
    from (
        select
        ds
        ,val
        ,lag( val,1 ) over( order by ds ) as val_1ago
        ,lag( val,7 ) over( order by ds ) as val_7ago
        from t_md_sosomap_user_uin
        where ds = 20210101
    ) tmp
    

    注意: lag表示往上面去N行, 这就要求往上面取7行,必须是LASTDAY_7

    展开全文
  • Hive计算绝对值同环比

    千次阅读 2019-06-25 00:30:05
    什么是绝对值同比 本期数据-同期数据/|同期数据| 例: 2019年1月1日的gmv -2018年1月1日的gmv/|2018年1月1日的gmv| 什么是绝对值环比 本期数据-上期数据/|上期数据| 例: 2019年2月2日的gmv -2018年2月1日的gmv/|...

    环比增长率和同比增长率

    什么是绝对值同比

    本期数据-同期数据/|同期数据|
    例: 2019年1月1日的gmv -2018年1月1日的gmv/|2018年1月1日的gmv|

    什么是绝对值环比

    本期数据-上期数据/|上期数据|
    例: 2019年2月2日的gmv -2018年2月1日的gmv/|2018年2月1日的gmv|

    数据集准备

    建表语句

    create table new_table(
    dt string,
    area string,
    province string,
    saleroom int
    );
    

    数据准备

    insert into new_table values('2017-12-01', 'hd', 'sh','3600000');
    insert into new_table values('2017-12-02', 'hd', 'js','2800000');
    insert into new_table values('2017-12-03', 'hd', 'zj','4500000');
    insert into new_table values('2017-12-04', 'hb', 'bj','3000000');
    insert into new_table values('2017-12-05', 'hb', 'tj','2800000');
    insert into new_table values('2018-12-01', 'hd', 'sh','3000000');
    insert into new_table values('2018-12-02', 'hd', 'js','2000000');
    insert into new_table values('2018-12-03', 'hd', 'zj','2500000');
    insert into new_table values('2018-12-04', 'hb', 'bj','2600000');
    insert into new_table values('2018-12-05', 'hb', 'tj','1500000');
    

    同比计算

    with tmp as (
    select 
    dt,
    area,
    province,
    saleroom,
    lag(saleroom,1,0) over(partition by concat(month(dt),"-",day(dt)),area,province order by dt asc) pre_sale
    from new_table)
    select 
    dt,area,province,saleroom,pre_sale,
    if(round((saleroom-pre_sale)/abs(pre_sale)*1.00,2) is null,100,round((saleroom-pre_sale)/abs(pre_sale)*1.00,2)*100) 
    from tmp;
    

    环比计算

    with tmp as (
    select 
    dt,
    area,
    province,
    saleroom,
    lag(saleroom,1,0) over(partition by concat(month(dt),"-",day(dt)),area,province order by dt asc) pre_sale
    from new_table)
    select 
    dt,area,province,saleroom,pre_sale,
    if(round((saleroom-pre_sale)/abs(pre_sale)*1.00,2) is null,100,round((saleroom-pre_sale)/abs(pre_sale)*1.00,2)*100) 
    from tmp ;
    
    
    展开全文
  • 稍加分析可知,这是一道典型的求同比环比类型的题目。知识点梳理同比一般情况下是今年第n月与去年第n月比。同比发展速度主要是为了消除季节变动的影响,用以说明本期发展水平与去年同期发展水平对比而达到的相对...
  • 同比环比计算 测试数据 1,2020-04-20,420 2,2020-04-04,800 3,2020-03-28,500 4,2020-03-13,100 5,2020-02-27,300 6,2020-01-07,450 7,2019-04-07,800 8,2019-03-15,1200 9,2019-02-17,200 10,2019-02-07,600 11,...
  • 当天 ‘${zdt.format(“yyyy-MM-dd”)}’ 昨天 ‘${zdt.addDay(-1).format(‘yyyy-MM-dd’)}’ 前30天 ‘${zdt.addDay(-30).format(‘yyyy-MM-dd’)}’ 本周-上周四至本周三 between date_sub(’${zdt.format(“yyyy...
  • 稍加分析可知,这是一道典型的求同比环比类型的题目。 知识点梳理同比一般情况下是今年第n月与去年第n月比。同比发展速度主要是为了消除季节变动的影响,用以说明本期发展水平与去年同期发展水平对比而达到的相对...
  • 说明:同比:同去年的今天进行比较 环比:同上个月的今天进行比较 环比计算 一、先看一下数据,这里只能进行7.10号6.10号进行对比,比到12号 这里是引用±------------------------±-----------------------±--...
  • 这篇文章旨在解决相邻两个月的统计值环比问题,比如说指标是饭店的自然月客流量指标,这个指标在月初月中进行数值环比没有一点问题,但是相邻两个月月末一对比就会出现问题: 比如说4月5月,那么就应该拿5.31号...
  • 窗口函数:计算环比和同比

    千次阅读 2019-03-27 12:45:47
    0 前言 环比,表示连续2个单位周期(比如连续两月)内的量的... 同比一般情况下是今年第n月与去年第n月比,用以说明本期发展水平与去年同期发展水平对比而达到的相对发展速度,计算公式:同比增长速度=(本期发展...
  • 目录1、说明同比和环比2、add_months函数介绍3、同比实战4、同比 1、说明 同比和环比 同比:同去年的今天进行比较 环比:同上个月的今天进行比较 2、add_months函数介绍 -1 代表减一个月; +1 代表加一个月; select ...

空空如也

空空如也

1 2 3 4 5 ... 11
收藏数 209
精华内容 83
关键字:

hivesql计算同比和环比

友情链接: VBRezQv33a.rar