最近做统计用到的几个常用sql

系统管理员 2022-12-25 03:54 127阅读 0赞

## 计算同比 ##

SELECT
        old.now_time,
        ROUND( ( new.count - old.count ) / old.count * 100, 2 ),
        new.count ncount,
        old.count ocount 
    FROM (
    SELECT
        sum( IF ( b.count > 1, 1, 1 ) ) count,
        b.now_time,
        b.tenant_code 
    FROM
        (
        SELECT
            date_format( ro.create_dt, '%Y-%m' ) AS now_time,
            a.tenant_code,
            a.order_no,
            count( a.id ) count 
        FROM
            t_rep_order_timeout a 
        INNER JOIN t_rep_order ro on 
            a.tenant_code = ro.tenant_code and a.order_no = ro.order_no
        WHERE
            a.tenant_code = 'zlyy' 
        GROUP BY
            date_format( ro.create_dt, '%Y-%m' ),
            a.tenant_code,
            a.order_no 
        ) b 
    GROUP BY
        b.now_time,
        b.tenant_code
        ) new 
    LEFT JOIN
    (SELECT
        sum( IF ( b.count > 1, 1, 1 ) ) count,
        b.now_time,
        b.tenant_code 
    FROM
        (
        SELECT
            date_format( DATE_ADD( ro.create_dt, INTERVAL 1 YEAR ), '%Y-%m') AS now_time,
            a.tenant_code,
            a.order_no,
            count( a.id ) count 
        FROM
            t_rep_order_timeout a 
        INNER JOIN t_rep_order ro on 
            a.tenant_code = ro.tenant_code and a.order_no = ro.order_no
        WHERE
            a.tenant_code = 'zlyy' 
        GROUP BY
            date_format( DATE_ADD( ro.create_dt, INTERVAL 1 YEAR ), '%Y-%m'),
            a.tenant_code,
            a.order_no 
        ) b 
    GROUP BY
        b.now_time,
        b.tenant_code) old 
        on old.tenant_code = new.tenant_code and old.now_time = new.now_time

## 计算环比 ##

SELECT
        old.now_time,
        ROUND( ( new.count - old.count ) / old.count * 100, 2 ),
        new.count ncount,
        old.count ocount 
    FROM (
    SELECT
        sum( IF ( b.count > 1, 1, 1 ) ) count,
        b.now_time,
        b.tenant_code 
    FROM
        (
        SELECT
            date_format( ro.create_dt, '%Y-%m' ) AS now_time,
            a.tenant_code,
            a.order_no,
            count( a.id ) count 
        FROM
            t_rep_order_timeout a 
        INNER JOIN t_rep_order ro on 
            a.tenant_code = ro.tenant_code and a.order_no = ro.order_no
        WHERE
            a.tenant_code = 'zlyy' 
        GROUP BY
            date_format( ro.create_dt, '%Y-%m' ),
            a.tenant_code,
            a.order_no 
        ) b 
    GROUP BY
        b.now_time,
        b.tenant_code
        ) new 
    LEFT JOIN
    (SELECT
        sum( IF ( b.count > 1, 1, 1 ) ) count,
        b.now_time,
        b.tenant_code 
    FROM
        (
        SELECT
            date_format( DATE_ADD( ro.create_dt, INTERVAL 1 MONTH ), '%Y-%m') AS now_time,
            a.tenant_code,
            a.order_no,
            count( a.id ) count 
        FROM
            t_rep_order_timeout a 
        INNER JOIN t_rep_order ro on 
            a.tenant_code = ro.tenant_code and a.order_no = ro.order_no
        WHERE
            a.tenant_code = 'zlyy' 
        GROUP BY
            date_format( DATE_ADD( ro.create_dt, INTERVAL 1 MONTH ), '%Y-%m'),
            a.tenant_code,
            a.order_no 
        ) b 
    GROUP BY
        b.now_time,
        b.tenant_code) old 
        on old.tenant_code = new.tenant_code and old.now_time = new.now_time

## 获取某一年份所有月份 ##

SELECT
    CASE
    
        WHEN
            length( mon ) = 1 THEN
                concat( '2019-0', mon ) ELSE concat( '2019-', mon ) 
            END months 
    FROM
        ( SELECT @m := @m + 1 mon FROM t_rep_order_timeout, ( SELECT @m := 0 ) a ) aa 
        LIMIT 12

此处所用的辅助表t\_rep\_oder\_timeot表数据必须超过12条

## 删除表内的重复数据 ##

DELETE t 
    FROM
        interview t
        LEFT JOIN ( SELECT title, min( id ) AS min_id FROM interview GROUP BY title ) t1 ON t.id = t1.min_id 
    WHERE
        t1.min_id IS NULL;

> 欢迎搜索关注本人与朋友共同开发的微信面经小程序【大厂面试助手】和公众号【微瞰技术】

![file][] ![file][file 1]

[file]: /images/20221120/eea59232bb094d8cbb062ae6839b8f68.png
[file 1]: https://img-blog.csdnimg.cn/20201130143653600.png