【SQL 实现计算已历完整月份不同日期的场景】

冬月中旬，上海又降温了。穿着厚大的外套、袜子把自己包的严严实实，坐在冰冷的客厅，外面是呼呼狂吹的寒风，接上一杯热水，打开电脑，续上思路。

业务场景

假设现在有一种业务场景，需要给每月平均与公司交易超过30笔的客户打上一个“结算大户”标签（通过已历完整月计算），如月均果交易笔数超过30笔，则是“结算大户”，否则不是。

提示：月均交易笔数 = 客户当前日期下在某个机构的交易总数 / 已历完整月；

思路分析

月均交易笔数重点在已历完整月，可以分为三种情况：

如果是当年1月份非1月底，则已历完整月统计为上一年的12个月；

如果是当年每月月底，则统计当年已历完整月份；

还有一种情况是当年1月份后非月底，统计当年年初1月至当前批量月上一个月（已历完整月）。

实现过程

假设通过数据处理后落成了一张客户交易统计信息表，主要字段包含客户编号（cust_id），统计机构（org_id），交易笔数（trans_cnt）,数据日期（data_dt）（YYYY-MM-DD）。

明确一个概念，批量日期，我们在数据仓库等系统中对数据进行批量处理的日期，会作为一个参数 ${arg:yyyy-mm-dd}（YYYY-MM-DD）在脚本中执行。

SELECT
pp.data_dt     AS data_dt
, pp.cust_id   AS cust_id
, pp.org_id    AS org_id
, 'Y'          AS biz_active_flg   -- 交易活跃标识，即是否为“结算大户”(/Y-是/N-否/)
FROM (--1.当年1月底前-----SELECT${arg:yyyy-mm-dd}        AS data_dt           -- 数据批量日期, cust_id                AS cust_id           -- 客户编号, org_id                 AS org_id            -- 机构编号, SUM( trans_cnt ) / 12  AS trans_cnt_monavg  -- 月均交易笔数FROM table_nameWHERE SUBSTR( ${arg:yyyy-mm-dd}, 6, 5 ) < '01-31'  -- 1 月底前AND SUBSTR( data_dt, 1, 4 ) = SUBSTR( ${arg:yyyy-mm-dd} , 1, 4 ) - 1 -- 已历完整月 上年 12 个月GROUP BY cust_id, org_id--2.当年月底------UNION ALLSELECT${arg:yyyy-mm-dd}        AS data_dt, cust_id                AS cust_id, org_id                 AS org_id, SUM( trans_cnt ) / ( MONTH( SUBSTR( ${arg:yyyy-mm-dd}, 1, 10 ) - 0 )  AS trans_cnt_monavgFROM table_nameWHERE SUBSTR( ${arg:yyyy-mm-dd}, 6, 5 ) >= '01-31' AND SUBSTR( CAST( DATE_ADD( CAST ( ${arg:yyyy-mm-dd AS DATE ), INTERVAL 1 DAY ) AS STRING ) , 9, 2 ) = '01'  -- 月底，通过日期 +1 取下一日是否为1号判断当日是否为月底AND CONCAT( SUBSTR( ${arg:yyyy-mm-dd}, 1, 4 ), '-01' ) <= SUBSTR( data_dt, 1, 7 ) AND SUBSTR( data_dt, 1, 7 ) <= SUBSTR(${arg:yyyy-mm-dd}, 1, 7 ) -- 已历完整月 计算年初 1 月至当前批量月份GROUP BY cust_id, org_id--3.当年1月后非月底------UNION ALL${arg:yyyy-mm-dd}        AS data_dt, cust_id                AS cust_id, org_id                 AS org_idSUM( trans_cnt ) / ( MONTH( SUBSTR( ${arg:yyyy-mm-dd}, 1, 10) ) - 1 )  as trans_cnt_monavgFROM table_nameWHERE SUBSTR( ${arg:yyyy-mm-dd}, 6, 5 ) >= '01-31' AND SUBSTR( CAST( DATE_ADD( CAST ( ${arg:yyyy-mm-dd} AS DATE ), INTERVAL 1 DAY ) AS STRING ) , 9, 2) <> '01'  -- 非月底AND CONCAT( SUBSTR( ${arg:yyyy-mm-dd}, 1, 4 ), '-01' ) <= SUBSTR( data_dt, 1, 7 ) AND SUBSTR( data_dt, 1, 7 ) <= SUBSTR( ${arg:yyyy-mm-dd}, 1, 7 ) -- 已历完整月 计算年初 1 月至当前批量月份GROUP BY cust_id, org_id) pp
WHERE pp.trans_cnt_monavg > 30
;

再提示：在每天的批处理任务中，代码中的：（1.当年1月底前；2.当年月底；3.当年1月后非月底），只有其中某一段逻辑是可以跑出数据的。

以上，完。