[已用]创建门店每日耗卡统计视图.sql
2.53 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
-- 创建门店每日耗卡统计视图
-- 统计各个门店每日项目数（耗卡里面的项目数量）和消耗业绩（耗卡里面的金额）
DROP VIEW IF EXISTS `v_store_daily_consume_stats`;
CREATE VIEW `v_store_daily_consume_stats` AS
SELECT
hk.md AS store_id, -- 门店ID
hk.mdbh AS store_code, -- 门店编号
hk.mdmc AS store_name, -- 门店名称
DATE(hk.hksj) AS consume_date, -- 耗卡日期
COUNT(DISTINCT hk.F_Id) AS consume_count, -- 耗卡记录数
COALESCE(SUM(px.F_ProjectNumber), 0) AS total_project_count, -- 总项目数
COALESCE(SUM(px.F_TotalPrice), 0) AS total_consume_amount, -- 总消耗金额
COALESCE(SUM(CASE WHEN px.F_IsEnabled = 1 THEN px.F_ProjectNumber ELSE 0 END), 0) AS enabled_project_count, -- 有效项目数
COALESCE(SUM(CASE WHEN px.F_IsEnabled = 1 THEN px.F_TotalPrice ELSE 0 END), 0) AS enabled_consume_amount, -- 有效消耗金额
COALESCE(SUM(CASE WHEN px.F_SourceType = '开卡' THEN px.F_ProjectNumber ELSE 0 END), 0) AS card_project_count, -- 开卡项目数
COALESCE(SUM(CASE WHEN px.F_SourceType = '开卡' THEN px.F_TotalPrice ELSE 0 END), 0) AS card_consume_amount, -- 开卡消耗金额
COALESCE(SUM(CASE WHEN px.F_SourceType = '赠送' THEN px.F_ProjectNumber ELSE 0 END), 0) AS gift_project_count, -- 赠送项目数
COALESCE(SUM(CASE WHEN px.F_SourceType = '赠送' THEN px.F_TotalPrice ELSE 0 END), 0) AS gift_consume_amount, -- 赠送消耗金额
COALESCE(SUM(CASE WHEN px.F_SourceType = '其他' THEN px.F_ProjectNumber ELSE 0 END), 0) AS other_project_count, -- 其他项目数
COALESCE(SUM(CASE WHEN px.F_SourceType = '其他' THEN px.F_TotalPrice ELSE 0 END), 0) AS other_consume_amount, -- 其他消耗金额
COUNT(DISTINCT hk.hy) AS member_count, -- 消费会员数
hk.hksj AS create_time -- 创建时间
FROM lq_xh_hyhk hk
LEFT JOIN lq_xh_pxmx px ON hk.F_Id = px.F_ConsumeInfoId
WHERE hk.hksj IS NOT NULL
GROUP BY hk.md, hk.mdbh, hk.mdmc, DATE(hk.hksj), hk.hksj
ORDER BY hk.md, DATE(hk.hksj) DESC;
-- 添加视图注释
ALTER VIEW `v_store_daily_consume_stats` COMMENT = '门店每日耗卡统计视图 - 统计各门店每日项目数和消耗业绩';
-- 视图说明：
-- 1. 按门店和日期分组统计耗卡数据
-- 2. 统计项目数和消耗金额，区分不同来源类型
-- 3. 包含有效/无效项目统计
-- 4. 按来源类型（开卡、赠送、其他）分别统计
-- 5. 统计消费会员数量
-- 6. 按门店和日期降序排列