HiveSQL——sum(if()) 条件累加

2024-02-10 19:36
文章标签 条件 累加 sum hivesql

本文主要是介绍HiveSQL——sum(if()) 条件累加,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

注:参考文章:

HiveSql面试题10--sum(if)统计问题_hive sum if-CSDN博客文章浏览阅读5.8k次,点赞6次,收藏19次。0 需求分析t_order表结构字段名含义oid订单编号uid用户idotime订单时间(yyyy-MM-dd)oamount订单金额(元)所有在2018年1月下过单并且在2月没有下过单的用户,在3月份的下单情况:目标字段名含义_hive sum ifhttps://blog.csdn.net/godlovedaniel/article/details/108325219

0  需求分析

t_order表结构如图:

  要求:t_order表扫描次数不超过2次的前提下,统计所有在2018年1月下过单且在2月份没有下过单的用户,该用户在3月份的下单情况:

1 数据准备

CREATE TABLE t_order (oid int ,uid int ,otime string,oamount int)
ROW format delimited FIELDS TERMINATED BY ",";
load data local inpath "/opt/module/hive_data/t_order.txt" into table t_order;

2  数据分析

完整代码为:

with tmp as (selectoid,uid,otime,date_format(otime, 'yyyy-MM') as                                                  dt,oamount,---计算rk的目的是为了获取记录中的第一条row_number() over (partition by uid,date_format(otime, 'yyyy-MM') order by otime) rk,--- 计算cnt的目的是为了获取记录中的最后一条count(*) over (partition by uid,date_format(otime, 'yyyy-MM'))                    cntfrom t_orderorder by uid
)
selectuid,--每个用户一月份的订单数sum(if(dt = '2018-01', 1, 0)) as                 m1_count,--每个用户二月份的订单数sum(if(dt = '2018-02', 1, 0)) as                 m2_count,--每个用户三月份的订单数(当月订单金额超过10元的订单个数)sum(if(dt = '2018-03' and oamount > 10, 1, 0))   m3_count,--当月(3月份)首次下单的金额sum(if(dt = '2018-03' and rk = 1, oamount, 0))   m3_first_amount,--当月(3月份)末次下单的金额(rk =cnt小技巧)sum(if(dt = '2018-03' and rk = cnt, oamount, 0))  m3_last_amount
from tmp
group by uid
--将下单记录转化成下单次数判断
having m1_count >0 and m2_count=0;

 最终的输出结果为:

上述代码解析:

step1: 用date_format函数进行日期格式化,row_number() over() 获得排名rk, count(*)over()获得统计值cnt

selectoid,uid,otime,date_format(otime, 'yyyy-MM') as  dt,oamount,---计算rk的目的是为了获取记录中的第一条row_number() over (partition by uid,date_format(otime, 'yyyy-MM') order by otime) rk,--- 计算cnt的目的是为了获取记录中的最后一条count(*) over (partition by uid,date_format(otime, 'yyyy-MM'))  cntfrom t_order

step2:

  • 获取当月订单金额超过10元的订单个数 :sum(if(条件, 1, 0)) 或者 sum( case when 条件 then 1 else 0 end  );
  • 获取当月首次下单金额:rk=1
  • 获取当月末次下单金额:rk=cnt (每个分组的记录数cnt 同时也等于分组内,最后一条记录数的排序值rk)
with tmp as (selectoid,uid,otime,date_format(otime, 'yyyy-MM') as                                                  dt,oamount,---计算rk的目的是为了获取记录中的第一条row_number() over (partition by uid,date_format(otime, 'yyyy-MM') order by otime) rk,--- 计算cnt的目的是为了获取记录中的最后一条count(*) over (partition by uid,date_format(otime, 'yyyy-MM'))                    cntfrom t_orderorder by uid
)
selectuid,--每个用户一月份的订单数sum(if(dt = '2018-01', 1, 0)) as                 m1_count,--每个用户二月份的订单数sum(if(dt = '2018-02', 1, 0)) as                 m2_count,--每个用户三月份的订单数(当月订单金额超过10元的订单个数)sum(if(dt = '2018-03' and oamount > 10, 1, 0))   m3_count,--当月(3月份)首次下单的金额sum(if(dt = '2018-03' and rk = 1, oamount, 0))   m3_first_amount,--当月(3月份)末次下单的金额(rk =cnt小技巧)sum(if(dt = '2018-03' and rk = cnt, oamount, 0))  m3_last_amount
from tmp
group by uid
having m1_count >0 and m2_count=0;

3 小结

   本案例用到的知识点:

  • sum(if()) 有条件累加;
  •  row_number() over(partition by ....order by ..) 排序,求分组topN
  •  count(*) over(partition by ...) 分组统计记录数。每组的记录数同时也是最后一条记录的排序值。
  • 将下单记录转化成下单次数判断 m1_count >0 and m2_count=0;

这篇关于HiveSQL——sum(if()) 条件累加的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/697906

相关文章

SpringIntegration消息路由之Router的条件路由与过滤功能

《SpringIntegration消息路由之Router的条件路由与过滤功能》本文详细介绍了Router的基础概念、条件路由实现、基于消息头的路由、动态路由与路由表、消息过滤与选择性路由以及错误处理... 目录引言一、Router基础概念二、条件路由实现三、基于消息头的路由四、动态路由与路由表五、消息过滤

Nginx中location实现多条件匹配的方法详解

《Nginx中location实现多条件匹配的方法详解》在Nginx中,location指令用于匹配请求的URI,虽然location本身是基于单一匹配规则的,但可以通过多种方式实现多个条件的匹配逻辑... 目录1. 概述2. 实现多条件匹配的方式2.1 使用多个 location 块2.2 使用正则表达式

详解如何在React中执行条件渲染

《详解如何在React中执行条件渲染》在现代Web开发中,React作为一种流行的JavaScript库,为开发者提供了一种高效构建用户界面的方式,条件渲染是React中的一个关键概念,本文将深入探讨... 目录引言什么是条件渲染?基础示例使用逻辑与运算符(&&)使用条件语句列表中的条件渲染总结引言在现代

Oracle Expdp按条件导出指定表数据的方法实例

《OracleExpdp按条件导出指定表数据的方法实例》:本文主要介绍Oracle的expdp数据泵方式导出特定机构和时间范围的数据,并通过parfile文件进行条件限制和配置,文中通过代码介绍... 目录1.场景描述 2.方案分析3.实验验证 3.1 parfile文件3.2 expdp命令导出4.总结

Python按条件批量删除TXT文件行工具

《Python按条件批量删除TXT文件行工具》这篇文章主要为大家详细介绍了Python如何实现按条件批量删除TXT文件中行的工具,文中的示例代码讲解详细,感兴趣的小伙伴可以跟随小编一起学习一下... 目录1.简介2.运行效果3.相关源码1.简介一个由python编写android的可根据TXT文件按条件批

最大流=最小割=最小点权覆盖集=sum-最大点权独立集

二分图最小点覆盖和最大独立集都可以转化为最大匹配求解。 在这个基础上,把每个点赋予一个非负的权值,这两个问题就转化为:二分图最小点权覆盖和二分图最大点权独立集。   二分图最小点权覆盖     从x或者y集合中选取一些点,使这些点覆盖所有的边,并且选出来的点的权值尽可能小。 建模:     原二分图中的边(u,v)替换为容量为INF的有向边(u,v),设立源点s和汇点t

封装MySQL操作时Where条件语句的组织

在对数据库进行封装的过程中,条件语句应该是相对难以处理的,毕竟条件语句太过于多样性。 条件语句大致分为以下几种: 1、单一条件,比如:where id = 1; 2、多个条件,相互间关系统一。比如:where id > 10 and age > 20 and score < 60; 3、多个条件,相互间关系不统一。比如:where (id > 10 OR age > 20) AND sco

如何导入sun.misc.BASE64Encoder和sum.misc.BASE64Decoder

右击项目名--->Build Path--->Configure Build Path...--->java Build Path--->Access rules:1 rule defined,added to all librar...   --->Edit --->Add...

使用条件变量实现线程同步:C++实战指南

使用条件变量实现线程同步:C++实战指南 在多线程编程中,线程同步是确保程序正确性和稳定性的关键。条件变量(condition variable)是一种强大的同步原语,用于在线程之间进行协调,避免数据竞争和死锁。本文将详细介绍如何在C++中使用条件变量实现线程同步,并提供完整的代码示例和详细的解释。 什么是条件变量? 条件变量是一种同步机制,允许线程在某个条件满足之前进入等待状态,并在条件满

一些数学经验总结——关于将原一元二次函数增加一些限制条件后最优结果的对比(主要针对公平关切相关的建模)

1.没有分段的情况 原函数为一元二次凹函数(开口向下),如下: 因为要使得其存在正解,必须满足,那么。 上述函数的最优结果为:,。 对应的mathematica代码如下: Clear["Global`*"]f0[x_, a_, b_, c_, d_] := (a*x - b)*(d - c*x);(*(b c+a d)/(2 a c)*)Maximize[{f0[x, a, b,