kknongchang_2506/app/Module/Transfer/Database/fee_daily_stats.sql

-- Transfer模块手续费每日统计表
-- 用于记录每日手续费统计数据，以最后统计的ID记录进度

-- =====================================================
-- 手续费每日统计表
-- =====================================================

CREATE TABLE `kku_transfer_fee_daily_stats` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `stat_date` date NOT NULL COMMENT '统计日期',
  `transfer_app_id` int unsigned NOT NULL COMMENT '划转应用ID',
  `last_processed_order_id` bigint unsigned NOT NULL DEFAULT 0 COMMENT '最后处理的订单ID',
  
  -- 转入统计
  `in_order_count` int unsigned NOT NULL DEFAULT 0 COMMENT '转入订单数量',
  `in_total_amount` decimal(30,10) NOT NULL DEFAULT 0.0000000000 COMMENT '转入总金额',
  `in_fee_amount` decimal(30,10) NOT NULL DEFAULT 0.0000000000 COMMENT '转入手续费总额',
  `in_avg_fee_rate` decimal(8,5) NOT NULL DEFAULT 0.00000 COMMENT '转入平均手续费率',
  
  -- 转出统计
  `out_order_count` int unsigned NOT NULL DEFAULT 0 COMMENT '转出订单数量',
  `out_total_amount` decimal(30,10) NOT NULL DEFAULT 0.0000000000 COMMENT '转出总金额',
  `out_fee_amount` decimal(30,10) NOT NULL DEFAULT 0.0000000000 COMMENT '转出手续费总额',
  `out_avg_fee_rate` decimal(8,5) NOT NULL DEFAULT 0.00000 COMMENT '转出平均手续费率',
  
  -- 汇总统计
  `total_order_count` int unsigned NOT NULL DEFAULT 0 COMMENT '总订单数量',
  `total_amount` decimal(30,10) NOT NULL DEFAULT 0.0000000000 COMMENT '总交易金额',
  `total_fee_amount` decimal(30,10) NOT NULL DEFAULT 0.0000000000 COMMENT '总手续费金额',
  `avg_fee_rate` decimal(8,5) NOT NULL DEFAULT 0.00000 COMMENT '平均手续费率',
  
  -- 时间戳
  `created_at` timestamp NULL DEFAULT NULL COMMENT '创建时间',
  `updated_at` timestamp NULL DEFAULT NULL COMMENT '更新时间',
  
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_stat_date_app` (`stat_date`, `transfer_app_id`),
  KEY `idx_stat_date` (`stat_date`),
  KEY `idx_transfer_app_id` (`transfer_app_id`),
  KEY `idx_last_processed_order_id` (`last_processed_order_id`),
  KEY `idx_created_at` (`created_at`),
  
  CONSTRAINT `fk_fee_daily_stats_app` FOREIGN KEY (`transfer_app_id`) REFERENCES `kku_transfer_apps` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='手续费每日统计表';

-- =====================================================
-- 索引优化
-- =====================================================

-- 手续费金额统计索引
CREATE INDEX `idx_total_fee_amount` ON `kku_transfer_fee_daily_stats` (`total_fee_amount`, `stat_date`);

-- 应用统计查询索引
CREATE INDEX `idx_app_date_range` ON `kku_transfer_fee_daily_stats` (`transfer_app_id`, `stat_date`, `total_fee_amount`);

-- =====================================================
-- 统计视图
-- =====================================================

-- 手续费月度统计视图
CREATE VIEW `v_transfer_fee_monthly_stats` AS
SELECT 
    YEAR(stat_date) as year,
    MONTH(stat_date) as month,
    transfer_app_id,
    ta.keyname as app_keyname,
    ta.title as app_title,
    SUM(total_order_count) as monthly_order_count,
    SUM(total_amount) as monthly_total_amount,
    SUM(total_fee_amount) as monthly_fee_amount,
    AVG(avg_fee_rate) as monthly_avg_fee_rate,
    SUM(in_order_count) as monthly_in_orders,
    SUM(in_fee_amount) as monthly_in_fee,
    SUM(out_order_count) as monthly_out_orders,
    SUM(out_fee_amount) as monthly_out_fee,
    COUNT(*) as stat_days
FROM `kku_transfer_fee_daily_stats` tfs
LEFT JOIN `kku_transfer_apps` ta ON tfs.transfer_app_id = ta.id
GROUP BY YEAR(stat_date), MONTH(stat_date), transfer_app_id
ORDER BY year DESC, month DESC, monthly_fee_amount DESC;

-- 手续费应用汇总视图
CREATE VIEW `v_transfer_fee_app_summary` AS
SELECT 
    transfer_app_id,
    ta.keyname as app_keyname,
    ta.title as app_title,
    COUNT(*) as stat_days,
    SUM(total_order_count) as total_orders,
    SUM(total_amount) as total_amount,
    SUM(total_fee_amount) as total_fee,
    AVG(avg_fee_rate) as avg_fee_rate,
    SUM(in_order_count) as total_in_orders,
    SUM(in_fee_amount) as total_in_fee,
    SUM(out_order_count) as total_out_orders,
    SUM(out_fee_amount) as total_out_fee,
    MIN(stat_date) as first_stat_date,
    MAX(stat_date) as last_stat_date,
    MAX(last_processed_order_id) as last_processed_order_id
FROM `kku_transfer_fee_daily_stats` tfs
LEFT JOIN `kku_transfer_apps` ta ON tfs.transfer_app_id = ta.id
GROUP BY transfer_app_id
ORDER BY total_fee DESC;

-- =====================================================
-- 存储过程：获取指定日期范围的手续费统计
-- =====================================================

DELIMITER $$

CREATE PROCEDURE `GetFeeStatsByDateRange`(
    IN p_app_id INT,
    IN p_start_date DATE,
    IN p_end_date DATE
)
READS SQL DATA
COMMENT '获取指定应用和日期范围的手续费统计'
BEGIN
    SELECT 
        stat_date,
        transfer_app_id,
        ta.keyname as app_name,
        ta.title as app_title,
        total_order_count,
        total_amount,
        total_fee_amount,
        avg_fee_rate,
        in_order_count,
        in_fee_amount,
        out_order_count,
        out_fee_amount,
        last_processed_order_id,
        created_at
    FROM `kku_transfer_fee_daily_stats` tfs
    LEFT JOIN `kku_transfer_apps` ta ON tfs.transfer_app_id = ta.id
    WHERE (p_app_id = 0 OR transfer_app_id = p_app_id)
    AND stat_date BETWEEN p_start_date AND p_end_date
    ORDER BY stat_date DESC, total_fee_amount DESC;
END$$

DELIMITER ;

-- =====================================================
-- 示例查询
-- =====================================================

-- 查询最近7天的手续费统计
-- SELECT * FROM `kku_transfer_fee_daily_stats` 
-- WHERE stat_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
-- ORDER BY stat_date DESC;

-- 查询指定应用的手续费统计
-- SELECT * FROM `kku_transfer_fee_daily_stats` 
-- WHERE transfer_app_id = 1
-- ORDER BY stat_date DESC;

-- 查询手续费收入最高的日期
-- SELECT stat_date, SUM(total_fee_amount) as daily_fee
-- FROM `kku_transfer_fee_daily_stats`
-- GROUP BY stat_date
-- ORDER BY daily_fee DESC
-- LIMIT 10;