SQL83 牛客的课程订单分析(七)

1. 题目

• 点击：SQL83 牛客的课程订单分析(七)
• 题目描述

# 写出一个sql语句查询在2025-10-15以后，
# 同一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程的来源信息，
# 第一列是显示的是客户端名字，如果是拼团订单则显示GroupBuy，
# 第二列显示这个客户端(或者是拼团订单)有多少订单，
# 最后结果按照第一列(source)升序排序

• 两张表
  
• 所需查询结果
  

2. 解题

思路：
1、所求字段（列），一个是全新字段（source），一个是聚合统计项（cnt），肯定需要用子查询+ JOIN 构造一个新表；
然后用SELECT语句分别将条件语句（IF或者CASE WHEN）和聚合函数COUNT()（列）筛选出来；
还需注意对象是每种客户端，所以需要使用GROUP BY 进行分组；

注：构造新表时有两种做法，①将 JOIN 放在子查询中进行连接；
②将子查询放在外表当中进行连接

2、然后再看（行）过滤条件，只有这个【同一个用户下单2个以及2个以上】条件需要注意，因为这里又需要分组，但是对象与之前不同，所以可以直接使用窗口函数构造一个新的列——每个分区满足条件的数量

只要注意的是，必须使用LEFT JOIN，使用INNER JOIN 会报错！
其他部分就比较普通了，不再赘述。

2.1 方法一：子查询

# 子查询
SELECT s.source, COUNT(s.cnt_order)
FROM (
        SELECT IF(t.is_group_buy = 'Yes', 'GroupBuy', t.name) AS source,
               COUNT(product_name) OVER(PARTITION BY client_id) AS cnt_order
        #        COUNT(product_name)
        FROM (
              SELECT o.*, c.name,
                     COUNT(product_name) OVER(PARTITION BY user_id) AS cnt_user
              FROM order_info o
              LEFT JOIN client c
                   ON o.client_id = c.id
              WHERE date > '2025-10-15'
                    AND status = 'completed'
                    AND product_name IN ('C++', 'Java', 'Python')
              ) AS t
        WHERE t.cnt_user >= 2
        # GROUP BY client_id
        ORDER BY source ASC
        ) s
GROUP BY s.source

使用子查询很容易出错，开始想直接用group by 报错，所以又在外层加了一个 select

2.2 方法二：窗口函数

这里也有两种做法：

1、JOIN 放在子查询中

# 窗口函数方法1：JOIN在子查询中
 SELECT t.source,
        COUNT(*) AS cnt
 FROM (SELECT o.*,   ## 如果 JOIN 连接放在 子查询中，可能会出现重复字段的情况（这里是id）。这时就需要特别指定表格时就需要
              IF(is_group_buy = 'Yes', 'GroupBuy', name) AS source,
              COUNT(*) OVER(PARTITION BY user_id) num
       FROM order_info o 
       LEFT JOIN client c 
            ON o.client_id = c.id
       WHERE date > '2025-10-15'
             AND status = 'completed'
             AND product_name IN ('C++', 'Java', 'Python')
       ) AS t
 WHERE t.num >= 2
 GROUP BY t.source
 ORDER BY t.source ASC

这里外表中的 COUNT(*) 也可以换做使用子查询中的窗口函数：

SELECT t.source, 
       t.cnt cnt
FROM (SELECT o.*, c.name,
             IF(o.is_group_buy = 'Yes', 'GroupBuy', c.name) source,
             COUNT(*) OVER(PARTITION BY o.user_id) num,
             COUNT(*) OVER(PARTITION BY c.name) cnt
      FROM order_info o
      LEFT JOIN client c
      ON o.client_id = c.id
      WHERE o.date > '2025-10-15'
            AND o.status = 'completed'
            AND o.product_name IN ('C++', 'Java', 'Python')
     ) t 
WHERE 
      t.num >= 2
GROUP BY t.source
ORDER BY t.source ASC

当然 也可以将 IF条件语句放在外表中，如下面。

2、JOIN 放在外表当中

 #  窗口函数方法2：JOIN 放在外表当中
 SELECT IF(is_group_buy = 'Yes', 'GroupBuy', name) AS source,
        COUNT(*) AS cnt
 FROM (SELECT *,   # 这里就不再需要特别自定表格
              COUNT(*) OVER(PARTITION BY user_id) num
       FROM order_info o 
       WHERE date > '2025-10-15'
             AND status = 'completed'
             AND product_name IN ('C++', 'Java', 'Python')
       ) AS t 
 LEFT JOIN client c 
      ON t.client_id = c.id
 WHERE t.num >= 2
 GROUP BY source
 ORDER BY source ASC