-- 客户单量统计脚本（无需修改表结构版本）
-- 说明：现有订单表和客户表无直接关联字段，可根据实际业务关联规则调整JOIN条件
-- 常见关联方式：1. 参考号前缀匹配客户代码 2. 通过API请求日志关联ApiKey 3. 其他业务规则

-- 1. 每日单量统计（示例：假设参考号前8位为客户代码，可根据实际情况调整）
SELECT 
    c.CustomerCode,
    c.CustomerName,
    DATE(lrr.CreatedAt) AS StatisticDate,
    COUNT(lrr.Id) AS OrderCount,
    SUM(CASE WHEN lrr.ReplaceStatus = 'Y' THEN 1 ELSE 0 END) AS SuccessOrderCount,
    SUM(CASE WHEN lrr.ReplaceStatus = 'N' THEN 1 ELSE 0 END) AS FrozenOrderCount
FROM 
    label_replace_requests lrr
LEFT JOIN 
    customers c ON LEFT(lrr.ReferenceNumber, 8) = c.CustomerCode -- 请根据实际关联规则修改此行
GROUP BY 
    c.CustomerCode, c.CustomerName, DATE(lrr.CreatedAt)
ORDER BY 
    StatisticDate DESC, OrderCount DESC;

-- 2. 每周单量统计（按自然周，周一为一周开始）
SELECT 
    c.CustomerCode,
    c.CustomerName,
    YEARWEEK(lrr.CreatedAt, 1) AS WeekNumber,
    CONCAT(DATE_SUB(DATE(lrr.CreatedAt), INTERVAL WEEKDAY(lrr.CreatedAt) DAY), ' ~ ', DATE_ADD(DATE(lrr.CreatedAt), INTERVAL 6 - WEEKDAY(lrr.CreatedAt) DAY)) AS WeekRange,
    COUNT(lrr.Id) AS OrderCount,
    SUM(CASE WHEN lrr.ReplaceStatus = 'Y' THEN 1 ELSE 0 END) AS SuccessOrderCount,
    SUM(CASE WHEN lrr.ReplaceStatus = 'N' THEN 1 ELSE 0 END) AS FrozenOrderCount
FROM 
    label_replace_requests lrr
LEFT JOIN 
    customers c ON LEFT(lrr.ReferenceNumber, 8) = c.CustomerCode -- 请根据实际关联规则修改此行
GROUP BY 
    c.CustomerCode, c.CustomerName, YEARWEEK(lrr.CreatedAt, 1)
ORDER BY 
    WeekNumber DESC, OrderCount DESC;

-- 3. 无关联字段时的临时统计方案（按中性面单前缀分组，可映射为客户）
-- 每日统计
SELECT 
    LEFT(lrr.NeutralWaybillNumber, 6) AS CustomerTag, -- 可根据实际情况调整截取长度
    DATE(lrr.CreatedAt) AS StatisticDate,
    COUNT(lrr.Id) AS OrderCount
FROM 
    label_replace_requests lrr
GROUP BY 
    LEFT(lrr.NeutralWaybillNumber, 6), DATE(lrr.CreatedAt)
ORDER BY 
    StatisticDate DESC, OrderCount DESC;

-- 每周统计
SELECT 
    LEFT(lrr.NeutralWaybillNumber, 6) AS CustomerTag,
    YEARWEEK(lrr.CreatedAt, 1) AS WeekNumber,
    CONCAT(DATE_SUB(DATE(lrr.CreatedAt), INTERVAL WEEKDAY(lrr.CreatedAt) DAY), ' ~ ', DATE_ADD(DATE(lrr.CreatedAt), INTERVAL 6 - WEEKDAY(lrr.CreatedAt) DAY)) AS WeekRange,
    COUNT(lrr.Id) AS OrderCount
FROM 
    label_replace_requests lrr
GROUP BY 
    LEFT(lrr.NeutralWaybillNumber, 6), YEARWEEK(lrr.CreatedAt, 1)
ORDER BY 
    WeekNumber DESC, OrderCount DESC;

-- 5. 快捷查询示例：查询最近7天的每日单量
-- SELECT * FROM v_daily_order_statistics 
-- WHERE StatisticDate >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
-- ORDER BY StatisticDate DESC;

-- 6. 快捷查询示例：查询最近4周的每周单量
-- SELECT * FROM v_weekly_order_statistics 
-- WHERE WeekNumber >= YEARWEEK(DATE_SUB(CURDATE(), INTERVAL 4 WEEK), 1)
-- ORDER BY WeekNumber DESC;
