活动数据提取SQL

发表于 分类于 ， 阅读次数：

交易统计

1
2
SELECT pay_activity_id AS "活动id",trade_type AS "交易类型",COUNT(*) AS "交易笔数" , SUM(trade_amount) AS "交易金额", SUM(less_amount) AS "优惠金额" 
FROM nawesm_pay_activity_trade_temp_copy GROUP BY pay_activity_id,trade_type;

增加活动名称

1
2
3
4
5
6
SELECT activity_name,datas.* FROM 
	(SELECT pay_activity_id,trade_type AS "交易类型",COUNT(*) AS "交易笔数" , SUM(trade_amount) AS "交易金额", SUM(less_amount) AS "优惠金额" 
	 FROM nawesm_pay_activity_trade_temp_copy GROUP BY pay_activity_id,trade_type) AS datas
LEFT JOIN
	nawesm_pay_activity_info
ON nawesm_pay_activity_info.unionpay_activity_no = datas.pay_activity_id

人数统计

仅交易表查询

1
SELECT pay_activity_id AS "活动id",COUNT(DISTINCT(user_id)) AS '参与用户数' FROM nawesm_pay_activity_trade_temp_copy GROUP BY pay_activity_id;

增加活动名称

1
2
3
4
5
SELECT activity_name,datas.* FROM 
	(SELECT pay_activity_id,COUNT(DISTINCT(user_id)) AS '参与用户数' FROM nawesm_pay_activity_trade_temp_copy GROUP BY pay_activity_id) AS datas
LEFT JOIN
	nawesm_pay_activity_info
ON nawesm_pay_activity_info.unionpay_activity_no = datas.pay_activity_id

分月交易统计

1
2
SELECT pay_activity_id AS "活动id",SUBSTRING(trade_time,1,7) AS "月份",trade_type AS "交易类型",COUNT(*) AS "交易笔数" , SUM(trade_amount) AS "交易金额", SUM(less_amount) AS "优惠金额" 
FROM nawesm_pay_activity_trade_temp_copy GROUP BY pay_activity_id,SUBSTRING(trade_time,1,7),trade_type;

增加活动信息关联

1
2
3
4
5
6
SELECT activity_name,datas.* FROM 
	(SELECT pay_activity_id,SUBSTRING(trade_time,1,7) AS "月份",trade_type AS "交易类型",COUNT(*) AS "交易笔数" , SUM(trade_amount) AS "交易金额", SUM(less_amount) AS "优惠金额" 
FROM nawesm_pay_activity_trade_temp_copy GROUP BY pay_activity_id,SUBSTRING(trade_time,1,7),trade_type) AS datas
LEFT JOIN
	nawesm_pay_activity_info
ON nawesm_pay_activity_info.unionpay_activity_no = datas.pay_activity_id

分月人数统计

1
2
SELECT pay_activity_id AS "活动id",SUBSTRING(trade_time,1,7) AS "月份",COUNT(DISTINCT(user_id)) AS '参与用户数' 
FROM nawesm_pay_activity_trade_temp_copy GROUP BY pay_activity_id,SUBSTRING(trade_time,1,7)

增加活动信息关联后

1
2
3
4
5
6
SELECT activity_name,datas.* FROM 
	(SELECT pay_activity_id ,SUBSTRING(trade_time,1,7) AS "月份",COUNT(DISTINCT(user_id)) AS '参与用户数' 
FROM nawesm_pay_activity_trade_temp_copy GROUP BY pay_activity_id,SUBSTRING(trade_time,1,7)) AS datas
LEFT JOIN
	nawesm_pay_activity_info
ON nawesm_pay_activity_info.unionpay_activity_no = datas.pay_activity_id