买分产整合

一、背景

大部分采购、分发、使用并产出是3个组织，要求出投产比。需要将这3个组织的日期进行统一。

采购方知道采购日期和消费，但不知道具体数量；

分发方知道分发日期和具体数量，但不知道采购日期；

使用和产出方知道分发日期和产出日期。

说明：第一次分发和第二次分发之间所有的数量均是第一次消费所有购买的。

二、数据准备

2.1 在数据库中建表

-- 建买彩票表
CREATE TABLE [test].[cnblogs].[maicaipiao] (
  [购买日期] date  NULL,
  [消费] float(53)  NULL,
)  
;

INSERT INTO [test].[cnblogs].[maicaipiao]([购买日期], [消费]) VALUES ('2021-01-01', 1000);
INSERT INTO [test].[cnblogs].[maicaipiao]([购买日期], [消费]) VALUES ('2021-02-02', 2000);
INSERT INTO [test].[cnblogs].[maicaipiao]([购买日期], [消费]) VALUES ('2021-03-03', 3000);
INSERT INTO [test].[cnblogs].[maicaipiao]([购买日期], [消费]) VALUES ('2021-04-04', 4000);
INSERT INTO [test].[cnblogs].[maicaipiao]([购买日期], [消费]) VALUES ('2021-05-05', 5000);
INSERT INTO [test].[cnblogs].[maicaipiao]([购买日期], [消费]) VALUES ('2021-06-06', 6000);

;


-- 建分彩票表

CREATE TABLE [test].[cnblogs].[fencaipiao] (
  [分彩票日期] date  NULL,
  [彩票数] float(53)  NULL
)  
;
INSERT INTO [test].[cnblogs].[fencaipiao]([分彩票日期], [彩票数]) VALUES ('2021-01-01', 6);
INSERT INTO [test].[cnblogs].[fencaipiao]([分彩票日期], [彩票数]) VALUES ('2021-01-05', 9);
INSERT INTO [test].[cnblogs].[fencaipiao]([分彩票日期], [彩票数]) VALUES ('2021-02-01', 15);
INSERT INTO [test].[cnblogs].[fencaipiao]([分彩票日期], [彩票数]) VALUES ('2021-02-03', 50);
INSERT INTO [test].[cnblogs].[fencaipiao]([分彩票日期], [彩票数]) VALUES ('2021-02-13', 30);
INSERT INTO [test].[cnblogs].[fencaipiao]([分彩票日期], [彩票数]) VALUES ('2021-03-03', 10);
INSERT INTO [test].[cnblogs].[fencaipiao]([分彩票日期], [彩票数]) VALUES ('2021-03-04', 20);
INSERT INTO [test].[cnblogs].[fencaipiao]([分彩票日期], [彩票数]) VALUES ('2021-04-02', 30);
INSERT INTO [test].[cnblogs].[fencaipiao]([分彩票日期], [彩票数]) VALUES ('2021-04-10', 90);
INSERT INTO [test].[cnblogs].[fencaipiao]([分彩票日期], [彩票数]) VALUES ('2021-05-05', 100);
INSERT INTO [test].[cnblogs].[fencaipiao]([分彩票日期], [彩票数]) VALUES ('2021-06-06', 50);
INSERT INTO [test].[cnblogs].[fencaipiao]([分彩票日期], [彩票数]) VALUES ('2021-06-08', 50);
INSERT INTO [test].[cnblogs].[fencaipiao]([分彩票日期], [彩票数]) VALUES ('2021-06-18', 50);

;


-- 建刮彩票表
CREATE TABLE [test].[cnblogs].[guacaipiao] (
  [刮彩票日期] date  NULL,
  [分彩票日期] date  NULL,
  [中奖金额] float(53)  NULL
)  
;

INSERT INTO [test].[cnblogs].[guacaipiao]([刮彩票日期], [分彩票日期], [中奖金额]) VALUES ('2021-01-01', '2021-01-01', 100);
INSERT INTO [test].[cnblogs].[guacaipiao]([刮彩票日期], [分彩票日期], [中奖金额]) VALUES ('2021-01-02', '2021-01-01', 101);
INSERT INTO [test].[cnblogs].[guacaipiao]([刮彩票日期], [分彩票日期], [中奖金额]) VALUES ('2021-01-06', '2021-01-01', 102);
INSERT INTO [test].[cnblogs].[guacaipiao]([刮彩票日期], [分彩票日期], [中奖金额]) VALUES ('2021-02-03', '2021-02-03', 103);
INSERT INTO [test].[cnblogs].[guacaipiao]([刮彩票日期], [分彩票日期], [中奖金额]) VALUES ('2021-02-13', '2021-02-13', 104);
INSERT INTO [test].[cnblogs].[guacaipiao]([刮彩票日期], [分彩票日期], [中奖金额]) VALUES ('2021-03-04', '2021-03-03', 105);
INSERT INTO [test].[cnblogs].[guacaipiao]([刮彩票日期], [分彩票日期], [中奖金额]) VALUES ('2021-03-05', '2021-03-03', 106);
INSERT INTO [test].[cnblogs].[guacaipiao]([刮彩票日期], [分彩票日期], [中奖金额]) VALUES ('2021-03-06', '2021-03-03', 107);
INSERT INTO [test].[cnblogs].[guacaipiao]([刮彩票日期], [分彩票日期], [中奖金额]) VALUES ('2021-03-04', '2021-03-04', 108);
INSERT INTO [test].[cnblogs].[guacaipiao]([刮彩票日期], [分彩票日期], [中奖金额]) VALUES ('2021-03-05', '2021-03-04', 109);
INSERT INTO [test].[cnblogs].[guacaipiao]([刮彩票日期], [分彩票日期], [中奖金额]) VALUES ('2021-03-06', '2021-03-04', 110);
INSERT INTO [test].[cnblogs].[guacaipiao]([刮彩票日期], [分彩票日期], [中奖金额]) VALUES ('2021-08-15', '2021-04-02', 111);
INSERT INTO [test].[cnblogs].[guacaipiao]([刮彩票日期], [分彩票日期], [中奖金额]) VALUES ('2022-08-15', '2021-04-02', 112);
INSERT INTO [test].[cnblogs].[guacaipiao]([刮彩票日期], [分彩票日期], [中奖金额]) VALUES ('2021-04-10', '2021-04-10', 113);
INSERT INTO [test].[cnblogs].[guacaipiao]([刮彩票日期], [分彩票日期], [中奖金额]) VALUES ('2021-07-06', '2021-06-06', 114);
INSERT INTO [test].[cnblogs].[guacaipiao]([刮彩票日期], [分彩票日期], [中奖金额]) VALUES ('2021-07-16', '2021-06-06', 115);

2.2 Excel中查看数据形式

三、思路提示

对刮彩票表的分彩票日期进行聚合，求出每个分彩票日期的中奖金额，形成临时表#1
分彩票表左联#1，求出购买日期和彩票数、中奖金额之间的关系，形成临时表#2
买彩票表左联#2，求出购买日期、消费、彩票数、中奖金额之间的关系
推出股票单价（CPA）和投产（ROI）之间的关系

四、代码展示

4.1 基本过程

-- 将中奖金额按照分彩票日期进行聚合
DROP TABLE IF EXISTS #1 ;
SELECT 分彩票日期,SUM(中奖金额) as '中奖金额'
INTO  #1
FROM [test].[cnblogs].[guacaipiao] 
GROUP BY 分彩票日期
;

-- 形式分彩票日期、彩票数、中奖金额之间的关系
DROP TABLE IF EXISTS #2 ;
SELECT A.分彩票日期,A.彩票数,B.中奖金额 
INTO #2
FROM [test].[cnblogs].[fencaipiao]  AS A
LEFT JOIN  #1 AS B
ON A.分彩票日期=B.分彩票日期
;

-- 第一次分发和第二次分发之间所有的数量均是第一次消费所有购买的
DROP TABLE IF EXISTS #3 ;
SELECT * 
INTO #3
FROM
(
SELECT *,
Row_Number() OVER (partition by B.分彩票日期,B.彩票数,B.中奖金额 ORDER BY A.购买日期  DESC ) AS '购买日期逆排'
FROM [test].[cnblogs].[maicaipiao] AS A
CROSS JOIN #2 AS B
WHERE A.购买日期<= B.分彩票日期
)AS t1
WHERE 购买日期逆排 = 1

4.2 结果展示

SELECT 购买日期,MIN(消费) as '消费',SUM(彩票数) as '彩票数',SUM(中奖金额) as '中奖金额',
MIN(消费)/SUM(彩票数) AS 'CPA',SUM(中奖金额)/MIN(消费) AS 'ROI'
from #3
GROUP BY 购买日期

posted @ 2021-08-15 14:04 qsl_你猜阅读(56) 评论(0) 编辑收藏举报

刷新页面返回顶部

qsl_你猜