0.知识补充

• begin end

DECLARE @a varchar(50)
SET @a = `sql开发`
begin
    select @a
end

• if else

DECLARE @a varchar(50)
SET @a = `sql开发`
if len(@a) >5
    select @a
else 
    select `less than 5`

1.查询既学过01也学过03的课程的学生ID

--Method 1 笛卡尔积
select * from F0215 a, F0215 b where a.StuID = b.StuID and a.CID =1 and b.CID = 3

--Method 2 子查询
select * from (SELECT * FROM F0215 WHERE CID =1) A1
INNER JOIN 
( SELECT * FROM F0215 WHERE CID =3)A2
ON A1.StuID = A2.StuID

--Method3 分组
SELECT StuID FROM F0215 
WHERE CID IN (1,3)
GROUP BY StuID
HAVING COUNT(StuID)=2

--Method 4 交集
SELECT SC.STUID FROM F0215 SC
WHERE  SC.CID=1
INTERSECT
SELECT SC.STUID FROM F0215 SC
WHERE  SC.CID=3

2.取price最大的一行的所有数据

--方法1
SELECT a.* FROM F0217 a
JOIN
(
SELECT Uname,Max(Price) Price FROM F0217
GROUP BY Uname 
) b ON a.Uname=b.Uname AND a.Price=b.Price

--方法2
with new_price as(
SELECT *,ROW_NUMBER() OVER(partition by Uname order by Price desc) num FROM F0217  
) 
select * from new_price where num =1

--方法3
SELECT * 
FROM   
 ( SELECT * , ROW_NUMBER() OVER ( PARTITION BY Uname ORDER BY Price DESC ) AS num
   FROM F0217
 )  t
WHERE num = 1

注意:方法2和方法3本质上是一样的

统计胜利队伍和失败的队伍

image.png

select 
    Team_1, COUNT(1) as matches_played, SUM(score) as matches_won, COUNT(1) - SUM(score) as matches_not_won
from (
    select 
        Team_1, case when Winner = Team_1 then 1 else 0 end as score
    from icc_world_cup
    union all
    select 
        Team_2, case when Winner = Team_2 then 1 else 0 end as score
    from icc_world_cup) as all_team
    group by Team_1

找出新顾客和重复的顾客

• 统计出每日的新顾客人数和重复逛店的人数

  
  
  image.png

• 原表

  
  
  image.png
• 分析：
  （1）新顾客的天数就是根据客户最早购物的天数，所以需要找出min(order_date) 创建新的first_visited表
  (2) 在用原表join新表，在根据第一次购买的天数相等则是新用户，否则为旧用户
  （3）CTEs的final表的使用
• sql

with first_visited as(
select customer_id,MIN(order_date) as first_visited_day from customer_orders
group by customer_id),
visited_flag as
(
select b.*,
case when a.first_visited_day = b.order_date then 1 else 0 end as new_customer,
case when a.first_visited_day != b.order_date then 1 else 0 end as reapet_customer
from first_visited a
inner join customer_orders b
on a.customer_id = b.customer_id
)
select order_date,SUM(new_customer) as new_customer,SUM(reapet_customer) AS reapet_customer
from visited_flag
GROUP BY order_date

游客的访问记录和访问的设备

image.png

• 前置必会：将原表转为

  
  
  image.png

with a as(
select distinct name, [resources] from [entries]
)
select name, string_agg(resources,',') as resources_used from a group by name

• 思路：
  1. 为了找到最多访问的楼层，需要引入rank() 排序将最多楼层的访问次数排到第一名，这样才可以选择
  2. 对于前置resources的获取，可以使用两个cte来获得

with dis_resources as(
  select distinct name, [resources] from [entries]),
  agg_resources as (select name, string_agg(resources,',') as resources_used from dis_resources group by name),
 floor_vists as
    (
    SELECT name,floor , COUNT(email) as floor_visits,
    rank() over(partition by name order by COUNT(email) desc) as r
    FROM [entries]
    GROUP BY name,floor
 )
select 
    a.name,
    SUM(floor_visits) as total_visits, 
    most_v_floor = (select floor from floor_vists b where r =1 and a.name =b.name),ar.resources_used 
from floor_vists a
inner join agg_resources ar on a.name =ar.name
group by a.name,ar.name,ar.resources_used

找出哪些百分之20的商品提供了百分之80的销售额

 WITH pro_sales as(
 SELECT [Product_ID], SUM(Sales) AS product_sales
 FROM [SQL_EVERY_DAY].[dbo].[Orders]
 group by [Product_ID]), total_running_sales as(
select [Product_ID],product_sales,sum(product_sales) over(order by product_sales desc) as running_sales from pro_sales), cal_sales as(
select [Product_ID],product_sales,running_sales from total_running_sales where running_sales <= (select sum(Sales)*0.8 from [Orders]) )
select COUNT(1)*1.0/(select COUNT(distinct Product_ID) from [Orders]) as product_20 from cal_sales

cte递归

1. 一个简单的例子理解递归

WITH cte_nums as(
    select 1 as num  --锚点
    union all 
    select num +1  --循环的query
    from cte_nums
    where num<6  --filter to stop recursion
)
select num from cte_nums

查找注册30天内升级成了P会员的人

• users表：所有的用户

  
  
  image.png

• events表：用户的操作，例如加入会员，升级为P用户

  
  
  image.png

• 思路：
  1.先找到Music的用户

  
  
  image.png
  
  

  2.找到所有音乐会员但是不是P的用户

  
  
  image.png
  
  3.统计，并且除

select  
    COUNT(distinct u.user_id) total_user,
    SUM(CASE WHEN DATEDIFF(day,u.join_date,e.access_date)<30 THEN 1 ELSE 0 END) P_user,
    1.0*SUM(CASE WHEN DATEDIFF(day,u.join_date,e.access_date)<30 THEN 1 ELSE 0 END)/COUNT(distinct u.user_id) P_rate
from users u 
left join events e on u.user_id = e.user_id and e.type = 'P'
where 
u.user_id in (select user_id from events where type = 'Music')

image.png

分割字符串，统计出线次数(CROSS APPLY)

image.png

• CROSS APPLY和简单的笛卡尔积的区别在于，它可以与任意的function或者数组进行笛卡尔积，另外一种只能和表

#笛卡尔积
select * from airbnb_searches a, airbnb_searches b
#cross apply 
select * from airbnb_searches
cross apply string_split(filter_room_types,',')

image.png

select value as room_type,COUNT(1) as sum_types from airbnb_searches
cross apply string_split(filter_room_types,',')
group by value

创建动态插入语句

• 使用concat sql的时候，字符串的一个单引号，用四个单引号表示

select CONCAT('insert into emp values(',emp_id,',','''',emp_name,'''',',',salary,',','''',dob,'''',')') from emp

image.png