sql - 如何生成具有开始月份日期和结束月份日期的动态表 Teradata 或 SAS SQL
问题描述
我想生成一个动态表,其中一个月的开始日期作为一列,一个月的结束日期作为另一列。
理想情况下,我想提供两年,即 2016 年和 2021 年。提供这两年时我希望得到的输出如下:
Begin_of_Month End_of_Month
2016-01-01 2016-01-31
2016-02-01 2016-02-29
.
.
.
2021-12-01 2021-12-31
请注意,我需要从 2016 年到 2021 年的所有年份的输出。在我上面的示例中,这意味着应该包括 2017 2018 2019 2020。
我曾尝试使用 Teradata 的时间序列函数,但未能获得结果。
我尝试在 Teradata 中重新创建的解决方案是:如何生成具有开始月份日期和结束月份日期的日历表
此外,我尝试了 Teradata 的 EXPAND ON PERIOD 时间序列功能。
解决方案
我敢肯定有一些花哨的方法可以做到这一点,但我认为只需点击内置日历表可能是最简单的:
SELECT DISTINCT
min(calendar_date) OVER (PARTITION BY year_of_calendar, month_of_calendar) as start_of_month,
max(calendar_date) OVER (PARTITION BY year_of_calendar, month_of_calendar) as end_of_month
FROM sys_calendar.calendar
WHERE year_of_calendar BETWEEN 2016 and 2021
要在没有表引用的情况下执行此操作,它会变得有点难看。EXPAND ON
似乎是一条明显的路线,但如果 FROM 子句中没有表引用,则会出错。UNION
遇到同样的问题,但我们可以UNION
通过使用 cte 来作弊。EXPAND ON
更挑剔,为了欺骗它,我们可以劫持 Teradata 的JSON_TABLE
功能:
SELECT BEGIN(dt), PRIOR(END(dt))
FROM JSON_TABLE
(
ON (SELECT 1 as id, NEW JSON('{"startdate":"2016-01-01","enddate":"2021-12-31"}') jd)
USING
rowexpr('$')
colexpr('[{"jsonpath" : "$.startdate", "type" : "DATE"},
{"jsonpath" : "$.enddate", "type" : "DATE"}]')
) as jt(id, startdate, enddate)
EXPAND ON PERIOD(startdate, enddate) as dt BY ANCHOR MONTH_BEGIN
您也可以使用递归 CTE 来构建月份,这感觉不那么 hacky,但需要更长的时间来生成。
WITH startend AS
(
SELECT
DATE '2016-01-01' periodstartdate,
DATE '2021-12-31' AS periodenddate
)
,RECURSIVE months AS
(
SELECT periodstartdate,
periodenddate,
periodstartdate as monthstartdate,
1 as monthoffset
FROM startend
UNION ALL
SELECT periodstartdate,
periodenddate,
ADD_MONTHS(periodstartdate, monthoffset),
monthoffset + 1
FROM
months
WHERE monthoffset < months_between(periodenddate, periodstartdate)
)
SELECT monthstartdate, monthstartdate + INTERVAL '1' MONTH - INTERVAL '1' DAY as monthenddate from months;
如果有更优雅的方式来解决这个问题,我会非常感兴趣。如果没有dual
其他 RDBMS 中的序列生成,则构建没有表引用的数据集的选项非常有限。
推荐阅读
- python - 从字符串表示构造 QueryDict
- powershell - 如何在 Powershell 中从这个类似 Hashtable 的文本中提取值?
- node.js - Nodejs Typescript 仅类型包未正确导出所有类型
- r - 按间隔和条件对数据进行分组
- powershell - 在 Azure Devops 的计划中执行 exe
- arrays - TypeError:赋值的右侧不能在数组映射上解构
- javascript - 使用 Service Worker 时检测在线状态不起作用
- javascript - 如何使用谷歌地理编码 API 从地址获取纬度和经度?
- javascript - 图形的对数图(PHP 或 JS)
- swift - Firestore Array 查询超过 10 个元素(分页)