踏踏实实练习SQL--day09

2024-03-26 16

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

简介： 踏踏实实练习SQL--day09

查询所有用户的连续登录两天及以上的日期区间

select user_id,
       min(login_date) start_date,
       max(login_date) end_date
from (
         select user_id,
                login_date,
                date_sub(login_date, rn) flag
         from (
                  select user_id,
                         login_date,
                         row_number() over (partition by user_id order by login_date) rn
                  from (
                           select user_id,
                                  date_format(login_ts, 'yyyy-MM-dd') login_date
                           from user_login_detail
                           group by user_id, date_format(login_ts, 'yyyy-MM-dd')
                       ) t1
              ) t2
     ) t3
group by user_id, flag
having count(*) >= 2;

男性和女性每日的购物总金额统计

题目需求

从订单信息表（order_info）和用户信息表（user_info）中，分别统计每天男性和女性用户的订单总金额，如果当天男性或者女性没有购物，则统计结果为0。期望结果如下：

create_date （日期）	total_amount_male （男性用户总金额）	total_amount_female （女性用户总金额）
2021-09-27	29000.00	0.00
2021-09-28	70500.00	0.00
2021-09-29	43300.00	0.00
2021-09-30	860.00	0.00
2021-10-01	0.00	171680.00
2021-10-02	0.00	76150.00
2021-10-03	89880.00	5910.00
2021-10-04	9390.00	120100.00
2021-10-05	109760.00	69850.00
2021-10-06	101070.00	54300.00
2021-10-07	54700.00	129480.00
2021-10-08	51950.00	24020.00

select create_date,
       sum(if(gender = '男', total_amount, 0)) total_amount_male,
       sum(if(gender = '女', total_amount, 0)) total_amount_female
from order_info oi
         left join
     user_info ui
     on oi.user_id = ui.user_id
group by create_date;

订单金额趋势分析

题目需求

查询截止每天的最近3天内的订单金额总和以及订单金额日平均值，保留两位小数，四舍五入。期望结果如下：

create_date （日期）	total_3d （最近3日订单金额总和）	avg_ad （最近3日订单金额日平均值）
2021-09-27	29000.00	29000.00
2021-09-28	99500.00	49750.00
2021-09-29	142800.00	47600.00
2021-09-30	114660.00	38220.00
2021-10-01	215840.00	71946.67
2021-10-02	248690.00	82896.67
2021-10-03	343620.00	114540.00
2021-10-04	301430.00	100476.67
2021-10-05	404890.00	134963.33
2021-10-06	464470.00	154823.33
2021-10-07	519160.00	173053.33
2021-10-08	415520.00	138506.67

select
create_date,
      round( sum(total_amount_by_day) over (order by create_date rows between 2 preceding
           and current row ),2) total_3d,
       round(avg(total_amount_by_day) over(order by create_date rows between 2 preceding
           and current row ),2) avg_3d

       from
(select create_date,
       sum(total_amount) total_amount_by_day
from order_info2
group by create_date)t1

--由起点到当前行的开窗函数
sum(score) over(partition by subject order by score rows between unbounded preceding and current row)
--当前行和前面一行的窗口聚合
sum(score) over (partition by subject order by score rows between 1 preceding and 1 following)
--当前和后面所有行
sum(score) over (partition by subject order by score rows between current and unbounding following)
join列合并（行），union行合并（上下合并）