SQL Server——五个超级经典

一、每门课程问题

用一条 SQL 语句查询 学生表每门课都大于 80 分的学生姓名。

解决办法一: having

思路:如果最小的课程都大于80分,那他所有的课程分数,肯定都大于80分!

代码实现

SELECT name
FROM xuesheng
GROUP BY name
HAVING MIN(score)> 80

解决办法二:not in

可以用反向思维,先查询出表里面有小于 80 分的 name,然后用 not in 去除掉

代码实现

SELECT DISTINCT name
FROM xuesheng
WHERE name NOT IN
    (SELECT DISTINCT name
    FROM xuesheng
    WHERE score <=80);

二、topN 问题

案例:查询各科成绩前两名的记录

代码实现

row_number() over( partition by 课程 order by 成绩 desc) as rank

where rank <= 2  — 前两名

三、连续问题(7 天连续登陆)

实现思路:

  1. 因为每天用户登录次数可能不止一次,所以需要先将用户每天的登录日期去重。
  2. 再用row_number() over(partition by _ order by _)函数将用户id分组,按照登陆时间进行排序。
  3. 计算登录日期减去第二步骤得到的结果值,用户连续登陆情况下,每次相减的结果都相同。
  4. 按照id和日期分组并求和,筛选大于等于7的即为连续7天登陆的用户。

代码实现

SELECT user_id, MAX(count_val) AS max_count  — 查出了最大连续登陆,where>=7,即7天连续
FROM (
     — group by相同日期
  SELECT user_id, symbol_date, COUNT(*) AS count_val
  FROM (
       — 日期减rank,连续登陆的话,会得到相同日期
    SELECT user_id, log_date, date_sub(log_date, CAST(rn AS INT)) AS symbol_date
    FROM (
         —  打上rank标识
      SELECT user_id, log_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY log_date) AS rn
      FROM user_logging_format
    ) c
  ) d
  GROUP BY user_id, symbol_date
) e
GROUP BY user_id;

四、行转列问题

行转列可谓是经典中的经典了,必须掌握了!

6行变2行,2列变3列

代码实现

SELECT SID,
MAX(case CID when ’01’ then score else 0 end) ’01’,
MAX(case CID when ’02’ then score else 0 end)’02’,
MAX(case CID when ’03’ then score else 0 end)’03’
FROM SC
GROUP BY SID

五、留存问题

留存率是衡量用户质量的最重要指标之一,因此计算用户留存率是用户数据分析中必须掌握的技能之一。同样也成为了面试经典sql之一。

留存率指标中,通常需要关注次日留存、3日留存、7日留存和月留存。对新增用户而言,需要关注更细颗粒度的数据,也就是7日内每天的留存率。

代码实现

select
dd
, count( if(id=lead_id and datediff(dd,lead_dd)=1 ,id, null ) ) as ‘1 日留存’
, count( if(id=lead_id7 and datediff(dd,lead_dd7)=7 ,id, null ) ) as ‘7 日留存’
from
(
select
id, dd
, lead(dd,1) over(partition by id order by dd asc ) as lead_dd
, lead(id,1) over(partition by id order by dd asc ) as lead_id
, lead(dd,7) over(partition by id order by dd asc ) as lead_dd7
, lead(id,7) over(partition by id order by dd asc ) as lead_id7
from  
 (
select ‘slm’ as id, ‘2018-12-26’ as dd
union all select ‘slm’ as id, ‘2018-12-27’ as dd
union all select ‘slm’ as id, ‘2018-12-28’ as dd
union all select ‘hh ‘ as id, ‘2018-12-26’ as dd
union all select ‘hh ‘ as id, ‘2018-12-28’ as dd ) aa
) bb

 

作者:不吃西红柿丶

链接:https://blog.csdn.net/weixin_39032019?type=blog

给TA打赏
共{{data.count}}人
人已打赏
SQL Server

SQL Server高级查询与T-SQL编程 第6章数据库安全管理

2022-8-24 18:37:25

SQL Server

T-SQL编程子查询面试题

2022-9-13 21:03:48

0 条回复 A文章作者 M管理员
    暂无讨论,说说你的看法吧
个人中心
今日签到
有新私信 私信列表
搜索