我正在尝试插入我从中提取的表中不存在的行,它是按days_to_cancel列排序的,并从零开始,并从新的年/月条目开始。我使用以下查询:
CREATE TABLE reporting.tbl_exec_retention_curve_cumulative (days_to_cancel int, cancels int, cumulative_cancels int, enroll_dt varchar(50));
SELECT rc.days_to_cancel,
rc.cancels,
(@run_total := CASE WHEN rc.days_to_cancel <> 0 THEN @run_total +
rc.cancels ELSE rc.cancels END) AS cumulative_cancels,
LEFT(rc.client_enroll_dt,7) AS client_enroll_dt
FROM (SELECT rc.days_to_cancel,
SUM(CASE WHEN rc.client_status = 'CAN' THEN 1 ELSE 0 END) AS cancels,
LEFT(rc.client_enroll_dt,7) AS client_enroll_dt
FROM t1.table rc
GROUP BY 1,3
ORDER BY 3,1
) rc
JOIN (SELECT @run_total := 0) r
GROUP BY 1,4
ORDER BY 4,1;
我得到了以下示例结果
days_to_cancel | cancels | cumulative_cancels | client_enroll_dt
---------------+---------+--------------------+------------------
42 | 2 | 376 | 2019-02
47 | 0 | 376 | 2019-02
0 | 0 | 0 | 2019-03
10 | 4 | 4 | 2019-03
11 | 9 | 13 | 2019-03
因此,我的目标是输入缺少的天数,并在输入那些缺少的天数的新行时,使用以前的cumulative_cancels和enroll_dt值以及取消列的0。我尝试了多种方法,包括使用变量,但我不知道该怎么做。我不知道是否有可能,考虑到行从一开始就不存在。如果重要的话,我使用的是10.0.35版的MariaDB。
发布于 2019-03-21 05:17:58
我假设您从t1.table
获取数据,并希望将结果插入到同一个表中。
我的解决方案是将结果放入一个临时表中,然后通过left join查找缺少的天数。
SELECT rc.days_to_cancel,
rc.cancels,
(@run_total := CASE WHEN rc.days_to_cancel <> 0 THEN @run_total +
rc.cancels ELSE rc.cancels END) AS cumulative_cancels,
LEFT(rc.client_enroll_dt,7) AS client_enroll_dt
--put your result into a temp table
into #temp
FROM (SELECT rc.days_to_cancel,
SUM(CASE WHEN rc.client_status = 'CAN' THEN 1 ELSE 0 END) AS cancels,
LEFT(rc.client_enroll_dt,7) AS client_enroll_dt
FROM t1.table rc
GROUP BY 1,3
ORDER BY 3,1
) rc
JOIN (SELECT @run_total := 0) r
GROUP BY 1,4
ORDER BY 4,1;
insert into t1.table (days_to_cancel, cancels,cumulative_cancels, client_enroll_dt)
select tm.days_to_cancel, tm.cancels, tm.cumulative_cancels, tm.client_enroll_d from #temp tm
left join t1.table rc on rc.days_to_cancel = tm.days_to_cancel
where rc.days_to_cancel is null --find out missing days
drop table #temp
https://stackoverflow.com/questions/55268487
复制相似问题