SQL面试题:按照时间序列补全数据

简介: HiveSQL面试题,根据时间以最新数据补全字段缺失值

题目:给定表t,字段信息如下

date

c1

c2

c3

2022-09-15

AB

12

bc

2022-09-16


23


2022-09-17



d

2022-09-18

BC



问题:如何补全数据,结果如下

date

c1

c2

c3

2022-09-15

AB

12

bc

2022-09-16

AB

23

bc

2022-09-17

AB

23

d

2022-09-18

BC

23

bc

思路:可以对缺失的数据进行分组,组内累加该字段值,但是目前这题不能使用此方法,因为字段值为含有字符串不能直接累加,可以采用分组之后取第一值。


构造数据:使用SQL先构造上述数据

with t as(selectdate,        c1,        c2,        c3
fromvalues('2022-09-15','AB',12,'bc'),('2022-09-16',null,23,null),('2022-09-17',null,null,'d'),('2022-09-18','BC',null,null)               tmp(date,c1,c2,c3))date        c1  c2  c3
2022-09-15  AB  12  bc
2022-09-16  \N  23  \N
2022-09-17  \N  \N  d
2022-09-18  BC  \N  \N


解法一:分组累加思想

selectdate,    first_value(c1) over(partition by flag1 orderbydate) c1,    first_value(c2) over(partition by flag2 orderbydate) c2,    first_value(c3) over(partition by flag3 orderbydate) c3
from(selectdate,        c1,        c2,        c3,        lag_c1,        lag_c2,        lag_c3,        sum(if(c1 isnotnulland lag_c1 isnull,1,0)) over(orderbydate) flag1,        sum(if((c2 isnotnulland lag_c2 isnull)or(c2 isnotnulland lag_c2 isnotnull),1,0)) over(orderbydate) flag2,        sum(if(c3 isnotnulland lag_c3 isnull,1,0)) over(orderbydate) flag3
from(selectdate,            c1,            c2,            c3,            lag(c1,1) over(orderbydate) lag_c1,            lag(c2,1) over(orderbydate) lag_c2,            lag(c3,1) over(orderbydate) lag_c3
from t
) a
) b
date        c1  c2  c3
2022-09-15  AB  12  bc
2022-09-16  AB  23  bc
2022-09-17  AB  23  d
2022-09-18  BC  23  d

解法二:解法一存在特殊情况,比如c2的连续不为空,分组时就得单独考虑,以下这个方法无需考虑改情况且较为巧妙用count代替sum累加

selectdate,  first_value(c1) over(partition by aa orderbydate)as c1,  first_value(c2) over(partition by bb orderbydate)as c2,  first_value(c3) over(partition by cc orderbydate)as c3
from(selectdate,    c1,    c2,    c3,count(c1) over(orderbydate)as aa,count(c2) over(orderbydate)as bb,count(c3) over(orderbydate)as cc
from t
) a;date        c1  c2  c3
2022-09-15  AB  12  bc
2022-09-16  AB  23  bc
2022-09-17  AB  23  d
2022-09-18  BC  23  d

此题应该还有其他解法,欢迎交流

拜了个拜

目录
相关文章
|
23天前
|
SQL 人工智能 JSON
Flink 2.1 SQL:解锁实时数据与AI集成,实现可扩展流处理
简介:本文整理自阿里云高级技术专家李麟在Flink Forward Asia 2025新加坡站的分享,介绍了Flink 2.1 SQL在实时数据处理与AI融合方面的关键进展,包括AI函数集成、Join优化及未来发展方向,助力构建高效实时AI管道。
339 43
|
17天前
|
SQL 人工智能 JSON
Flink 2.1 SQL:解锁实时数据与AI集成,实现可扩展流处理
本文整理自阿里云的高级技术专家、Apache Flink PMC 成员李麟老师在 Flink Forward Asia 2025 新加坡[1]站 —— 实时 AI 专场中的分享。将带来关于 Flink 2.1 版本中 SQL 在实时数据处理和 AI 方面进展的话题。
104 0
Flink 2.1 SQL:解锁实时数据与AI集成,实现可扩展流处理
|
2月前
|
SQL
SQL如何只让特定列中只显示一行数据
SQL如何只让特定列中只显示一行数据
|
4月前
|
缓存 NoSQL 关系型数据库
美团面试:MySQL有1000w数据,redis只存20w的数据,如何做 缓存 设计?
美团面试:MySQL有1000w数据,redis只存20w的数据,如何做 缓存 设计?
美团面试:MySQL有1000w数据,redis只存20w的数据,如何做 缓存 设计?
|
6月前
|
SQL 自然语言处理 数据库
【Azure Developer】分享两段Python代码处理表格(CSV格式)数据 : 根据每列的内容生成SQL语句
本文介绍了使用Python Pandas处理数据收集任务中格式不统一的问题。针对两种情况:服务名对应多人拥有状态(1/0表示),以及服务名与人名重复列的情况,分别采用双层for循环和字典数据结构实现数据转换,最终生成Name对应的Services列表(逗号分隔)。此方法高效解决大量数据的人工处理难题,减少错误并提升效率。文中附带代码示例及执行结果截图,便于理解和实践。
145 4
|
2月前
|
SQL
SQL中如何删除指定查询出来的数据
SQL中如何删除指定查询出来的数据
|
2月前
|
SQL 关系型数据库 MySQL
SQL如何对不同表的数据进行更新
本文介绍了如何将表A的Col1数据更新到表B的Col1中,分别提供了Microsoft SQL和MySQL的实现方法,并探讨了多表合并后更新的优化方式,如使用MERGE语句提升效率。适用于数据库数据同步与批量更新场景。
|
3月前
|
SQL DataWorks 数据管理
SQL血缘分析实战!数据人必会的3大救命场景
1. 开源工具:Apache Atlas(元数据管理)、Spline(血缘追踪) 2. 企业级方案:阿里DataWorks血缘分析、腾讯云CDW血缘引擎 3. 自研技巧:在ETL脚本中植入版本水印,用注释记录业务逻辑变更 📌 重点总结:
|
4月前
|
SQL 数据挖掘 关系型数据库
【SQL 周周练】一千条数据需要做一天,怎么用 SQL 处理电表数据(如何动态构造自然月)
题目来自于某位发帖人在某 Excel 论坛的求助,他需要将电表缴费数据按照缴费区间拆开后再按月份汇总。当时用手工处理数据,自称一千条数据就需要处理一天。我将这个问题转化为 SQL 题目。
160 12
|
4月前
|
SQL 数据采集 资源调度
【SQL 周周练】爬取短视频发现数据缺失,如何用 SQL 填充
爬虫爬取抖音和快手的短视频数据时,如果遇到数据缺失的情况,如何使用 SQL 语句完成数据的补全。
105 5