这些SQL你练习过吗？（网友提供的SQL）

SQL实践：分组查询、中位数计算、分数段统计及用户行为分析

原创于 2024-01-27 11:00:09 发布 · 837 阅读

15 ·

CC 4.0 BY-SA版权

文章标签：

#sql #数据库

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

36 篇文章

订阅专栏

Hive

7 篇文章

订阅专栏

文章主要内容涉及SQL练习，包括将成绩数据按科目汇总、查询特定ID的子节点、计算男女成绩的中位数、分数段统计、消费行为升级降级、会话识别以及朋友关系的互为朋友数量的计算。

行转列SQL练习

题目

把图1转换成图2结果展示

图1

CREATE TABLE `TEST_TB_GRADE` (
  `ID` int(10) NOT NULL AUTO_INCREMENT,
  `USER_NAME` varchar(20) DEFAULT NULL,
  `COURSE` varchar(20) DEFAULT NULL,
  `SCORE` float DEFAULT '0',
  PRIMARY KEY (`ID`)
)


insert into TEST_TB_GRADE(USER_NAME, COURSE, SCORE)  values
("张三", "数学", 34),
("张三", "语文", 58),
("张三", "英语", 58),
("李四", "数学", 45),
("李四", "语文", 87),
("李四", "英语", 45),
("王五", "数学", 76),
("王五", "语文", 34),
("王五", "英语", 89);

参考答案

select 
	USER_NAME,
  sum(if(COURSE='数学',SCORE,0)) as '数学',
  sum(if(COURSE='英语',SCORE,0)) as '英语',
  sum(if(COURSE='语文',SCORE,0)) as '语文' 
from TEST_TB_GRADE 
group by USER_NAME;

求某ID的所有子结点

题目

给一个表 , 有 ID 和 PARENT_ID 两个字段 , 然后求某ID的所有子结点

ID	PARENT_ID
900	NULL
9011	901
9012	901
9013	9012
9014	9013

比如 求 901 的所有子结点 
结果为：
9011
9012
9013
9014

参考

with temp as(
    select * from table_name where PARENT_ID = '901'
    union all
    select t0.*  from temp,table_name t0 where temp.ID=t0.PARENT_ID
)

select ID from temp;

用SQL求中位数

关注一起学习，这些sql都是讨论的结果，可以关注进群一起学习

题目

在不使用现成的中位数函数情况下，统计男生和女生分别成绩的中位数是多少？

name	gender	score
A	男	2
B	男	3
C	女	2
D	女	3
E	女	2

参考

select gender,avg(score)
from 
(
  select gender,score 
  ,row_number() over(partition by gender order by score) as rn 
  ,count(*) over(partition by gender) as n
  from tmp
)as t 
where rn in (floor(n/2)+1,if(mod(n,2) = 0,floor(n/2),floor(n/2)+1))
group by gender
;

select 
gender,
avg(score) score
from ( 
select 
gender,
score, 
count(0) over (partition by gender) cnt_all , 
row_number() over(partition by gender order by score) rn 
from test 
) t 
where t.rn >= round(t.cnt_all/2.0) and t.rn <= round(t.cnt_all/2.0+0.5)
group by gender ;

with tmp_d as (
select 'A' name , '男' gender, '2' score
from dual
union all
select 'B', '男', '3'
from dual
union all
select 'C', '女', '2'
from dual
union all
select 'D', '女', '3'
from dual
union all
select 'E', '女', '2'
from dual
)
SELECT /*+parallel(t,4)*/ tc.gender
,sum(tc.score)/count(*) 中位数 
from (
SELECT /*+parallel(t,4)*/ t.name
,t.gender
,t.score
,case when mod(count(*)over(partition by t.gender order by t.score 
rows between unbounded preceding and UNBOUNDED FOLLOWING) ,2)=0 then 1 else 0 end if_o
,count(*)over(partition by t.gender order by t.score 
rows between unbounded preceding and UNBOUNDED FOLLOWING) counts
,row_number() over(partition by t.gender order by t.score )rnn
from tmp_d t
)tc
-- 则当N为奇数时 N/2，；当N为偶数时:N/2 + (N/2+1) 两个值相加/2 
where (tc.if_o = 1 and (tc.rnn =counts/2 or tc.rnn =counts/2 +1 ))
or (tc.if_o = 0 and ceil(tc.counts/2) = tc.rnn)
group by tc.gender

一分一段

题目

考生分数倒序（分数由高到低）。
一分一档次统计人数。
结果展示一分为一段累加人数（你可以理解大于等于这个分段的人数）。

注意点：

（1）分数相同的则为并列与累计人数。

（2）模拟数据少，需要考虑周全的是：中间不是连续递减分数，没有的需要补齐。

简单罗列字段

字段列	数据类型	描述
id	bigint	序号
stu_no	string	考生号
score	int	成绩

模拟数据

id	stu_no	score
1	100001	690
2	100002	690
3	100003	688
4	100004	687
5	100005	687
6	100006	686
7	100007	686
8	100008	686
9	100009	685
10	100010	689
11	100011	684
12	100012	684

目标的结果

分数	考生人数
690	2
689	3
688	4
687	6
686	9
685	10
684	12
683	12
682	13
681	13
680	14
679	15

参考

insert into henan_gaokao_score 
values
(
(1,'100001', 690),
(2,'100002',690),
(3 ,'100003',688),
(4 ,'100004',687),
(5 ,'100005',687),
(6 ,'100006',686),
(7 ,'100007', 686),
(8,'100008',686),
(9 ,'100009',685),
(10 ,'100010',689),
(11 ,'100011',684),
(12 ,'100012',684),
(13 ,'100013',682),
(14 ,'100014',679)
);

初始化维表

这里需要初始化一张维表，模拟记录 0-750分的 751条数据，作为分数段。(考虑点:如果不借助维表,单表操作你怎么做?)

数据生成方式很多，我罗列以下几个:

insert into score_batch(id,score)
values
(
(1,690),
(2,689),
(3,688),
(4,687),
(5,686),
(6,685),
(7,684),
(8,683),
(9,682),
(10,681),
(11,680),
(12,679),
(13,678)
);

SQL

select 
 t.score 分数,
 sum(t.cnt) over(order by t.score desc rows between UNBOUNDED PRECEDING and current row) 位次
from 
 (
    select
        sb.score score,
        NVL(sc.cnt,0) cnt
    from score_batch sb
    left join 
        (
         select 
            score,
            count(1) cnt,
            min(score) min_score,
            max(score) max_score
         from  henan_gaokao_score 
            group by score
        ) sc on sc.score = sb.score 
                and sb.score>=sc.min_score 
                and sb.score<=sc.max_score
) t;

连续升级多少次

题目

第一次消费 50元，排序为1，第二次消费70元，那么标记为消费升级，排序为2

第三次消费60元，那么低于第二次，则标记为消费降级，重新开始计算排序为1

模拟数据

CREATE TABLE consume 
(
id bigint ,   
name STRING , 
stat_date string,
amount bigint
)....;

insert into table consume values
(1,'dong','2022-04-03',50),
(2,'dong','2022-05-10',70),
(3,'dong','2022-05-22',60),
(4,'dong','2022-05-31',80),
(5,'dong','2022-06-17',75),
(6,'wang','2022-04-23',70),
(7,'wang','2022-05-04',60),
(8,'wang','2022-05-17',95),
(9,'wang','2022-05-31',60),
(10,'wang','2022-06-17',55)
(11,'dong','2022-05-20',80);

参考

select 
    *,
    ROW_NUMBER() over (partition by name, row2 order by `date`) as row3
from (
    select 
        *,
        sum(lags) over(partition by name order by `date`)  as row2 
    from (
        select *,
        if((amount - lag(amount) over(partition by name order by `date`))<0,1,0) as lags
        from consume
        ) a
    ) b
order by b.`date`;

流量表点击

select
  userid,
  time ,
  url,
  event,
  roomid,
  first_value(roomid) over(partition by userid,flag,ct order by time) result1 
from (
     select 
       userid,
       time,
       url,
       event,
       roomid,
       flag,
       count(roomid) over(partition by userid,flag order by time) ct 
     from (
       SELECT 
						userid ,
						time,url,
						event ,roomid,result,sum(flag) over(partition by userid order by time)flag
       from 
       (
        select 
          userid ,time,url,event ,roomid,result,SUBSTR(time,15,2)  hou ,
          SUBSTR(time,15,2) - lag(SUBSTR(time,15,2)) over(PARTITION by userid,left(time,12)) ,
          if(SUBSTR(time,15,2) - lag(SUBSTR(time,15,2)) over(PARTITION by userid,left(time,12) order by left(time,2)  ) >=5 ,1,0) flag
        from event 
       ) a 
     )b
)c

退货数量

with 
-- 模拟发货单 
t1 as (

select '1' as order_no,'A' as order_em_name,1 as order_num union all
select '1' as order_no,'B' as order_em_name,4 as order_num union all
select '2' as order_no,'A' as order_em_name,2 as order_num union all
select '2' as order_no,'B' as order_em_name,1 as order_num union all
select '2' as order_no,'C' as order_em_name,3 as order_num

),
-- 模拟退单 [ 退单号,退单数量 ]
t2 as (

select '1' as return_no , 2 as return_num union all
select '2' as return_no , 3 as return_num

),
-- 关联退单表,累减退单数量.
t3 as (

select 
t1.order_no,
t1.order_em_name,
t1.order_num,
t2.return_num as all_return_num,
( sum(t1.order_num) over(partition by order_no order by order_em_name) - t2.return_num ) as return_num
from t1 left join t2
on t1.order_no = t2.return_no

),
-- 三种情况 
t4 as (
select
order_no,order_em_name,order_num,all_return_num,
case 
when return_num<=0 then order_num
when return_num>=0 and return_num<order_num then order_num-return_num
else 0 end as return_num
from t3
)

-- 最终结果
select * from t4;

总成绩接近某个数

求session

select
uid,
ts,
(sum(ts1) over(partiontion by uid order by ts))+1 sessionId
from 
(
select 
uid,
ts,
if((ts - lag(ts，1，0) over(partiontion by uid order by ts))>=5,1,0) ts1
from event
) aa



select uid,ts, sum(case when lag >=5 then 1 else 0 end ) over (partition by uid order by ts rows between unbounded preceding and current row ) + 1 as session_id
from
(select uid, ts, ts - lag(ts) over (partition by uid order by ts) as lag
from app_login_log)tmp;

求互为朋友的数量

人员表user：(user_id bigint,name string)

朋友关系表relation：(user_id,friend_id)

输入示例：(1,2),(2,1),(1,3),(3,4)

输出示例1：1组用户互为朋友

输出示例2：1,2,张三,李四

select 
count( distinct left. userid ,left. friendid )/2 as num 
from relation as left 
inner join relation as right 
on left.frend_id = right.user_id
and left.user_id = right.frend_id