SQL面试题练习 —— 查询最近一笔有效订单
目录
- 1 题目
- 2 建表语句
- 3 题解
题目来源：字节跳动。
1 题目
现有订单表t_order，包含订单ID，订单时间,下单用户，当前订单是否有效，请查询出每个用户每笔订单的上一笔有效订单
+---------+----------------------+----------+-----------+
| ord_id | ord_time | user_id | is_valid |
+---------+----------------------+----------+-----------+
| 1 | 2023-12-11 12:01:03 | a | 1 |
| 2 | 2023-12-11 12:02:06 | a | 0 |
| 3 | 2023-12-11 12:03:15 | a | 0 |
| 4 | 2023-12-11 12:04:20 | a | 1 |
| 5 | 2023-12-11 12:05:03 | a | 1 |
| 6 | 2023-12-11 12:01:02 | b | 1 |
| 7 | 2023-12-11 12:03:03 | b | 0 |
| 8 | 2023-12-11 12:04:01 | b | 1 |
| 9 | 2023-12-11 12:07:03 | b | 1 |
+---------+----------------------+----------+-----------+
期望查询结果如下：
+---------+----------------------+----------+-----------+--------------------+
| ord_id | ord_time | user_id | is_valid | last_valid_ord_id |
+---------+----------------------+----------+-----------+--------------------+
| 1 | 2023-12-11 12:01:03 | a | 1 | NULL |
| 2 | 2023-12-11 12:02:06 | a | 0 | 1 |
| 3 | 2023-12-11 12:03:15 | a | 0 | 1 |
| 4 | 2023-12-11 12:04:20 | a | 1 | 1 |
| 5 | 2023-12-11 12:05:03 | a | 1 | 4 |
| 6 | 2023-12-11 12:01:02 | b | 1 | NULL |
| 7 | 2023-12-11 12:03:03 | b | 0 | 6 |
| 8 | 2023-12-11 12:04:01 | b | 1 | 6 |
| 9 | 2023-12-11 12:07:03 | b | 1 | 8 |
+---------+----------------------+----------+-----------+--------------------+
2 建表语句
--建表语句
create table t_order
(
ord_id bigint COMMENT '订单ID',
ord_time string COMMENT '订单时间',
user_id string COMMENT '用户',
is_valid bigint COMMENT '订单是否有效'
) COMMENT '订单记录表'
stored as orc
;
-- 数据插入
insert into t_order(ord_id,ord_time,user_id,is_valid)
values
(1,'2023-12-11 12:01:03','a',1),
(2,'2023-12-11 12:02:06','a',0),
(3,'2023-12-11 12:03:15','a',0),
(4,'2023-12-11 12:04:20','a',1),
(5,'2023-12-11 12:05:03','a',1),
(6,'2023-12-11 12:01:02','b',1),
(7,'2023-12-11 12:03:03','b',0),
(8,'2023-12-11 12:04:01','b',1),
(9,'2023-12-11 12:07:03','b',1);
3 题解
（1）先查询出有效订单，然后计算出每笔有效订单的上一单有效订单；
select ord_id,ord_time,user_id,is_valid,lag(ord_id) over (partition by user_id order by ord_time asc) as last_valid_ord_id
from (select ord_id,ord_time,user_id,is_validfrom t_orderwhere is_valid = 1) t
执行结果
+---------+----------------------+----------+-----------+--------------------+
| ord_id | ord_time | user_id | is_valid | last_valid_ord_id |
+---------+----------------------+----------+-----------+--------------------+
| 1 | 2023-12-11 12:01:03 | a | 1 | NULL |
| 4 | 2023-12-11 12:04:20 | a | 1 | 1 |
| 5 | 2023-12-11 12:05:03 | a | 1 | 4 |
| 6 | 2023-12-11 12:01:02 | b | 1 | NULL |
| 8 | 2023-12-11 12:04:01 | b | 1 | 6 |
| 9 | 2023-12-11 12:07:03 | b | 1 | 8 |
+---------+----------------------+----------+-----------+--------------------+
（2）原始的明细数据与新的有效订单表按照用户进行关联，有效订单表的订单时间大于等于原始订单表；
with tmp as (-- 有效订单及其上一单有效记录select ord_id,ord_time,user_id,is_valid,lag(ord_id) over (partition by user_id order by ord_time asc) as last_valid_ord_idfrom (select ord_id,ord_time,user_id,is_validfrom t_orderwhere is_valid = 1) t)
select t1.*,t2.*
from t_order t1
left join tmp t2
on t1.user_id = t2.user_id
where t1.ord_time <= t2.ord_time
执行结果
+------------+----------------------+-------------+--------------+------------+----------------------+-------------+--------------+-----------------------+
| t1.ord_id | t1.ord_time | t1.user_id | t1.is_valid | t2.ord_id | t2.ord_time | t2.user_id | t2.is_valid | t2.last_valid_ord_id |
+------------+----------------------+-------------+--------------+------------+----------------------+-------------+--------------+-----------------------+
| 1 | 2023-12-11 12:01:03 | a | 1 | 1 | 2023-12-11 12:01:03 | a | 1 | NULL |
| 1 | 2023-12-11 12:01:03 | a | 1 | 4 | 2023-12-11 12:04:20 | a | 1 | 1 |
| 2 | 2023-12-11 12:02:06 | a | 0 | 4 | 2023-12-11 12:04:20 | a | 1 | 1 |
| 3 | 2023-12-11 12:03:15 | a | 0 | 4 | 2023-12-11 12:04:20 | a | 1 | 1 |
| 4 | 2023-12-11 12:04:20 | a | 1 | 4 | 2023-12-11 12:04:20 | a | 1 | 1 |
| 1 | 2023-12-11 12:01:03 | a | 1 | 5 | 2023-12-11 12:05:03 | a | 1 | 4 |
| 2 | 2023-12-11 12:02:06 | a | 0 | 5 | 2023-12-11 12:05:03 | a | 1 | 4 |
| 3 | 2023-12-11 12:03:15 | a | 0 | 5 | 2023-12-11 12:05:03 | a | 1 | 4 |
| 4 | 2023-12-11 12:04:20 | a | 1 | 5 | 2023-12-11 12:05:03 | a | 1 | 4 |
| 5 | 2023-12-11 12:05:03 | a | 1 | 5 | 2023-12-11 12:05:03 | a | 1 | 4 |
| 6 | 2023-12-11 12:01:02 | b | 1 | 6 | 2023-12-11 12:01:02 | b | 1 | NULL |
| 6 | 2023-12-11 12:01:02 | b | 1 | 8 | 2023-12-11 12:04:01 | b | 1 | 6 |
| 7 | 2023-12-11 12:03:03 | b | 0 | 8 | 2023-12-11 12:04:01 | b | 1 | 6 |
| 8 | 2023-12-11 12:04:01 | b | 1 | 8 | 2023-12-11 12:04:01 | b | 1 | 6 |
| 6 | 2023-12-11 12:01:02 | b | 1 | 9 | 2023-12-11 12:07:03 | b | 1 | 8 |
| 7 | 2023-12-11 12:03:03 | b | 0 | 9 | 2023-12-11 12:07:03 | b | 1 | 8 |
| 8 | 2023-12-11 12:04:01 | b | 1 | 9 | 2023-12-11 12:07:03 | b | 1 | 8 |
| 9 | 2023-12-11 12:07:03 | b | 1 | 9 | 2023-12-11 12:07:03 | b | 1 | 8 |
+------------+----------------------+-------------+--------------+------------+----------------------+-------------+--------------+-----------------------+
（3）使用row_number，原始订单记录表中的user_id、ord_id进行分组，按照有效订单表的时间排序，增加分组排序
with tmp as (-- 有效订单及其上一单有效记录select ord_id,ord_time,user_id,is_valid,lag(ord_id) over (partition by user_id order by ord_time asc) as last_valid_ord_idfrom (select ord_id,ord_time,user_id,is_validfrom t_orderwhere is_valid = 1) t)
select t1.*,t2.*,row_number() over (partition by t1.ord_id,t1.user_id order by t2.ord_time asc) as rn
from t_order t1
left join tmp t2
on t1.user_id = t2.user_id
where t1.ord_time <= t2.ord_time
执行结果
+------------+----------------------+-------------+--------------+------------+----------------------+-------------+--------------+-----------------------+-----+
| t1.ord_id | t1.ord_time | t1.user_id | t1.is_valid | t2.ord_id | t2.ord_time | t2.user_id | t2.is_valid | t2.last_valid_ord_id | rn |
+------------+----------------------+-------------+--------------+------------+----------------------+-------------+--------------+-----------------------+-----+
| 1 | 2023-12-11 12:01:03 | a | 1 | 1 | 2023-12-11 12:01:03 | a | 1 | NULL | 1 |
| 1 | 2023-12-11 12:01:03 | a | 1 | 4 | 2023-12-11 12:04:20 | a | 1 | 1 | 2 |
| 1 | 2023-12-11 12:01:03 | a | 1 | 5 | 2023-12-11 12:05:03 | a | 1 | 4 | 3 |
| 2 | 2023-12-11 12:02:06 | a | 0 | 4 | 2023-12-11 12:04:20 | a | 1 | 1 | 1 |
| 2 | 2023-12-11 12:02:06 | a | 0 | 5 | 2023-12-11 12:05:03 | a | 1 | 4 | 2 |
| 3 | 2023-12-11 12:03:15 | a | 0 | 4 | 2023-12-11 12:04:20 | a | 1 | 1 | 1 |
| 3 | 2023-12-11 12:03:15 | a | 0 | 5 | 2023-12-11 12:05:03 | a | 1 | 4 | 2 |
| 4 | 2023-12-11 12:04:20 | a | 1 | 4 | 2023-12-11 12:04:20 | a | 1 | 1 | 1 |
| 4 | 2023-12-11 12:04:20 | a | 1 | 5 | 2023-12-11 12:05:03 | a | 1 | 4 | 2 |
| 5 | 2023-12-11 12:05:03 | a | 1 | 5 | 2023-12-11 12:05:03 | a | 1 | 4 | 1 |
| 6 | 2023-12-11 12:01:02 | b | 1 | 6 | 2023-12-11 12:01:02 | b | 1 | NULL | 1 |
| 6 | 2023-12-11 12:01:02 | b | 1 | 8 | 2023-12-11 12:04:01 | b | 1 | 6 | 2 |
| 6 | 2023-12-11 12:01:02 | b | 1 | 9 | 2023-12-11 12:07:03 | b | 1 | 8 | 3 |
| 7 | 2023-12-11 12:03:03 | b | 0 | 8 | 2023-12-11 12:04:01 | b | 1 | 6 | 1 |
| 7 | 2023-12-11 12:03:03 | b | 0 | 9 | 2023-12-11 12:07:03 | b | 1 | 8 | 2 |
| 8 | 2023-12-11 12:04:01 | b | 1 | 8 | 2023-12-11 12:04:01 | b | 1 | 6 | 1 |
| 8 | 2023-12-11 12:04:01 | b | 1 | 9 | 2023-12-11 12:07:03 | b | 1 | 8 | 2 |
| 9 | 2023-12-11 12:07:03 | b | 1 | 9 | 2023-12-11 12:07:03 | b | 1 | 8 | 1 |
+------------+----------------------+-------------+--------------+------------+----------------------+-------------+--------------+-----------------------+-----+
（4）去除冗余字段，筛选rn=1 的记录
with tmp as (-- 有效订单及其上一单有效记录select ord_id,ord_time,user_id,is_valid,lag(ord_id) over (partition by user_id order by ord_time asc) as last_valid_ord_idfrom (select ord_id,ord_time,user_id,is_validfrom t_orderwhere is_valid = 1) t)
select *
from (select t1.*,t2.last_valid_ord_id,row_number() over (partition by t1.ord_id,t1.user_id order by t2.ord_time asc) as rnfrom t_order t1left join tmp t2on t1.user_id = t2.user_idwhere t1.ord_time <= t2.ord_time) tt
where rn = 1
执行结果
+------------+----------------------+-------------+--------------+-----------------------+--------+
| tt.ord_id | tt.ord_time | tt.user_id | tt.is_valid | tt.last_valid_ord_id | tt.rn |
+------------+----------------------+-------------+--------------+-----------------------+--------+
| 1 | 2023-12-11 12:01:03 | a | 1 | NULL | 1 |
| 2 | 2023-12-11 12:02:06 | a | 0 | 1 | 1 |
| 3 | 2023-12-11 12:03:15 | a | 0 | 1 | 1 |
| 4 | 2023-12-11 12:04:20 | a | 1 | 1 | 1 |
| 5 | 2023-12-11 12:05:03 | a | 1 | 4 | 1 |
| 6 | 2023-12-11 12:01:02 | b | 1 | NULL | 1 |
| 7 | 2023-12-11 12:03:03 | b | 0 | 6 | 1 |
| 8 | 2023-12-11 12:04:01 | b | 1 | 6 | 1 |
| 9 | 2023-12-11 12:07:03 | b | 1 | 8 | 1 |
+------------+----------------------+-------------+--------------+-----------------------+--------+
相关文章：
SQL面试题练习 —— 查询最近一笔有效订单
目录 1 题目2 建表语句3 题解 题目来源：字节跳动。 1 题目 现有订单表t_order，包含订单ID，订单时间,下单用户，当前订单是否有效，请查询出每个用户每笔订单的上一笔有效订单 ----------------------------------------…...
分享HTML显示2D/3D粒子时钟
效果截图 实现代码 线上体验：three.jscannon.js Web 3D <!DOCTYPE html> <head> <title>three.jscannon.js Web 3D</title><meta charset"utf-8"><meta name"viewport" content"widthdevice-width,ini…...
Java——IDEA使用
一、IDEA介绍 IntelliJ IDEA 是 JetBrains 公司开发的一款功能强大的集成开发环境（IDE），主要用于 Java 编程语言，但也支持多种其他语言和框架。由于其强大的功能和灵活性，IntelliJ IDEA 被广泛应用于软件开发领域&…...
高性能STL库 EASTL 、高性能JSON库
GitHub - electronicarts/EASTL: EASTL stands for Electronic Arts Standard Template Library. It is an extensive and robust implementation that has an emphasis on high performance. 兄弟们，对STL要求性能高的可以试试这个EASTL库！！…...
多通道采集器采样接口设计[进行中...]
1.技术问题 这是一个非常小的设计，完全不值得把它展示出来。但是因为这个接口设计关系到一些细微的配置和技术限制，仍然有一些细节需要处理，并且很容易出错，我们先把技术问题罗列一下： 多个传感器对应的多个逻辑通道…...
rapidjson使用中crash问题分析
问题 在使用rapidjson时，使用Document的Parse方法解析json字符串，程序crash。 分析 可以参考https://github.com/Tencent/rapidjson/issues/1269，由于rapidjson的内存分配器默认认为内存分配成功，没有对分配后做判空判断&#…...
TCP协议中的三次握手和四次挥手机制
TCP协议中的三次握手和四次挥手机制 TCP（Transmission Control Protocol，传输控制协议）是一种面向连接的、可靠的、基于字节流的通信协议，它的三次握手和四次挥手机制是建立和断开连接的关键步骤。 三次握手： 第一次…...
Linux双网卡默认路由的metric设置不正确，导致SSH连接失败问题定位
测试环境 VMware虚拟机 RockyLinux 9 x86_64 双网卡：eth0(访问外网): 10.206.216.92/24; eth1(访问内网) 192.168.1.4/24 问题描述 虚拟机重启后，SSH连接失败，提示"Connection time out"，重启之前SSH连接还是正常的…...
Batch入门学习：从零开始掌握批处理脚本
目录 1. Batch脚本简介 1.1 什么是Batch脚本？ 1.2 Batch脚本的历史 1.3 Batch脚本的应用场景 2. Batch脚本基本语法 2.1 注释 2.2 变量 2.3 常用命令 2.3.1 ECHO 2.3.2 PAUSE 2.3.3 CLS 2.3.4 GOTO 2.3.5 IF 2.3.6 FOR 2.4 参数传递 2.5 输入输出重…...
diffusion model(十八)：diffusion model中negative prompt的工作机制
info个人博客主页http://myhz0606.com/article/ncsn 前置阅读： DDPM： http://myhz0606.com/article/ddpm classifier-guided：http://myhz0606.com/article/guided classifier-free guided：http://myhz0606.com/article/classi…...
Python | Leetcode Python题解之第200题岛屿数量
题目： 题解： class Solution:def dfs(self, grid, r, c):grid[r][c] 0nr, nc len(grid), len(grid[0])for x, y in [(r - 1, c), (r 1, c), (r, c - 1), (r, c 1)]:if 0 < x < nr and 0 < y < nc and grid[x][y] "1":self.d…...
利用圆上两点和圆半径求解圆心坐标
已知圆上两点P1，P2，坐标依次为 ( x 1 , y 1 ) , ( x 2 , y 2 ) (x_1,y_1),(x_2,y_2) (x1,y1),(x2,y2)，圆的半径为 r r r，求圆心的坐标。 假定P1，P2为任意两点，则两点连成线段的中点坐标是 x m i …...
从ChatGPT代码执行逃逸到LLMs应用安全思考
摘要 11月7日OpenAI发布会后，GPT-4的最新更新为用户带来了更加便捷的功能，包括Python代码解释器、网络内容浏览和图像生成能力。这些创新不仅开辟了人工智能应用的新境界，也展示了GPT-4在处理复杂任务方面的惊人能力。然而，与所有…...
Python入门-基础知识-变量
1.标识符与关键字 Python语言规定，标识符由字母、数字和下画线组成，且不允许以数字开头。合法的标识符可以 是student_1、 addNumber、num等，而3number、2_student等是不合法的标识符。在使用标识符时应注意以下几点。 (1)命名时应遵循见名知…...
设计模式原则——接口隔离原则
设计模式原则 设计模式示例代码库地址： https://gitee.com/Jasonpupil/designPatterns 接口隔离原则 要求程序员尽量将臃肿庞大的接口拆分为更小的和更具体的接口，让接口中只包含客户感兴趣的方法接口隔离原则的目标是降低类或模块之间的耦合度&…...
MySQL数据库——在Centos7环境安装
MySQL在Centos7环境安装 1.切换root用户 安装与卸载中，用户全部切换成为root，安装好后，普通用户也能使用 2.卸载不要的环境 要将自己环境中有关mysql的全都删除，避免安装过程中被影响 ps axj | grep mariadb 先检查是否有mari…...
怎样规避液氮容器内部结霜的问题
液氮容器内部结霜问题一直是我们在使用液氮储存罐时遇到的一个棘手难题。液氮的极低温度使得容器内部很容易产生结霜现象，这不仅影响了容器的正常使用，还可能对内部样品或设备造成损坏。因此，如何有效规避液氮容器内部结霜问题成为了每个使用…...
冶金工业5G智能工厂工业物联数字孪生平台，推进制造业数字化转型
冶金工业5G智能工厂工业物联数字孪生平台，推进制造业数字化转型。传统生产方式难以满足现代冶金工业的发展需求，数字化转型成为必然趋势。通过引入5G、工业物联网和数字孪生等先进技术，冶金工业可以实现生产过程智能化、高效化和绿色化&#…...
一文入门机器学习参数调整实操
作者前言: 通过向身边的同事大佬请教之后，大佬指点我把本文的宗旨从“参数调优”改成了“参数调整”。实在惭愧，暂时还没到能“调优”的水平，本文只能通过实操演示“哪些操作会对数据训练产生影响”，后续加深学习之后，…...
基于51单片机的银行排队呼叫系统设计
一.硬件方案 本系统是以排队抽号顺序为核心，客户利用客户端抽号，工作人员利用叫号端叫号；通过显示器及时显示当前所叫号数，客户及时了解排队信息，通过合理的程序结构来执行排队抽号。电路主要由51单片机最小系统LCD12…...
OpenLayers 可视化之热力图
注：当前使用的是 ol 5.3.0 版本，天地图使用的key请到天地图官网申请，并替换为自己的key 热力图（Heatmap）又叫热点图，是一种通过特殊高亮显示事物密度分布、变化趋势的数据可视化技术。采用颜色的深浅来显示…...
大话软工笔记—需求分析概述
需求分析，就是要对需求调研收集到的资料信息逐个地进行拆分、研究，从大量的不确定“需求”中确定出哪些需求最终要转换为确定的“功能需求”。 需求分析的作用非常重要，后续设计的依据主要来自于需求分析的成果，包括: 项目的目的…...
51c自动驾驶~合集58
我自己的原文哦~ https://blog.51cto.com/whaosoft/13967107 #CCA-Attention 全局池化局部保留，CCA-Attention为LLM长文本建模带来突破性进展 琶洲实验室、华南理工大学联合推出关键上下文感知注意力机制（CCA-Attention），…...
python/java环境配置
环境变量放一起 python： 1.首先下载Python Python下载地址：Download Python | Python.org downloads ---windows -- 64 2.安装Python 下面两个，然后自定义，全选 可以把前4个选上 3.环境配置 1）搜高级系统设置 2…...
线程同步：确保多线程程序的安全与高效！
全文目录： 开篇语前序前言第一部分：线程同步的概念与问题1.1 线程同步的概念1.2 线程同步的问题1.3 线程同步的解决方案 第二部分：synchronized关键字的使用2.1 使用 synchronized修饰方法2.2 使用 synchronized修饰代码块 第三部分ÿ…...
关于nvm与node.js
1 安装nvm 安装过程中手动修改 nvm的安装路径， 以及修改 通过nvm安装node后正在使用的node的存放目录【这句话可能难以理解，但接着往下看你就了然了】 2 修改nvm中settings.txt文件配置 nvm安装成功后，通常在该文件中会出现以下配置&…...
【Go】3、Go语言进阶与依赖管理
前言 本系列文章参考自稀土掘金上的 【字节内部课】公开课，做自我学习总结整理。 Go语言并发编程 Go语言原生支持并发编程，它的核心机制是 Goroutine 协程、Channel 通道，并基于CSP（Communicating Sequential Processes࿰…...
ETLCloud可能遇到的问题有哪些？常见坑位解析
数据集成平台ETLCloud，主要用于支持数据的抽取（Extract）、转换（Transform）和加载（Load）过程。提供了一个简洁直观的界面，以便用户可以在不同的数据源之间轻松地进行数据迁移和转换。…...
C# 类和继承(抽象类)
抽象类 抽象类是指设计为被继承的类。抽象类只能被用作其他类的基类。 不能创建抽象类的实例。抽象类使用abstract修饰符声明。 抽象类可以包含抽象成员或普通的非抽象成员。抽象类的成员可以是抽象成员和普通带 实现的成员的任意组合。抽象类自己可以派生自另一个抽象类。例…...
ElasticSearch搜索引擎之倒排索引及其底层算法
文章目录 一、搜索引擎1、什么是搜索引擎？2、搜索引擎的分类3、常用的搜索引擎4、搜索引擎的特点二、倒排索引1、简介2、为什么倒排索引不用B+树1.创建时间长，文件大。2.其次，树深，IO次数可怕。3.索引可能会失效。4.精准度差。三. 倒排索引四、算法1、Term Index的算法2、 …...