MySQL练习2

最新推荐文章于 2024-10-02 06:14:43 发布

李小乐哦

最新推荐文章于 2024-10-02 06:14:43 发布

阅读量116

点赞数

文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/weixin_45951114/article/details/128984886

版权

大数据同时被 2 个专栏收录

18 篇文章 0 订阅

订阅专栏

mysql

7 篇文章 0 订阅

订阅专栏

该文提供了SQL查询示例，包括统计2017年每月的订单数、用户数和总金额，以及识别2017年11月的新客户。同时展示了不同方法实现这些统计，如使用COUNT、SUM和DATE_FORMAT函数，以及LEFTJOIN和REGEXP操作。

摘要由CSDN通过智能技术生成

第一题：
建表test3：

CREATE TABLE test3 (
           dt string,
           order_id string,
           user_id string,
           amount DECIMAL ( 10, 2 ) )
ROW format delimited FIELDS TERMINATED BY '\t';

插入数据：

INSERT INTO TABLE test3 VALUES ('2017-01-01','10029028','1000003251',33.57);
INSERT INTO TABLE test3 VALUES ('2017-01-01','10029029','1000003251',33.57);
INSERT INTO TABLE test3 VALUES ('2017-01-01','100290288','1000003252',33.57);
INSERT INTO TABLE test3 VALUES ('2017-01-01','10290282','1000003252',33.57);
INSERT INTO TABLE test3 VALUES ('2017-11-01','10290283','1000003252',33.57);
INSERT INTO TABLE test3 VALUES ('2017-11-01','10290284','1000003252',33.57);
INSERT INTO TABLE test3 VALUES ('2017-11-01','10290284','99',33.57);
INSERT INTO TABLE test3 VALUES ('2017-11-01','10290284','100',33.57);
INSERT INTO TABLE test3 VALUES ('2017-02-02','10029088','1000003251',33.57);
INSERT INTO TABLE test3 VALUES ('2017-02-02','100290281','1000003251',33.57);
INSERT INTO TABLE test3 VALUES ('2017-02-02','100290282','1000003253',33.57);
INSERT INTO TABLE test3 VALUES ('2017-11-02','10290282','100003253',234);
INSERT INTO TABLE test3 VALUES ('2018-11-02','10290284','100003243',234);

字段：
    dt string,
    order_id string,
    user_id string,
    amount DECIMAL ( 10, 2 )
数据样例:2017-01-01,10029028,1000003251,33.57。

**问题：**请给出sql进行统计:
(1)给出 2017年每个月的订单数、用户数、总成交金额。
(2)给出2017年11月的新客数(指在11月才有第一笔订单)

(1)给出 2017年每个月的订单数、用户数、总成交金额。
思路：
table:test3
维度：月 date_format(dt,‘yyyy-MM’)
指标：订单数 count、用户数 count(distinct)、总成交金额 sum
where：2017年

方法一：

select mm,count(1) as cnt,count(distinct user_id) as u_cnt,sum(amount) as all_amount
--查询 mm(时间),数量别名cnt， 数量(去重 用户id)  别名 u_cnt,总和(交易金额) 别名all_amount
from(
--从(
    -- 取出需要的字段 +【去重】 => etl
    select date_format(dt, 'yyyy-MM') as mm,order_id,user_id,amount
    --查询 时间(将dt字段格式改为'yyyy-MM')别名 mm，订单id，用户id，交易金额
    from test3
    --从 表test3
    where date_format(dt, 'yyyy') = '2017'
    --哪个 时间(将dt字段格式改为'yyyy-MM') = '2017'
) a
--) 别名a
group by mm;
--分组通过 mm

方法二：

select mm,sum(cnt) as cnt_alias,sum(all_amount) as all_amount_alias,sum(u_cnt) as u_cnt_alias
--查询 mm，总和(cnt) 别名 cnt_alias，总和(all_amount) 别名 all_amount_alias，总和(all_amount) 别名u_cnt_alias
from (
--从 (
    -- 订单数 count 总成交金额 sum
    select mm,count(order_id) as cnt,sum(amount) as all_amount,0 as u_cnt
    --查询 mm，数量(订单id) 别名 cnt，总和(amount) 别名 all_amount,这个表没有下表的u_cnt字段用0代表u_cnt字段
    from(
    --从 (
        select date_format(dt, 'yyyy-MM') as mm,order_id,amount
        --查询 时间(将dt字段格式改为'yyyy-MM') 别名 mm，order_id,amount
        from test3
        --从 表test3
        where date_format(dt, 'yyyy') = '2017'
        --哪个 时间(将dt字段格式改为'yyyy-MM') = '2017'
    ) a
    --) 别名a
    group by mm;
    --分组通过 mm
    union all -- 用户数
    --合并两个表(不去重)
    select mm,0 as cnt,0 as all_amount,count(1) as u_cnt
    --查询 mm，这个表没有上表的cnt字段用0代表cnt字段，这个表没有上表的all_amount字段用0代表all_amount字段，count(1) 别名 u_cnt
    from (
    --从 (
        select date_format(dt, 'yyyy-MM') as mm,user_id
        --查询 时间(将dt字段格式改为'yyyy-MM') 别名 mm，user_id
        from test3
        --从 表test3
        where date_format(dt, 'yyyy') = '2017'
        --哪个 时间(将dt字段格式改为'yyyy-MM') = '2017'
        group by mm,user_id
        --分组 通过 mm,user_id
    ) a
    --) 别名a
    group by mm
    --分组通过 mm
) a
--) 别名a
group by mm;
--分组通过 mm

(2)给出2017年11月的新客数(指在11月才有第一笔订单)
思路：
table: test3
维度： 2017年11月
指标：新客数
where =》新客户
业务主线：test3 =》 2017年11月用户 a
维表：2017年11月之前的数据 b
==》取出新用户

方法一：

select count(1) as new_u_cnt
--查询 数量 别名new_u_cnt
from(
--从(
    select a.mm,a.user_id
    --查询 a表的mm,a表的user_id
    from (
    --从(
        -- 主表 :11月所有的用户
        select date_format(dt, 'yyyy-MM') as mm,user_id
        --查询 时间(将dt字段格式改为'yyyy-MM') 别名 mm，user_id
        from test3
        --从 表test3
        where date_format(dt, 'yyyy-MM') = '2017-11'
        --哪个 时间(将dt字段格式改为'yyyy-MM') = '2017'
        group by mm,user_id
        --分组 通过 mm,user_id
    ) a
    --) 别名a
    left join (
    --左连接(
        -- 维表 :11月前所有的用户
        select date_format(dt, 'yyyy-MM') as mm,user_id
        --查询 时间(将dt字段格式改为'yyyy-MM') 别名 mm，user_id
        from test3
        --从 表test3
        where date_format(dt, 'yyyy-MM') < '2017-11'
        --哪个 时间(将dt字段格式改为'yyyy-MM') < '2017'
        group by mm,user_id
        --分组 通过 mm,user_id
    ) b on a.user_id = b.user_id
    --)别名 b 在a表的user_id = b表的user_id
    where b.user_id is null
    --哪个 b表的user_id 是 null
) a;
--) 别名a

方法二：

select count(*)
--查询 数量
from (
--从(
    select min(dt),user_id
    --查询 最小值(dt),user_id
    from test3
    --从 表test3
    group by user_id
    --分组通过 user_id
) a
--)别名 a
where date_format("yyyy-MM")="2017-11"
--哪个 时间(格式为'yyyy-MM') ="2017-11"

第二题：
建表test6

CREATE TABLE test6 (
       userid string,
       money decimal(10,2),
       paymenttime string,
       orderid string);INSERT INTO TABLE test6 VALUES('001',100,'2017-10-01','123');

插入数据：

INSERT INTO TABLE test6 VALUES('001',200,'2017-10-02','124');
INSERT INTO TABLE test6 VALUES('002',500,'2017-10-01','125');
INSERT INTO TABLE test6 VALUES('001',100,'2017-11-01','126');

**问题：**请用sql写出所有用户中在今年10月份第一次购买商品的金额，

表ordertable字段:购买用户：userid，金额：money，购买时间：paymenttime(格式：2017-10-01)，订单id：orderid

思路：

select a,b,c
from table
where (a,b) in (
    select a,b
    from t2
    where dt="xxxxx"
)

方法一：

select userid,money,paymenttime,orderid,rank
--查询 购买用户，金额，购买时间，订单id，rank
from (
--从(
    select userid,money,paymenttime,orderid,row_number() over(partition by userid order by paymenttime) as rank
    --查询 购买用户，金额，购买时间，订单id，先根据userid分组，在分组内部根据paymenttime排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内编号连续的唯一的) 别名rank
    from (
    --从(
        -- etl
        select userid,money,paymenttime,orderid
        --查询 购买用户，金额，购买时间，订单id
        from test6
        --从 表test6
        where date_format(paymenttime, "yyyy-MM") = "2017-10"
        --哪个时间(格式为'yyyy-MM') ="2017-10"
    ) a
    --)别名 a
) a
--)别名 a
where rank = 1;
哪个 rank=1

方法二：

select *
--查询 所有
from test6
--从 表test6
where (paymenttime, userid) in (
--哪个 (购买时间,购买用户) 在
    select min(paymenttime),userid
    --查询 最小值(购买时间),购买用户
    from test6
    --从 表test6
    where paymenttime regexp "2017-10-*"
    --哪个 购买时间 正则表达式 2017年10月所有天
    group by userid
    --分组通过 购买用户
);
--)(记录集)

第三题：
建表test4user：

CREATE TABLE test4user
           (user_id string,
            name string,
            age int);

建表test4log：

CREATE TABLE test4log
                        (user_id string,
                        url string);

插入数据：

INSERT INTO TABLE test4user VALUES('001','u1',10);
INSERT INTO TABLE test4user VALUES('002','u2',15);
INSERT INTO TABLE test4user VALUES('003','u3',15);
INSERT INTO TABLE test4user VALUES('004','u4',20);
INSERT INTO TABLE test4user VALUES('005','u5',25);
INSERT INTO TABLE test4user VALUES('006','u6',35);
INSERT INTO TABLE test4user VALUES('007','u7',40);
INSERT INTO TABLE test4user VALUES('008','u8',45);
INSERT INTO TABLE test4user VALUES('009','u9',50);
INSERT INTO TABLE test4user VALUES('0010','u10',65);
INSERT INTO TABLE test4log VALUES('001','url1');
INSERT INTO TABLE test4log VALUES('002','url1');
INSERT INTO TABLE test4log VALUES('003','url2');
INSERT INTO TABLE test4log VALUES('004','url3');
INSERT INTO TABLE test4log VALUES('005','url3');
INSERT INTO TABLE test4log VALUES('006','url1');
INSERT INTO TABLE test4log VALUES('007','url5');
INSERT INTO TABLE test4log VALUES('008','url7');
INSERT INTO TABLE test4log VALUES('009','url5');
INSERT INTO TABLE test4log VALUES('0010','url1');

**问题：**有一个5000万的用户文件(user_id，name，age)，
一个2亿记录的用户看电影的记录文件(user_id，url)，
根据年龄段观看电影的次数进行排序？

思路：
join：
维度：年龄
指标：观看电影的次数 count

--创建一个临时表
create table tmp as
--创建 表 tmp 作为
select age,count(1) as cnt
--查询 age,数量 别名 cnt
from(
--从(
    select age,url
    --查询 age,url
    from test4log as a
    --从 表test4log 别名 a
    left join test4user as b on a.user_id=b.user_id
    --左连接 表test4user 别名 b 在 a表的user_id=b表的user_id
) a
--)别名a
group by age;
--分组 通过 age

select
--查询
    case
    --开始
        when age >= 10 and age < 15 then '10-15'
        --当 年龄 >= 10 并且 年龄 <= 15 然后 取10-15'
        when age >= 15 and age < 20 then '15-20'
        --当 年龄 >= 10 并且 年龄 <= 15 然后 取10-15'
        when age >= 20 and age < 30 then '20-30'
        --当 年龄 >= 10 并且 年龄 <= 15 然后 取10-15'
    end as flag_age,
    --结束 别名 flag_age
    sum(cnt) as all_cnt
    --总和(cnt) 别名 all_cnt
from tmp
--从 表tmp
group by flag_age;
--分组通过 flag_age

第四题：
建表test5：

CREATE TABLE test5(
dt string,
user_id string,
age int)
ROW format delimited fields terminated BY ',';

插入数据：

INSERT INTO TABLE test5 VALUES ('2019-02-11','test_1',23);
INSERT INTO TABLE test5 VALUES ('2019-02-11','test_2',19);
INSERT INTO TABLE test5 VALUES ('2019-02-11','test_3',39);
INSERT INTO TABLE test5 VALUES ('2019-02-11','test_1',23);
INSERT INTO TABLE test5 VALUES ('2019-02-11','test_3',39);
INSERT INTO TABLE test5 VALUES ('2019-02-11','test_1',23);
INSERT INTO TABLE test5 VALUES ('2019-02-12','test_2',19);
INSERT INTO TABLE test5 VALUES ('2019-02-13','test_1',23);
INSERT INTO TABLE test5 VALUES ('2019-02-15','test_2',19);
INSERT INTO TABLE test5 VALUES ('2019-02-16','test_2',19);

**问题：**请写出代码求得所有用户和活跃用户的总数及平均年龄。（活跃用户指连续两天都有访问记录的用户）
思路：
a => 所有用户
union all
b =》活跃用户

drop table tmp1;
--删除 表 tmp1

-- 活跃用户
create table tmp1 as
--创建 表 tmp1 作为
select user_id,age,dt,row_number() over(partition by user_id order by dt) as rk
-- select user_id,age,dt,先根据userid分组，在分组内部根据dt排序，而此函数计算的值就表示每组内部排序后的顺序编号(组内编号连续的唯一的) 别名rk
from (
--从(
    select user_id,dt,age
    --查询 user_id,dt,age
    from test5
    --从 表test5
    group by user_id,dt,age
    --分组 通过 user_id,dt,age
) a;
--) 别名 a

select sum(all_cnt) as all_cnt_alias,sum(avg_age) as avg_age_alias,sum(active_cnt) as active_cnt_alias,sum(active_age) as active_age_alias
--查询 总和(all_cnt) 别名 all_cnt_alias,总和(avg_age) 别名 avg_age_alias,总和(active_cnt) 别名 active_cnt_alias,总和(active_age) 别名 active_age_alias
from (
--从(
    -- 所有用户 count avg age
    select count(1) as all_cnt,avg(age) as avg_age,0 as active_cnt,0 as avg_age
    --查询 数量 别名 all_cnt,平均值(年龄) 别名 avg_age,这个表没有上表的active_cnt字段用0代表active_cnt字段，这个表没有上表的avg_age字段用0代表avg_age字段，
    from(
    --从(
        select user_id,age
        --查询 user_id,age
        from test5
        --从 表test5
        group by user_id,age
        --分组 通过 user_id,age
    ) a ;
    --)别名 a
    union all -- 活跃用户
    --合并(不去重)
    select 0 as all_cnt,0 as avg_age,count(1) as active_cnt,avg(age) as avg_age
    --查询 这个表没有上表的all_cnt字段用0代表all_cnt字段，这个表没有上表的avg_age字段用0代表avg_age字段，平均(年龄) 别名 avg_age
    from (
    --从 (
        select user_id
        --查询 user_id
        from tmp1 as a join tmp1 as b on a.user_id = b.user_id and b.dt = date_add(a.dt, 1)
        --从 表tmp1 别名 a 连接 表tmp1 别名 b 在 a表的user_id = b表的user_id and b表的dt = 指定从a表的dt时间添加1天
        group by user_id
        --分组 通过 user_id
    ) a;
    --)别名a
) a;
--)别名a