首页 > 数据库 >面试速通-SQL编码题

面试速通-SQL编码题

时间:2024-09-03 09:53:46浏览次数:10  
标签:编码 速通 ip cost user SQL 数据 id select

〇、前话

SQL题是数据相关的面试无可避免的小BOSS,虽然不同公司面试出题会披上不同的马甲,侧重各异,但总结起来题型就是那4、5种。下面让我们花上几分钟一起来通关它吧!

一、连续日期计算

1,例题

表字段:用户ID,账户余额,系统余额日期。求出余额大于1000的用户的最大连续天数。

2,解题要点

关键要点是找到能标识连续的字段标记。其次要区分出来每个用户要满足的条件。

3,题解

1)过滤余额大于1000的用户的记录。2)按用户ID开窗,按日期进行升序排序,并生成排序序数。3)使用系统余额日期减去1970-01-01获得日期间隔,即生成自然排序序数。4)使用自然序数减去排序序数,生成自然连续的标记。自然序数减去排序序数间隔相同的记录集合,该部分记录是连续的,否则不连续。5)再按题目要求进行统计最大值的聚合计算。

4,真题回顾

二、开窗函数的妙用

1,例题

有效值追溯问题:现在有一张商品入库表,包括商品id、商品成本和入库日期3个字段,由于某些原因,导致部分商品的成本缺失(为0或者没有值都是缺失),这样不利于我们计算成本。所以现在要把缺失的商品进价补充完整,补充的依据是使用相同商品的最近一次有效成本作为当前商品的成本。比如2023-11-04号101商品的cost就需要用300.39填充。

2,解题要点

题目中「相同商品的最近一次有效成本」这个问题具象起来就是按时间排序之后每个商品价格记录的上一条记录,并找到合适的方法取出放到本条商品价格记录即可。

lag() over()开窗函数就正好适合解决这个问题。返回窗口内上一行数据中的对应字段值。参考:https://spark.apache.org/docs/latest/api/sql/#lag附:开窗函数类的题目属于见过就会做的类型。常用的聚合、排序开窗都要滚瓜烂熟。

例如:(1) sum() over() ,要注意开窗中加order by和不加order by的区别;(2)lag() over();(3)last_value() over() ;(4)row_number() over() ,要注意rank\dense_rank\row_number三者的区别。

3,题解

select product_id
,date
,last_value(cost,true) over(partition by paroduct_id order by date asc) as cost
from (
select product_id
,date
,if(cost=0 or cost is null,null,cost) as cost 
from product_table 
)

4,真题回顾

三、简单Join&过滤聚合

1,例题

问题:现在有一张用户登陆日志表,该表包括user_id,ip,log_time三个字段,现在需要找出共同使用ip数量超过3个(含)的所有用户对。比如下面的示例数据,101和102用户共同使用的ip为4个,101和103用户共同使用的ip为3个,102和103用户共同使用的ip为3个。

2,解题要点

按IP关联join就能找到使用相同ip的用户。但要注意谓词下推,提高处理效率。

3,题解

select t1.user_id,t2.user_id,count(*)
from (
select user_id,ip  from(select distinct user_id,ip from tmp)
)t1
full outer join (
select user_id,ip  from(select distinct user_id,ip from tmp)
)t2
on t1.user_id>t2.user_id and t1.ip=t2.ip
group by t1.user_id,t2.user_id having count(*)>=3

4,真题回顾

四、启闭标记统计

1,例题

如下为某电商公司用户访问网站的数据,包括用户id和访问时间两个字段。现有如下规则:如果某个用户的连续的访问记录时间间隔小于60秒,则属于同一个会话,现在需要计算每个用户有多少个会话。比如A用户在第1秒,60秒,200秒,230秒有四次访问记录,则该用户有2个会话,其中第一个会话是第1秒和第60秒的记录,第二个会话是第200秒和230秒的记录。

2,解题要点

如何用数据具象来标识一次会话是关键。参考下面的flag,每个用户按时间戳顺排之后,上次会话记录和本次会话记录如果在60秒内标记为0,否则标记为1。作为一次会话的开启和关闭标记。再将flag进行逐行累加,这时候就能得出总共有多少次会话了。

3,题解

select product_id
,date
,last_value(cost,true) over(partition by paroduct_id order by date asc) as cost
from (
select product_id
,date
,if(cost=0 or cost is null,null,cost) as cost 
from product_table 
)

4,真题回顾

五、互相关注的人

1,例题

现在有一张relation表,里面只有两个字段:from_user和to_user,代表关注关系从from指向to,即from_user关注了to_user。现在要找出互相关注的所有人。

2,解题要点

其实就是找(from_user=x,to_user=y)和(from_user=y,to_user=x)同时存在的人。暴力解法是直接用relation表自关联,使用左表的to_user和右表的from_user进行left join。右表记录非空的则是存在互相关注的人。优化解是建立relation表的copy表并交换from_user和to_user字段位置。再union之后,统计相同记录行数,大于2的为相互关注的人。

3,题解

with relation as
(
select "乔峰" as from_user,"段誉" as to_user
  union all
select "乔峰" as from_user,"虚竹" as to_user
  union all
select "虚竹" as from_user,"乔峰" as to_user
  union all
select "徐风年" as from_user,"徐骁" as to_user
  union all
select "徐骁" as from_user,"徐风年" as to_user
)
select from_user,to_user,count(*)
from (
select from_user,to_user from tmp 
union all 
select from_user as to_user ,to_user as from_user 
from tmp 
)group by from_user,to_user having count(*) > 1

4,真题回顾

无,但这种题在社区类业务岗位的面试中经常遇到。

六、SQL拓展问题

结合SQL探究SparkSQL、HiveSQL知识点

1,例题

表字段如下user_id bigintTimestamp bigint tag_id string 取每个用户最早和最晚的tag_id和对应的时间戳。因为表数据量级非常大!注意要使用效率最高的方式。

2,解题要点

1)不使用row_number()而使用max,min。因为max,min可以局部排序,而row_number是全局排序的。2)bigint和string可以相加,向下兼容合并。user_id和tag_id可以合并为1个字段,避免多次的sort join。3)分组的逻辑,对user_id中的热点值进行处理,避免倾斜。对于user_id中的热点值可以单独处理完成之后再union在结果中。

3,题解

Select 
user_id
,tag_id
  ,casewhenTimestamp=max_ts then ‘max’
when Timestamp=min_ts then ‘min’
  endastype
from(Select 
user_id
,tag_id
  ,Timestamp
max(Timestamp) over(partition by user_id+tag_id) as max_ts,
min(Timestamp) over(partition by user_id+tag_id) as min_ts
  Fromtable 
)whereTimestamp=max_ts orTimestamp=min_ts

4,真题回顾

30,常见的开窗函数有哪些?31,sum()开窗的话加order by和不加order by有区别吗?

排序的函数有哪些?分别的区别是什么?

1,一个hiveSQL select from where group by having order by这几个关键词组成的SQL,在引擎中的执行顺序是什么?

2,在hive中做行列转换,行转列,列转行分别用什么函数。

3,group by和distinct去重的时候,谁的执行效率会更高呢,从原理的角度来解释一下。

标签:编码,速通,ip,cost,user,SQL,数据,id,select
From: https://blog.csdn.net/m0_73359763/article/details/141660142

相关文章

  • mysql创建数据库和表
    MySQL数据库与表的创建:结合具体案例分析MySQL是一种开源的关系型数据库管理系统(RDBMS),广泛应用于各种数据密集型的应用程序中。在数据库设计中,创建数据库和表是基础且关键的一步。本文将结合具体案例,详细分析如何在MySQL中创建数据库和表。一、创建数据库在MySQL中,创建数据库......
  • MySQL外键使用详解
    一、基本概念1、MySQL中“键”和“索引”的定义相同,所以外键和主键一样也是索引的一种。不同的是MySQL会自动为所有表的主键进行索引,但是外键字段必须由用户进行明确的索引。用于外键关系的字段必须在所有的参照表中进行明确地索引,InnoDB不能自动地创建索引。2、外键可以是一对......
  • MySQL视图(view)
    一、基本概念视图是一个虚拟表,是sql的查询结果,其内容由查询定义。同真实的表一样,视图包含一系列带有名称的列和行数据,在使用视图时动态生成。视图的数据变化会影响到基表,基表的数据变化也会影响到视图[insertupdatedelete];创建视图需要createview权限,并且对于查询涉及的......
  • 前端速通面经八股系列(八)—— 全网最高质量的前端浏览器原理篇
    浏览器原理系列目录一、浏览器安全1.什么是XSS攻击?(1)概念(2)攻击类型2.如何防御XSS攻击?3.什么是CSRF攻击?(1)概念(2)攻击类型4.如何防御CSRF攻击?5.什么是中间人攻击?如何防范中间人攻击?6.有哪些可能引起前端安全的问题?7.网络劫持有哪几种,如何防范?二、进程与线......
  • 数据库守护者:揭秘MySQL组复制的高可用魔法
    mysql高可用之组复制(MGR)(数据库守护者:揭秘MySQL组复制的高可用魔法)什么是MySQLGroupReplication?MySQLGroupReplication是一个基于组通信的复制解决方案,它允许将多个MySQL实例组织成一个组,在该组内进行事务的一致性复制。这样可以确保即使某个实例发生故障,其他实例......
  • 一个功能丰富的SQL审核查询平台
    前言今天大姚给大家分享一个功能丰富的SQL审核查询平台:Archery。核心功能SQL审核:提供SQL语句的审核功能,确保语句的安全性和效率。查询执行:支持SQL查询的执行,便于用户获取数据库中的数据。备份与回滚:集成备份功能,并能生成回滚语句,提升数据库运维的安全性。功能清单演示截图在线演示......
  • mssql windows 账户登录不了 要登录sa创建出来 才可以本地登录
    SQLSERVER登陆错误:18456 SQLWindows身份登录失败,错误码:18456先用sa进入EXECxp_instance_regreadN'HKEY_LOCAL_MACHINE',N'SOFTWARE\Microsoft\MicrosoftSQLServer\MSSQLServer',N'LoginMode'--启用TCP/IP协议EXECxp_instance_regwriteN'H......
  • MySQL多表查询
    目录等值连接单表等值连接表别名多表等值连接自然连接USINGONUSING和ON的区别自连接外连接1.左外连接 LEFTOUTERJOIN2.右外连接 RIGHTOUTERJOIN子查询单行子查询多行子查询相关子查询EXISTS等值连接单表等值连接SELECT表名1.列名1,表名2.列名2FR......
  • 呵,老板不过如此,SQL还是得看我
    2018年7月,大三暑假进行时,时间过得飞快,我到这边实习都已经一个月了。我在没工作之前,我老是觉得生产项目的代码跟我平时自学练的会有很大的区别。以为生产项目代码啥的都会规范很多,比如在接口上会做很多安全性的工作(自学练的时候确实学到的类似的案例),代码设计上会有很多设计模式的......
  • 详细分析MySQL事务日志(redo log和undo log)
    innodb事务日志包括redolog和undolog。redolog是重做日志,提供前滚操作,undolog是回滚日志,提供回滚操作。undolog不是redolog的逆向过程,其实它们都算是用来恢复的日志:1.redolog通常是物理日志,记录的是数据页的物理修改,而不是某一行或某几行修改成怎样怎样,它用来恢复提交后的......