【最全最经典SQL题】五 产生连续数值

一、前言

从事数仓，数据开发的同学应该知道，日常工作中主要要用到SQL去实现业务各种数据需求。本系列【最全最经典SQL题】将列举日常工作或者笔试面试中频繁、经典的业务场景并通过SQL去实现。欢迎大家收藏起来，遇到对应的场景可以马上用起来。本系列不定期更新，内容如下：

【最全最经典SQL题】一 行列转换

【最全最经典SQL题】二 排名取它值

【最全最经典SQL题】三 累计求值

【最全最经典SQL题】四 窗口大小控制

【最全最经典SQL题】五 产生连续数值

【最全最经典SQL题】六 数据扩充与收缩

【最全最经典SQL题】七 容器

【最全最经典SQL题】八 时间序列

【最全最经典SQL题】九 非等值连接

更新中........

---

二、需求描述

我们知道，在数仓开发过程中，很多时候我们要补全连续的数据，有时候我们可以借助维表或者手工表去实现，但是如果不借助外表的情况下，如何产生连续整数？

三、MySQL实现

方法一 自定义变量

构造两个临时表 一个4一个6，通过笛卡尔积可以构造24行记录

	SELECT @xi:=@xi+1 as xc from 
			(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 ) xc1, 
			(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) xc2,  
			(SELECT @xi:=0) xc0 

扩展：生产过去2年的年月字段

SELECT DATE_FORMAT(DATE_SUB(NOW(), INTERVAL xc MONTH), '%Y-%m') as yearmonth
FROM ( 
			SELECT @xi:=@xi+1 as xc from 
			(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 ) xc1, 
			(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) xc2,  
			(SELECT @xi:=0) xc0 
) xcxc

 

方法二 recursive递归

在MySQL 8.0版本中，引入recursive。Recursive是一种基于递归思想的MySQL查询方式，可以实现对数据的递归查询和处理，返回符合条件的数据。这种递归查询方式可以应用在很多场景下，比如对于树形结构、层级结构的数据处理，以及对数据进行分类汇总等。

with recursive t(n) as (
select 1
union all
select n+1 from t where n<24
)
select * from t;

 

四、Hive SQL实现

lateral view posexplode

select
id_start+pos as id
from(
    select
    1 as id_start,
    10 as id_end
) m  lateral view posexplode(split(space(id_end-id_start), '')) t as pos, val

好了，大家赶紧收藏起来去实现吧~