当前位置: 首页 > article >正文

HiveSQL——连续增长问题

注:参考文章:

SQL连续增长问题--HQL面试题35_sql判断一个列是否连续增长-CSDN博客文章浏览阅读2.6k次,点赞6次,收藏30次。目录0 需求分析1 数据准备3 小结0 需求分析假设我们有一张订单表shop_order shop_id,order_id,order_time,order_amt 我们需要计算过去至少3天销售金额连续增长的商户shop_id。数据如下:shop_idorder_amtorder_time11002021-05-10 10:03:5411012021-05-10 10:04:5413002021-0_sql判断一个列是否连续增长https://blog.csdn.net/godlovedaniel/article/details/119080882

0 需求分析

  现有一张订单表shop_order ,含有字段shop_id,order_id,order_time,order_amt, 需要统计过去至少连续3天销售金额连续增长的商户shop_id。

1 数据准备

create table shop_order(
    shop_id int,
    order_amt int,
    order_time string
)
row format delimited fields terminated by '\t';
load data local inpath "/opt/module/hive_data/shop_order.txt" into table shop_order;

2 数据分析

   完整的代码如下:

with tmp as (
    select
        shop_id,
        to_date(order_time) as dt,
        sum(order_amt)      as amt
    from shop_order
    group by shop_id, to_date(order_time)
)
select
    shop_id
from (select *,
            -- 判断日期是否连续
             date_sub(dt, row_number() over (partition by shop_id order by dt )) as order_date_diff
      from (
               select
                   shop_id,
                   dt,
                   amt,
             --判断销售额是否增长
             -- 当前行的销售金额与上一行的销售金额之间的差值 order_amt_diff
                   amt - lag(amt, 1, 0) over (partition by shop_id order by dt) as order_amt_diff 
               from tmp
           ) t1
        -- 差值大于0的代表销售额增长
      where order_amt_diff > 0
     ) t2
group by shop_id, order_date_diff
having count(1) >=3;

输出结果为 shop_id 为2

上述代码分析:

 step1: 求出每家商户销售金额连续增长的记录

with tmp as (
    select
        shop_id,
        to_date(order_time) as dt,
        sum(order_amt)      as amt
    from shop_order
    group by shop_id, to_date(order_time)
)

select *
from (
         select
             shop_id,
             dt,
             amt,
             --判断销售额是否增长
             -- 当前行的销售金额与上一行的销售金额之间的差值 order_amt_diff
             amt - lag(amt, 1, 0) over (partition by shop_id order by dt) as order_amt_diff
         from tmp
     ) t1
        -- 差值大于0的代表销售额增长
where order_amt_diff > 0

 step2: 求出每家商户至少连续3天销售金额连续增长,在step1的基础上,还要求dt是连续的

with tmp as (
    select
        shop_id,
        to_date(order_time) as dt,
        sum(order_amt)      as amt
    from shop_order
    group by shop_id, to_date(order_time)
)

select *,
      -- 判断日期是否连续
       date_sub(dt, row_number() over (partition by shop_id order by dt )) as order_date_diff
from (
         select
             shop_id,
             dt,
             amt,
             --判断销售额是否增长
             -- 当前行的销售金额与上一行的销售金额之间的差值 order_amt_diff
             amt - lag(amt, 1, 0) over (partition by shop_id order by dt) as order_amt_diff
         from tmp
     ) t1
    -- 差值大于0的代表销售额增长
where order_amt_diff > 0

step3: 对商户shop_id以及日期差值order_date_diff这两个字段分组,求出最终结果

with tmp as (
    select
        shop_id,
        to_date(order_time) as dt,
        sum(order_amt)      as amt
    from shop_order
    group by shop_id, to_date(order_time)
)
select
    shop_id
from (select *,
            -- 判断日期是否连续
             date_sub(dt, row_number() over (partition by shop_id order by dt )) as order_date_diff
      from (
               select
                   shop_id,
                   dt,
                   amt,
             --判断销售额是否增长
             -- 当前行的销售金额与上一行的销售金额之间的差值 order_amt_diff
                   amt - lag(amt, 1, 0) over (partition by shop_id order by dt) as order_amt_diff --判断是否增长
               from tmp
           ) t1
        -- 差值大于0的代表销售额增长
      where order_amt_diff > 0
     ) t2
group by shop_id, order_date_diff
having count(1) >=3;

3 小结

   date_sub(日期减少函数)

  • 语法:date_sub(string startdate,int days)
  • 返回值:string
  • 说明:返回   开始日期startdate 减去days天后的日期
  • 举例:select  date_sub('2024-02-01',3) --->2024-01-29

lag

  • 语法:lag(column,n,default) over(partition by ....order by....)
  • 说明:取得column列前边的第n行数据,如果存在则返回,如果不存在,返回默认值default

     针对【日期连续】等类型的题型,一般处理思路:先计算date_sub(dt, row_number() over (partition by shop_id order by dt )) as dt_diff ,再对dt_diff 分组,求count()值

    针对【xx连续增长】等类型的题型,一般处理思路:利用前后函数lag或者lead往前/往后取一行,计算两者的差值diff,再利用 if( diff >0,1,0) as flag 等条件判断函数 进行打标签,基于标签再进行后续的分组计算.......


http://www.kler.cn/a/234091.html

相关文章:

  • 【ArcGIS Pro微课1000例】0064:栅格目录、栅格数据集、镶嵌数据集
  • GUI07-学工具栏,懂MVC
  • leetcode212. 单词搜索 II
  • Burp与其他安全工具联动及代理设置教程
  • Kali操作系统简单介绍
  • PHP 微信棋牌开发全解析:高级教程
  • ECMAScript Modules规范的示例详解
  • 微信小程序(四十)API的封装与调用
  • 【J2EE笔记】2万字!我当时学习时做的笔记
  • vue3 之 商城项目—项目搭建起步
  • JAVA学习笔记9
  • 力扣[面试题 01.02. 判定是否互为字符重排(哈希表,位图)
  • 使用client-only 解决组件不兼容SSR问题
  • 【十四】【C++】list 的常见用法
  • 【Qt 学习之路】在 Qt 使用 ZeroMQ
  • Rust基础拾遗--核心功能
  • 深入探究 HTTP 简化:httplib 库介绍
  • 用EXCEL从地址(上海)中提取各区(浦东新区等区)信息
  • 【笔记】Harmony学习:下载安装 DevEco Studio 开发工具IDE
  • Stable Diffusion 模型下载:GhostMix(幽灵混合)
  • 海外云手机——平台引流的重要媒介
  • NAS如何成为生产力?使用绿联DX4600 Pro搭建图床并实现创作自由
  • Solidworks:平面草图练习
  • 前端小案例——动态导航栏文字(HTML + CSS, 附源码)
  • C# CAD交互界面-自定义面板集-添加快捷命令(五)
  • 机器学习2--逻辑回归(案列)