【Hive SQL 每日一题】统计用户连续下单的日期区间

本文涉及的产品
实时数仓Hologres,5000CU*H 100GB 3个月
实时计算 Flink 版,5000CU*H 3个月
智能开放搜索 OpenSearch行业算法版,1GB 20LCU 1个月
简介: 该SQL代码用于统计用户连续下单的日期区间。首先按`user_id`和`order_date`分组并去除重复,然后使用`row_number()`标记行号,并通过`date_sub`与行号计算潜在的连续日期。接着按用户ID和计算后的日期分组,排除连续订单数少于2的情况,最后提取连续下单的起始和结束日期。输出结果展示了用户连续下单的日期范围。

@[toc]

测试数据

create table test(user_id string,order_date string);

INSERT INTO test(user_id, order_date) VALUES('101', '2021-09-21'),('101', '2021-09-22'),('101', '2021-09-23'),('101', '2021-09-27'),('101', '2021-09-28'),('101', '2021-09-29'),('101', '2021-09-30'),('102', '2021-10-01'),('102', '2021-10-02'),('102', '2021-10-05'),('102', '2021-10-06'),('102', '2021-10-07'),('106', '2021-10-04'),('106', '2021-10-05'),('106', '2021-10-08'),('107', '2021-10-05'),('107', '2021-10-06');

需求说明

统计用户连续下单的日期区间,所以连续的下单日期必须 >= 2,例如:2023-01-01,2023-01-02

分析步骤如下:

  1. user_idorder_date 进行分组,同天的下单日期只保留一条。

  2. 使用 row_number 窗口函数对行号进行标记。

  3. 使用 date_sub 函数与行号标记进行运算,如果数据连续的话,那么运算后的日期必然是一样的。

  4. user_iddate_sub 运算后日期进行分组,过滤数量 < 2 的分组,最大值与最小值日期统计。

需求实现

select
    user_id,
    min(order_date) order_start_date,
    max(order_date) order_end_date
from
    (select
        user_id,
        order_date,
        date_sub(order_date,rn) same_day
    from
        (select
            user_id,
            order_date,
            row_number() over (partition by user_id order by order_date) rn
        from
            test
        group by
            user_id,
            order_date )t1 -- 分组后进行行号标记
       )t2 -- 使用日期和行号进行运算
group by
    user_id,same_day
having
    count(user_id) >= 2;

输出结果

image.png

解决这题的关键是使用 row_number 窗口函数进行行号标记,然后和 date_sub 进行运算,如果日期是连续的,那么运算结果得到的日期就是一致的,如下所示:

date        rn
2023-05-04     1
2023-05-05     2
2023-05-06     3

运算后,日期结果都为 2023-05-03,显然该日期是连续的,利用这一特性完成该需求。

相关文章
|
6月前
|
SQL 分布式计算 资源调度
Dataphin功能Tips系列(48)-如何根据Hive SQL/Spark SQL的任务优先级指定YARN资源队列
如何根据Hive SQL/Spark SQL的任务优先级指定YARN资源队列
165 4
|
11月前
|
SQL 分布式计算 Hadoop
Hadoop-12-Hive 基本介绍 下载安装配置 MariaDB安装 3台云服务Hadoop集群 架构图 对比SQL HQL
Hadoop-12-Hive 基本介绍 下载安装配置 MariaDB安装 3台云服务Hadoop集群 架构图 对比SQL HQL
254 3
|
11月前
|
SQL 开发框架 .NET
sql server日期时间函数
sql server日期时间函数
145 2
|
11月前
|
SQL 关系型数据库 MySQL
SQL日期函数
SQL日期函数
158 0
|
11月前
|
SQL 分布式计算 Java
大数据-96 Spark 集群 SparkSQL Scala编写SQL操作SparkSQL的数据源:JSON、CSV、JDBC、Hive
大数据-96 Spark 集群 SparkSQL Scala编写SQL操作SparkSQL的数据源:JSON、CSV、JDBC、Hive
217 0
|
11月前
|
SQL 分布式计算 关系型数据库
Hadoop-24 Sqoop迁移 MySQL到Hive 与 Hive到MySQL SQL生成数据 HDFS集群 Sqoop import jdbc ETL MapReduce
Hadoop-24 Sqoop迁移 MySQL到Hive 与 Hive到MySQL SQL生成数据 HDFS集群 Sqoop import jdbc ETL MapReduce
269 0
|
SQL 关系型数据库 MySQL
【超全整理】SQL日期与时间函数大汇总会:MySQL与SQL Server双轨对比教学,助你轻松搞定时间数据处理难题!
【8月更文挑战第31天】本文介绍了在不同SQL数据库系统(如MySQL、SQL Server、Oracle)中常用的日期与时间函数,包括DATE、NOW()、EXTRACT()、DATE_ADD()、TIMESTAMPDIFF()及日期格式化等,并提供了具体示例。通过对比这些函数在各系统中的使用方法,帮助开发者更高效地处理日期时间数据,满足多种应用场景需求。
1451 1
|
Java 应用服务中间件 Maven
从零到英雄:一步步构建你的首个 JSF 应用程序,揭开 JavaServer Faces 的神秘面纱
【8月更文挑战第31天】JavaServer Faces (JSF) 是一种强大的 Java EE 标准,用于构建企业级 Web 应用。它提供了丰富的组件库和声明式页面描述语言 Facelets,便于开发者快速开发功能完善且易于维护的 Web 应用。本文将指导你从零开始构建一个简单的 JSF 应用,包括环境搭建、依赖配置、Managed Bean 编写及 Facelets 页面设计。
234 0
|
SQL 数据采集 数据挖掘
大数据行业应用之Hive数据分析航班线路相关的各项指标
大数据行业应用之Hive数据分析航班线路相关的各项指标
331 1
|
4月前
|
SQL 分布式计算 大数据
大数据新视界 --大数据大厂之Hive与大数据融合:构建强大数据仓库实战指南
本文深入介绍 Hive 与大数据融合构建强大数据仓库的实战指南。涵盖 Hive 简介、优势、安装配置、数据处理、性能优化及安全管理等内容,并通过互联网广告和物流行业案例分析,展示其实际应用。具有专业性、可操作性和参考价值。
大数据新视界 --大数据大厂之Hive与大数据融合:构建强大数据仓库实战指南