sql错行数据计算

目录

1、场景描述：

1.1窗口分析函数

1.2适用数据库

2、数据准备

3、窗口函数使用：

3.1错行比较差值

 3.2比较2个字段，排序之后数据首尾相连。

 4、总结

---

1、场景描述：

在数据表数据质量检核中，需要检核数据合理性，比如数据表中相邻2行数据，指定字段的差值是等差数列，这就涉及到需要得到错行相减的数据。错行相减可以使用窗口分析函数。

1.1窗口分析函数

（1）LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值

第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）；

（2）LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值

第一个参数为列名，第二个参数为往下第n行（可选，默认为1），第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）；

（3）FIRST_VALUE(col） 取分组内排序后，截止到当前行，第一个值；

（4）LAST_VALUE(col） 取分组内排序后，截止到当前行，最后一个值；

1.2适用数据库

窗口函数适用数据库PG、hive、mysql，别的数据库暂时没有验证

---

2、数据准备

CREATE TABLE `test_product` (

`id` bigint unsigned NOT NULL AUTO_INCREMENT,

`save_year` char(4) NOT NULL,

`save_month` char(1) NOT NULL,

`product_num` int NOT NULL,

`day_num` int DEFAULT NULL,

`save_date` datetime DEFAULT NULL,

PRIMARY KEY (`id`),

UNIQUE KEY `id` (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb3;

INSERT INTO test_product (save_year,save_month,product_num,day_num,save_date)

VALUES

( '2020', '1', 3200, 3210, '2020-01-11 08:00:00' ),

( '2020', '2', 3210, 3220, '2020-02-11 09:00:00' ),

( '2020', '3', 3220, 3230, '2020-03-11 09:00:00' ),

( '2020', '4', 3230, 3240, '2020-04-11 09:00:00' ),

( '2021', '1', 3240, 3250, '2021-01-11 09:00:00' ),

( '2021', '2', 3250, 3260, '2021-02-11 00:00:00' ),

( '2021', '3', 3260, 3270, '2021-03-11 09:00:00' ),

( '2021', '4', 3270, 3280, '2021-04-11 00:00:00' );

---

3、窗口函数使用：

3.1错行比较差值

1、查询上一行数据的值。

select * ,lag(product_num,1,null) over(order by save_date asc) as pre_product_numfrom test_product 

pre_product_num就是上一行数据的值

2、 错行数据求差值。查询product_num,下一条数据和上一条数据的差值

    select *,product_num-pre_product_num as diff
    from(
    select * ,lag(product_num,1,null) over(order by save_date asc) as pre_product_num
    from test_product 
    )t

 

 3.2比较2个字段，排序之后数据首尾相连。

例如数据表test_product，day_num的值 = 下一条数据product_num的值

1、查询当前记录以及新增pre_day_num记录上一条记录的day_num

select * ,lag(day_num,1,null) over(order by save_date asc) as pre_day_num
from test_product

 2、查询不满足连续性的数据个数

select count(*)
from (select * ,lag(day_num,1,null) over(order by save_date asc) as pre_day_num
from test_product 
)t
where product_num != pre_day_num and pre_day_num is not null
    

 

结论： 按时间排序之后，2个字段的数据连续

 4、总结

本文介绍了窗口分析函数在hive中的应用，主要是错行数据之间数据的比较以及计算