sdd_weekly.sqlx

sdd_weekly.sqlx

-- ! =============== 通用方法 ==================
func daily(table_name):
	-- 每日统计加上周统计日字段
	(
		SELECT 
			*, 
			CASE WHEN date_format(date(`日期`),'%w') < 5 
				THEN subdate(date(`日期`), date_format(date(`日期`),'%w') -4 ) 
				ELSE subdate(date(`日期`), date_format(date(`日期`),'%w') -11 ) 
			END as `统计日` 
		FROM {table_name}
		WHERE
			date(`日期`) >= '2020-01-01'
	) as daily
end


func month_target():
	-- 当月目标
	(
		SELECT * FROM target WHERE `时间` = REPLACE(LEFT(CURDATE(),7), '-', '')
	) as month_target
end


func year_target():
    -- 当年目标
	(
		SELECT * FROM target WHERE `时间` = LEFT(CURDATE(), 4)
	) as year_target
end

func sum(field_name):
    -- ! 累加后如果是 Null 则会使用 0 代替
	COALESCE(SUM({field_name}), 0)
end


func wsum(field_name, n):
    -- ! 累加后如果是 Null 则会使用 0 代替, 然后除以 10000
    -- ! 也就是统计结果以万为单位
    -- ! 保留 n 位小数
	ROUND(COALESCE(SUM({field_name}), 0) / 10000, {n})
end
-- ===============================================



-- ================ DDC 周业务情况 ================
SELECT 
	`统计日`, 
	{wsum(`调用量`,1)} as `调用量`,
	ROUND({wsum(`税前收入`,1)} / 1.06, 2) as `管理收入`
FROM
	{daily(daily_ddc)}
GROUP BY
	`统计日`
;


-- ================ DDC 月度累计 ================
SELECT 
	'月度' as `标题`,
	{wsum(`调用量`,1)} as `调用量`,
	ROUND({wsum(`税前收入`,1)} / 1.06, 2) as `管理收入`,
	ROUND({sum(`税前收入`)} / 1.06 / `ddc_收入目标` * 100, 1) AS `收入达成率`
FROM
	daily_ddc
LEFT JOIN
	{month_target()}
ON
	1=1
WHERE
	LEFT(`日期`, 6) = REPLACE(LEFT(CURDATE(),7), '-', '')
;

-- ================ DDC 年度累计 ================
SELECT 
	'年度' as `标题`,
	{wsum(`调用量`,1)} as `调用量`,
	ROUND({wsum(`税前收入`,1)} / 1.06, 2) as `管理收入`,
	ROUND({sum(`税前收入`)} / 1.06 / `ddc_收入目标` * 100, 1) AS `收入达成率`
FROM
	daily_ddc
LEFT JOIN
	{year_target()}
ON
	1=1
WHERE
	LEFT(`日期`, 4) = LEFT(CURDATE(), 4)
;


-- ================ DMS 周业务情况 ================
SELECT 
	`统计日`, 
	{wsum(`GMV`,0)} as `GMV`,
	{wsum(`交易笔数`,0)} as `交易笔数`
FROM
	{daily(daily_dms)}
GROUP BY
	`统计日`
;


-- ================ DMS 月度累计 ================
SELECT 
	'月度' as `标题`,
	{wsum(`GMV`,0)} as `GMV`,
	{wsum(`交易笔数`,0)} as `交易笔数`,
	ROUND({sum(`GMV`)} / `DMS_GMV目标` * 100, 1) AS `GMV达成率`
FROM
	daily_dms
LEFT JOIN
	{month_target()}
ON
	1=1
WHERE
	LEFT(`日期`, 6) = REPLACE(LEFT(CURDATE(),7), '-', '')
;

-- ================ DMS 年度累计 ================
SELECT 
	'年度' as `标题`,
	{wsum(`GMV`,0)} as `GMV`,
	{wsum(`交易笔数`,0)} as `交易笔数`,
	ROUND({sum(`GMV`)} / `DMS_GMV目标` * 100, 1) AS `GMV达成率`
FROM
	daily_dms
LEFT JOIN
	{year_target()}
ON
	1=1
WHERE
	LEFT(`日期`, 4) = LEFT(CURDATE(), 4)
;


-- ================ ATD 周业务情况 ================
SELECT 
	`统计日`, 
	{wsum(`分发收入`,0)} + {wsum(`数据服务收入`,0)} + {wsum(`建模收入`,0)} + {wsum(`其他收入`,0)} as `管理收入`,
	ROUND(AVG(`覆盖率`) * 100, 1) as `覆盖率`,
	ROUND(AVG(`命中率`) * 100, 1) as `命中率`
FROM
	{daily(daily_atd)}
GROUP BY
	`统计日`
;


-- ================ ATD 月度累计 ================
SELECT 
	'月度' as `标题`,
	{wsum(`分发收入`,0)} + {wsum(`数据服务收入`,0)} + {wsum(`建模收入`,0)} + {wsum(`其他收入`,0)} as `管理收入`,	
	ROUND(({sum(`分发收入`)} + {sum(`数据服务收入`)} + {sum(`建模收入`)} + {sum(`其他收入`)}) / `ATD_管理收入目标` * 100, 1) AS `收入达成率`
FROM
	daily_atd
LEFT JOIN
	{month_target()}
ON
	1=1
WHERE
	LEFT(`日期`, 6) = REPLACE(LEFT(CURDATE(),7), '-', '')
;

-- ================ ATD 年度累计 ================
SELECT 
	'年度' as `标题`,
	{wsum(`分发收入`,0)} + {wsum(`数据服务收入`,0)} + {wsum(`建模收入`,0)} + {wsum(`其他收入`,0)} as `管理收入`,	
	ROUND(({sum(`分发收入`)} + {sum(`数据服务收入`)} + {sum(`建模收入`)} + {sum(`其他收入`)}) / `ATD_管理收入目标` * 100, 1) AS `收入达成率`
FROM
	daily_atd
LEFT JOIN
	{year_target()}
ON
	1=1
WHERE
	LEFT(`日期`, 4) = LEFT(CURDATE(), 4)
;


-- ================ BDM 周业务情况 ================
SELECT
	t1.`统计日`,
	ROUND(`商品销售额` / 10000) as `商品销售额`,
	ROUND(`分期手续费` / 10000) as `分期手续费`
FROM
	(
		SELECT 
			`统计日`, 
			{sum(`商品销售额`)} as `商品销售额`
		FROM
			{daily(bdm_tm.ads_merchandise_sales)}
		GROUP BY
			`统计日`
	) as t1
LEFT JOIN
	(
		SELECT 
			`统计日`, 
			{sum(`分期手续费`)} as `分期手续费`
		FROM
			{daily(bdm_tm.ads_stmt)}
		GROUP BY
			`统计日`
	) as t2
ON
	t1.`统计日` = t2.`统计日`
;


-- ================ BDM 月度累计 ================

SELECT 
	`标题`,
	CASE WHEN `标题` = '商品销售额' THEN `商品销售额` 
		 WHEN `标题` = '分期手续费' THEN `分期手续费` 
	END as `月度累计`,
	CASE WHEN `标题` = '商品销售额' THEN `商品销售额达成率` 
		 WHEN `标题` = '分期手续费' THEN `分期手续费达成率` 
	END as `达成率`
FROM
	(
		SELECT 
			{wsum(`商品销售额`,0)} as `商品销售额`,
			{wsum(`分期手续费`,0)} as `分期手续费`,
			ROUND({sum(`商品销售额`)} / `BDM_商品销售额目标` * 100, 1) AS `商品销售额达成率`,
			ROUND({sum(`分期手续费`)} / `BDM_分期手续费目标` * 100, 1) AS `分期手续费达成率`
		FROM
			(
				SELECT 
					`日期`, 
					{sum(`商品销售额`)} as `商品销售额`
				FROM
					bdm_tm.ads_merchandise_sales
				GROUP BY
					`日期`
			) as t1
		LEFT JOIN
			(
				SELECT 
					`日期`, 
					{sum(`分期手续费`)} as `分期手续费`
				FROM
					bdm_tm.ads_stmt
				GROUP BY
					`日期`
			) as t2
		ON
			t1.`日期` = t2.`日期`
		LEFT JOIN
			{month_target()}
		ON
			1=1
		WHERE
			LEFT(t1.`日期`, 7) = LEFT(CURDATE(),7)
	) as t1
LEFT JOIN
	(
		SELECT '商品销售额' as `标题` union SELECT '分期手续费' as `标题`
	) as t2
ON
	1=1
;


-- ================ BDM 年度累计 ================
SELECT 
	`标题`,
	CASE WHEN `标题` = '商品销售额' THEN `商品销售额` 
		 WHEN `标题` = '分期手续费' THEN `分期手续费` 
	END as `年度累计`,
	CASE WHEN `标题` = '商品销售额' THEN `商品销售额达成率` 
		 WHEN `标题` = '分期手续费' THEN `分期手续费达成率` 
	END as `达成率`
FROM
	(
		SELECT 
			{wsum(`商品销售额`,0)} as `商品销售额`,
			{wsum(`分期手续费`,0)} as `分期手续费`,
			ROUND({sum(`商品销售额`)} / `BDM_商品销售额目标` * 100, 1) AS `商品销售额达成率`,
			ROUND({sum(`分期手续费`)} / `BDM_分期手续费目标` * 100, 1) AS `分期手续费达成率`
		FROM
			(
				SELECT 
					`日期`, 
					{sum(`商品销售额`)} as `商品销售额`
				FROM
					bdm_tm.ads_merchandise_sales
				GROUP BY
					`日期`
			) as t1
		LEFT JOIN
			(
				SELECT 
					`日期`, 
					{sum(`分期手续费`)} as `分期手续费`
				FROM
					bdm_tm.ads_stmt
				GROUP BY
					`日期`
			) as t2
		ON
			t1.`日期` = t2.`日期`
		LEFT JOIN
			{year_target()}
		ON
			1=1
		WHERE
			LEFT(t1.`日期`, 4) = LEFT(CURDATE(), 4)
	) as t1
LEFT JOIN
	(
		SELECT '商品销售额' as `标题` union SELECT '分期手续费' as `标题`
	) as t2
ON
	1=1
;


-- ================ ISB 周业务情况 ================
SELECT 
	`统计日`, 
	{wsum(`预收保费`,1)} as `新增保费`  -- 标题是新增保费，实际数据用的是预收保费
FROM
	{daily(daily_isb)}
GROUP BY
	`统计日`
;


-- ================ ISB 月度累计 ================
SELECT 
	'月度' as `标题`,
	{wsum(`预收保费`,1)} as `新增保费`,	
	ROUND({sum(`预收保费`)} / `ISB_新增保费目标` * 100, 1) AS `新增保费达成率`
FROM
	daily_isb
LEFT JOIN
	{month_target()}
ON
	1=1
WHERE
	LEFT(`日期`, 6) = REPLACE(LEFT(CURDATE(),7), '-', '')
;


-- ================ ISB 年度累计 ================
SELECT 
	'年度' as `标题`,
	{wsum(`预收保费`,1)} as `新增保费`,	
	ROUND({sum(`预收保费`)} / `ISB_新增保费目标` * 100, 1) AS `新增保费达成率`,
	{wsum(`续收保费`,1)} as `续收保费`,	
	ROUND({sum(`续收保费`)} / `ISB_续收保费目标` * 100, 1) AS `续收保费达成率`
FROM
	daily_isb
LEFT JOIN
	{year_target()}
ON
	1=1
WHERE
	LEFT(`日期`, 4) = LEFT(CURDATE(), 4)
;