
--创建表scdn_student_score_test 并且初始化三个学生成绩
create table hdw_tmp_dev.scdn_student_score_test as 
select '张三' as name,'数学' as subject ,'80' as score 
union all 
select '张三' as name,'语文' as subject ,'90' as score
union all 
select '张三' as name,'英语' as subject ,'90' as score 
union all 
select '李四' as name,'数学' as subject ,'90' as score
union all 
select '李四' as name,'语文' as subject ,'90' as score
union all 
select '李四' as name,'英语' as subject ,'70' as score 
union all 
select '王五' as name,'数学' as subject ,'90' as score
union all 
select '王五' as name,'语文' as subject ,'90' as score
union all 
select '王五' as name,'英语' as subject ,'50' as score 
--查询结果显示
| scdn_student_score_test.name  | scdn_student_score_test.subject  | scdn_student_score_test.score  |
+-------------------------------+----------------------------------+--------------------------------+
| 张三                            | 数学                               | 80                             |
| 张三                            | 语文                               | 90                             |
| 张三                            | 英语                               | 90                             |
| 李四                            | 数学                               | 90                             |
| 李四                            | 语文                               | 90                             |
| 李四                            | 英语                               | 70                             |
| 王五                            | 数学                               | 90                             |
| 王五                            | 语文                               | 90                             |
| 王五                            | 英语                               | 50                             |
+-------------------------------+----------------------------------+---------------------
 
--求所有学科成绩都大于等于80分的学生姓名

思路一（思维转换）：所有问题找最小（最大）


select 
    t1.name
    ,t1.min_score 
from 
    (
        select 
            name 
            ,min(score)  as min_score
        from hdw_tmp_dev.scdn_student_score_test
        group by name 
    ) as t1 --求出最小的成绩
where t1.min_score >=80 --最小成绩大于等于80，则这个学生的所有成绩都会大于等于80

执行结果

思路二（巧用左关联进行筛选）：


select 
    t1.name 
from 
(
    select 
        name 
    from hdw_tmp_dev.scdn_student_score_test
    group by name 
) as t1 
left join 
(
    select 
        name 
    from hdw_tmp_dev.scdn_student_score_test
    where score <80
    group by name 
) as t2 on t1.name = t2.name
where t2.name is null

连续登录问题

建表+初始化


create table hdw_tmp_dev.csdn_user_login_test as 
select 'xiaoming' as user_name,'2024-01-01' as login_date 
union all 
select 'xiaoming' as user_name,'2024-01-02' as login_date 
union all 
select 'xiaoming' as user_name,'2024-01-03' as login_date
union all 
select 'xiaoming' as user_name,'2024-01-04' as login_date
union all 
select 'xiaoming' as user_name,'2024-01-05' as login_date
union all 
select 'dahuang' as user_name,'2024-01-02' as login_date 
union all 
select 'dahuang' as user_name,'2024-01-03' as login_date
union all 
select 'dahuang' as user_name,'2024-01-04' as login_date
union all 
select 'dahuang' as user_name,'2024-01-05' as login_date
union all 
select 'lucky_dog' as user_name,'2024-01-01' as login_date 
union all 
select 'lucky_dog' as user_name,'2024-01-03' as login_date
union all 
select 'lucky_dog' as user_name,'2024-01-04' as login_date
union all 
select 'lucky_dog' as user_name,'2024-01-05' as login_date

Sql参考


select 
    t2.user_name 
    ,t2.date_begin_flag
    ,count(1) as max_login_days
from 
(
    select 
        t1.user_name 
        ,t1.login_date
        ,date_sub(date(login_date),t1.rn) as date_begin_flag
    from 
    (
        select 
            user_name 
            ,login_date 
            ,row_number()over(partition by user_name order by login_date) as rn 
        from hdw_tmp_dev.csdn_user_login_test
    ) as t1
) as t2  
group by t2.user_name 
    ,t2.date_begin_flag

执行过程+结果

行列转换问题

建表+初始化：参考问题一的表


| t1.name              | t1.subject             | t1.score  |
+-------------------------------+----------------------------------+-----------------+
| 张三                 | 数学                    | 80                             |
| 张三                 | 语文                    | 90                             |
| 张三                 | 英语                    | 90                             |
| 李四                 | 数学                    | 90                             |
| 李四                 | 语文                    | 90                             |
| 李四                 | 英语                    | 70                             |
| 王五                 | 数学                    | 90                             |
| 王五                 | 语文                    | 90                             |
| 王五                 | 英语                    | 50                             |
+-------------------------------+----------------------------------+------------------

行专列Sql参考


select 
    name 
    ,max(case when subject = '数学' then score end) as math_score
    ,max(case when subject = '语文' then score end) as china_score
    ,max(case when subject = '英语' then score end) as english_score
from hdw_tmp_dev.scdn_student_score_test
group by name

行专列执行结果

列转行：可以参考建表初始化语句（union all）


select 
    name
    ,collect_set(subject)  as subject_set
from hdw_tmp_dev.scdn_student_score_test
group by name

每个学生选课结果（多行变一行）：

将上面的结果展开（一行变多行）数据准备：


create table hdw_tmp_dev.scdn_student_score_test_collect as 
select 'zhangsan' as name ,'"数学","语文","英语"' as subject_list
union all 
select 'lisi' as name ,'"美术","生物","物理"' as subject_list
union all 
select 'wangwu' as name ,'"计算机","日语","韩语"' as subject_list

炸开代码


select 
    name
    ,subject_list
    ,subject_name
from hdw_tmp_dev.scdn_student_score_test_collect
lateral view explode(split(subject_list,',')) extend_sub as subject_name

结果

留存问题：看当天登录后第N天是否登录

建表+初始化：参照连续登录表

Sql参考


--方案一：利用lead(日期，N)是否等于 当天登录实践+N天
select 
    t1.user_name
    ,t1.logon_date
    ,case when lead1_logon_date = date_add(logon_date,1) then '1天留存' end as 1day_remain
    ,case when lead3_logon_date = date_add(logon_date,3) then '3天留存' end as 3day_remain
from 
(
    select 
        user_name 
        ,logon_date 
        ,lead(user_name,1)over(partition by user_name order by logon_date) as lead1_user_name
        ,lead(logon_date,1)over(partition by user_name order by logon_date) as lead1_logon_date
        ,lead(user_name,3)over(partition by user_name order by logon_date) as lead3_user_name
        ,lead(logon_date,3)over(partition by user_name order by logon_date) as lead3_logon_date
    from hdw_tmp_dev.csdn_user_logon_test
) as t1 
--方案二：
select 
    t2.first_log_date       as first_log_date
    ,count(t2.user_id)      as new_user_cnt --新用户数 
    ,count(t3.user_id)      as next_user_id --次日回访用户数
    ,count(t4.user_id)      as 30_user_id   --30天回访用户数
    ,count(t3.user_id)/count(t2.user_id)    as next_back_rate --次日回访率
    ,count(t4.user_id)/count(t2.user_id)    as 30_back_rate   --30天回访率
from 
(
    select 
        first_log_date
        ,user_id 
        ,date_add(first_log_date,1)  as next_log_date
        ,date_add(first_log_date,29) as 30_log_date
    from 
    (
        select 
            user_id 
            ,log_time 
            ,first_value(date(log_time))over(partition by user_id) as first_log_date 
        from user_log 
    ) as t1 
    group by first_log_date
        ,user_id 
) as t2 
left join 
(
    select 
        user_id 
        ,date(log_date) as log_date  
    from user_log 
    group by user_id 
        ,date(log_date) as log_date 
) as t3 on t2.user_id = t2.user_id and t2.next_log_date = t3.log_date
left join 
(
    select 
        user_id 
        ,date(log_date) as log_date  
    from user_log 
    group by user_id 
        ,date(log_date) as log_date 
) as t4 on t2.user_id = t4.user_id and t2.30_log_date = t4.log_date
group by t2.first_log_date

方案一执行结果

TopN问题

建表+初始化


create table hdw_tmp_dev.scdn_student_score_test1 as 
select '张三' as name,'数学' as subject ,'80' as score 
union all 
select '张三' as name,'语文' as subject ,'90' as score
union all 
select '张三' as name,'英语' as subject ,'90' as score 
union all 
select '李四' as name,'数学' as subject ,'90' as score
union all 
select '李四' as name,'语文' as subject ,'90' as score
union all 
select '李四' as name,'英语' as subject ,'70' as score 
union all 
select '王五' as name,'数学' as subject ,'90' as score
union all 
select '王五' as name,'语文' as subject ,'90' as score
union all 
select '王五' as name,'英语' as subject ,'50' as score 
union all 
select '小明' as name,'数学' as subject ,'88' as score
union all 
select '小明' as name,'语文' as subject ,'99' as score
union all 
select '小明' as name,'英语' as subject ,'77' as score
union all 
select '小文' as name,'数学' as subject ,'66' as score
union all 
select '小文' as name,'语文' as subject ,'89' as score
union all 
select '小文' as name,'英语' as subject ,'90' as score

Sql参考：求每科前三名对应的人员的成绩单


select 
    *
from 
(
    select 
        name
        ,subject
        ,score
        ,row_number()over(partition by subject order by score desc) as rn 
    from hdw_tmp_dev.scdn_student_score_test1
) as t1 
where rn<=3

执行结果

累计计算问题

建表+初始化


create table hdw_tmp_dev.user_sale_date as 
select '001' as user_id,'2024-02-01' as sale_date, 100 as amount 
union all 
select '001' as user_id,'2024-02-02' as sale_date, 200 as amount 
union all 
select '001' as user_id,'2024-02-03' as sale_date, 300 as amount 
union all 
select '001' as user_id,'2024-02-04' as sale_date, 400 as amount 
union all 
select '001' as user_id,'2024-02-05' as sale_date, 500 as amount 
union all 
select '001' as user_id,'2024-02-06' as sale_date, 600 as amount

SQL逻辑


select 
    user_id
    ,sale_date
    ,amount
    ,sum(amount)over(partition by user_id order by sale_date) as accuma_amount --按日期逐渐累加
    ,sum(amount)over(partition by user_id) as total_amount --按人汇总
    ,avg(amount)over(partition by user_id) as avg_amount --安人平均每天
    ,max(amount)over(partition by user_id) as max_amount --单日最大销售
    ,min(amount)over(partition by user_id) as min_amount --单日最小销售
from hdw_tmp_dev.user_sale_date

结果展示

HSQL进阶版

直播间在线最大人数

建表+初始化


create table hdw_tmp_dev.csdn_user_login_time_detail as 
select '001' as user_id,'2024-02-01 10:00:00' as begin_time,'2024-02-01 12:00:00' as end_time 
union all 
select '002' as user_id,'2024-02-01 11:00:00' as begin_time,'2024-02-01 13:00:00' as end_time 
union all 
select '003' as user_id,'2024-02-01 11:00:00' as begin_time,'2024-02-01 14:00:00' as end_time 
union all 
select '004' as user_id,'2024-02-01 15:00:00' as begin_time,'2024-02-01 16:00:00' as end_time

SQL逻辑


select 
    t1.user_id 
    ,t1.time1
    ,t1.flag
    ,sum(flag)over(order by t1.time1) as user_cnt 
from 
(
    select 
        user_id
        ,begin_time as time1 
        ,1    as flag 
    from hdw_tmp_dev.csdn_user_login_time_detail
    union all 
    select 
        user_id
        ,end_time
        ,-1    as flag 
    from hdw_tmp_dev.csdn_user_login_time_detail
) as t1

结果展示

SQL循环

建表+初始化


create table hdw_tmp_dev.cycle_1 as 
select '1011' as a 
union all 
select '0101' as a

SQL逻辑


select  
        a,
        concat_ws(",",collect_list(cast(index  as  string)))  as  res
from  (
        select  
                a,
                index+1  as  index,
                chr
        from  (
                select  
                        a,
                        concat_ws(",",substr(a,1,1),substr(a,2,1),substr(a,3,1),substr(a,-1))  str
                from  hdw_tmp_dev.cycle_1 as t8
        )  tmp1
        lateral  view  posexplode(split(str,","))  t  as  index,chr
        where  chr  =  "1"
)  tmp2
group  by  a;

结果展示

计算中位数

建表+初始化:


create table hdw_tmp_dev.user_sale_date as 
select '001' as user_id,'2024-02-01' as sale_date, 100 as amount 
union all 
select '001' as user_id,'2024-02-02' as sale_date, 200 as amount 
union all 
select '001' as user_id,'2024-02-03' as sale_date, 300 as amount 
union all 
select '001' as user_id,'2024-02-04' as sale_date, 400 as amount 
union all 
select '001' as user_id,'2024-02-05' as sale_date, 500 as amount 
union all 
select '001' as user_id,'2024-02-06' as sale_date, 600 as amount

SQL逻辑


select 
    t1.user_id
    ,t1.sale_date
    ,t1.amount
    ,avg(t1.amount)over(partition by t1.user_id) as zhongwenshu
from 
(
    select 
        user_id
        ,sale_date
        ,amount
        ,row_number()over(partition by user_id order by amount) as rn 
    from hdw_tmp_dev.user_sale_date
) as t1 
left join 
(
    select 
        user_id 
        ,count(1) as cnt 
    from hdw_tmp_dev.user_sale_date
    group by user_id 
) as t2 on t1.user_id = t2.user_id 
where t1.rn in (cnt/2,(cnt+1)/2,cnt/2+1)
--总个数为奇数命中(cnt+1)/2；总个数为偶数命中：cnt/2,cnt/2+1，两数相加求平均值

结果展示

产生连续数值

SQL逻辑


--产生1到10的连续数据
select
    start_id + pos  as  id
    ,pos
    ,val
from(
    select
        1   as  start_id,
        10  as  end_id
)  m  lateral  view  posexplode(split(space(end_id - start_id),''))  t  as  pos,  val
--方案二
select
    row_number()  over()  as  id
from    
    (select  split(space(99), '') as  x)  t
lateral  view
explode(x)  ex;

结果展示

声明：本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：【wpsshop博客】