2016-10-11-mysql-mysql查询命令

Author:@南非波波

编者按:

这篇文章会陆续记录在工作中使用mysql进行数据查询的一些命令，以及将一些需求和命令的实现进行结合讲解。

一、获取在(北京、广州、深圳)近三个月内有登录行为的用户信息，字段：用户名，邮箱，手机号，创建应用数量，注册时间，最后一次登录时间

SELECT
    userName,
    loginName,
    phone,
    num,
    province,
    FROM_UNIXTIME(
        ct / 1000,
        '%Y-%m-%d %H:%i:%S'
    ) AS ct,
    FROM_UNIXTIME(
        loginTime / 1000,
        '%Y-%m-%d %H:%i:%S'
    ) AS loginTime
FROM
    TB_Enterprise_User
INNER JOIN (
    SELECT
        userId,
        COUNT(*) AS num
    FROM
        cad_app_user
    WHERE
        type = 0
    GROUP BY
        userId
    ORDER BY
        num
) tb1 ON TB_Enterprise_User.enterUserId = tb1.userId
WHERE
    LENGTH(phone) = 11
AND phone IS NOT NULL
AND loginTime >= UNIX_TIMESTAMP('2016-07-11 00:00:00') * 1000

AND (
    province = '北京'
    OR province = '广州'
    OR province = '深圳'
)
ORDER BY
    loginTime ASC

需求分析：

用户名，邮箱，手机号，注册时间，最后一次登录时间这几个字段都存放在一张表中(TB_Enterprise_User),只需要写好相应的条件就行。
创建应用数需要从另一张表中(cad_app_user)进行统计查询形成新表与TB_Enterprise_User进行表关联。
这里需要注意loginTime、ct记录的是毫秒值，而UNIX_TIMESTAMP('2016-07-11 00:00:00')转换的记录值是秒

二、获取过去一周新增loader用户的信息

查询指定时间增加loader用户的信息：
    1.统计服务器每天新增文件total_day/new.txt获取appId
        awk -F "|" '{print $1}' 20161023 |sort -k 1 -n -r|uniq -c|awk -F " " '{print $2}' >> test.txt （多天就执行多条，把文件名换一下，然后执行下面一句）
        awk -F "|" '{print $1}' test.txt |sort -k 1 -n -r|uniq -c|awk -F " " '{print $2}' >> test_2.txt(获取到全部的新增loader的应用ID)
    2.根据应用ID获取对应的userId
    3.查询对应userId创建应用的数量
    4.进行表关联，查询用户表，获取用户的信息


SELECT
    userName,
    loginName,
    phone,
    FROM_UNIXTIME(
        TB_Enterprise_User.ct / 1000,
        '%Y-%m-%d %H:%i:%S'
    ) AS ct,
    province,
    num
FROM 
    TB_Enterprise_User
INNER JOIN (
    SELECT
        userId,COUNT(*) AS num
    FROM
        cad_app_user
    WHERE
        userId IN (
            SELECT
                userId
            FROM
                cad_app_user
            WHERE
                type = 0
            AND appId IN('A6999979578606','A6999956860949','A6999909977189')
            GROUP BY
                userId
        )
    GROUP BY
        userId
) tb1 ON TB_Enterprise_User.enterUserId = tb1.userId
ORDER BY
    TB_Enterprise_User.enterUserId ASC;

三、指定时间的自定义loader的使用用户

SELECT
    userName,
    loginName,
    phone,
    FROM_UNIXTIME(
        TB_Enterprise_User.ct / 1000,
        '%Y-%m-%d %H:%i:%S'
    ) AS ct,
    province
FROM
    TB_Enterprise_User
INNER JOIN loader_pack ON TB_Enterprise_User.enterUserId = loader_pack.userId
WHERE
    loader_pack.ct >= UNIX_TIMESTAMP('2016-10-17 00:00:00') * 1000
AND loader_pack.ct < UNIX_TIMESTAMP('2016-10-24 00:00:00') * 1000
GROUP BY enterUserId
ORDER BY
    TB_Enterprise_User.enterUserId ASC;

四、10月28号-11月8号访问课程页面“未买课程”的用户数据。需要信息：注册时间、手机号、邮箱、应用个数、最后登录时间

SELECT
    userName,
    loginName,
    phone,
    FROM_UNIXTIME(
        TB_Enterprise_User.ct / 1000,
        '%Y-%m-%d %H:%i:%S'
    ) AS ct,
    FROM_UNIXTIME(
        TB_Enterprise_User.loginTime / 1000,
        '%Y-%m-%d %H:%i:%S'
    ) AS loginTime,
    num,
    MAX(vip_click.date) AS date
FROM
    TB_Enterprise_User
INNER JOIN vip_click ON TB_Enterprise_User.enterUserId = vip_click.userId
INNER JOIN (
    SELECT
        userId,
        COUNT(*) AS num
    FROM
        cad_app_user
    WHERE
        type = 0
    GROUP BY
        userId
) tb1 ON TB_Enterprise_User.enterUserId = tb1.userId
WHERE
    UNIX_TIMESTAMP(vip_click.date) >= UNIX_TIMESTAMP('2016-10-28 00:00:00')
AND UNIX_TIMESTAMP(vip_click.date) < UNIX_TIMESTAMP('2016-11-09 00:00:00')
AND vip_click.url = '/vipservice/course'
AND vip_click.userId NOT IN (select userid from training_user GROUP BY userid)
GROUP BY
    vip_click.userId
ORDER BY
    vip_click.date ASC;

五、10月21号-11月8号注册的新用户“未访问过课程页面的用户”需要信息：注册时间、手机号、邮箱、最后登录时间

SELECT
    userName,
    loginName,
    phone,
    FROM_UNIXTIME(
        TB_Enterprise_User.ct / 1000,
        '%Y-%m-%d %H:%i:%S'
    ) AS ct,
    FROM_UNIXTIME(
        TB_Enterprise_User.loginTime / 1000,
        '%Y-%m-%d %H:%i:%S'
    ) AS loginTime
FROM
    TB_Enterprise_User
WHERE
    UNIX_TIMESTAMP(ct) >= UNIX_TIMESTAMP('2016-10-28 00:00:00')
AND UNIX_TIMESTAMP(ct) < UNIX_TIMESTAMP('2016-11-09 00:00:00')
AND enterUserId NOT IN(select userId from vip_click GROUP BY
    vip_click.userId)
ORDER BY
    enterUserId ASC;

六、点击公开直播课程按钮的用户信息

SELECT
    userName,
    loginName,
    phone,
    FROM_UNIXTIME(
        TB_Enterprise_User.ct / 1000,
        '%Y-%m-%d %H:%i:%S'
    ) AS ct,
    num
FROM
    TB_Enterprise_User
INNER JOIN (
    SELECT
        userId,
        COUNT(*) AS num
    FROM
        cad_app_user
    WHERE
        type = 0
    GROUP BY
        userId
) tb1 ON TB_Enterprise_User.enterUserId = tb1.userId
where 
    enterUserId in (select userid from vip_click where  url='openclass' and date >'2016-11-06')
ORDER BY
    TB_Enterprise_User.enterUserId ASC;

七、获取指定城市注册用户的信息

SELECT
    userName,
    loginName,
    phone,
    FROM_UNIXTIME(
        ct / 1000,
        '%Y-%m-%d %H:%i:%S'
    ) AS ct,
    FROM_UNIXTIME(
        loginTime / 1000,
        '%Y-%m-%d %H:%i:%S'
    ) AS loginTime,
    province,
    num
FROM
    TB_Enterprise_User
INNER JOIN (
    SELECT
        userId,
        COUNT(*) AS num
    FROM
        cad_app_user
    WHERE
        type = 0
    GROUP BY
        userId
) tb1 ON TB_Enterprise_User.enterUserId = tb1.userId
WHERE
    state != 100
AND (
    TB_Enterprise_User.province = '青岛'
    OR TB_Enterprise_User.province = '潍坊'
    OR TB_Enterprise_User.province = '济南'
    OR TB_Enterprise_User.province = '淄博'
)
ORDER BY
    loginTime ASC

八、获取指定城市购买过七天培训课程的用户信息(运行库为CAD)

SELECT
    userName,
    loginName,
    phone,
    FROM_UNIXTIME(
        ct / 1000,
        '%Y-%m-%d %H:%i:%S'
    ) AS ct,
    FROM_UNIXTIME(
        loginTime / 1000,
        '%Y-%m-%d %H:%i:%S'
    ) AS loginTime,
    province,
    num
FROM
    TB_Enterprise_User
INNER JOIN (
    SELECT
        userId,
        COUNT(*) AS num
    FROM
        cad_app_user
    WHERE
        type = 0
    GROUP BY
        userId
) tb1 ON TB_Enterprise_User.enterUserId = tb1.userId
WHERE
    state != 100
AND (
    TB_Enterprise_User.province = '青岛'
    OR TB_Enterprise_User.province = '潍坊'
    OR TB_Enterprise_User.province = '济南'
    OR TB_Enterprise_User.province = '淄博'
)
AND enterUserId IN (
    SELECT
        userid
    FROM
        training_user
    GROUP BY
        userid
    UNION
        SELECT
            userid
        FROM
            admin2.tr_order
        GROUP BY
            userid
)
ORDER BY
    loginTime ASC


===============================================================================
参考：http://www.cnblogs.com/kissdodog/archive/2013/06/24/3152743.html
使用了union关键字做查询结果的并集 
    select * from table1
    union [all]
    select * from table2
#注意事项：两个表的查询结果数据结构要一致。另外如果使用 union all组合关键字，求并集的时候不会去掉重复行
===============================================================================
#延伸知识
#求交集
    SELECT Name FROM Person_1
    INTERSECT
    SELECT Name FROM Person_2
#求差集
    SELECT Name FROM Person_1
    EXCEPT
    SELECT Name FROM Person_2
===============================================================================

九、获取指定时间的移动端未完全注册渠道来源用户 

SELECT
    userName,
    loginName,
    phone,
    FROM_UNIXTIME(
        TB_Enterprise_User.ct / 1000,
        '%Y-%m-%d %H:%i:%S'
    ) AS ctime,
    channel
FROM
    TB_Enterprise_User
WHERE
    state = 10
AND ct >= UNIX_TIMESTAMP('2016-11-09 00:00:00') * 1000
AND ct < UNIX_TIMESTAMP('2016-11-23 00:00:00') * 1000
AND channelType=0
ORDER BY
ct ASC;

本文标题:2016-10-11-mysql-mysql查询命令

文章作者:南非波波

发布时间:2016-10-11, 11:18:50

最后更新:2016-11-22, 13:53:23

原始链接:http://blog.songqingbo.cn/2016/10/11/2016-10-11-mysql-mysql查询命令/

许可协议: "署名-非商用-相同方式共享 4.0" 转载请保留原文链接及作者。

文章目录