同享会数据分析

1.经纪人类别分布计算

-- 全量注册经纪人分布
SELECT
    a.AgentType as '经纪人类别',
    COUNT (*) as '数量'
FROM
    Agent a
WHERE
    a.Deleted = 0
GROUP BY
    a.AgentType;
-- 2014年注册经纪人分布
SELECT
    a.AgentType as '经纪人类别',
    COUNT (*) as '数量'
FROM
    Agent a
WHERE
    a.Deleted = 0 and RegisterTime < '2015-01-01 00:00:00.533'
GROUP BY
    a.AgentType;
-- 2015年注册经纪人分布
SELECT
    a.AgentType as '经纪人类别',
    COUNT (*) as '数量'
FROM
    Agent a
WHERE
    a.Deleted = 0 and RegisterTime < '2016-01-01 00:00:00.533' and RegisterTime > '2015-01-01 00:00:00.533'
GROUP BY
    a.AgentType;
-- 2016 年注册经纪人分布
SELECT
    a.AgentType as '经纪人类别',
    COUNT (*) as '数量'
FROM
    Agent a
WHERE
    a.Deleted = 0 and  RegisterTime > '2016-01-01 00:00:00.533'
GROUP BY
    a.AgentType;
View Code

2.年份经纪人有效性分布

  1 SELECT
  2     '2014年经纪人注册总量',
  3     COUNT (*)
  4 FROM
  5     Agent a
  6 WHERE
  7     a.Deleted = 0
  8 AND RegisterTime < '2015-01-01 00:00:00.533'
  9 UNION ALL
 10     SELECT
 11         '2014年有效经纪人总量',
 12         COUNT (DISTINCT a.ID)
 13     FROM
 14         Agent a
 15     LEFT JOIN Customer b ON CONVERT (VARCHAR, a.ID) = CONVERT (VARCHAR, b.AgentID)
 16     WHERE
 17         a.Deleted = 0
 18     AND a.RegisterTime < '2015-01-01 00:00:00.533'
 19     AND b.Deleted = 0
 20     AND b.RecommendTime < '2015-01-01 00:00:00.533'
 21     UNION ALL
 22         SELECT
 23             '2014年有成交经纪人总量',
 24             COUNT (DISTINCT a.ID)
 25         FROM
 26             Agent a
 27         LEFT JOIN Customer b ON CONVERT (VARCHAR, a.ID) = CONVERT (VARCHAR, b.AgentID)
 28         WHERE
 29             a.Deleted = 0
 30         AND a.RegisterTime < '2015-01-01 00:00:00.533'
 31         AND b.Deleted = 0
 32         AND b.RecommendTime < '2015-01-01 00:00:00.533'
 33         AND b.Status IN ('5', '6', '7')
 34         UNION ALL
 35             SELECT
 36                 '2015年经纪人注册总量',
 37                 COUNT (*)
 38             FROM
 39                 Agent a
 40             WHERE
 41                 a.Deleted = 0
 42             AND RegisterTime < '2016-01-01 00:00:00.533'
 43             AND RegisterTime > '2015-01-01 00:00:00.533'
 44             UNION ALL
 45                 SELECT
 46                     '2015年有效经纪人总量',
 47                     COUNT (DISTINCT a.ID)
 48                 FROM
 49                     Agent a
 50                 LEFT JOIN Customer b ON CONVERT (VARCHAR, a.ID) = CONVERT (VARCHAR, b.AgentID)
 51                 WHERE
 52                     a.Deleted = 0
 53                 AND a.RegisterTime < '2016-01-01 00:00:00.533'
 54                 AND a.RegisterTime > '2015-01-01 00:00:00.533'
 55                 AND b.Deleted = 0
 56                 AND b.RecommendTime < '2016-01-01 00:00:00.533'
 57                 UNION ALL
 58                     SELECT
 59                         '2015年有成交经纪人总量',
 60                         COUNT (DISTINCT a.ID)
 61                     FROM
 62                         Agent a
 63                     LEFT JOIN Customer b ON CONVERT (VARCHAR, a.ID) = CONVERT (VARCHAR, b.AgentID)
 64                     WHERE
 65                         a.Deleted = 0
 66                     AND a.RegisterTime < '2016-01-01 00:00:00.533'
 67                     AND a.RegisterTime > '2015-01-01 00:00:00.533'
 68                     AND b.Deleted = 0
 69                     AND b.RecommendTime < '2016-01-01 00:00:00.533'
 70                     AND b.Status IN ('5', '6', '7')
 71                     UNION ALL
 72                         SELECT
 73                             '2016年经纪人注册总量',
 74                             COUNT (*)
 75                         FROM
 76                             Agent a
 77                         WHERE
 78                             a.Deleted = 0
 79                         AND RegisterTime > '2016-01-01 00:00:00.533' -- 2015 
 80                         -- 2016
 81                         UNION ALL
 82                             SELECT
 83                                 '2016年有效经纪人总量',
 84                                 COUNT (DISTINCT a.ID)
 85                             FROM
 86                                 Agent a
 87                             LEFT JOIN Customer b ON CONVERT (VARCHAR, a.ID) = CONVERT (VARCHAR, b.AgentID)
 88                             WHERE
 89                                 a.Deleted = 0
 90                             AND a.RegisterTime > '2016-01-01 00:00:00.533'
 91                             AND b.Deleted = 0
 92                             AND b.RecommendTime > '2016-01-01 00:00:00.533'
 93                             UNION ALL
 94                                 SELECT
 95                                     '2016年有成交经纪人总量',
 96                                     COUNT (DISTINCT a.ID)
 97                                 FROM
 98                                     Agent a
 99                                 LEFT JOIN Customer b ON CONVERT (VARCHAR, a.ID) = CONVERT (VARCHAR, b.AgentID)
100                                 WHERE
101                                     a.Deleted = 0
102                                 AND a.RegisterTime > '2016-01-01 00:00:00.533'
103                                 AND b.Deleted = 0
104                                 AND b.RecommendTime > '2016-01-01 00:00:00.533'
105                                 AND b.Status IN ('5', '6', '7')
106                                 UNION ALL
107                                     SELECT
108                                         '全量经纪人注册总量',
109                                         COUNT (*)
110                                     FROM
111                                         Agent a
112                                     WHERE
113                                         a.Deleted = 0
114                                     UNION ALL
115                                         SELECT
116                                             '全量有效经纪人总量',
117                                             COUNT (DISTINCT a.ID)
118                                         FROM
119                                             Agent a
120                                         LEFT JOIN Customer b ON CONVERT (VARCHAR, a.ID) = CONVERT (VARCHAR, b.AgentID)
121                                         WHERE
122                                             a.Deleted = 0
123                                         AND b.Deleted = 0
124                                         UNION ALL
125                                             SELECT
126                                                 '全量有成交经纪人总量',
127                                                 COUNT (DISTINCT a.ID)
128                                             FROM
129                                                 Agent a
130                                             LEFT JOIN Customer b ON CONVERT (VARCHAR, a.ID) = CONVERT (VARCHAR, b.AgentID)
131                                             WHERE
132                                                 a.Deleted = 0
133                                             AND b.Deleted = 0
134                                             AND b.Status IN ('5', '6', '7') -- 2015
View Code

3.经纪人类别，客户状态

 1 SELECT
 2     '所有推荐',
 3     b.AgentType,
 4     COUNT (a.DC_ID)
 5 FROM
 6     Customer a
 7 LEFT JOIN Agent b ON CONVERT (VARCHAR, b.ID) = CONVERT (VARCHAR, a.AgentID)
 8 WHERE
 9     b.Deleted = 0
10 AND a.Deleted = 0
11 GROUP BY
12     b.AgentType
13 UNION ALL
14     SELECT
15         '已到访',
16         b.AgentType,
17         COUNT (a.DC_ID)
18     FROM
19         Customer a
20     LEFT JOIN Agent b ON CONVERT (VARCHAR, b.ID) = CONVERT (VARCHAR, a.AgentID)
21     WHERE
22         b.Deleted = 0
23     AND a.Deleted = 0
24     AND a.Status IN ('3', '4', '5', '6', '7')
25     GROUP BY
26         b.AgentType
27     UNION ALL
28         SELECT
29             '已认筹',
30             b.AgentType,
31             COUNT (a.DC_ID)
32         FROM
33             Customer a
34         LEFT JOIN Agent b ON CONVERT (VARCHAR, b.ID) = CONVERT (VARCHAR, a.AgentID)
35         WHERE
36             b.Deleted = 0
37         AND a.Deleted = 0
38         AND a.Status IN ('4', '5', '6', '7')
39         GROUP BY
40             b.AgentType
41         UNION ALL
42             SELECT
43                 '已认购',
44                 b.AgentType,
45                 COUNT (a.DC_ID)
46             FROM
47                 Customer a
48             LEFT JOIN Agent b ON CONVERT (VARCHAR, b.ID) = CONVERT (VARCHAR, a.AgentID)
49             WHERE
50                 b.Deleted = 0
51             AND a.Deleted = 0
52             AND a.Status IN ('5', '6', '7')
53             GROUP BY
54                 b.AgentType
55             UNION ALL
56                 SELECT
57                     '已签约',
58                     b.AgentType,
59                     COUNT (a.DC_ID)
60                 FROM
61                     Customer a
62                 LEFT JOIN Agent b ON CONVERT (VARCHAR, b.ID) = CONVERT (VARCHAR, a.AgentID)
63                 WHERE
64                     b.Deleted = 0
65                 AND a.Deleted = 0
66                 AND a.Status IN ('6', '7')
67                 GROUP BY
68                     b.AgentType
69                 UNION ALL
70                     SELECT
71                         '已回款',
72                         b.AgentType,
73                         COUNT (a.DC_ID)
74                     FROM
75                         Customer a
76                     LEFT JOIN Agent b ON CONVERT (VARCHAR, b.ID) = CONVERT (VARCHAR, a.AgentID)
77                     WHERE
78                         b.Deleted = 0
79                     AND a.Deleted = 0
80                     AND a.Status IN ('7')
81                     GROUP BY
82                         b.AgentType
View Code

4.客户转化率

 1 SELECT
 2     '所有推荐',
 3     COUNT (*)
 4 FROM
 5     Customer a
 6 UNION ALL
 7     -- SELECT '推荐中' ,count(*) from Customer a where a.Status in ('1') UNION ALL
 8     -- SELECT '已预约' ,count(*) from Customer a where a.Status in ('2') UNION ALL
 9     SELECT
10         '已到访',
11         COUNT (*)
12     FROM
13         Customer a
14     WHERE
15         a.Status IN ('3', '4', '5', '6', '7')
16     UNION ALL
17         SELECT
18             '已认筹',
19             COUNT (*)
20         FROM
21             Customer a
22         WHERE
23             a.Status IN ('4', '5', '6', '7')
24         UNION ALL
25             SELECT
26                 '已认购',
27                 COUNT (*)
28             FROM
29                 Customer a
30             WHERE
31                 a.Status IN ('5', '6', '7')
32             UNION ALL
33                 SELECT
34                     '已签约',
35                     COUNT (*)
36                 FROM
37                     Customer a
38                 WHERE
39                     a.Status IN ('6', '7')
40                 UNION ALL
41                     SELECT
42                         '已汇款',
43                         COUNT (*)
44                     FROM
45                         Customer a
46                     WHERE
47                         a.Status IN ('7')
View Code

 5.经纪人推荐客户比以及推荐成交客户经纪人比

 1 SELECT
 2     b.AgentType,
 3     COUNT (a.DC_ID)
 4 FROM
 5     Customer a
 6 LEFT JOIN Agent b ON CONVERT (VARCHAR, b.DC_ID) = CONVERT (VARCHAR, a.AgentID)
 7 WHERE
 8     b.Deleted = 0
 9 GROUP BY
10     b.AgentType;
11 
12 SELECT
13     b.AgentType,
14     COUNT (a.DC_ID)
15 FROM
16     Customer a
17 LEFT JOIN Agent b ON CONVERT (VARCHAR, b.DC_ID) = CONVERT (VARCHAR, a.AgentID)
18 WHERE
19     b.Deleted = 0
20 AND a.Status IN ('5', '6', '7')
21 GROUP BY
22     b.AgentType;
View Code

6.金牌经纪人

1 SELECT DISTINCT c.AgentID, a.Name,b.Phone,b.RegisterTime,b.IDNumber + '#' from Customer c 
2 LEFT JOIN Agent b ON CONVERT (VARCHAR, b.ID) = CONVERT (VARCHAR, c.AgentID)
3 JOIN City a  on b.City = a.ID
4 where c.Deleted=0
5 and b.Deleted=0;
6 
7 SELECT a.AgentID,MAX(a.RecommendTime) from Customer a 
8 GROUP BY a.AgentID;
View Code

 

posted @ 2016-07-18 20:49  侠之大者kamil  阅读(103)  评论(0)    收藏  举报