如何使用sql(不带过程)获得n个月的每个月的第一天和最后一天

Question

需要获取过去n个月的日期N =月数(5)

如果6月6日运行，预计0/p:

01-01-2021 31-01-2021
01-02-2021 28-02-2021
01-03-2021 31-03-2021
01-04-2021 30-04-2021
01-05-2021 31-05-2021

我不想使用python的任何包，因为它们没有安装在我的服务器上，无法导入

Answer 1

尝试使用concattransform高阶函数

Example:

n=6
spark.sql(f"select explode(transform(sequence(1,{n}), n -> (to_date(concat_ws('-','2018',n,'01')) as d2,last_day(to_date(concat_ws('-','2018',n,'01')))as d3)))").
select("col.*").
show(10,False)
#+----------+----------+
#|d2        |d3        |
#+----------+----------+
#|2018-01-01|2018-01-31|
#|2018-02-01|2018-02-28|
#|2018-03-01|2018-03-31|
#|2018-04-01|2018-04-30|
#|2018-05-01|2018-05-31|
#+----------+----------+

Answer 2

试试这个spark-sql

spark.sql("""
with t1( select sequence(1, 5, 1) seq, date'2021-06-06' dt  ),
t2( select explode(seq) a, dt from t1 ),
t3( select add_months(dt,-a) d1 ,day(dt) dy from t2 )
select date_format(d1,'yyyy-MM-01') d2, last_day(date_format(d1,'yyyy-MM-01')) d3 from t3 order by d2
""").show(false)

另一个解决方案:

可以使用spark内置的range(n)表

spark.sql("""
with t1( select id+1 id, date'2021-06-06' dt from range(5) ),
t2( select add_months(dt,-id) d1 , day(dt) dy from t1 )
select date_format(d1,'yyyy-MM-01') d2, last_day(date_format(d1,'yyyy-MM-01')) d3 from t2 order by d2
""").show(false)
+----------+----------+
|d2        |d3        |
+----------+----------+
|2021-01-01|2021-01-31|
|2021-02-01|2021-02-28|
|2021-03-01|2021-03-31|
|2021-04-01|2021-04-30|
|2021-05-01|2021-05-31|
+----------+----------+