-- =====================================================
-- 销售订单毛利统计 - QuickBI汇总查询SQL
-- =====================================================

-- 1. 合计（全部数据）
SELECT 
    SUM(num) AS num,
    SUM(should_amt) AS should_amt,
    SUM(cost_amt) AS cost_amt,
    SUM(profit_amt) AS profit_amt,
    ROUND(SUM(profit_amt) / SUM(cost_amt + profit_amt) * 100, 2) AS profit_rate,
    SUM(sum_price) AS sum_price,
    ROUND(AVG(REPLACE(discount, '%', '')), 2) AS discount,
    SUM(blq_amt) AS blq_amt,
    SUM(pay_amt) AS pay_amt,
    SUM(pay_num) AS pay_num,
    SUM(pay_cost_price) AS pay_cost_price,
    SUM(pay_profit_amt) AS pay_profit_amt
FROM fact_fee_grossprofit
WHERE sync_date BETWEEN '2025-01-01' AND '2026-04-12';

-- 2. 按月份汇总
SELECT 
    month,
    SUM(num) AS num,
    SUM(should_amt) AS should_amt,
    SUM(cost_amt) AS cost_amt,
    SUM(profit_amt) AS profit_amt,
    SUM(sum_price) AS sum_price,
    SUM(blq_amt) AS blq_amt,
    SUM(pay_amt) AS pay_amt,
    SUM(pay_num) AS pay_num,
    SUM(pay_cost_price) AS pay_cost_price,
    SUM(pay_profit_amt) AS pay_profit_amt
FROM fact_fee_grossprofit
WHERE sync_date BETWEEN '2025-01-01' AND '2026-04-12'
GROUP BY month
ORDER BY month;

-- 3. 按诊所汇总
SELECT 
    org_name,
    SUM(num) AS num,
    SUM(should_amt) AS should_amt,
    SUM(cost_amt) AS cost_amt,
    SUM(profit_amt) AS profit_amt,
    SUM(sum_price) AS sum_price,
    SUM(blq_amt) AS blq_amt,
    SUM(pay_amt) AS pay_amt,
    SUM(pay_num) AS pay_num,
    SUM(pay_cost_price) AS pay_cost_price,
    SUM(pay_profit_amt) AS pay_profit_amt
FROM fact_fee_grossprofit
WHERE sync_date BETWEEN '2025-01-01' AND '2026-04-12'
GROUP BY org_name
ORDER BY SUM(should_amt) DESC;

-- 4. 按收费项目汇总
SELECT 
    name,
    fee_class,
    SUM(num) AS num,
    SUM(should_amt) AS should_amt,
    SUM(cost_amt) AS cost_amt,
    SUM(profit_amt) AS profit_amt,
    SUM(sum_price) AS sum_price
FROM fact_fee_grossprofit
WHERE sync_date BETWEEN '2025-01-01' AND '2026-04-12'
GROUP BY name, fee_class
ORDER BY SUM(should_amt) DESC
LIMIT 50;

-- 5. 按集团汇总
SELECT 
    group_name,
    SUM(num) AS num,
    SUM(should_amt) AS should_amt,
    SUM(cost_amt) AS cost_amt,
    SUM(profit_amt) AS profit_amt,
    SUM(sum_price) AS sum_price,
    SUM(blq_amt) AS blq_amt,
    SUM(pay_amt) AS pay_amt,
    SUM(pay_num) AS pay_num
FROM fact_fee_grossprofit
WHERE sync_date BETWEEN '2025-01-01' AND '2026-04-12'
GROUP BY group_name
ORDER BY SUM(should_amt) DESC;

-- =====================================================
-- 参数说明：
-- sync_date: 同步日期，按天分区
-- month: 月份 (2025-01格式)
-- org_name: 诊所名称
-- group_name: 集团名称
-- name: 收费项目名称
-- fee_class: 收费分类
-- =====================================================