调整特定于月份的 SQL 查询，以跨大于基准月的所有月份进行迭代

Question

我继承了一个查询，该查询具有指定单个所需月份的拉取数据的参数。然后，数据提取将手动添加到 Excel 中上个月的数据提取中。我想通过调整现有查询来消除手动部分，以迭代大于给定基准月的所有月份，然后(如果这是最有意义的)合并各个"最终"输出。

我的尝试是将每个特定月份的整个代码块添加到现有代码中，然后一起运行。我的想法是，我每个新月都会粘贴一个新块。我知道这是非常低效的，但我没有奢侈地学习如何有效地做到这一点，所以如果它有效，我会很高兴。

我遇到了问题，因为现有查询有两个子查询，然后用于创建最终表，并且我无法弄清楚如何在代码末尾保留最终表，以便以后可以在联合中引用它(fwiw，我试图对最终表使用 Select Into)。

with eligibility_and_customer_type AS                           
(SELECT DISTINCT ON(sub_id, mbr_sfx_id)                         
sub_id      AS subscriber_id                           
, mbr_sfx_id  AS member_suffix_id                           
, src_mbr_key                           
, ctdv.cstmr_typ_cd                         
, gdv.grp_name                          
FROM adw_common.cstmr_typ_dim_vw ctdv                           
JOIN adw_common.mbr_eligty_by_mo_fact_vw                            
ON ctdv.cstmr_typ_key = mbr_eligty_by_mo_fact_vw.cstmr_typ_key                          
AND mbr_eligty_yr = '2018'                          
AND mbr_eligty_mo = '12'                              
JOIN adw_common.prod_cat_dim_vw                         
ON prod_cat_dim_vw.prod_cat_key = mbr_eligty_by_mo_fact_vw.prod_cat_key                         
AND prod_cat_dim_vw.prod_cat_cd = 'M'                           
JOIN adw_common.mbr_dim_abr                         
ON mbr_eligty_by_mo_fact_vw.mbr_key = mbr_dim_abr.mbr_key                           
JOIN consumer.facets_xref_abr fxf                           
ON mbr_dim_abr.src_mbr_key = fxf.source_member_key                          
JOIN adw_common.grp_dim_vw gdv                          
ON gdv.grp_key=mbr_eligty_by_mo_fact_vw.grp_key),                           
facets_ip as                            
(select distinct cl.meme_ck                            
FROM gpgen_cr_ai.cmc_clcl_claim_abr cl                            
/* LEFT JOIN gpgen_cr_ai.cmc_clhp_hosp_abr ch                            
ON cl.clcl_id = ch.clcl_id*/                            
LEFT JOIN gpgen_cr_ai.cmc_cdml_cl_line cd                           
ON cl.clcl_id = cd.clcl_id                          
WHERE cd.pscd_id = '21'                         
/*AND ch.clcl_id IS NULL*/                          
AND cl.clcl_cur_sts NOT IN ('91','92')                          
AND cl.clcl_low_svc_dt  >= '20181201'                       
and cl.clcl_low_svc_dt <= '20181231'                     
group by 1)                          

select distinct c.meme_ck,                            
e.cstmr_typ_cd,
'201812' as Yearmo                         
from facets_ip c                          
left join eligibility_and_customer_type e                         
on c.meme_ck = e.src_mbr_key; 

上面的代码具有日期参数，可在必要时进行更新。

最终输出将是上面创建的最终表的一个版本，但结果对应于，例如，201801 - 存在。

Answer 1

如果您提供：

1. 基础表的 DDL
2. 基础表的示例数据
3. 预期结果集
4. 您正在使用的数据库管理系统

，那么人们将能够在这里提供最佳解决方案。

在不了解它们的情况下，正如您所说，您只关心每个月动态循环，这是您可以利用代码在 SQL Server 中循环访问它的一种方法。请填写变量@StartDate和@EndDate值，并为meme_ck和cstmr_typ_cd提供正确的数据类型。

IF OBJECT_ID ('tempdb..#TempTable', N'U') IS NOT NULL
BEGIN
DROP TABLE #TempTable
END
CREATE  TABLE   #TempTable
(
meme_ck         <ProvideProperDataTypeHere>
,cstmr_typ_cd   <ProvideProperDataTypeHere>
,Yearmo         VARCHAR(10)
)
DECLARE @StartDate  DATE = '<Provide the first day of the start month>'
DECLARE @EndDate    DATE = '<Provide the end date inclusive>'
WHILE @StartDate <= @EndDate
BEGIN
DECLARE @MonthEndDate DATE = CASE WHEN DATEADD(DAY, -1, DATEADD(MONTH, 1, @StartDate)) <= @EndDate THEN DATEADD(DAY, -1, DATEADD(MONTH, 1, @StartDate)) ELSE @EndDate END
DECLARE @MonthYear VARCHAR(6) = LEFT(CONVERT(VARCHAR(8), @StartDate, 112), 6)

--This is your code which I am not touching without not knowing any detail about it. Just feeding the variables to make it dynamic
;with eligibility_and_customer_type AS                           
(SELECT DISTINCT ON(sub_id, mbr_sfx_id)                         
sub_id      AS subscriber_id                           
, mbr_sfx_id  AS member_suffix_id                           
, src_mbr_key                           
, ctdv.cstmr_typ_cd                         
, gdv.grp_name                          
FROM adw_common.cstmr_typ_dim_vw ctdv                           
JOIN adw_common.mbr_eligty_by_mo_fact_vw                            
ON ctdv.cstmr_typ_key = mbr_eligty_by_mo_fact_vw.cstmr_typ_key                          
AND mbr_eligty_yr = CAST(YEAR(@StartDate) AS VARCHAR(10))   -- NO need to cast if mbr_eligty_yr is an Integer
AND mbr_eligty_mo = CAST(MONTH(@StartDate) AS VARCHAR(10))  -- NO need to cast if mbr_eligty_yr is an Integer
JOIN adw_common.prod_cat_dim_vw                         
ON prod_cat_dim_vw.prod_cat_key = mbr_eligty_by_mo_fact_vw.prod_cat_key                         
AND prod_cat_dim_vw.prod_cat_cd = 'M'                           
JOIN adw_common.mbr_dim_abr                         
ON mbr_eligty_by_mo_fact_vw.mbr_key = mbr_dim_abr.mbr_key                           
JOIN consumer.facets_xref_abr fxf                           
ON mbr_dim_abr.src_mbr_key = fxf.source_member_key                          
JOIN adw_common.grp_dim_vw gdv                          
ON gdv.grp_key=mbr_eligty_by_mo_fact_vw.grp_key),                           
facets_ip as                            
(select distinct cl.meme_ck                            
FROM gpgen_cr_ai.cmc_clcl_claim_abr cl                            
/* LEFT JOIN gpgen_cr_ai.cmc_clhp_hosp_abr ch                            
ON cl.clcl_id = ch.clcl_id*/                            
LEFT JOIN gpgen_cr_ai.cmc_cdml_cl_line cd                           
ON cl.clcl_id = cd.clcl_id                          
WHERE cd.pscd_id = '21'                         
/*AND ch.clcl_id IS NULL*/                          
AND cl.clcl_cur_sts NOT IN ('91','92')                          
AND cl.clcl_low_svc_dt  BETWEEN @StartDate AND @MonthEndDate                  
group by 1)                    
INSERT INTO #TempTable
(
meme_ck         
,cstmr_typ_cd   
,Yearmo         
)
select distinct c.meme_ck,                            
e.cstmr_typ_cd,
@MonthYear as Yearmo                         
from facets_ip c                          
left join eligibility_and_customer_type e                         
on c.meme_ck = e.src_mbr_key; 

SET @StartDate = DATEADD(MONTH, 1, @StartDate)
END
SELECT * FROM #TempTable;

Answer 2

我没有足够的表格信息来真正创建最佳解决方案。 我提供的解决方案只有一个参数(表名)，对于您的解决方案，您需要为日期过滤器传入一个附加参数。

"循环"的想法不是你在Greenplum需要做的事情。 这对于像SQL Server或Oracle这样的OLTP数据库来说很常见，它们不能很好地处理大数据，并且必须一次处理较小的数据量。

对于这些示例解决方案，需要一个包含一些数据的表。

CREATE TABLE public.foo
(id integer,
fname text,
lname text)
DISTRIBUTED BY (id);
insert into foo values (1, 'jon', 'roberts'), 
(2, 'sam', 'roberts'),
(3, 'jon', 'smith'),
(4, 'sam', 'smith'),
(5, 'jon', 'roberts'),
(6, 'sam', 'roberts'),
(7, 'jon', 'smith'),
(8, 'sam', 'smith');

解决方案 1：了解函数在数据库中的工作方式。 下面是它如何工作的快速示例。

创建一个函数，该函数执行创建表为选择 (CTAS)，您可以在其中传入参数。

注意：您无法直接在函数中执行 DDL 语句，因此必须改用"EXECUTE"。

create or replace function fn_test(p_table_name text) returns void as
$$
declare
v_sql text;
begin
v_sql :='drop table if exists ' || p_table_name;
execute v_sql;
v_sql := 'create table ' || p_table_name || ' with (appendonly=true, compresstype=quicklz) as
with t as (select * from foo)
select * from t
distributed by (id)';
execute v_sql;
end;
$$
language plpgsql;

使用简单的 select 语句执行函数。

select fn_test('foo3');

请注意我如何传入执行函数时将创建的表名。

解决方案 2：使用 psql 变量

创建包含以下内容的 sql 文件名"test.sql"。

drop table if exists :p_table_name;
create table :p_table_name with (appendonly=true, compresstype=quicklz) as
with t as (select * from foo)
select * from t
distributed by (id);

接下来，执行 psql 并传入变量p_table_name。

psql -f test.sql -v p_table_name=foo4
psql:test.sql:1: NOTICE:  table "foo4" does not exist, skipping
DROP TABLE
SELECT 8