当前位置：首页 > article >正文

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

article 2024/11/23 19:31:00

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

业务场景

假设现在有一种业务场景，需要给每月平均与公司交易超过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_name
    WHERE 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 ALL
    SELECT
    ${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_monavg
    FROM table_name
    WHERE 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_id
    SUM( trans_cnt ) / ( MONTH( SUBSTR( ${arg:yyyy-mm-dd}, 1, 10) ) - 1 )  as trans_cnt_monavg
    FROM table_name
    WHERE 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
;