首页统计查询，一周7天的数据统计，没有的补零，成功率计

最新推荐文章于 2023-01-28 11:25:24 发布

原创最新推荐文章于 2023-01-28 11:25:24 发布 · 1.3k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #mybatis

Java 专栏收录该内容

12 篇文章

订阅专栏

本文详细介绍了一种使用SQL进行数据统计和分析的方法，包括如何创建日期列表、查询有效数据并进行汇总，以及如何通过左连接将多个数据源整合，确保数据完整性。此外，还提供了具体的SQL查询实例和MyBatis的mapper示例，帮助读者理解和应用。

1、先用一个查询把指定日期范围的日期列表搞出来（一周七天（LImit 7））：

tips：t_admin_user 不固定，任意一张表中的数据大于等于日期范围即可

注意：'@' 表示：在sql中创建变量，':=' 表示：在sql中赋值操作

SELECT
	@OLD_CREATE := DATE_SUB( @OLD_CREATE, INTERVAL 1 DAY ) AS date_str,
	0 AS count_str 
FROM
	( SELECT @OLD_CREATE := DATE_ADD( CURDATE( ), INTERVAL 1 DAY ) FROM t_admin_user LIMIT 7 ) t1

2、查询符合条件的有效数据，并且给日期设置别名即可：

tips：判断业务场景数据：（CASE WHEN a = 0 THEN a ELSE 1 END）

SELECT
	COUNT( t.PAY_ID ) AS countNumber,
	SUM( CASE WHEN t.RESULT_STATUS = ( 1 ) THEN 1 ELSE 0 END ) AS successNumber,
	SUM( t.PAY_AMOUNT ) AS orderAmount,
	SUM( CASE WHEN t.RESULT_STATUS = ( 1 ) THEN t.PAY_AMOUNT ELSE 0 END ) AS successAmount,
	SUM( CASE WHEN t.RESULT_STATUS = ( 1 ) THEN t.TIP ELSE 0 END ) AS tip,
	CONVERT ( t.create_time, DATE ) AS date_str,
	SUM( CASE WHEN t.RESULT_STATUS = ( 1 ) THEN t.TOTAL_AMOUNT ELSE 0 END ) AS totalAmount 
FROM
	t_pay t 
WHERE
	t.FLAG = 0 
GROUP BY
	( CONVERT ( t.create_time, DATE ) )

3、把两个表的查询结果用左连接合起来，没有日期的数据用0来补充：

SELECT
	t2.date_str, -- 日期
	COALESCE(t3.countNumber, 0) AS countNumber, -- 订单
	COALESCE(t3.successNumber, 0) AS successNumber, -- 成功
	COALESCE(t3.orderAmount, 0) AS orderAmount, -- 订单
	COALESCE(t3.successAmount, 0) AS successAmount, -- 成功
	COALESCE(t3.tip, 0) AS tip, -- 订单
	COALESCE(t3.totalAmount, 0) AS totalAmount, -- 付
	COALESCE(t4.topUpAmount, 0) as topUpAmount, -- 充值
	COALESCE(t4.topUpTip, 0) AS topUpTip -- 充值
FROM
	(SELECT
		@OLD_CREATE := DATE_SUB( @OLD_CREATE, INTERVAL 1 DAY ) AS date_str,
		0 AS count_str 
	FROM
		( SELECT @OLD_CREATE := DATE_ADD( CURDATE( ), INTERVAL 1 DAY ) FROM t_admin_user LIMIT 7 ) t1) t2
LEFT JOIN
	(SELECT
		COUNT( t.PAY_ID ) AS countNumber,
		SUM( CASE WHEN t.RESULT_STATUS = ( 1 ) THEN 1 ELSE 0 END ) AS successNumber,
		SUM( t.PAY_AMOUNT ) AS orderAmount,
		SUM( CASE WHEN t.RESULT_STATUS = ( 1 ) THEN t.PAY_AMOUNT ELSE 0 END ) AS successAmount,
		SUM( CASE WHEN t.RESULT_STATUS = ( 1 ) THEN t.TIP ELSE 0 END ) AS tip,
		CONVERT ( t.create_time, DATE ) AS date_str ,
		SUM(CASE WHEN t.RESULT_STATUS = ( 1 ) THEN t.TOTAL_AMOUNT ELSE 0 END ) AS totalAmount
	FROM
		t_pay t 
	WHERE
		t.FLAG = 0
	GROUP BY
		( CONVERT ( t.create_time, DATE ) ) ) t3
ON t2.date_str = t3.date_str
LEFT JOIN
	(SELECT
		SUM( u.AMOUNT ) AS topUpAmount,
		SUM( u.TIP ) AS topUpTip,
		CONVERT ( u.create_time, DATE ) AS date_str 
	FROM
		t_top_up u 
	WHERE
		u.FLAG = 0
	GROUP BY
		( CONVERT ( u.create_time, DATE ) ) ) t4
ON t3.date_str = t4.date_str
ORDER BY
	date_str ASC

执行SQL例子：

SELECT
	t2.date_str,
	COALESCE ( t3.countNumber, 0 ) AS countNumber,
	COALESCE ( t3.successNumber, 0 ) AS successNumber,
	COALESCE ( t3.orderAmount, 0 ) AS orderAmount,
	COALESCE ( t3.successAmount, 0 ) AS successAmount,
	COALESCE ( t3.tip, 0 ) AS tip,
	COALESCE ( t3.totalAmount, 0 ) AS totalAmount,
	COALESCE ( t4.topUpAmount, 0 ) AS topUpAmount,
	COALESCE ( t4.topUpTip, 0 ) AS topUpTip 
FROM
	(
SELECT
	@OLD_CREATE := DATE_SUB( @OLD_CREATE, INTERVAL 1 DAY ) AS date_str,
	0 AS count_str 
FROM
	( SELECT @OLD_CREATE := DATE_ADD( '2020-06-14', INTERVAL 1 DAY ) FROM t_admin_user LIMIT 7 ) t1 
	) t2
	LEFT JOIN (
SELECT
	COUNT( t.PAY_ID ) AS countNumber,
	SUM( CASE WHEN t.RESULT_STATUS = ( 1 ) THEN 1 ELSE 0 END ) AS successNumber,
	SUM( t.PAY_AMOUNT ) AS orderAmount,
	SUM( CASE WHEN t.RESULT_STATUS = ( 1 ) THEN t.PAY_AMOUNT ELSE 0 END ) AS successAmount,
	SUM( CASE WHEN t.RESULT_STATUS = ( 1 ) THEN t.TIP ELSE 0 END ) AS tip,
	CONVERT ( t.create_time, DATE ) AS date_str,
	SUM( CASE WHEN t.RESULT_STATUS = ( 1 ) THEN t.TOTAL_AMOUNT ELSE 0 END ) AS totalAmount 
FROM
	t_pay t 
WHERE
	t.FLAG = 0 
	AND t.CREATE_BY IN ( NULL, 888904 ) 
GROUP BY
	( CONVERT ( t.create_time, DATE ) ) 
	) t3 ON t2.date_str = t3.date_str
	LEFT JOIN (
SELECT
	SUM( u.AMOUNT ) AS topUpAmount,
	SUM( u.TIP ) AS topUpTip,
	CONVERT ( u.create_time, DATE ) AS date_str 
FROM
	t_top_up u 
WHERE
	u.FLAG = 0 
GROUP BY
	( CONVERT ( u.create_time, DATE ) ) 
	) t4 ON t3.date_str = t4.date_str 
ORDER BY
	date_str ASC;

XML案例：

<select id="indexCount" parameterType="****************.entity.Pay" resultType="java.util.Map">
        SELECT
            t2.date_str,
            COALESCE(t3.countNumber, 0) AS countNumber,
            COALESCE(t3.successNumber, 0) AS successNumber,
            COALESCE(t3.orderAmount, 0) AS orderAmount,
            COALESCE(t3.successAmount, 0) AS successAmount,
            COALESCE(t3.tip, 0) AS tip,
            COALESCE(t3.totalAmount, 0) AS totalAmount,
            COALESCE(t4.topUpAmount, 0) as topUpAmount,
            COALESCE(t4.topUpTip, 0) AS topUpTip
        FROM
            (SELECT
                    @OLD_CREATE := DATE_SUB( @OLD_CREATE, INTERVAL 1 DAY ) AS date_str,
                    0 AS count_str
                FROM
                    ( SELECT @OLD_CREATE := DATE_ADD( #{remarks, jdbcType=TIMESTAMP}, INTERVAL 1 DAY ) FROM t_admin_user LIMIT 7 ) t1) t2
        LEFT JOIN
            (SELECT
                    COUNT( t.PAY_ID ) AS countNumber,
                    SUM( CASE WHEN t.RESULT_STATUS = ( 1 ) THEN 1 ELSE 0 END ) AS successNumber,
                    SUM( t.PAY_AMOUNT ) AS orderAmount,
                    SUM( CASE WHEN t.RESULT_STATUS = ( 1 ) THEN t.PAY_AMOUNT ELSE 0 END ) AS successAmount,
                    SUM( CASE WHEN t.RESULT_STATUS = ( 1 ) THEN t.TIP ELSE 0 END ) AS tip,
                    CONVERT ( t.create_time, DATE ) AS date_str ,
                    SUM(CASE WHEN t.RESULT_STATUS = ( 1 ) THEN t.TOTAL_AMOUNT ELSE 0 END ) AS totalAmount
                FROM
                    t_pay t
                WHERE
                    t.FLAG = 0
                AND
                    t.CREATE_BY
                IN
                    (#{createBy, jdbcType=INTEGER}, #{updateBy, jdbcType=INTEGER})
                GROUP BY
                    ( CONVERT ( t.create_time, DATE ) ) ) t3
        ON t2.date_str = t3.date_str
        LEFT JOIN
            (SELECT
                    SUM( u.AMOUNT ) AS topUpAmount,
                    SUM( u.TIP ) AS topUpTip,
                    CONVERT ( u.create_time, DATE ) AS date_str
                FROM
                    t_top_up u
                WHERE
                    u.FLAG = 0
                GROUP BY
                    ( CONVERT ( u.create_time, DATE ) ) ) t4
        ON t3.date_str = t4.date_str
        ORDER BY
            date_str ASC
    </select>

mapper案例：

    /**
     * 首页数据统计
     * @param pay
     * @return
     */
    List<Map> indexCount(Pay pay);

首页统计成功率计算：

<select id="oldCount" parameterType="com.example.dolabor20200513.entity.Pay" resultType="java.util.Map">
        SELECT
            COUNT( t.PAY_ID ) AS countNumber,-- 订单数
            SUM( CASE WHEN t.RESULT_STATUS = ( 1 ) THEN 1 ELSE 0 END ) AS successNumber,-- 成功订单数
            -- 'CONCAT'连接字符串 % 'ROUND'把数值字段舍入为指定的小数位数
            CONCAT(ROUND(SUM( CASE WHEN t.RESULT_STATUS = ( 1 ) THEN 1 ELSE 0 END ) / COUNT( t.PAY_ID ) * 100.00, 2), '%') AS successRate,-- 成功率
            SUM( t.PAY_AMOUNT ) AS orderAmount,-- 订单总金额
            SUM( CASE WHEN t.RESULT_STATUS = ( 1 ) THEN t.PAY_AMOUNT ELSE 0 END ) AS successAmount,-- 成功订单金额
            SUM( CASE WHEN t.RESULT_STATUS = ( 1 ) THEN t.TIP ELSE 0 END ) AS tip, -- 订单手续费
            (SELECT	SUM( CASE WHEN u.TOP_UP_STATUS = ( 1 ) THEN u.AMOUNT ELSE 0 END ) FROM t_top_up u WHERE u.FLAG = 0 AND u.CREATE_BY IN (#{createBy, jdbcType=INTEGER}, #{updateBy, jdbcType=INTEGER})) AS topUpAmount,-- 总充值
            (SELECT SUM( CASE WHEN u.TOP_UP_STATUS = ( 1 ) THEN u.TIP ELSE 0 END ) FROM t_top_up u WHERE u.FLAG = 0 AND u.CREATE_BY IN (#{createBy, jdbcType=INTEGER}, #{updateBy, jdbcType=INTEGER})) AS topUpTip, -- 充值手续费
            SUM(CASE WHEN t.RESULT_STATUS = ( 1 ) THEN t.TOTAL_AMOUNT ELSE 0 END ) AS totalAmount -- 代付总扣款
        FROM
            t_pay t
        WHERE
            t.FLAG = 0
        AND
            t.CREATE_BY IN (#{createBy, jdbcType=INTEGER}, #{updateBy, jdbcType=INTEGER})
    </select>

查询一周时间的数据方法二：

tips：CURDATE( ) 当前日期可换成指定字符串日期

SELECT
	t1.date_str,-- 日期
	IFNULL( t2.merchantNumber, 0 ) AS merchantNumber,-- 商户数量
	IFNULL( t3.topUpAmount, 0 ) AS topUpAmount,-- 金额
	IFNULL( t4.countNumber, 0 ) AS countNumber,-- 数量
	IFNULL( t4.successNumber, 0 ) AS successNumber,-- 成功数量
	IFNULL( t4.orderAmount, 0 ) AS orderAmount,-- 金额
	IFNULL( t4.successAmount, 0 ) AS successAmount,-- 金额
	IFNULL( t4.payAmount, 0 ) AS payAmount -- 待付金额
	
FROM
	(
SELECT
CURDATE( ) AS date_str UNION ALL
SELECT
	DATE_SUB( CURDATE( ), INTERVAL 1 DAY ) AS date_str UNION ALL
SELECT
	DATE_SUB( CURDATE( ), INTERVAL 2 DAY ) AS date_str UNION ALL
SELECT
	DATE_SUB( CURDATE( ), INTERVAL 3 DAY ) AS date_str UNION ALL
SELECT
	DATE_SUB( CURDATE( ), INTERVAL 4 DAY ) AS date_str UNION ALL
SELECT
	DATE_SUB( CURDATE( ), INTERVAL 5 DAY ) AS date_str UNION ALL
SELECT
	DATE_SUB( CURDATE( ), INTERVAL 6 DAY ) AS date_str 
	) t1
	LEFT JOIN (
	SELECT CONVERT
		( a.CREATE_TIME, DATE ) AS date_str,
		COUNT( * ) AS merchantNumber -- 数量
		
	FROM
		t_admin_user a
		LEFT JOIN t_merchant_info m ON a.SYS_USER_ID = m.MERCHANT_ID 
	WHERE
		m.MERCHANT_ID IS NOT NULL 
		AND a.flag = 0 
	GROUP BY
		CONVERT ( a.CREATE_TIME, DATE ) 
	) t2 ON t1.date_str = t2.date_str
	LEFT JOIN (
	SELECT
		SUM( CASE WHEN u.TOP_UP_STATUS = ( 1 ) THEN u.AMOUNT ELSE 0 END ) AS topUpAmount,-- 金额
		CONVERT ( u.create_time, DATE ) AS date_str 
	FROM
		t_top_up u 
	WHERE
		u.FLAG = 0 
	GROUP BY
		CONVERT ( u.create_time, DATE ) 
	) t3 ON t2.date_str = t3.date_str
	LEFT JOIN (
	SELECT
		COUNT( t.PAY_ID ) AS countNumber,-- 数量
		SUM( CASE WHEN t.RESULT_STATUS = ( 1 ) THEN 1 ELSE 0 END ) AS successNumber,-- 数量
		SUM( t.PAY_AMOUNT ) AS orderAmount,-- 金额
		SUM( CASE WHEN t.RESULT_STATUS = ( 1 ) THEN t.PAY_AMOUNT ELSE 0 END ) AS successAmount,-- 金额
		SUM( CASE WHEN t.RESULT_STATUS = ( 2 ) THEN t.PAY_AMOUNT ELSE 0 END ) AS payAmount,-- 代付金额
		CONVERT ( t.create_time, DATE ) AS date_str 
	FROM
		t_pay t 
	WHERE
		t.FLAG = 0 
	GROUP BY
		( CONVERT ( t.create_time, DATE ) ) 
	) t4 ON t3.date_str = t4.date_str 
ORDER BY
t1.date_str ASC;