添加转化率统计关联字段.sql
5.36 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
-- ============================================
-- 为转化率统计添加关联字段
-- ============================================
-- 说明：此脚本为转化率统计功能添加关联字段，用于追踪邀约→预约→开单/耗卡的完整业务链路
-- 执行顺序：按照业务链路顺序依次执行
--
-- 业务链路：
-- 邀约记录 (lq_yaoyjl) → 预约记录 (lq_yyjl) → 开单记录 (lq_kd_kdjlb) / 耗卡记录 (lq_xh_hyhk)
--
-- 字段说明：
-- 1. lq_yyjl.F_InviteId：关联邀约记录ID，用于统计邀约→预约转化率
-- 2. lq_kd_kdjlb.F_AppointmentId：关联预约记录ID，用于统计预约→开单转化率
-- 3. lq_xh_hyhk.F_AppointmentId：关联预约记录ID，用于统计预约→耗卡转化率
--
-- 注意事项：
-- - 所有字段允许为NULL，因为历史数据可能没有这些关联
-- - 字段类型为VARCHAR(50)，与其他ID字段保持一致
-- - 已为所有字段创建索引，以优化查询性能
-- ============================================
-- 1. lq_yyjl（预约记录表） - 添加邀约ID字段和未成交说明字段
-- ============================================
-- 说明：关联邀约记录，用于统计邀约→预约转化率
ALTER TABLE lq_yyjl
ADD COLUMN F_InviteId VARCHAR(50) NULL COMMENT '邀约记录ID（关联lq_yaoyjl.F_Id，用于统计邀约→预约转化率）' AFTER F_Status,
ADD COLUMN F_NoDealRemark VARCHAR(2000) NULL COMMENT '未成交说明' AFTER F_InviteId;
-- 创建索引以优化查询性能
CREATE INDEX idx_yyjl_invite_id ON lq_yyjl(F_InviteId);
-- ============================================
-- 2. lq_kd_kdjlb（开单记录表） - 添加预约ID字段
-- ============================================
-- 说明：关联预约记录，用于统计预约→开单转化率
ALTER TABLE lq_kd_kdjlb
ADD COLUMN F_AppointmentId VARCHAR(50) NULL COMMENT '预约记录ID（关联lq_yyjl.F_Id，用于统计预约→开单转化率）' AFTER F_ActivityId;
-- 创建索引以优化查询性能
CREATE INDEX idx_kdjlb_appointment_id ON lq_kd_kdjlb(F_AppointmentId);
-- ============================================
-- 3. lq_xh_hyhk（耗卡记录表） - 添加预约ID字段
-- ============================================
-- 说明：关联预约记录，用于统计预约→耗卡转化率
ALTER TABLE lq_xh_hyhk
ADD COLUMN F_AppointmentId VARCHAR(50) NULL COMMENT '预约记录ID（关联lq_yyjl.F_Id，用于统计预约→耗卡转化率）' AFTER F_OvertimeSgfy;
-- 创建索引以优化查询性能
CREATE INDEX idx_hyhk_appointment_id ON lq_xh_hyhk(F_AppointmentId);
-- ============================================
-- 4. 验证字段创建
-- ============================================
-- 验证 lq_yyjl 表
-- SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_COMMENT
-- FROM INFORMATION_SCHEMA.COLUMNS
-- WHERE TABLE_NAME = 'lq_yyjl'
-- AND (COLUMN_NAME = 'F_InviteId' OR COLUMN_NAME = 'F_NoDealRemark');
-- 验证 lq_kd_kdjlb 表
-- SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_COMMENT
-- FROM INFORMATION_SCHEMA.COLUMNS
-- WHERE TABLE_NAME = 'lq_kd_kdjlb'
-- AND COLUMN_NAME = 'F_AppointmentId';
-- 验证 lq_xh_hyhk 表
-- SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_COMMENT
-- FROM INFORMATION_SCHEMA.COLUMNS
-- WHERE TABLE_NAME = 'lq_xh_hyhk'
-- AND COLUMN_NAME = 'F_AppointmentId';
-- ============================================
-- 5. 验证索引创建
-- ============================================
-- 验证索引
-- SHOW INDEX FROM lq_yyjl WHERE Key_name = 'idx_yyjl_invite_id';
-- SHOW INDEX FROM lq_kd_kdjlb WHERE Key_name = 'idx_kdjlb_appointment_id';
-- SHOW INDEX FROM lq_xh_hyhk WHERE Key_name = 'idx_hyhk_appointment_id';
-- ============================================
-- 6. 转化率统计示例SQL（供参考）
-- ============================================
-- 示例1：邀约→预约转化率
-- SELECT
-- COUNT(DISTINCT yyjl.F_Id) AS 预约数量,
-- COUNT(DISTINCT yyjl.F_InviteId) AS 来自邀约的预约数量,
-- COUNT(DISTINCT yyjl.F_InviteId) * 100.0 / COUNT(DISTINCT yyjl.F_Id) AS 转化率百分比
-- FROM lq_yyjl yyjl
-- WHERE yyjl.F_CreateTime >= '2024-01-01';
-- 示例2：预约→开单转化率
-- SELECT
-- COUNT(DISTINCT yyjl.F_Id) AS 预约数量,
-- COUNT(DISTINCT kdjlb.F_Id) AS 开单数量,
-- COUNT(DISTINCT kdjlb.F_Id) * 100.0 / COUNT(DISTINCT yyjl.F_Id) AS 转化率百分比
-- FROM lq_yyjl yyjl
-- LEFT JOIN lq_kd_kdjlb kdjlb ON kdjlb.F_AppointmentId = yyjl.F_Id AND kdjlb.F_IsEffective = 1
-- WHERE yyjl.F_CreateTime >= '2024-01-01';
-- 示例3：预约→耗卡转化率
-- SELECT
-- COUNT(DISTINCT yyjl.F_Id) AS 预约数量,
-- COUNT(DISTINCT hyhk.F_Id) AS 耗卡数量,
-- COUNT(DISTINCT hyhk.F_Id) * 100.0 / COUNT(DISTINCT yyjl.F_Id) AS 转化率百分比
-- FROM lq_yyjl yyjl
-- LEFT JOIN lq_xh_hyhk hyhk ON hyhk.F_AppointmentId = yyjl.F_Id AND hyhk.F_IsEffective = 1
-- WHERE yyjl.F_CreateTime >= '2024-01-01';
-- 示例4：邀约→开单转化率（跨步转化）
-- SELECT
-- COUNT(DISTINCT yaoyjl.F_Id) AS 邀约数量,
-- COUNT(DISTINCT kdjlb.F_Id) AS 开单数量,
-- COUNT(DISTINCT kdjlb.F_Id) * 100.0 / COUNT(DISTINCT yaoyjl.F_Id) AS 转化率百分比
-- FROM lq_yaoyjl yaoyjl
-- LEFT JOIN lq_yyjl yyjl ON yyjl.F_InviteId = yaoyjl.F_Id
-- LEFT JOIN lq_kd_kdjlb kdjlb ON kdjlb.F_AppointmentId = yyjl.F_Id AND kdjlb.F_IsEffective = 1
-- WHERE yaoyjl.F_CreateTime >= '2024-01-01';