【Hive SQL 每日一题】统计最近7天内连续下单3日的用户量

本文涉及的产品
实时计算 Flink 版,5000CU*H 3个月
检索分析服务 Elasticsearch 版,2核4GB开发者规格 1个月
大数据开发治理平台 DataWorks,不限时长
简介: 创建了一个名为`sales`的测试表,包含`user_id`、`product_id`、`quantity`和`sale_date`字段,插入了多条销售数据。需求是找出最近7天内连续下单3天的用户数量。SQL查询通过分组和窗口函数`row_number()`检查日期连续性,最终计算满足条件的唯一用户数。示例结果显示有3名用户符合条件。

@[toc]

测试数据

drop table if exists sales;
create table if not exists sales(
user_id int,
product_id int,
quantity int,
sale_date string);

INSERT INTO sales (user_id, product_id, quantity, sale_date) VALUES
(1001, 100, 1, '2024-05-16'),
(1001, 101, 3, '2024-05-15'),
(1001, 102, 2, '2024-05-14'),
(1001, 101, 2, '2024-05-14'),
(1002, 101, 3, '2024-05-17'),
(1002, 101, 3, '2024-05-16'),
(1002, 101, 2, '2024-05-15'),
(1002, 100, 1, '2024-05-15'),
(1002, 102, 1, '2024-05-14'),
(1002, 101, 3, '2024-05-11'),
(1002, 101, 2, '2024-05-10'),
(1003, 101, 3, '2024-05-17'),
(1003, 101, 3, '2024-05-16'),
(1003, 101, 2, '2024-05-14'),
(1003, 102, 1, '2024-05-13'),
(1003, 101, 2, '2024-05-10'),
(1004, 101, 3, '2024-05-17'),
(1004, 101, 2, '2024-05-14'),
(1004, 101, 2, '2024-05-10'),
(1005, 101, 3, '2024-05-17'),
(1005, 101, 4, '2024-05-16'),
(1005, 100, 2, '2024-05-16'),
(1005, 101, 1, '2024-05-16'),
(1005, 101, 2, '2024-05-15'),
(1005, 101, 3, '2024-05-13'),
(1005, 101, 2, '2024-05-12'),
(1005, 101, 2, '2024-05-11');

字段说明

  • user_id:用户ID;
  • product_id:商品ID;
  • quantity:商品销售数量;
  • sale_date:商品销售日期。

需求说明

统计最近 7 天内连续下单 3 日的用户量(假设今天为 2024-05-18)。

结果示例:

order_3_user_count
3

其中:

  • order_3_user_count 表示最近 7 天内连续下单 3 日及以上的用户个数。

需求实现

select
    count(distinct user_id) order_3_user_count
from
    (select
        user_id
    from
        (select
            user_id,  
            date_sub(sale_date,row_number() over(partition by user_id order by sale_date)) start_date
        from
            (select
                user_id,
                sale_date
            from
                sales
            group by
                user_id,sale_date)t1 )t2
    group by
        user_id,start_date
    having
        count(user_id) >= 3)t3;

输出结果如下:

image.png

一共有 3 名用户在 7 天内连续下单了 3 日及以上。

那么现在来说说这个需求是如何实现的,通过如下 1005 用户的购买数据来进行演化说明:

user_id product_id quantity sale_date
1005 101 3 '2024-05-17'
1005 101 4 '2024-05-16'
1005 100 2 '2024-05-16'
1005 101 1 '2024-05-16'
1005 101 2 '2024-05-15'
1005 101 3 '2024-05-13'
1005 101 2 '2024-05-12'
1005 101 2 '2024-05-11'

在这个需求中,并不关心用户购买了多少商品,在意的是用户是否进行了下单,同天下单多次的用户也只算作一次。

select
    user_id,
    sale_date
from
    sales
group by
    user_id,sale_date;

按用户、下单时间进行聚合,每天仅保留一条数据,聚合后,数据变化如下:

user_id sale_date
1005 '2024-05-17'
1005 '2024-05-16'
1005 '2024-05-15'
1005 '2024-05-13'
1005 '2024-05-12'
1005 '2024-05-11'

那么现在就有一个问题,就是该如何判断日期是否连续呢?

其实很简单,我们可以通过开窗的方式,计算日期是否连续,我这里是通过打标记来实现的。

那么具体是如何实现呢?需要先了解 row_number 方法的使用,如下所示:

user_id sale_date row_number start_date
1005 '2024-05-17' 6 '2024-05-11'
1005 '2024-05-16' 5 '2024-05-11'
1005 '2024-05-15' 4 '2024-05-11'
1005 '2024-05-13' 3 '2024-05-10'
1005 '2024-05-12' 2 '2024-05-10'
1005 '2024-05-11' 1 '2024-05-10'

通过上面的规律可以发现,如果日期是连续的,那么当它们减去一串顺序的数字后,得到的结果是一致的,我们可以利用这一规则,判断日期是否连续。

select
    user_id,  
    date_sub(sale_date,row_number() over(partition by user_id order by sale_date)) start_date
from
    (select
        user_id,
        sale_date
    from
        sales
    group by
        user_id,sale_date)t1;

既然求出了日期是否连续,那么下面就是分组判断各个用户是否满足连续购买 3 日的条件。

select
    user_id
from
    (select
        user_id,  
        date_sub(sale_date,row_number() over(partition by user_id order by sale_date)) start_date
    from
        (select
            user_id,
            sale_date
        from
            sales
        group by
            user_id,sale_date)t1 )t2
group by
    user_id,start_date
having
    count(user_id) >= 3;

image.png

现在得到的结果并不是最终的结果,因为某个用户(1005)可能在 7 日内满足多次该条件,但是在这个需求中,每个用户只能算一次,所以还需要去重才能得到最终结果。

select
    count(distinct user_id) order_3_user_count
from
    (select
        user_id
    from
        (select
            user_id,  
            date_sub(sale_date,row_number() over(partition by user_id order by sale_date)) start_date
        from
            (select
                user_id,
                sale_date
            from
                sales
            group by
                user_id,sale_date)t1 )t2
    group by
        user_id,start_date
    having
        count(user_id) >= 3)t3;

image.png

解本题的关键在于如何判断日期是否连续,通过 row_number 开窗可以轻松实现这一需求。

相关文章
|
22天前
|
SQL HIVE
【Hive SQL 每日一题】环比增长率、环比增长率、复合增长率
该文介绍了环比增长率、同比增长率和复合增长率的概念及计算公式,并提供了SQL代码示例来计算商品的月度增长率。环比增长率是相邻两期数据的增长率,同比增长率是与去年同期相比的增长率,复合增长率则是连续时间段内平均增长的速率。文章还包含了一组销售数据用于演示如何运用这些增长率进行计算。
|
21天前
|
SQL HIVE
【Hive SQL 每日一题】统计用户连续下单的日期区间
该SQL代码用于统计用户连续下单的日期区间。首先按`user_id`和`order_date`分组并去除重复,然后使用`row_number()`标记行号,并通过`date_sub`与行号计算潜在的连续日期。接着按用户ID和计算后的日期分组,排除连续订单数少于2的情况,最后提取连续下单的起始和结束日期。输出结果展示了用户连续下单的日期范围。
|
22天前
|
SQL HIVE
【Hive SQL】字符串操作函数你真的会用吗?
本文介绍了SQL中判断字符串是否包含子串的几种方法。`IN`函数判断元素是否完全等于给定元素组中的某项,而非包含关系。`INSTR`和`LOCATE`函数返回子串在字符串中首次出现的位置,用于检测是否存在子串。`SUBSTR`则用于提取字符串的子串。`LIKE`用于模糊匹配,常与通配符配合使用。注意`IN`并非用于判断子串包含。
|
22天前
|
SQL 关系型数据库 HIVE
【Hive SQL 每日一题】统计最近1天/7天/30天商品的销量
这段内容是关于SQL查询的示例,目标是统计`sales`表中最近1天、7天和30天的商品销量和销售次数。表结构包含`id`、`product_id`、`quantity`和`sale_date`字段。初始查询方法通过三个独立的子查询完成,但效率较低。优化后的查询使用了`lateral view explode`将数据炸裂,通过一次查询同时获取所有所需时间段的数据,提高了效率。示例中展示了优化前后的SQL代码及结果对比。
|
7天前
|
SQL DataWorks NoSQL
DataWorks产品使用合集之如何将SQL Server中的数据转存到MongoDB
DataWorks作为一站式的数据开发与治理平台,提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案,帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述,涵盖数据处理的各个环节。
|
1月前
|
SQL API 流计算
实时计算 Flink版产品使用合集之在Mac M1下的Docker环境中开启SQL Server代理的操作步骤是什么
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
156 1
|
24天前
|
SQL 存储 搜索推荐
SQL server增删改查(1)
SQL server增删改查(1)
106 0
|
25天前
|
SQL 关系型数据库 数据库
阿里云数据库 RDS SQL Server版实战【性能优化实践、优点探析】
本文探讨了Amazon RDS SQL Server版在云数据库中的优势,包括高可用性、可扩展性、管理便捷、安全性和成本效益。通过多可用区部署和自动备份,RDS确保数据安全和持久性,并支持自动扩展以适应流量波动。可视化管理界面简化了监控和操作,而数据加密和访问控制等功能保障了安全性。此外,弹性计费模式降低了运维成本。实战应用显示,RDS SQL Server版能有效助力企业在促销高峰期稳定系统并保障数据安全。阿里云的RDS SQL Server版还提供了弹性伸缩、自动备份恢复、安全性和高可用性功能,进一步优化性能和成本控制,并与AWS生态系统无缝集成,支持多种开发语言和框架。
169 2
|
25天前
|
SQL JSON atlas
实时计算 Flink版产品使用合集之SQL Server CDC是否支持抽取SQL Server视图
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
30天前
|
SQL 数据处理 API
实时计算 Flink版产品使用合集之遇到SQL Server锁表问题如何解决
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
31 0

热门文章

最新文章