常用脚本

清楚用户资料示料，通常很多表都和用户相关

 

-- 假设已知要查询的手机号
SET @phone = '18675918218';
-- 查询fr_user表中对应手机号的用户id
SELECT @user_id := id FROM fr_user WHERE phone = @phone;
-- 删除其他表中对应user_id的记录
DELETE FROM fr_user_asset WHERE user_id = @user_id;
DELETE FROM fr_company_user WHERE user_id = @user_id;
DELETE FROM fr_user_stat WHERE user_id = @user_id;
DELETE FROM fr_user_day_stat WHERE user_id = @user_id;
DELETE FROM fr_user_asset_old WHERE user_id = @user_id;
DELETE FROM zyb_oil_pay_record WHERE user_id = @user_id;
DELETE FROM fr_company_coupon WHERE user_id = @user_id;
DELETE FROM fr_user_recharge_record WHERE user_id = @user_id;

查询三个月内没有定单，但是半年内有订单的用户

-- 定义近三个月和近半年的时间范围
SET @three_months_ago = DATE_SUB(CURDATE(), INTERVAL 3 MONTH);
SET @six_months_ago = DATE_SUB(CURDATE(), INTERVAL 6 MONTH);

-- 查询公司ID为123的用户，且近三个月内没有消费记录的客户
WITH NoRecentConsumers AS (
    SELECT 
        fu.id AS user_id,
        fu.phone AS user_phone,
        fu.nick_name AS user_nickname
    FROM 
        fr_company_user fcu
    JOIN 
        fr_user fu ON fcu.user_id = fu.id
    WHERE 
        fcu.company_id = 123
        AND NOT EXISTS (
            SELECT 1
            FROM zyb_oil_pay_record zop
            WHERE zop.user_id = fu.id
              AND zop.create_time >= @three_months_ago
              AND zop.company_id = 123
        )
),
MonthlyConsumption AS (
    SELECT 
        user_id,
        DATE_FORMAT(create_time, '%Y-%m') AS month,
        SUM(actual_amount) AS total_amount,  -- 消费总金额
        COUNT(*) AS consumption_count  -- 加油次数
    FROM 
        zyb_oil_pay_record
    WHERE 
        create_time >= @six_months_ago
        AND company_id = 123
    GROUP BY 
        user_id, DATE_FORMAT(create_time, '%Y-%m')
),
OilTypeConsumption AS (
    SELECT 
        user_id,
        oil_number,  -- 油品类型
        COUNT(*) AS oil_count,  -- 该油品的加油次数
        SUM(actual_amount) AS oil_total_amount  -- 该油品的总金额
    FROM 
        zyb_oil_pay_record
    WHERE 
        create_time >= @six_months_ago
        AND company_id = 123
    GROUP BY 
        user_id, oil_number
),
MonthlyConsumptionArray AS (
    SELECT 
        user_id,
        JSON_ARRAYAGG(JSON_OBJECT('month', month, 'total_amount', total_amount, 'consumption_count', consumption_count)) AS monthly_consumption_array,
        COUNT(*) AS consumption_count,  -- 有消费记录的月份数
        SUM(total_amount) AS total_amount  -- 过去半年内所有消费金额的总和
    FROM 
        MonthlyConsumption
    GROUP BY 
        user_id
),
OilTypeConsumptionArray AS (
    SELECT 
        user_id,
        JSON_ARRAYAGG(JSON_OBJECT('oil_number', oil_number, 'oil_count', oil_count, 'oil_total_amount', oil_total_amount)) AS oil_type_consumption_array,
        GROUP_CONCAT(DISTINCT oil_number ORDER BY oil_number SEPARATOR ', ') AS oil_numbers  -- 用逗号分隔的油品编号
    FROM 
        OilTypeConsumption
    GROUP BY 
        user_id
)
SELECT 
    nrc.user_id,
    nrc.user_phone,
    nrc.user_nickname,
    COALESCE(mca.monthly_consumption_array, JSON_ARRAY()) AS monthly_consumption_array,
    COALESCE(mca.consumption_count, 0) AS consumption_count,  -- 过去半年内有消费记录的月份数
    COALESCE(mca.total_amount, 0) AS total_amount,  -- 过去半年内所有消费金额的总和
    COALESCE(otca.oil_type_consumption_array, JSON_ARRAY()) AS oil_type_consumption_array,  -- 按油品汇总的消费数据
    COALESCE(otca.oil_numbers, '') AS oil_numbers  -- 用逗号分隔的油品编号
FROM 
    NoRecentConsumers nrc
LEFT JOIN 
    MonthlyConsumptionArray mca ON nrc.user_id = mca.user_id
LEFT JOIN 
    OilTypeConsumptionArray otca ON nrc.user_id = otca.user_id
ORDER BY 
    nrc.user_id;