odps窗口函数row_number使用

Zerol_Yan

于 2024-09-08 18:24:10 发布

阅读量959

点赞数 20

分类专栏：数据库文章标签： odps 数据库 sql

本文链接：https://blog.csdn.net/Zerol_Yan/article/details/142029111

版权

数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

需求

给一张用户表user表，和一张用户历史行为表history_data，两张表通过user_id关联，查询出每个用户每个季度的最后一条行为记录。

分析需求，sql要实现两点，1、历史行为表中以用户为维度拆分所有数据。2、识别出用户历史行为中每条数据所属的季度，找到每个季度的最后一条记录

`row_number函数`

在ODPS（‌Open Data Processing Service）‌中，‌row_number()函数是窗口函数的一种，‌基于指定的排序规则，可以为结果集中的每一行分配一个唯一的序号。

基本语法

row_number() over (partition by <column> order by <column>)

其中，分组 partition by 是可选的，排序 order by 是必须的，且排序必须是明确的，唯一的，避免运行过程中的不确定性。

使用场景

分组排序：使用 partition by 子句，可在每个分组内独立排序

数据排名：配合使用 over，对每条记录根据排序字段打上标号，返回构成一个新的字段，这个字段可以自己重命名

分页：数据排序之后，对新返回的字段，使用WHERE子句和BETWEEN关键字来筛选出需要的行，实现分页查询。

识别数据所属季度

假设用户行为表 history_data 中String类型字段 query_date 存储了请求时间，格式为："2023062317143068086011",前14位分别为年yyyy，月mm，日dd，时HH，分MM，秒SS；识别所属季度写作 yyyyQn 的格式，其中n取值（1,2,3,4）

在之前的文章 python 判断一个时间是另一个时间往前推的第几个季度中有提到，根据日期字段判断所属季度的方法，在 SQL 中替换成相关语法就可以了。

基本公式

判断这个日期是当年的第几个季度，公式为：(当前月份-1) 除以3下取整之后再加上1

odps中下取整-floor函数

floor 函数返回小于或等于给定数字的最大整数，即向下取整

使用示例

-- 这里返回的结果为3
select floor(11/3);

-- 面对负数，也同样会执行取整操作，下述示例返回的结果为 -257
select floor(-256.67);

odps中字符串截取-substring函数

substring函数可以截取字符串中的任意一串作为子串返回

基本语法

string：原字符串

start：起始位置，从1开始

length：截取的子串的长度

SUBSTRING(string, start, length)

使用示例

-- 示例返回的结果为：Hello
SELECT SUBSTRING('Hello World', 1, 5) AS result;

odps中字符串拼接-concat函数

concat()函数可以将多个字符串参数拼接成一个字符串。

基本语法

CONCAT(string1, string2, ...)

使用示例

-- 以下示例输出为：Thisisconcatexample
SELECT CONCAT('This', 'is', 'concat', 'example') AS concatenated_string;
-- 拼接时因为没有空格，所以上述示例打印出来是挤在一起的，如果为了视觉美观，可以将空格加上
SELECT CONCAT('This', ' ', 'is', ' ', 'concat', ' ', 'example') AS concatenated_string;
-- 上述SQL输出为：This is concat example

实现所属季度判断

基本函数和公式都介绍完毕了，最终识别所属季度的SQL也就出来了，示例如下：

-- 以下示例输出：2023Q2
select concat(substring('2023062317143068086011', 1, 4), 'Q', cast(floor((cast(substring('2023062317143068086011', 5, 2) as bigint) - 1) / 3) + 1 as string)) as year_quarter
-- 代入日期字段
select concat(substring(query_date, 1, 4), 'Q', cast(floor((cast(substring(query_date, 5, 2) as bigint) - 1) / 3) + 1 as string)) as year_quarter

注意这里的前提 query_date 是String类型的，如果是其他类型，作相应处理就好

查询每个用户每个季度最新的数据

使用 row_number 函数，对用户和季度进行分组，对 query_date 字段前14位表示日期时间的信息进行从大到小排序，最后取出每个分组的第一条数据，就是每个用户每个季度最新的数据啦。


SELECT
    count(*) -- 统计一下有多少条数据，也可以打印具体的字段
FROM (
    SELECT
        DISTINCT user_id -- 对用户id去重
    from
        user
    WHERE
        1 = 1 -- 这里写user表查询条件
) a
JOIN (
    SELECT 
        concat(substring(query_date, 1, 4), 'Q', cast(floor((cast(substring(query_date, 5, 2) as bigint) - 1) / 3) + 1 as string)) as quarter, -- 构造季度字段，为了打印，如果不需要可以不要该行
        row_number() over (partition by user_id, concat(substring(query_date, 1, 4), 'Q', cast(floor((cast(substring(query_date, 5, 2) as bigint) - 1) / 3) + 1 as string)) order by substring(query_date, 1, 14) desc) as rn -- 按季度和用户分组，‌选择时间最新的记录
    FROM 
        history_data
    WHERE 
        1 = 1 -- 这里写查询 history_data 表的筛选条件，如果没有，可以不要where，代表全表操作
) b ON a.user_id = b.user_id
WHERE 
    b.rn = 1;

扩展：实现分页

分页查询用户信息

select 
    *
from 
    (
        select
            name,
            user_id,
            age,
            ROW_NUMBER() OVER (ORDER BY created_date) AS row_num
        from user
    )AS sub_query
WHERE row_num BETWEEN 1 AND 10; -- 指定需要查询的页数和每页的行数