ToB企服应用市场:ToB评测及商务社交产业平台

标题: 浅谈SQL中的循环 [打印本页]

作者: 反转基因福娃 时间: 2022-9-16 17:22
标题: 浅谈SQL中的循环
在日常的数据分析中，经常会遇到一类问题：计算从某个时间点开始的累计数据，但在某些时间点又是没有数据的，需得到的结果是每个时间点的累计数据。
比如以下情况，左边是原始数据，右边是期望输出数据：

因为大部分数据库不像其他编程语言，没有函数式编程，不能直接for循环。如果支持游标循环，则比较容易实现。
下面介绍游标循环的思路以及在不支持游标的情况下该如何处理。
方法1：游标循环（Cursor For Loops）

具体逻辑如下：

按一定顺序遍历时间date；
where条件的时间范围为[本月第一天，date]
以date分组，这就把在这个时间范围内的数据聚合起来了

FOR date IN list_of_dates
LOOP
INSERTINTO final_table(date, revenue_mtd)
SELECT @dateasdate, sum(revenue) as revenue_mtd
FROM sales
WHERE sales.dt between date_trunc('month',@date) and @date;
ENDLOOP;

复制代码

所以支持游标循环的数据库是比较方便做一些函数式编程的。
方法2：构造辅助列

inner join

/* FABRICATE SOME EXAMPLES */
WITH fake_sales AS (
select'2020-12-01'::date dt, 100.00 revenue unionall
select'2020-12-02'::date dt, 200.00 revenue unionall
select'2020-12-08'::date dt, 300.00 revenue unionall
select'2020-12-09'::date dt, 400.00 revenue unionall
select'2020-12-10'::date dt, 500.00 revenue
)
, fake_dates AS (
SELECT'2020-12-01'::date + SEQ4() dt
FROMTABLE (GENERATOR(ROWCOUNT => 31)) v
)
,
/* THE ACTUAL CODE */
monthly_mtd_window AS (
SELECT dt pivot_date, date_trunc(MONTH, dt) dt_from, dt dt_to
FROM fake_dates
WHERE dt < '2020-12-13'::date
)
SELECT pivot_date asdate, sum(sales.revenue) as revenue_mtd
FROM fake_sales
INNERJOIN monthly_mtd_window
ON sales.dt BETWEEN dt_from and dt_to
groupby pivot_date

复制代码

思路拆解：

先通过连续时间列，构造出monthly_mtd_window--pivot_date(统计日)，dt_from日期起点（月初第一天），dt_to日期终点（统计日）。也就是，统计日对应的当月的时间范围；
通过inner join 把每个统计日所在的时间区间内所对应的原始数据找出来，比如说统计统计12-04的月累计数据，在12-01~12-04范围内的原始数据有2条；
根据统计日，聚合步骤2中找出的原始数据。

方法3：开窗函数

这应该是最佳解决方案了。
构造表：以连续日期为主表关联原始数据，不连续的日期位置上的统计量为空。ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ，该窗口代表首行到当前行，这样就能实现在一个范围内聚合。

/* FABRICATE SOME EXAMPLES */
WITH fake_sales AS (
select'2020-12-01'::date dt, 100.00 revenue unionall
select'2020-12-02'::date dt, 200.00 revenue unionall
select'2020-12-08'::date dt, 300.00 revenue unionall
select'2020-12-09'::date dt, 400.00 revenue unionall
select'2020-12-10'::date dt, 500.00 revenue
)
, fake_dates AS (
SELECT'2020-12-01'::date + SEQ4() dt
FROMTABLE (GENERATOR(ROWCOUNT => 31)) v
)
SELECT fake_dates.dt asdate,
sum(sales.revenue) over (orderby fake_dates.dt
ROWSBETWEENUNBOUNDEDPRECEDING
ANDCURRENTROW) as revenue_mtd
FROM fake_dates
LEFTJOIN fake_sales sales
ON sales.dt = fake_dates.dt

复制代码

比如，还可以join on辅助日期表，日期不等条件也可以。
还有啥方法，欢迎各位补充~
欢迎关注个人公众号：DS数说

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！

欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/)