Leetcode SQL会员题【吐血总结~~】第五天

目录：

1294. 不同国家的天气类型
1303. 求团队人数
1308. 不同性别每日分数总计
1321. 餐馆营业额变化增长
1322. 广告效果
1327. 列出指定时间段内所有的下单产品
1336. 每次访问的交易次数
1341. 电影评分
1350. 院系无效的学生
1355. 活动参与者
1364. 顾客的可信联系人数量
1369. 获取最近第二次的活动
1378. 使用唯一标识码替换员工ID
1384. 按年度列出销售总额
1398. 购买了产品 A 和产品 B却没有购买产品C的顾客

1294. 不同国家的天气类型

• 源代码：

  select 
      country_name, 
      case when avg(weather_state) <= 15 then 'Cold' when avg(weather_state) >= 25 then 'Hot' else 'Warm' end as weather_type
  from weather w
  join countries c
  on w.country_id = c.country_id
  where day like '2019-11%'
  group by w.country_id
  

• 思路：就是分组聚合，比较简单

1303. 求团队人数

• 源代码：

  select employee_id, count(employee_id) over(partition by team_id) as team_size
  from employee
  

• 思路：对count函数进行开窗

1308. 不同性别每日分数总计

• 源代码：

  select gender, day, sum(score_points) over(partition by gender order by day) as total
  from scores
  

• 思路：对sum函数进行开窗，没什么特别的逻辑

1321. 餐馆营业额变化增长

• 源代码：

  select visited_on, round(amount, 2) amount, round(average_amount, 2) average_amount
  from (
      select 
          visited_on, 
          sum(amount) over(order by visited_on rows between 6 preceding and current row) as amount,
          avg(amount) over(order by visited_on rows between 6 preceding and current row) as average_amount
      from (
          select visited_on, sum(amount) amount
          from customer
          group by visited_on
      ) t
  ) t
  where visited_on >= (
      select date_add(min(visited_on), interval 6 day)
      from customer
  )
  

• 思路：核心点是在限制窗口的范围：rows between 6 preceding and current row

1322. 广告效果

• 源代码：

  select 
     ad_id,
     ifnull(round(sum(action = 'Clicked') / sum(action != 'Ignored') * 100, 2), 0) ctr
  from ads
  group by ad_id
  order by ctr desc, ad_id
  

• 思路：注意这个题目要求的CTR可以转换为 【是clicked 除以 不是ignored 】

1327. 列出指定时间段内所有的下单产品

• 源代码：

  select product_name, unit
  from (
      select product_id, sum(unit) unit
      from orders
      where substr(order_date, 1, 7) = '2020-02'
      group by product_id, substr(order_date, 1, 7)
  ) t
  join products
  on products.product_id = t.product_id
  where unit >= 100
  

• 思路：简单

1336. 每次访问的交易次数

• 源代码：

  with random_int as (
      select 0 as rk
      union
      select row_number() over(order by transaction_date) rk 
      from transactions
  ),
  tmp2 as (
      select cnt transactions_count, count(*) visits_count
      from (
          select visits.user_id, visit_date, ifnull(visits_count, 0) cnt
          from visits
          left join (
              select user_id, transaction_date, count(*) visits_count
              from transactions 
              group by user_id, transaction_date
          ) t
          on visits.user_id = t.user_id and visits.visit_date = t.transaction_date
      ) t
      group by cnt
  )
  select rk as transactions_count, ifnull(visits_count, 0) as visits_count
  from random_int
  left join tmp2
  on random_int.rk = tmp2.transactions_count
  where rk <= (
      select max(transactions_count)
      from tmp2
  )
  

• 思路：这个题目主要的卡点：生成1-n的整数序列，并且需要对n进行限制，最终采用的是 row_number的方式

1341. 电影评分

• 源代码：

  with tmp as (
      select user_id, count(movie_id) cnt
      from movierating
      group by user_id
  ), tmp1 as (
      select movie_id, avg(rating) avg_rating
      from movierating
      where substr(created_at, 1, 7) = '2020-02'
      group by movie_id
  )
  (
      select name as results
      from (
          select user_id
          from tmp
          where cnt = (
              select max(cnt)
              from tmp
          )
      ) t
      join users
      on t.user_id = users.user_id
      order by name
      limit 1
  )
  union 
  (
      select title as results
      from (
          select movie_id
          from tmp1
          where avg_rating = (
              select max(avg_rating)
              from tmp1
          )
      ) t
      join movies
      on t.movie_id = movies.movie_id
      order by title
      limit 1
  )
  

• 思路：两套思路其实是一样的，比较简单

1350. 院系无效的学生

• 源代码：

  select s.id, s.name
  from students s
  left join departments d
  on s.department_id = d.id
  where d.id is null;
  

• 思路：左连接

1355. 活动参与者

• 源代码：

  select activity
  from (
      select activity, count(id) cnt, max(count(id)) over() as max, min(count(id)) over() as min
      from friends
      group by activity
  ) t
  where cnt != max and cnt != min
  

• 思路：这种开窗技巧非常实用，我们窗口不进行任何限制也就是整张表，这样我们就可以在分组的时候同时求出最大值和最小值

1364. 顾客的可信联系人数量

• 源代码：

  select invoice_id, customer_name, price, ifnull(cnt1, 0) contacts_cnt, ifnull(cnt2, 0) trusted_contacts_cnt
  from invoices inv
  left join customers cus
  on inv.user_id = cus.customer_id
  left join (
      select user_id, count(user_id) cnt1
      from contacts
      group by user_id
  ) t1
  on inv.user_id = t1.user_id
  left join (
      select user_id, count(user_id) cnt2
      from contacts
      where contact_name in (
          select customer_name 
          from customers
      )
      group by user_id
  ) t2
  on inv.user_id = t2.user_id
  order by invoice_id
  

• 思路：弄清楚主表是哪一张，然后一张一张的left join上去

1369. 获取最近第二次的活动

• 源代码：

  select username, activity, startdate, enddate
  from (
      select 
          username, 
          activity, 
          startdate, 
          enddate,
          row_number() over(partition by username order by startdate desc) rk, 
          count(username) over(partition by username) total
      from useractivity
  ) t
  where rk = 2 or total = 1
  

• 思路：唯一就是假如只有一次活动，如何限制条件将这个活动取出

1378. 使用唯一标识码替换员工ID

• 源代码：

  select unique_id, name 
  from employees
  left join employeeuni
  on employees.id = employeeuni.id
  

• 思路：左连接

1384. 按年度列出销售总额 **

• 源代码：

  select 
      s.product_id, 
      p.product_name, 
      y.year report_year,
      s.average_daily_sales * 
          (
              -- 如果结束日期大于了报告年份，那么就说明这一条记录跨越了报告年份，那么应该报告年份的结束日期取最后一天；否则就说明报告年份的结束日期就应该是表中的结束日期
              if(year(s.period_end) > y.year, y.days_of_year, dayofyear(s.period_end)) - 
              -- 如果开始日期小于了报告年份，那么就报告年份的开始日期取第一天；否则报告年份的开始日期就应该是表中的开始日期
              if(year(s.period_start) < y.year, 1, dayofyear(s.period_start)) + 1
          ) as total_amount
  from sales s
  join (
      select '2018' year, 365 days_of_year
      union all 
      select '2019' year, 365 days_of_year
      union all 
      select '2020' year, 366 days_of_year
  ) y 
  on year(s.period_start) <= y.year and year(s.period_end) >= y.year
  join product p -- 为了返回product_name
  on s.product_id = p.product_id
  order by s.product_id, y.year
  

• 思路：重点就在于中间的计算公式了
• 函数补充：
  • year(日期)：获取当前日期对应的年份
  • dayofyear(日期)：获取当前日期对应在当年的第几天

1398. 购买了产品 A 和产品 B却没有购买产品C的顾客

• 源代码：

  select 
      * 
  from customers
  where customer_id in (
      select customer_id from orders
      where product_name = 'A' 
  )
  and customer_id in (
      select customer_id from orders
      where product_name = 'B' 
  )
  and customer_id not in (
      select customer_id from orders
      where product_name = 'C' 
  )
  ;
  

• 思路：利用三个子查询解决问题