本网站(662p.com)打包出售,且带程序代码数据,662p.com域名,程序内核采用TP框架开发,需要联系扣扣:2360248666 /wx:lianweikj
精品域名一口价出售:1y1m.com(350元) ,6b7b.com(400元) , 5k5j.com(380元) , yayj.com(1800元), jiongzhun.com(1000元) , niuzen.com(2800元) , zennei.com(5000元)
需要联系扣扣:2360248666 /wx:lianweikj
mysql常用报表处理及数据迁移写法SQL
iamitnan · 455浏览 · 发布于2019-09-30 +关注

熟悉一些常用的sql写法便于工作中快速导出数据,本文不涉及到业务,所以对表库做了名字的修改,仅提供一些用法的说明。

以下直接举例子并讲解

1 单表批量数据迁移

场景:日志迁移

具体实例:将test_log2日志表2的数据全部迁移到test_log1日志表1

sql:

INSERT INTO `xxx`.test_log1

(

    `operate_account_id`,

    `student_id`, `transfer_type`, `gmt_create`, `gmt_modify`

)

SELECT

    operate_account_id AS operate_account_id

    ,student_id AS student_id    

    ,1 AS transfer_type

    ,gmt_create AS gmt_create

    ,gmt_modify AS gmt_modify

FROM `xxx`.test_log2;

说明:数据量只有几十万的话,还是很轻松的,可以直接使用上面sql

2 联表批量更新数据

场景:tab2表的数据

具体实例:需要把tab2表的最新记录的insertTime更新到tab1的lastFollowTime

sql:

UPDATE `xxx`.tab1 t

    INNER JOIN (

        SELECT MAX(insertTime) AS insertTime,userId FROM `xx`.tab2

            WHERE type!=2 OR (type=2 and (content is not null and content != "" ))  GROUP BY userId

    ) f

    ON t.UserId = f.userId

    SET t.lastFollowTime = f.insertTime

    WHERE f.insertTime is not null

        AND (t.lastFollowTime IS NULL

            OR DATE_FORMAT(f.insertTime, '%Y-%m-%d %H:%i:%s') <  DATE_FORMAT(f.insertTime, '%Y-%m-%d %H:%i:%s')

        );

说明:这里有两个小技巧

- 第一个是left join子查询里面用分组+max找出当前userId最新的那条记录(算是一个找最新记录的小技巧)

- 第二个是DATE_FORMAT(f.insertTime, '%Y-%m-%d %H:%i:%s')进行比较,因为`xx`.tab2的时间格式是这样的"2019-09-10 17:57:48.793",所以“2019-09-10 17:57:48.793”会永远大于“2019-09-10 17:57:48”,为了过滤这些条件,就加了个DATE_FORMAT 

3 多次left join联表导出数据

场景:查出某个时间段带有某些字段的报表数据

具体实例:查出2019/0.9/01至2019/10/01之间的数据,以下会说明一些查的技巧

sql:

SELECT

    # S点

    DATE_FORMAT(tc.start_date, "%Y/%m/%d %H:%i") AS "开始时间"

    ,DATE_FORMAT(date_add(tc.start_date, interval 1 hour), "%Y/%m/%d %H:%i") AS "结束时间"

    # A点

    ,dep.name AS "部门"

    # B点

    ,IF(aspc.payDate IS NOT NULL, "是", "否") AS "是否存在B点数据"

    ,IF(aspc.payDate IS NOT NULL, aspc.payDate, "") AS "B点数据对应时间"

    

FROM `xxxx`.`u_t_c_s` ut

    INNER JOIN `xxxx`.`t_c_s` tc ON ut.t_c_s_id = tc.id     

    INNER JOIN `xxx`.userinfo ui ON ui.Id = tc.user_id

    # 获得A点数据

    INNER JOIN `xxxx`.account acc ON acc.userId = ui.Id

    INNER JOIN

    (

        SELECT a2.name AS groupName,a1.id AS groupId FROM `xxxx`.`dept` a1,`xxxx`.`dept` a2

            WHERE a1.pId = a2.id

    ) dep

    ON dep.groupId = acc.deptId

    # 获得B点数据

    LEFT JOIN

    (

        SELECT MAX(IF(fitstDate IS NOT NULL, fitstDate, secondDate)) AS payDate, userId FROM `xxx`.table_b WHERE state=3 GROUP BY userId

    ) aspc

    ON aspc.userId = ut.user_id

    

WHERE tc.start_date >= '2019-09-01 00:00:00' AND tc.start_date <= '2019-10-01 00:00:00'

    AND tc.type IN (1, 2) AND tc.`status`=0 AND ut.`status`=0;    

说明:上面sql使用了点技巧

- S点的技巧:DATE_FORMAT用法,可以直接获取当前时间一个钟后时间,用法见:https://www.cnblogs.com/kitor/p/11099196.html

- A点技巧:获取A点数据采用了dept自己跟自己连接的方式来处理,使用pId父Id关联

- B点技巧:这里采用了跟"2 联表批量更新数据"类似的技巧,max+group by筛选出每一个userId对应最新的那条数据

额外:其中IINER JOIN是内连,必须要符合的条件(可用于筛选过滤条件),LEFT JOI是外连,用于关联一些可能存在的数据

4 按范围导出多个时间段的数量分布

范围也属于条件的一种,可用CASE..WHEN,或者用区分INTERVAL这个范围更简单,事例如下:

INTERVAL用法:

SELECT

    INTERVAL(tmp.totalTime,5*60,10*60,20*60,30*60,40*60,50*60,60*60+1) AS TIME,

   COUNT(*)

FROM

(

    SELECT MAX(totalTime) AS totalTime,account FROM

  `xxxx`.`tab_a`

    WHERE

    id IN (

     # 这里是一系列的子查询

     SELECT xx FROM XXXXXXXX

  )

  GROUP BY account     

) tmp

GROUP BY TIME;

这里的步骤是:

    - 红色部分子查询先用group by account 根据account分组,找出每个account对应的最大totalTime的那条记录

    - 以上面作为结果集返回,然后使用INTERVAL划分区间,表示为如下:

        - 区间为:(tmp.totalTime < 5*60)、(tmp.totalTime >= 5*60 && tmp.totalTime < 10*60)、(tmp.totalTime >= 10*60 && tmp.totalTime < 20*60)....................

    - 然后COUNT(*)就可以把每个区间的数量统计出来

参考文章:https://www.cnblogs.com/bindot/p/interval.html

 

CASE..WHEN用法(作用与上面一致):

SELECT

CASE

    totalTime

    WHEN totalTime = 0 THEN

    '0分钟'

    WHEN 5>totalTime >= 0 THEN

    '5分钟'

    WHEN 10>totalTime >= 5 THEN

    '10分钟'

    WHEN 20>totalTime >= 10 THEN

    '20分钟'

    WHEN 30>totalTime >= 20 THEN

    '30分钟'

    WHEN 40>totalTime >= 30 THEN

    '40分钟'

    WHEN 50>totalTime >= 40 THEN

    '50分钟'

    WHEN 60>totalTime >= 50 THEN

    '60分钟'

  END AS time,

  count(*)

FROM

(

    SELECT MAX(totalTime) AS totalTime,account FROM

    `xxx`.`tab_a`

        WHERE

          id IN (

                SELECT MAX(totalTime) AS totalTime,account FROM

                  `xxxx`.`tab_a`

                    WHERE

                    id IN (

                     # 这里是一系列的子查询

                     SELECT xx FROM XXXXXXXX

                  )

                  GROUP BY account     

          )

)

GROUP BY

CASE

    totalTime

    WHEN totalTime = 0 THEN

  '0分钟'

  WHEN 5>totalTime >= 0 THEN

    '5分钟'

  WHEN 10>totalTime >= 5 THEN

    '10分钟'

    WHEN 20>totalTime >= 10 THEN

    '20分钟'

    WHEN 30>totalTime >= 20 THEN

    '30分钟'

    WHEN 40>totalTime >= 30 THEN

    '40分钟'

    WHEN 50>totalTime >= 40 THEN

    '50分钟'

    WHEN 60>totalTime >= 50 THEN

    '60分钟'  

  END;

 

总结:mysql性能毕竟还是不够高,使用连接的时候并没有使用hash连接,就像有一次我写了一条很多left join的sql,结果使用mysql导出要半个钟左右,后改改用分布式的SQL查询引擎(Presto),导出仅需十几秒,类似这些工具也可以多采用。

相关推荐

使用SELECT语句检索数据

奔跑的男人 · 793浏览 · 2019-06-03 09:33:43
部署MySQL延迟从库的几个好处

吴振华 · 657浏览 · 2019-05-14 21:57:51
MongoDB凭什么跻身数据库排行前五?

iamitnan · 716浏览 · 2019-06-18 10:04:56
Oracle开启和关闭的几种模式

qq2360248666 · 738浏览 · 2019-06-04 10:18:47
加载中

0评论

评论
分类专栏
小鸟云服务器
扫码进入手机网页