创建金三角月度业绩统计视图.sql
2.41 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
-- 创建金三角月度业绩统计视图
-- 功能：获取每个金三角的每个月开单业绩总和
-- 关联表：lq_kd_jksyj（健康师业绩表）、lq_ycsd_jsj（金三角设定表）、lq_jinsanjiao_user（金三角用户绑定表）
CREATE VIEW v_jsj_monthly_performance AS
SELECT
jsj.F_Id AS jsj_id, -- 金三角ID
jsj.jsj AS jsj_name, -- 金三角名称
jsj.yf AS month, -- 月份
jsj.md AS store_id, -- 门店ID
md.dm AS store_name, -- 门店名称
COUNT(DISTINCT jksyj.F_Id) AS order_count, -- 开单数量
SUM(CAST(jksyj.jksyj AS DECIMAL(18,2))) AS total_performance, -- 总业绩
AVG(CAST(jksyj.jksyj AS DECIMAL(18,2))) AS avg_performance, -- 平均业绩
MAX(jksyj.yjsj) AS last_order_date, -- 最后开单日期
MIN(jksyj.yjsj) AS first_order_date, -- 首次开单日期
COUNT(DISTINCT jksyj.jks) AS health_teacher_count, -- 健康师数量
GROUP_CONCAT(DISTINCT jksyj.jksxm ORDER BY jksyj.jksxm SEPARATOR ',') AS health_teachers -- 健康师姓名列表
FROM
lq_ycsd_jsj jsj
LEFT JOIN lq_kd_jksyj jksyj ON jsj.F_Id = jksyj.jsj_id
LEFT JOIN lq_mdxx md ON jsj.md = md.F_Id
WHERE
jsj.yf IS NOT NULL
AND jksyj.yjsj IS NOT NULL
AND jksyj.jksyj IS NOT NULL
AND jksyj.jksyj != ''
AND jksyj.jksyj != '0'
GROUP BY
jsj.F_Id,
jsj.jsj,
jsj.yf,
jsj.md,
md.dm
ORDER BY
jsj.yf DESC,
total_performance DESC;
-- 创建索引以优化查询性能
CREATE INDEX idx_lq_kd_jksyj_jsj_yjsj ON lq_kd_jksyj(jsj_id, yjsj);
CREATE INDEX idx_lq_kd_jksyj_jksyj ON lq_kd_jksyj(jksyj);
-- 使用示例查询：
-- 1. 查询所有金三角的月度业绩
-- SELECT * FROM v_jsj_monthly_performance ORDER BY month DESC, total_performance DESC;
-- 2. 查询指定月份的金三角业绩
-- SELECT * FROM v_jsj_monthly_performance WHERE month = '202412' ORDER BY total_performance DESC;
-- 3. 查询指定金三角的业绩历史
-- SELECT * FROM v_jsj_monthly_performance WHERE jsj_id = '具体金三角ID' ORDER BY month DESC;
-- 4. 查询指定门店的金三角业绩
-- SELECT * FROM v_jsj_monthly_performance WHERE store_id = '具体门店ID' ORDER BY month DESC, total_performance DESC;
-- 5. 查询业绩排名前10的金三角
-- SELECT * FROM v_jsj_monthly_performance WHERE month = '202412' ORDER BY total_performance DESC LIMIT 10;