数据仓库和sql 高级查询技巧

背景

最近根据自己对一些大数据分析相关文章的一些阅读和理解，整理了一些数据分析、Sparksql（也可以是hivesql）常见高级技巧的使用示例，包括语句的实现和特定场景优化等。这些示例有些是大厂数据开发面试中的常见题目。

Sql案例

行转列与列转行

1）首先是行转列。

行转列就是衍生出多个新的列，使用sum+case的语句进行条件求和

2）列转行

将以上原数据值为数组的列转成行，使用lateral view posexplode

条件求和

跟上一小节行转列一样用的sum+case

数据仓库分层的意义

类似软件服务的分层架构，数据仓库在表的划分上也采用了分层的设计。分层在软件领域上是一种常见的设计方法，有以下好处： 高内聚、低耦合、复用性、可扩展、便于团队分工。

数据仓库分层主要有以下层级：

1、ODS层，也就是原始数据层；

2、DWD层，数据明细层，对数据对字段选取，数据清洗；

3、DWM层，数据中间层，轻度聚合，存储中间数据；

4、DWS，数据汇总层，存储高度聚合的数据宽表；

5、APP，数据应用层，存储指标计算结果，对接数据产品

数据仓库分层的好处：

1、维护成本降低。避免代码复杂度过高

2、隔离变化。也就是低耦合，不同层之间数据互不影响

3、增加了复用性，进而带来一致性的提升和开发效率的提升。中间层数据可复用 持久化可以用空间换时间，节省计算资源。将某些中间结果数据持久化，可以重复使用，不需要重新计算

4、提升数据安全。对不同层的数据可以开放不同的权限给不同用户

5、统一建设思路。可以规范开发需求 血缘清晰。使开发中数据的流向清晰

直播间最大在线人数

有一个直播间用户登入登出记录表，包含字段uid、oid、op_time、op_type

需求1：求直播间最大在线人数

将用户出直播间的操作记为-1，进直播间的操作记为1。按操作时间排序，对进出直播间操作数从开始到当前行累加，就得到当前的在线人数，最后用max再求一个最大值即可。

需求2：求直播间出现峰值后持续的时间

出现高峰值的时间到下一个时间中间的时间差就是持续时间。

实现Sql：

拉链表的查询

现有一张员工在职所在部门信息表，包含员工ID、所属部门、开始日期、结束日期：

请查询出如下内容

1.2024年1月31日A部门在职员工数；

判断2024年1月31日在起始时间到结束时间之间部门为A的用户数

select count(1) as a_depart_num from emp_dep_info where department = 'A' and start_date <= '2024-01-31' and end_date > '2024-01-31';

2.2024年1月份A部门员工最多时有多少员工；

求某个时候用户最多的场景，类似直播间最大人数。将过程分解成进和出两种动作，分别设为1和-1，然后用窗口函数sum排序后求和，得到每一时刻的用户数。

3.2024年1月份A部门平均有多少员工；

补全日期和补全数据

题目：

有一个用户交易账户表deal，表中有三个字段： dt: 用户交易的日期 uid: 用户id amt: 用户账户金额 举例数据如下: dt uid amt 2024-01-01 a 100 2024-01-02 a 80 2024-01-04 a 60 2024-01-06 a 40 2024-01-10 a 10

求：用户的每天平均金额，如果日期中断当天没有数据, 则取上一天数据的账户金额数据；

Sql：

1. 先求出最早和最晚日期，得到日期天数差
2. 利用函数space结合posexplode、 laterval view 获取完整连续的日期
3. 拿完整的日期和原表关联，利用last_value(amt, true)补全金额数据
4. 最后求取用户每天的平均金额

WITH deal AS (
SELECT '2024-01-01' AS dt,
'a' AS uid,
100 AS amt
UNION ALL SELECT '2024-01-02' AS dt, 'a' AS uid, 80 AS amt UNION ALL
SELECT '2024-01-04' AS dt, 'a' AS uid, 60 AS amt UNION ALL
SELECT '2024-01-06' AS dt, 'a' AS uid, 40 AS amt UNION ALL
SELECT '2024-01-10' AS dt, 'a' AS uid, 10 AS amt),
-- 求最小和最大日期 deal_min_max_dt as
( SELECT uid,
min(dt) AS start_date,
max(dt) AS end_date
FROM deal GROUP BY uid),
--补全缺失的日期
date_addition AS (
SELECT uid,
date_add(start_date, pos) dt
FROM deal_min_max_dt lateral VIEW posexplode(split(space(datediff(end_date, start_date)), '')) t AS pos, val),
-- 分组内排序 取最后一行的值 补全金额数据
day_amt AS (
SELECT t1.dt,
t1.uid,
t2.amt,
COALESCE (t2.amt, 0) AS amt_nvl,
--如果为null即为0
last_value(t2.amt, FALSE ) over( PARTITION BY t1.uid ORDER BY t1.dt) AS amo,
last_value(t2.amt, TRUE ) over( PARTITION BY t1.uid ORDER BY t1.dt) AS amount,
--如果为null取上面日期数据
FROM date_addition t1
LEFT JOIN deal t2 ON t1.dt = t2.dt)
-- 得到用户每天的平均金额
SELECT
  uid,
  dt,
  amt,
  amt_nvl,
  amount,
  round(avg(amount) over(partition BY uid ORDER BY dt), 2)  AS avg_amount,                                                                                             AS avg_amount2,
  sum(amount)  over (partition BY uid ORDER BY dt) AS sum_amount,
  count(dt)   over (partition BY uid ORDER BY dt) AS cnt_dt
FROM day_amt

查询连续登录的用户

题目：

问题提出： 根据登录信息表（t_login），统计所有用户连续登录两天及以上的日期区间，以登录时间为准。 注意：这里为什么要强调以登录时间为准呢，是因为牵扯到跨天的情况。比如说某用户登陆时间 是某日的23点59分23秒，登出时间是次日的0点35分30秒，那么为了不产生歧义，从而能够准确地 进行分析，我们约定统计标准是登录时间。

Sql方案：

1、使用substring( login_time, 1, 10 )获取日期的月份信息；

2、使用窗口函数对userid分组，给每个用户下的登录数据按登录时间排序获得序号；

3、 对于每一行，将登录日期和序号相减得到一个新的日期；

4、判断在用户分组下新的日期是否有出现2次及以上的，有则就是符合条件的

SELECT userid, MIN(login_date) AS start_date, MAX(login_date) AS end_date
FROM ( SELECT userid, 
login_date, 
DATE_SUB(login_date,INTERVAL ranking DAY) AS new_date 
FROM ( SELECT userid, 
login_date, 
ROW_NUMBER() OVER (PARTITION BY userid order by login_date) AS ranking 
FROM ( SELECT DISTINCT userid, 
substring( login_time, 1, 10 ) AS login_date 
FROM t_login ) t1 ) t2) t3
 GROUP BY userid,new_date
HAVING COUNT(*) >= 2;

查询每个月最后一天的数据

题目：

我已经通过订单明细表统计了每天的订单量，但是老板现在需要知道每月最后1天的订单量情况， 如果某月没有记录，则说明该月最后1天没有产生订单，并不是说该月没有产生订单。

Sql方案：

SELECT LAST_DAY(order_date) AS last_day_of_month, 
order_quantity
FROM t_order 
WHERE order_date = LAST_DAY(order_date);

断点去重

题目：

这是一个Sql查询中一个场景，来看一下这个例子

需求：处理一下某个用户的玩游戏的先后顺序链条,按照时间排序后游戏相同的要合并例子：AABBA需要合并成ABA例子结果：

Sql方案：

select dtstatdate , qq , dteventtime , game
from (select dtstatdate , qq , dteventtime , game --构建辅助变量进行断点去重 , 
lag(game, 1, '') over (partition by dtstatdate,qq order by dteventtime) last_game 
from (--第一步按照事件时间先去重 
select dtstatdate , qq , dteventtime , max(game) game 
from user_login 
group by dtstatdate, qq, dteventtime) t) t
where game != last_game; 

这里使用了lag窗口函数构建辅助变量 last_game，得到上一行game字段的值，最终过滤出当前行game值与 last_game不同的行数据。

需求最后是要获取一个游戏路径以及对应的时间序列，按日期和用户分组。 这里认识一个窗口函数:collect_set() over (order by xxx)

可以实现分组后收集某个字段的值为一个列表，接着用concat_ws(‘,’, list)拼接成一个字符串

select dtstatdate , qq , dteventtime , game 
--获取时间集 , 
concat_ws(',', collect_list(dteventtime) over (partition by dtstatdate,qq order by dteventtime )) time_set
 --获取路径集 , 
concat_ws(',', collect_list(game) over (partition by dtstatdate,qq order by dteventtime )) game_set
from (select dtstatdate , qq , dteventtime , game 
--构建辅助变量进行断点去重 , 
lag(game, 1, '') over (partition by dtstatdate,qq order by dteventtime) last_game 
from (
--第一步按照事件时间先去重
 select dtstatdate , qq , dteventtime , max(game) game 
from user_login 
group by dtstatdate, qq, dteventtime) t) 
twhere game != last_game

最后的结果是每个用户玩游戏的路径，所以每个用户的分组数据中我们只要最后一条，也就是包含 最长游戏序列的数据，所以只要再对用户分组，使用聚合函数max即可。

-- 取出路径集
select dtstatdate , qq ,
max(time_set) time_set ,
max(game_set) game_set
from (
-- 此处省略） 
group by dtstatdate , qq 

分库分表概念

题目：

假设有一个订单表，因为业务快速上涨，表的订单数量每个月都可能有几百万的上涨量，这样 当表的数据量达到上亿甚至几亿的时候，查询就存在瓶颈。

方案：

优化的策略就是单库内对订单表水平分表，按月份对数据做分表，也就是将订单表分为 month_bills_202401、month_bill_202402、month_bill_202403。。。 使用的逻辑是： 1、能够自动按月创建一个新表，存储该月的数据； 2、读数据的时候，根据订单的月份找到所属的表进行读取； 3、写数据的时候，根据订单的月份找到所属的表写入数据。

分组过滤和聚合

题目：按班级分组查看学生平均成绩，并且只要人数超过20人的班级

Sql方案：

Select avg（score）
From student
Group  by class 
Having（count（*） > 20）

子查询

子查询的几种形式

Select avg（score）
From student
Group  by class 
Having（count（*） > 20）

以上sql通过x字段连接a表和b表，获取到a表的col1列和b表的col2列。

SELECT a.col1
FROM a
WHERE 1 = (SELECT count(*)
           FROM b
           WHERE b.x = a.x);  --------标量子查询

找到b表中有相应的x字段值的a的col1列。 对于a表的每一行，都需要到b表进行全表扫描。

使用join进行优化

SELECT a.col1,
       b.col2
FROM a
   LEFT JOIN b ON b.x = a.x;

左连接，以左表为基准，将b表中满足连接条件的数据扩展到a表中，如果b表有多行匹配到a表的一行，则最后生成多行，如果b表没有匹配的，加到a表中的b表字段值就为NULL。

SELECT a.col1
FROM a
   JOIN b ON b.x = a.x
GROUP BY a.pkey, a.col1
HAVING count(*) = 1;

外连接返回两表的所有数据，没匹配到则字段为NULL。

Join替换子查询的好处。

1、sql解析引擎对join便于优化

2、减少全表扫描，join在sql引擎的优化下可以一次性处理多表多行的数据

3、减少中间结果数据

使用in的子查询

SELECT a.col1
FROM a
WHERE a.foo IN (SELECT b.col2
                 FROM b
                 WHERE a.x = b.x);
等价于

SELECT a.col1
FROM a
WHERE EXISTS (SELECT NULL
              FROM b
              WHERE a.x = b.x
                AND a.foo = b.col2);

都是根据a的foo列等于b的col2列去找数据

结论：

• 尽可能地使用不相关的子查询，只要它们不会让语句难以理解
• 不相关子查询指不引用外部数据，也就是只在主查询的表中建立子查询。相关子查询指对于主查询的表的每一行记录，子查询都会不同。
• 在所有地方避免使用相关子查询，除非在EXISTS、NOT EXISTS和IN子句中
• 总是将NOT IN重写为NOT EXISTS

数据仓库-数据模型

数据仓库是面向主题的，集合了多个不同源的集成数据的中央存储库。 数据仓库分层架构 ：

• ODS：存储原始的数据，转换成结构化数据，高度无损；
• DWD：明细层，对原始层数据做字段的筛选，数据清洗、过滤；
• DWM：存储轻度汇总的中间数据，数据供上层复用；
• DWS：按照主题业务组织宽表；
• DIM：维表层，存储维度数据；
• DM：高度汇总的数据，形成报表；
• ADS：也是报表，应用数据；什么是理想的数据仓库模型？ 数据模型可复用，完善且规范。

存在问题的数据模型：

• 数据不能复用
• 数据意义不明确
• 数据重复（烟囱式数仓）
• 表名命名不规范
• 应用层和数据集市层直接引用ODS层，应该先生成DWD层

分组求topN

题目；

一个表存储了一个公司各个部门的员工薪水信息，包含字段如员工id，员工姓名name，所属部门 department，当前薪水salary等。 写一个SQL求每个部门里在不同 工资 中排前三的员工数据。

Sql方案：

分组不减行数，则用窗口函数。 因为需要获取薪水值排前三的数据，也就是相同薪水的排名相同，所以使用DENSE_RANK函数按 部门分组然后根据salary排序。 窗口函数的语法： 窗口函数（）over （partition by 分组字段 order by 排序字段） 别名

SELECT "Department", "Employee", "Salary" 
FROM( 
SELECT D.name "Department",   
 E.name "Employee" , SALARY "Salary" ,   
--通过rank开窗，获取分组后的排名列 
DENSE_RANK() OVER(PARTITION BY E.departmentid order by salary desc) RANK 
FROM Employee E 
JOIN Department D 
ON E.DepartmentID = D.ID) T
WHERE RANK <=3 -
-筛选前3的值 

上下行交换

题目：

Sql方案：

/* ORACLE */
SELECT id "id", 
CASE WHEN MOD(ID,2) = 1 THEN coalesce(LEAD,STUDENT) ELSE LAG END "student“
FROM( 
SELECT ID, STUDENT, 
lag(student,1) over(order by id) lag , 
lead(student,1) over(order by id) lead 
FROM seat) T 

Sql数据倾斜-TopN

题目：分组求top N数据

Sql方案：

select   cate_id
        ,property_id
        ,value_id
from
        (select   cate_id
                 ,property_id
                 ,value_id
                 ,row_number() over(partition by cate_id order by property_id asc,value_id asc) as rn
         from     demo_tbl
         where    ds = '${bizdate}'
         ) p
where    rn <= N;

使用窗口函数按目标字段分组，根据行号获取到每一组的前N个数据。

这里和前一种写法不同的是，在开始对目标字段cate_id分组的时候加入了一个随机数使分组更加均匀，也就是partition by cate_id，sec_part这里，然后照样求各组的topN。这里求出来的细粒度下的topN，需要再对这些数据进行二次聚合，这次就对cate_id分组，然后求TopN即可。

Sql数据倾斜-count distinct

题目：count distinct引发的数据倾斜怎么解决

正常写法 SELECT c1 ,count(DISTINCT c2) FROM 明细表 GROUP BY c1 当c1存在某个值对应的数量很大时引起数据倾斜

优化Sql方案：

SELECT  c1
        ,COUNT(*) AS cnt
FROM    (
            SELECT  c1
                    ,c2
            FROM    明细表
            GROUP BY c1
                     ,c2
        ) 
GROUP BY c1

这里可以看出内部先对c1和c2进行分组，这样显然粒度更小，因此数据分组更均匀（解决数据倾斜的根本就是使其分组均匀）。聚合后c1、c2组合就是不重复的，然后再求按c1分组c2字段的不重复数目。

如果还是存在某个c1、c2组合的值数量很大，那么也还是会导致数据倾斜，也就是存在一组c1、c2的分区数量过大，那么可以给c1字段加随机数进一步打散分组，加随机数的作用就是使得原本c1字段值相同的数据分散到多个组中

SELECT  SPLIT_PART(rand_c1, '_',2)
        ,COUNT(*) AS cnt
FROM    (
            SELECT  CONCAT(ROUND(RAND(),1)*10,'_', c1) AS rand_c1
                    ,c2
            FROM    明细表
            GROUP BY CONCAT(ROUND(RAND(),1)*10,'_', c1)
                     ,c2
        ) 
GROUP BY SPLIT_PART(rand_c1, '_',2)

这里CONCAT(ROUND(RAND(),1)*10,‘_’, c1) 将c1的值与一个随机数拼接，最后二次聚合再 重新按照c1进行分组，相当与把前面分散聚合的结果合并在一起

Sql数据倾斜-group

什么是数据倾斜？Sparksql中数据倾斜是指在shuffle过程中，一个或多个reduce任务耗时特别长。例如如下键值对表示字符串以及它出现的次数，统计各个字符串出现的次数：

正常写法

SELECT      key      ,
count(* )
FROM tablename
GROUP BY key

-- 假设长尾的Key已经找到K1
SELECT  a.Key        ,SUM(a.Cnt) AS Cnt
FROM    (            
SELECT  Key                    ,
COUNT(*) AS Cnt          
  FROM    TableName            GROUP BY Key                     ,
CASE    WHEN KEY = 'K1' THEN Hash(Random()) % 50                              
 ELSE 0                       END        ) a
GROUP BY a.Key;

将长key映射成key和一个随机值，再进行group计算，这样该长key就会被打散到多个任务中，最后再group by key聚合最后的结果，这时候相同该key的值已经大大减少了，相当于先通过多个任务对这个key做一次预聚合后再二次聚合。